PlausiblyDamp

Quick Guide to Parametrising SQL Jump to the bit on using parameters Often when interacting databases we need to provide information to the database that can only be obtained at runtime (this could be from user input, other programmatic means or similar). Two potential approaches to this problem involve either parametrising the database code or relying on string concatenation. In this post I hope to show why concatenation is bad and parameters are good. To keep things simple I am using the simplest code I can and deliberately ignoring non-essential error handling, for similar reasons I am also choosing not to use stored procedures. I am choosing Northwind as the database as this is available to anyone with SQL or MS access but the ideas will convert to other database platforms. Another point to bear in mind is these problems will occur with any form of string concatenation be they String.Format, StringBuilder or some other means. Why not parametrising is bad A typical example of how to perform a query based on user input could look like the following (the example has a simple form with a button, textbox and datagrid - should work in either a web or windows environment) For sql server Dim conn As New SqlConnection("Data Source=.;Initial Catalog=Northwind;Integrated Security=true") Dim sql As String = "SELECT ProductID, ProductName FROM Products WHERE ProductName Like '" & TextBox1.Text & "%'" Dim cmd As New SqlCommand(sql, conn) Dim ds As New DataSet Dim da As New SqlDataAdapter(cmd) debug.WriteLine(sql) 'Lets us see the sql just before it is executed! da.Fill(ds) DataGridView1.DataSource = ds.Tables(0) 'DataBind 'Uncomment this line if a web application or for access Dim conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Nwind.mdb;Persist Security Info=True") Dim sql As String = "SELECT ProductID, ProductName FROM Products WHERE ProductName Like '" & TextBox1.Text & "%'" Dim cmd As New OleDbCommand(sql, conn) Dim ds As New DataSet Dim da As New OleDbDataAdapter(cmd) Debug.WriteLine(sql) da.Fill(ds) DataGridView1.DataSource = ds.Tables(0) 'DataBind 'Uncomment this line if a web application To see the application work try entering a value of chef into the textbox and see the resulting values. Notice this works and is simple, the initial reaction is therefore to use this method. notice the line Dim sql As String = "SELECT ProductID, ProductName FROM Products WHERE ProductName Like '" & TextBox1.Text & "%'" is the one that builds the query by concatenating the contents of a textbox with a sql string, it is this concatenation that causes our potential problems. Problem 1 Maybe not a big problem but consider how complex the string building will get if the query is a multiple table join, with multiple textboxes that get concatenated into the sql query. How about if my code needs to use the " character? All of these situations can result in a piece of code spanning multiple lines, maintenance is not it's strong point ;) If this runs but the results are wrong how do we pinpoint the problem - is the SQL correct but the code we are using to build it wrong; is the SQL wrong but at least we are building it correctly; both bits of code are wrong and we really have our work cut out. Problem 2 To get a little more adventurous try searching for the string Chef Anton's and suddenly our system isn't running as smoothly... If you look at the resultant SQL (as displayed in the output window by the Debug.WriteLine(sql)) it looks like SELECT ProductID, ProductName FROM Products WHERE ProductName Like 'Chef Anton's%' Notice the end of the statement 'Chef Anton's%' contains invalid sql due to the single ' character contained within our textbox. If we need to search / insert etc. data that may contain this character we now have a major problem to contend with. Problem 3 change the sql assignment to 'sql server users use Dim sql As String = "SELECT FirstName,LastName FROM Employees WHERE HireDate ='" & TextBox1.Text & "'" 'oledb people use Dim sql As String = "SELECT FirstName,LastName FROM Employees WHERE HireDate =#" & TextBox1.Text & "#" and now try querying the value 17/10/1993 and see the results, also try 10/17/1993 and compare. I would like to tell you the results but there are no guarantees here. On my pc either value worked on the oledb version, while 17/10/1993 throws an exception and 10/17/1993 works against sql server. This is a result of us passing strings to the database that the database then needs to interpret correctly. Differences in client / server locale settings and user input formats can all cause problems here. Problem 4 Firstly if you are using Access then inherent limitations of it's SQL engine prevent this problem occurring, for most other databases however this is a valid and very dangerous problem. Let's revert back to the original sql string Dim sql As String = "SELECT ProductID, ProductName FROM Products WHERE ProductName Like '" & TextBox1.Text & "%'" and we will try something a little more fun. We already know that putting a single ' into the text box causes an error because the code then adds an additional ' to the end. Therefore if we search for a very simple - but invalid string like x' we will see the same error as before with the resultant sql being SELECT ProductID, ProductName FROM Products WHERE ProductName Like 'x'%' Notice if we change the textbox contents to x' -- then the resultant sql looks like SELECT ProductID, ProductName FROM Products WHERE ProductName Like 'x' --%' although this doesn't return any data neither is it crashing! By commenting out the extra ' character we now have well formed sql code again. Now enter the following into the text box and see what happens x' INSERT Employees (FirstName,LastName) VALUES('get here?','How did this') -- this gives us a resulting sql string of SELECT ProductID, ProductName FROM Products WHERE ProductName Like 'x' INSERT Employees (FirstName,LastName) VALUES('get here?','How did this') --%' What would you expect to happen if you ran this against your server? If you have been following along you have just ran this against your server - go have a look in the Employees table and check the last entry ;) If the above points haven't convinced you that string concatenation is bad then I'm probably fighting a losing battle and you might as well ignore the rest of this post. If you have encountered any of the above you may have also come across means of preventing them (detecting invalid characters, regular expressions, encoding and decoding characters etc.) these may work but can often involve more effort than simply doing things correctly in the first place.