I may not fully understand what happens under the hood when using MySQLPreparedStatement, but I thought it might catch and replace offending characters like apostrophes. Should it be? Or should I just make sure I’m doing a .ReplaceAll("'", "'")
Are you working on a Desktop or Web project?
Can you define ‘fails on apostrophe’? you get an error, crash, some different than an apostrophe?
Are you working with API1.0? (I think API2.0 offers prepared statements without the need of much extra code)
Can you share a sample project?
Hi Alberto, thanks for the response!
I’m using Web API 2.0, and what I’m encountering is a SQL INSERT error where it “sees” an unescaped apostrophe.
I’m receiving a JSON payload via an inbound WebHook, and sometimes the provider escapes the apostrophe in a value, e.g., “These book's are great” is sometimes “These book’s are great”.
I’m pushing these JSON payloads into MySQL as a String (datatype “MEDIUMTEXT”) and not as an actual JSON data type - which works great and is more portable in some ways.
In this example code ‘request’ is a WebRequest, thanks for helping!
Var sql As String
sql = "INSERT INTO `dbname`.`dbtable`"
sql = sql + "(`EVENT_TYPE`, `EVENT_BODY`, `EVENT_DATETIME`)"
sql = sql + "VALUES('"+event_type+"', '"+request.Body.ReplaceAll("'", "'")+"','"+LOCAL_TIME_PST.SQLDateTime+"');" 'IMPORTANT: MUST eliminate any apostrophes!
Var xFNdbase As MySQLCommunityServer = new_FNdbase_connection()
Try
xFNdbase.Connect()
'xFNdbase.ExecuteSQL(sql)
Var ps As MySQLPreparedStatement
ps = xFNdbase.Prepare(sql)
ps.ExecuteSQL()
xFNdbase.CommitTransaction
xFNdbase.Close
Catch error As DatabaseException
xFNdbase.Close
// some error handling here
End Try
You’ve rather missed the point of an SQL prepared statement. Prepared statements are specifically designed to avoid the problem of manipulating text to make it compatible with SQL, as you are doing; instead, you leave placeholders in your query and the Prepare() method call replaces them with properly escaped data:
select * from `someTable` where Name like $1
When you prepare the statement, you’ll give it some value to replace $1 and the framework will guarantee it is properly escaped, etc. for use on the server. Take a look at the PreparedStatement documentation for full details.
Hi @William_Reynolds, here’s my take on your code
Var sql As String = "INSERT INTO `dbname`.`dbtable` " + _
"(`EVENT_TYPE`, `EVENT_BODY`, `EVENT_DATETIME`)" + _
"VALUES(?, ?, ?);"
Var xFNdbase As MySQLCommunityServer = new_FNdbase_connection()
xFNdbase.Connect()
Try
xFNdbase.ExecuteSQL(sql, event_type, request.Body, DateTime.Now(New TimeZone("GMT"))
Catch error As DatabaseException
// some error handling here
End Try
xFNdbase.Close
As others have mentioned you are not using prepared statements as they were intended. The question marks are placeholders that you fill in on execution. In this case you can see that in the ExecuteSQL command we are passing the event_type which refers to the first ? in the sql string, request.body which is the second & a datetime which is the third.
Preparing a statement is only useful if you are going to repeat the SQL command multiple times with different data. Xojo uses prepared statements under the hood so ExecuteSQL will protect you from SQL injection & replace those pesky single quotes.
That’s a pretty broad statement. It’s true that ExecuteSQL can accept data for variable substitution, but there’s (apparently) nothing preventing you from avoiding this functionality by escaping your own variables. ExecuteSQL isn’t going to take apart your statement and guarantee that everything is properly quoted.
This is really not true. Prepared statements should ideally be used in every possible context to prevent against SQL injection vulnerabilities. They’re not just a convenience or an optimization, they’re a vital security feature.
True, while Xojo does use prepared statements under the hood you can still override that protection.
As Xojo uses prepared statements under the hood they are being used under normal circumstances (unless of course you’re still usind API 1 commands). So I stand by my statement that preparing a statement in Xojo is only useful for reuse.
1 Like
It most certainly does. ExecuteSQL uses a prepared statement, so it doesn’t need to “quote” anything. Data is passed verbatim without the need to deal with special characters.
1 Like
This is completely wrong. If I do something like:
Var myvar1, myvar2 as String
// Set myvar1 and 2 to some string values
sql = "update mytable set mycol1=?1, mycol2=?2 where id=27"
ExecuteSQL (sql, myvar1, myvar2)
Then I am completely protected against SQL injection, no matter what myvar1 and myvar2 contain. That is the whole point of these API2 methods.
Edit: in fact, some time before the API2 methods came along, I learnt about sql injection. But, not wanting to have to protect each of the 900 or so SQLexecute and SQLselect API1 calls in my app (by adding prepare code around each one individually), I adapted my already existing wrapper sql methods (which handle error logging) so that they would accept string arguments and do the necessary preparing. Thus the sql string and string arguments were just passed straight through to what were (then) API 1 methods.
So I was quite pleased when the API2 methods appeared and all I needed to do was to strip out the prepare code from my wrapper methods.
Brain just coming online here on the left coast - and super grateful for all the insightful replies! However, now I’m a bit confused (still). Admittedly my code example was poorly crafted - as I’m prototyping and looking for that catch-all protection against pesky ‘special characters’ that make my SQL INSERT’s bomb.
I’m hoping that @Tim_Hare is spot on with the ‘no need to deal with special chars’ statement. And @Wayne_Golding and @Eric_Williams offer great historical insight. (thanks for jarring my memory!)
My goal remains; erradicate issues with special chars that trip-up SQL statements.
Start by reading
https://documentation.xojo.com/api/databases/database.html#database-executesql
there is information about avoiding SQL injection with code like others commented here. From what I read you can save/retrieve any UTF8 character to/from a database using ExecuteSQL an feeding the parameters instead of using SQL command with +my_property+ code.
Please take note that saving an apostrophe to a database may no be your only problem if you are working with Web. Maybe you can have problems if you read from the database and try to use the information directly, sometimes you need encode the info to URL safe characters
https://documentation.xojo.com/api/text/encoding_text/encodeurlcomponent.html#encodeurlcomponent