Had an interesting discussion the other day and in the course of the discussion the use of prepared statements came up. Turned out that a few people had never used them and did all the leg work to created their own mechanism to concatenate strings.
For a long time in REALbasic. and REALStudio this was a requirement. Prepared statements didnt exist. But once they did I always recommended people use them instead of trying to roll their own set up.
Why? What are the advantages to using prepared statements instead of a roll your own set up using string concatenation?
First off security is a BIG reason to use them. SQL injection attacks used to be common simply because there were so many ways to cause them and trying to manually handle every case, even for a single database, could be difficult. There maybe cases where control characters are allowed and others where they are not and knowing the difference in any code that applied replacement patterns would grow into a very large body of code to deal with.
With string concatenation you run the risk of someone crafting an input string just so and wiping out your database. Hackers are very clever & skilled at this. So when a database has a mechanism designed to thwart them, like prepared statements, we should use it.
Specifically suppose you just made sure that you wrapped user input in quotes and passed that along.
dim sql as string = "select * from table where column = " + WrapInQuotes(userInput)and all WrapInQuotes did was
Function WrapInQuotes(inputString) as string
// this is deliberately VERY naive to illustrate the point
return """" + inputString + """"
End Function
This is deliberately VERY naive but it shows the reason prepared statements are used
Some database will let you execute more than one statement as part of their API – which Xojo uses. If a “hacker” were to put in, as user input,
"; delete from table;and you used the above code you would find that the sql end up as
select * from table where column = ""; delete from table;"and you just might find your table no longer has data in it. And despite your and my cleverness there are so many screwy little edge cases that its almost impossible to get any kind of code with replaceall to work right in all cases.
Prepared statements avoid all this as the statement doesnt get parsed in the same way as a full sql statement that has all the values stated. The parameters are basically marked as “not present” but they know the type, position (or a name) and the execution of the statement then passes those values to the DB engine via its API. What it does NOT do is reparse the sql every time and it also makes no attempt to “interpret” the data values as if they were part of the commands.
They are JUST data and cannot be part of a command – and so the engine doesnt try to use them as commands which it does do in our previous example because it has to understand the statement(s) in order to execute them
The other upside to prepared statements in many databases is that they can do the leg work to create the query plan for the specific statement once and then reuse it over & over as you pass new variables. Not all databases can do this when using the various Xojo API’s but some do.
So there can be speed benefits to using them as well