Quick Guide to Parametrising SQL

[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.

 

[PlausiblyDamp]

Part two - using Parameters

To show the simplest example of using a parametrised query let's take our original example and modify it slightly

Dim sql As String = "SELECT ProductID, ProductName FROM Products WHERE ProductName Like @ProductName + '%'"

Dim cmd As New SqlCommand(sql, conn)
'or
Dim cmd as New OleDbCommand(sql, conn)

cmd.Parameters.AddWithValue("@ProductName", TextBox1.Text)

Now run the sample again and try it with the following values and check the results

chef
Chef Anton's
x' INSERT Employees (FirstName,LastName) VALUES('get here?','How did this') --

notice that all of these work correctly, none crash and no extra rows get added to the Employees table. In fact if you check the sql being sent every time we are sending

SELECT ProductID, ProductName FROM Products WHERE ProductName Like @ProductName + '%'

cmd.Parameters.AddWithValue("@ProductName", TextBox1.Text)

Is the key to what makes parameters a more stable and useful solution - we are not generating long strings of sql that the server then has to interpret as the actual sql is fixed and all parameter values are sent as parameters - not as strings.

The only major difference you need to be aware of when dealing with different databases is how the ParameterName is handled - SQL Server allows us to add parameters in any order and will use the name to associate them correctly, other databases might not (for example OleDb with access requires you to add the parameters in the correct order and totally ignores the name).

As a further example if we switch the sql to

Dim sql As String = "SELECT FirstName,LastName FROM Employees WHERE HireDate = @HireDate"

and add our parameter in as

cmd.Parameters.AddWithValue("@HireDate", DateTime.Parse(TextBox1.Text))

and run our code again we are now passing a date to the database not a string that needs to be converted to a date, if the string is not parsable as a date we can still get errors but this is a .Net error and can be addressed in our front end (user input is after all a UI issue).

Without any real effort parameters can make the data access code far simpler and more robust - just look at the problems and errors in Part One that simply don't happen when using parameters.

If things get more involved such as requiring the ability to find all employees who were hired in a given date range our code now becomes

Dim sql As String = "SELECT FirstName,LastName FROM Employees WHERE HireDate BETWEEN @StartDate AND @EndDate"

'... line or two trimmed
cmd.Parameters.AddWithValue("@StartDate", DateTime.Parse(TextBox1.Text))
cmd.Parameters.AddWithValue("@EndDate", DateTime.Parse(TextBox2.Text))

which is a lot cleaner than the equivalent code would be using concatenation.

Parameters can be more complex and involved than this quick example shows, expect a further post in the near future dealing with some of the more complex issues.