Android Question Why use parameterized commands

[MrKim]

Regarding SQL queries.

In this post:

https://www.b4x.com/android/forum/threads/parallel-key-for.95247/

Erel states:

"You should never build such commands. Use parameterized commands instead:"

Wondering why?

Thanks

 

[KMatle]

What exactly do you mean?

 

[MrKim]

What exactly do you mean?

Part of my post disappeared. I have fixed it.

 

[MarkusR]

i think for security reason, that you are not able to input a sql query into a textbox which you inserted into your query.
https://en.wikipedia.org/wiki/SQL_injection

 

[KMatle]

Yep. If you create a full SQL query dynamically you build a string including the values. A value can contain anything. If a value contains valid SQL instructions, it will be added and then executed. This is called SQL injection.

It is essential to use prepared statements/parameterized commands. Here the SQL query is fixed, you use placeholders for the values which are then just values , no matter what they contain.

 

[Erel]

Worth watching this tutorial:


1. Safe from SQL injection.
2. Safe from escaping issues.
3. Easier to write and read.

 

[MrKim]

Worth watching this tutorial:


1. Safe from SQL injection.
2. Safe from escaping issues.
3. Easier to write and read.

I agree with everything but 3. Harder to write, especially harder to read.

 

[Erel]

I agree with everything but 3. Harder to write, especially harder to read.

Post an example...

 

[MrKim]

I suppose one of the reasons is I have been doing code queries in VBA for a Looong time and am used to it. The example below, instead of looking at ? I am looking at fields with similar names by the equal signs so I know which one goes with which.. But an old dog can learn new tricks. I will give the parameters a try next time.

sql1.AddNonQueryToBatch("UPDATE JobRelease SET JobRelease.Jr_StartQty = " & Jr_StartQty & " WHERE (((JobRelease.Jr_JobNum) = '" & ClK.JobNum & "') And ((JobRelease.Jr_ReleaseNum) = " & ClK.Rel & "));", Null)

Is this how it would look with parameters? Or do I have to put the parameters in an array?

sql1.AddNonQueryToBatch("UPDATE JobRelease SET JobRelease.Jr_StartQty = ? WHERE (((JobRelease.Jr_JobNum) = ?) And ((JobRelease.Jr_ReleaseNum) = ?));", Jr_StartQty, ClK.JobNum, ClK.Rel)

 

[Erel]

Please use [code]code here...[/code] tags when posting code.

It will look like this:

sql1.AddNonQueryToBatch("UPDATE JobRelease SET JobRelease.Jr_StartQty = ? WHERE (((JobRelease.Jr_JobNum) = ?) And ((JobRelease.Jr_ReleaseNum) = ?));",Array(Jr_StartQty, ClK.JobNum, ClK.Rel))

- No need to add the apostrophes characters.
- No need to worry about any of the values including an apostrophe (which will break your code).

 

[MarkusR]

at ms sql it will look like,if there is only one table used u do not need the tablename.
if you use "and" u not need brackets around

sql1.AddNonQueryToBatch("UPDATE JobRelease SET Jr_StartQty = ? WHERE Jr_JobNum = ? And Jr_ReleaseNum = ?;",Array(Jr_StartQty, ClK.JobNum, ClK.Rel))