MrKim Well-Known Member Licensed User Longtime User Jul 31, 2018 #1 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 Last edited: Jul 31, 2018
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
MrKim Well-Known Member Licensed User Longtime User Jul 31, 2018 #3 KMatle said: What exactly do you mean? Click to expand... Part of my post disappeared. I have fixed it. Upvote 0
KMatle said: What exactly do you mean? Click to expand... Part of my post disappeared. I have fixed it.
MarkusR Well-Known Member Licensed User Longtime User Jul 31, 2018 #4 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 Upvote 0
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 Expert Licensed User Longtime User Jul 31, 2018 #5 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. Upvote 0
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 B4X founder Staff member Licensed User Longtime User Aug 1, 2018 #6 Worth watching this tutorial: 1. Safe from SQL injection. 2. Safe from escaping issues. 3. Easier to write and read. Upvote 0
Worth watching this tutorial: 1. Safe from SQL injection. 2. Safe from escaping issues. 3. Easier to write and read.
MrKim Well-Known Member Licensed User Longtime User Aug 1, 2018 #7 Erel said: Worth watching this tutorial: 1. Safe from SQL injection. 2. Safe from escaping issues. 3. Easier to write and read. Click to expand... I agree with everything but 3. Harder to write, especially harder to read. Upvote 0
Erel said: Worth watching this tutorial: 1. Safe from SQL injection. 2. Safe from escaping issues. 3. Easier to write and read. Click to expand... I agree with everything but 3. Harder to write, especially harder to read.
Erel B4X founder Staff member Licensed User Longtime User Aug 2, 2018 #8 MrKim said: I agree with everything but 3. Harder to write, especially harder to read. Click to expand... Post an example... Upvote 0
MrKim said: I agree with everything but 3. Harder to write, especially harder to read. Click to expand... Post an example...
MrKim Well-Known Member Licensed User Longtime User Aug 2, 2018 #9 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) Upvote 0
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 B4X founder Staff member Licensed User Longtime User Aug 2, 2018 #10 Please use [code]code here...[/code] tags when posting code. It will look like this: Code: Copy 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). Upvote 0
Please use [code]code here...[/code] tags when posting code. It will look like this: Code: Copy 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 Well-Known Member Licensed User Longtime User Aug 2, 2018 #11 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 Code: Copy sql1.AddNonQueryToBatch("UPDATE JobRelease SET Jr_StartQty = ? WHERE Jr_JobNum = ? And Jr_ReleaseNum = ?;",Array(Jr_StartQty, ClK.JobNum, ClK.Rel)) Last edited: Aug 2, 2018 Upvote 0
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 Code: Copy sql1.AddNonQueryToBatch("UPDATE JobRelease SET Jr_StartQty = ? WHERE Jr_JobNum = ? And Jr_ReleaseNum = ?;",Array(Jr_StartQty, ClK.JobNum, ClK.Rel))
