Can you provide an example where my function would fail to properly sanitize SQL?
Yes, I agree that prepared statements would be the preferred method, only if they worked correctly. I’ve run into enough problems with them in Xojo that I simple don’t use them anymore. You should know Kem, as you’ve submitted Feedback reports about them yourself.
No, offhand I can’t, but that doesn’t mean it’s the right advice. The entire purpose of a prepared statement is to sanitize inputs and presumably the database developer will do a better job of it than we as programmers can. Yes, it’s a pain to convert code, but it should be done anyway. As you say, it’s the preferred method, and, frankly, anything else is a shortcut.
It’s true that, in the past, I’ve reported issues with prepared statements, but they’ve had workarounds and I continue to use them religiously.
Just because you can bang a nail into a wall with a wrench, it’s better to use the readily-available hammer even if it’s missing part of the rubber on its handle.
I would be VERY interested in where you have found these to NOT work properly?
I used to “sanitize” things… but then found some databases like lots of DOUBLE quotes, some HATE any DOUBLE quotes, while others got upset with SINGLE QUOTES, and yet others required using \ to escape things… Prepared statements made that all go away…
FYI. SQLite handles single/double quotes the same as long as matched up correctly… but ORACLE (for example) assigns a TOTALLY different meaning to Single vs Double…
Just search Feedback - easy to find.
[quote=334330:@Dave S]I used to “sanitize” things… but then found some databases like lots of DOUBLE quotes, some HATE any DOUBLE quotes, while others got upset with SINGLE QUOTES, and yet others required using \ to escape things… Prepared statements made that all go away…
FYI. SQLite handles single/double quotes the same as long as matched up correctly… but ORACLE (for example) assigns a TOTALLY different meaning to Single vs Double…[/quote]
Assuming you aren’t using multiple database backends in one application, it’s easy to adjust my ONE function to properly sanitize the SQL for the particular database.
Try something like this in Postgres:
db.SQLExecute "BEGIN;"
db.SQLExecute "CREATE TABLE temp_double (d numeric(12,2));"
dim sql as string = "INSERT INTO temp_double (d) values ($1);"
dim ps as PreparedSQLStatement = db.Prepare( sql )
ps.SQLExecute 99999.99
AddToResult db.ErrorMessage
db.RollbackYou get this error message:
ERROR: invalid input syntax for type numeric: "99,999.99"The workaround is to use str(value) as the value.
I can find 2 case Kem reported - both about postgresql prepared statements
<https://xojo.com/issue/41633>
<https://xojo.com/issue/35836>
No others
I cant find that you’ve reported any Jay
Thats hardly such a plethora of issues that they should be avoided from what I can see
Being less invasive doesn’t make it good advice
Jay,
The App crashes when attempting to retrieve the data from the DB. I’m using “wildcard” sql statements to populate various tables/ Custom Cell combinations i.e
sql=SELECT * FROM blah blah
The data is then read into an Array and displayed in the CustomCells. Somewhere in that process may lie the problem but I doubt it. Works fine in the simulator but not on the device so determining the error seems not to be possible. The device simply quits to the home screen. For instance entering the following into a Text Field “This is an example of (parentheses) and my App’s problem.” produces a crash on data retrieval but the same entry in a Text Area works fine. I have to believe that it’s got to be somehow connected to Styled text versus straight ASCII. I recall that steering the user along the path of righteousness with error trapping was a lot easier in RealBasic.
[quote=334334:@Norman Palardy]I can find 2 case Kem reported - both about postgresql prepared statements
<https://xojo.com/issue/41633>
<https://xojo.com/issue/35836>
No others
I cant find that you’ve reported any Jay
Thats hardly such a plethora of issues that they should be avoided from what I can see[/quote]
I added a comment to 35836 to inform everyone that this bug affects more situations than what Kem was reporting. Namely that you can’t save numeric values with more than 7 significant digits on either Mac or Windows using prepared statements. If you need me to create a separate case just to make that obvious, then fine.
For others, search for “prepared statement” and set the Status column to “Open”.
But we are going off on a tangent. See the OP’s reply where, as I was suspecting, his problem may not have anything to do with sql injection.
I believe you are right Jay. The more I read here, the more I think it has to do with the way Xojo iOs treats Text input. What’s OP stand for?
[quote=334334:@Norman Palardy]I can find 2 case Kem reported - both about postgresql prepared statements
Feedback Case #41633
Feedback Case #35836
No others[/quote]
Here you go…
Prepared statements do not work with Xojo and the MSSQL plugin. Not sure if it is Xojo or the native client. When using prepared statements the DB will not return the correct error from the database and blank fields are often filled with garbage. Try adding a record that violates a unique index - You will simply get an error code 3621 This statement has been terminated. Executing the same code in SQL Server Management studio works perfect and results in a meaningful error code, followed by the 3621 error. Unfortunately the plugin does not recognize this. I spent countless hours trying to come up a with work around. It’s broken, period.
Original Poster. Common lingo on forums.
[quote=334338:@Jay Madren]I added a comment to 35836 to inform everyone that this bug affects more situations than what Kem was reporting. Namely that you can’t save numeric values with more than 7 significant digits on either Mac or Windows using prepared statements. If you need me to create a separate case just to make that obvious, then fine.
For others, search for “prepared statement” and set the Status column to “Open”.
But we are going off on a tangent. See the OP’s reply where, as I was suspecting, his problem may not have anything to do with sql injection.[/quote]
There just aren’t that many
I searched for “prepared” on ALL open reports and the entire list I see is 17 items (and I definitely see items you dont)
9 are feature requests
8 bugs
6 are open bugs about prepared statements
one is about listbox with that search criteria
one an ide bug that has “prepare” in the summary
Str can take a FORMAT string so you can round to as many digits as you want
Thats been true for some time see http://documentation.xojo.com/index.php/Str
Kems bug about Postgresql prepared statement being left on the server has, as he mentioned, a workaround
In fact most of the m do except one about using PostgreSQL in multithreaded
And Waynes bug about MS SQL & long strings
Bug report # ?
I dont see this in feedback anywhere
Just Wayne’s case <https://xojo.com/issue/44563>
I just did some testing using straight SQL as well as Prepared Statements
and for a Column defined as either FLOAT or REAL in SQLite (may or may not apply to other DB)
the value automatically went to SciNotation at 7 digits and above
Note… the MANNER the SQL was crafted did NOT matter
and this same result occurred in an Xojo written App, and a 3rd party app that has no relation to Xojo at all…
So I’m thinking this is an SQLite issue, not something that Xojo could fix … even casting it as Text didn’t change the result
OK, Just got the simulator to crash. Looks like it will have to be a prepared statement fix. The text input was “It’s a (problem)” and here’s where the crash occurred:
[code]Set
// DEFAULTS
ChecklistItemLabel.Text = value
Dim dbFile As FolderItem
dbFile = SpecialFolder.Documents.Child(CustomCell2.MenuSelect)
If dbFile.Exists Then
App.ChecklistDB= New iOSSQLiteDatabase
App.ChecklistDB.DatabaseFile = dbFile
If App.ChecklistDB.Connect Then
//AlertBox.Title="Connected to "+CustomCell2.MenuSelect
//AlertBox.show
End If
Else
//Do Nothing
End If
Dim sql As Text
sql = "Select * From Checklist Where Name ='" + ChecklistItem + "'"
Dim data As iOSSQLiteRecordSet
data = App.ChecklistDB.SQLSelect(sql)
While Not data.EOF
TextFlag=data.Field("TextFlag").IntegerValue
Flag=data.Field("Flag").IntegerValue
Sect=data.Field("Section").TextValue
If Flag=1 Then
ImageView1.Image=EnhancedGreenCheckmark
ChecklistItemLabel.Enabled=False
end
If Flag=0 Then
ImageView1.Image=Nil
end
TextFlag=data.Field("TextFlag").IntegerValue
if TextFlag=1 Then
DisclosureButton.Enabled=True
ImageView2.Image=NextArrow2
end
if TextFlag=0 Then
ImageView2.Image=Nil
end
data.MoveNext
Wend
data.Close
Dim CheckedTotal as Integer[/code]
Pardon the sloppy code.
It appears that an apostrophe or quotation within the text input causes the error.
Here’s the error:
Exception iOSSQLiteException
Select * From Checklist Where Name =’ It’s a (problem)’
near “s”: syntax error
Any suggestions to to fix this about 300 times would be appreciated.
That’s the usual “need to escape quotes in data” problem. You can do the quick and dirty solution suggested by Jay. Or you can use PreparedStatements. Those are your only options.
Thanks Tim. I’m on it.
[quote=334354:@Werner Hamp]Exception iOSSQLiteException
Select * From Checklist Where Name =’ It’s a (problem)’
near “s”: syntax error[/quote]
JUST for clarity this is an Exception not a crash
While they both mean "the app has stopped working - an exception you can catch & actually do something about
In fact run in the debugger with BREAK ON EXCEPTIONS enabled and you application would have stopped right there and you could see whats up
A crash on iOS would return you to the home screen without being able to do ANYTHING about it
Sometimes you forget the obvious.
Thank you Norman and thank you all for your help.