Tutors Corner Moderated forum that should hold answers to the FAQs we have here. If you are new check this forum first for the answer to your question.

Go Back  Xtreme .NET Talk > Knowledge Base > Tutors Corner > Quick Guide to Parametrising SQL


Reply
 
Thread Tools Display Modes
  #1  
Old 06-13-2007, 06:47 AM
PlausiblyDamp's Avatar
PlausiblyDamp PlausiblyDamp is offline
Ultimate Contributor

Preferred language:
C#, VB
 
Join Date: Sep 2002
Location: Lancashire, UK
Posts: 6,462
PlausiblyDamp is on a distinguished road
Default Quick Guide to Parametrising SQL



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
Code:
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
Code:
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
Code:
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
Code:
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
Code:
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
sql Code:
SELECT ProductID, ProductName FROM Products WHERE ProductName LIKE 'Chef Anton's%'
Notice the end of the statement
Code:
'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
Code:
'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
Code:
17/10/1993
and see the results, also try
Code:
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
sql Code:
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
Code:
x'
we will see the same error as before with the resultant sql being
sql Code:
SELECT ProductID, ProductName FROM Products WHERE ProductName LIKE 'x'%'
Notice if we change the textbox contents to
Code:
x' --
then the resultant sql looks like
sql Code:
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
Code:
x' INSERT Employees (FirstName,LastName) VALUES('get here?','How did this') --
this gives us a resulting sql string of
sql Code:
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.
__________________
Posting Guidelines FAQ Post Formatting

Intellectuals solve problems; geniuses prevent them.
-- Albert Einstein

Last edited by PlausiblyDamp; 06-25-2008 at 09:46 AM.
Reply With Quote
  #2  
Old 06-13-2007, 06:47 AM
PlausiblyDamp's Avatar
PlausiblyDamp PlausiblyDamp is offline
Ultimate Contributor

Preferred language:
C#, VB
 
Join Date: Sep 2002
Location: Lancashire, UK
Posts: 6,462
PlausiblyDamp is on a distinguished road
Default Re: Quick Guide to Parametrising SQL

Part two - using Parameters

To show the simplest example of using a parametrised query let's take our original example and modify it slightly
Code:
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
Code:
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
sql Code:
SELECT ProductID, ProductName FROM Products WHERE ProductName LIKE @ProductName + '%'

Code:
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
Code:
Dim sql As String = "SELECT FirstName,LastName FROM Employees WHERE HireDate = @HireDate"
and add our parameter in as
Code:
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
Code:
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.
__________________
Posting Guidelines FAQ Post Formatting

Intellectuals solve problems; geniuses prevent them.
-- Albert Einstein

Last edited by PlausiblyDamp; 06-13-2007 at 07:44 AM.
Reply With Quote
Reply

Bookmarks

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Quick question, quick answer, about a string function... robplatt General 8 10-08-2006 08:11 PM
Beginner's Guide ADO.NET phreaky Database / XML / Reporting 1 04-01-2004 10:28 AM
Opinions on "The Programmer's Guide to SQL" Howard Kaikow Random Thoughts 0 03-26-2004 03:25 PM
Quick SQL String Question Disasterpiece Database / XML / Reporting 3 04-22-2003 01:19 PM
Dummies guide to ADO.net ?? I need an example. cgchris99 Database / XML / Reporting 1 07-09-2002 03:46 PM

Advertisement:







Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe