The Ins and OUTs of Parameterized Queries...

[cjard]

OK, so I'm frequently found, on the forums, bollocking people for writing dinosaur SQL queries that look like this:

Dim oc as New OracleCommand( _
  "SELECT a || '" & TextBox1.Text & "' as Thing FROM table1 " & _
  "WHERE col1 = '" & TextBox2.Text.Replace("'"c, "''") & "' OR c" & _
  "ol3 LIKE '" & TextBox3.Text & "%' AND col4date = #" & _
  dateTimePicker1.Value.ToString("MM/dd/yyyy HH:mm:ss") & "#" & _
  " AND someListCol5 NOT IN('" & TextBox6.Text.Split(","c)(0) & "'," & _
  "'" & s & "','" & CInt(iText).ToString("000") &"')")

There's usually some complaint that it doesnt work, or a date format is picking up the 3rd of January (03/01/2007) instead of the 1st of March (03/01/2007) depending on where you live, or just a plain old SQL syntax error that the database is choking on..

And it's hardly surprising, because though it might work, after you spend ages debugging which comma is in the wrong place.. It looks truly awful, doesnt it?
If another developer wrote that (and thats a relatively simple SELECT statement; you should see some of the SQLs I have to write; when printed they go on for tens of pages..) you would, should, go "eugh!"
Heck, you should probably go "eugh!" even if it's your baby.. because it's virually unmaintainable and being the proper, forward looking coders we are, we should be seeking to write nice code that's readable and easy to pick up again in 3 weeks time when we've forgotten we ever wrote it.

There are other good reasons for avoiding writing garbage SQLs like this:

• You cannot use any type other than that which is representable by a string. String is pretty good, but you tell me.. If you saw a date of 03/01/2007 written down.. is that march or january? Does your program know that continent youre on? Its not safe..
  • Sure you can format your date, in VB, to a known format like YYYYMMDD, and pass that as a string: date.ToString("yyyyMMdd")
  • Then, you can write your query decode this back into a date to use with the datecolumn: WHERE dateCol > TO_DATE(whatever_passed_string, 'YYYYMMDD')
  • But what's the point? user choice -> date ->string -> db -> date -> use in query... is long, and resource heavy.
  • By contrast... user choice -> date -> db -> use in query... is quick, easy and we never convert anything between forms where information might be lost
• The user writes something normal, like o'connor in the Last Name field. Instantly, that apostrophe will break the SQL
  • There's nothing more amusing (to the guy that doesnt like you) or embarassing (to you) when you show your wonderful new website off to the Big Boss that will be the web face of his mega empire.. he types in his name as John L'Oreal and bang.. "Server Error in Application"
  • This doesnt happen with parameterized queries.
• SQLs like this are prone to a form of hacking or deliberate tampering called SQL Injection. For oodles of information about it, see google
  • A malicious user could (on some systems) write '; DELETE FROM veryImportantTable; SELECT '1 in the Last Name field.. suddenly your innocent surname lookup option in your program allows a user to destroy whatever database table they feel like.
• There is no way that the server can look at your query (which eventually, after all the string concatentation and formatting) comes in as a straight bunch of text, and say "oh yes, clearly, that part there, after LastName LIKE .... is a varying part, so I'll cache the query as though that part will vary and optimize the index use for best access.. It CANNOT know this..
  • All the query engine sees is SELECT * FROM employee WHERE type = 'Programmer' AND lastName = 'cjard' - it cannot know that the 'cjard' part will vary between runs, but the 'Programmer' part is a constant.
  • If if knew that Programmer was a contant, then it could maybe (when it has some spare time) make a quick cache of all the entries that are programmer types, or consider that index1 is better than index2 for whatever reason

What are the good reasons for writing SQLs like this then?

• Er..
• Umm..
• Nope. There arent any.

So what's a parameterized query?
It's something similar to a computer program itself. Youre a VB coder, you can see the advantages of having variables:

MessageBox.Show(My.Settings.WelcomeMessage)

It saves you having to recompile the whole program every time you want to use a different string. It would really be a drag to recompile the whole thing for something as trivial, eh?

So, the same is true of SQLs.. every time you send a non-parameterized SQL to the server, it needs to be re-compiled. What a drag.

"SELECT * FROM table WHERE lastName = " & Textbox1.Text

Every time you change Textbox1.Text, you make a whole new query which must be recompiled all over again by the server. People dont really care because doing so is relatively quick, but it's a bit lame, no?


Introduce the parameter. Just like a variable in your code, it sa way of saying to the server "Hey.. this part might change!"


Here are some examples of parameterized queries in the Big 3 databases in use today:



Access (parameters must be created in order they appear in SQL):
"INSERT INTO table1 VALUES(?, ?, ?)"
"UPDATE table1 SET col1 = ? WHERE col2 = ?"
"SELECT * FROM table1 WHERE col1 = ? OR col2 LIKE ?"
"DELETE FROM table1 WHERE col1 = ?"

SQLServer (supports named parameters):
"INSERT INTO table1 VALUES(@param1, @param2, @param3)"
"UPDATE table1 SET col1 = @param1 WHERE col2 = @paramTwo"
"SELECT * FROM table1 WHERE col1 = @someCol1 OR col2 LIKE @theColour"
"DELETE FROM table1 WHERE col1 = @idToDelete"

Oracle (like SQLS, but uses : instead of @):
"INSERT INTO table1 VALUESparam1, 2, araThree)"
"UPDATE table1 SET col1 = :aColumn WHERE col2 = aramTwo"
"SELECT * FROM table1 WHERE col1 = :someCol1 OR col2 LIKE :theColour"
"DELETE FROM table1 WHERE col1 = :idToDelete"

The database command object (whichever you are using) has a .CommandText property, and that is where your query text goes. Youre already faimilar with that, I'm sure. If you like to add the SQL on creation, that too, is fine:

Dim oc as New OracleCommand("SELECT * FROM table WHERE lastName = LastName", theConnection)
or
oc.CommandText = "SELECT * FROM table WHERE lastName = LastName"

The new bit is, of course, in bold. I've chosen a name of pLastName for this oracle parameter though it can be anything I like. I use this because p=parameter, and LastName is what i'm parameterizing.
I could have called it :LastName, but then it might have looked a bit too much like the column name LastName and might be confusing in a big, long SQL.

If i'd been using SQLServer, i'd have used @pLastName
If I'd been using Acces, I personally would have used ? but Access does support named parameters in thsi style too. However, you MUST add the parameters in the order they appear in the SQL, whether you use ? or @Named

Adding parameters? Huh?

Well.. what I mean by this is, that now we have told our database command what the parameters names are and where they are to be used in code, you need to fill in the rest of the blanks (type? size? value?)

We do this through the command's parameters Collection:

Dim oc as New OracleCommand("SELECT * FROM table WHERE lastName = LastName", theConnection)
oc.Parameters.Add("pLastName", OracleType.VarChar)

In the bold line, we tell the command that the parameter named pLastName is of string type. Why? because LastName is a string column. Here are some other types:

Dim oc as New OracleCommand("SELECT * FROM table WHERE birthDayDate >= Birthday", theConnection)
oc.Parameters.Add("pBirthday", OracleType.DateTime)

Dim oc as New OracleCommand("SELECT * FROM table WHERE age >= Age", theConnection)
oc.Parameters.Add("pAge", OracleType.Number)

Great! No more messing about converting a date to a string, back to a date to a whatever.. It can stay as a date all the way through, no timezone fuss, no confusion over whether 03/01/2007 is march or january. Brilliant.

You may have noticed I didnt put a : at the start of the name when i was calling Parameters.Add. In my experience, with oracle, it doesnt matter. I doubt it does with any other drivers either, but if you hit problems, try it with/without.

Note you can use the same parameter more than once in the SQL and you DONT add it twice:
Dim oc as New OracleCommand("SELECT * FROM table WHERE lastName = LastName OR maidenName = LastName", theConnection)
oc.Parameters.Add("pLastName", OracleType.VarChar)


We arent there 100% yet. So far, I've told you how to set up your command.. its ready to go, but we havent done anything else.
A very important point i want to note is that YOU ONLY DO THIS SETUP ONCE.
Do NOT make a new command, and add a load of new parameters every time you click the button. DOnt even think of putting database code in a button handler. Do it somewhere in its own module or class, do it once and leave it.

Remember that you have created VARIABLES. You dont need to tear down the whole command and recreate everything just to vary the values..

So how do we do that?

simply:

oc.Parameters("pLastName").Value = "Smith"

That command is ready for executing now. So we run it for Smith. What happens when we want to run it again for Jones?

oc.Parameters("pLastName").Value = "Smith"
oc.ExecuteQuery()
oc.Parameters("pLastName").Value = "Jones"
oc.ExecuteQuery()


Right now the database is thanking us, for not making it recompile a whole new command text just because the text changed to jones


Things That Cannot Be Parameterised
You cannot parameterise column or table names:

"SELECT pColName FROM TableName WHERE OtherCol = Value"


For the same reason that when you write:

Dim s as String


That variable is called s in the code now, you have to recompile to change it. The only thing you can do, is change its value. That variable, no matter what its value, will always be called s in your code.

Remember: only values. If you want to change the tablename you will have to compile another query.. but then, if you have 2 or more tables whose structure is so similar that you think oyu should be able to parameterize the name in a query.. then your database normalisation possibly is flawed..


That mess I wrote at the start:

Dim oc as New OracleCommand( _
  "SELECT a || '" & TextBox1.Text & "' as Thing FROM table1 " & _
  "WHERE col1 = '" & TextBox2.Text.Replace("'"c, "''") & "' OR c" & _
  "ol3 LIKE '" & TextBox3.Text & "%' AND col4date = #" & _
  dateTimePicker1.Value.ToString("MM/dd/yyyy HH:mm:ss") & "#" & _
  " AND someListCol5 NOT IN('" & TextBox6.Text.Split(","c)(0) & "'," & _
  "'" & s & "','" & CInt(iText).ToString("000") &"')")

Becomes much nicer:

Dim oc as New OracleCommand( _
  "SELECT a || :pThingSuffix as Thing FROM table1 " & _
  "WHERE col1 = :pTextBox2 OR col3 LIKE :pTextBox3 AND " & _
  "col4date = :pSomeDate " & _
  " AND someListCol5 NOT IN(:pInList1, :pInList2, :pInList3)"

Someone might want to debug that now!

Remember; there is now no real reason to write anything other than a parameterized query. They can be cached, they are immune to SQL Injection, they dont mess up your dates, they can send data that cant be represented as string (binary) and they only need setting up once. Thats good news for your application performance and good news for the database server performance.

Use them!

 

[JohnH]

This thread (sticky) was split and discussion continues here: further discussions about parameterized queries (PQ)

 

[cjard]

In the ensuing debate from the original advisory, a couple of things came up. People asked how things like dynamic searches could be achieved. There are two ways that allow the use of parameters, and I'll present some code.

To write a query that can be used by the dataset designer (i.e. not dynamically changing the content of the string variable holding the SQL) create a query like this (SQLServer syntax, use ? for Access, ?name for MySql and :name for Oracle)

SELECT * FROM table WHERE
(col1 = @col1 OR @col1 IS NULL) AND
(col1 = @col2 OR @col2 IS NULL) AND
(col1 = @col3 OR @col3 IS NULL)

Now just pass a null/Nothing into any parameter you wish to wildcard:

myTableAdapter.FillByXXX(dt, Nothing, "Col2 Value", 3)


You can get a bit more clever with your datatypes too. For strings, some optimisations can be made to have the SQL look simpler:

SELECT * FROM table WHERE
(col1 LIKE @col1 AND col2 LIKE @col2 AND col3 LIKE @col3)

Now just pass a string "%" to any parameter to wildcard it:

myTableAdapter.FillByXXX(dt, "%", "Col2 Value", "3")


-

The next level takes us back to building an SQL string dynamically using string concatenation. The basic premise is that, if you can write a routine that concatenates a value into an SQL string, you can easily write a routine that concatenates a parameter name into a string:

'pseudocode !

Dim x as New DbCommand("SELECT * FROM table WHERE 1=1 ")
For Each parameter_you_want_to_add
  x.CommandText &= " AND columnName = @parametername"
  x.Parameters.AddWithValue("@parametername", value)
Next


'real code; Oracle example. 

Dim oc as New OracleCommand("SELECT * FROM orders WHERE 1=1 ")

'I normally use bound data, so to ask the user for what they wish to
'search, I clear the datatable currently bound, then add one row into
'which they type their search terms
Dim srchTerms as MyDataRow = myBoundTable.Rows(0)

For Each dc as DataColumn in srchTerms.Table.Columns

  If srchTerms.IsNull(dc) Then Continue

  Dim cName as String = dc.Name
  Dim pName as String = ":p" + dc.Name
  Dim pVal as Object = srchTerms.Item(dc)

    If TypeOf pVal Is String Then
    oc.CommandText += string.Format("AND {0} LIKE {1}", cName, pName)

  Else 'can add other type checks here
    oc.CommandText += string.Format("AND {0} = {1}", cName, pName)
    
  End If
  
  oc.Parameters.AddWithValue(pName, pVal)
Next

Naturally you don't have to do this in a loop. If you want to make the most complicated search form the world has ever seen, something to rival the query designer in MS Access, then you're going to have to get creative, but when all is done and written, you're going to have a nicely built SQL string:

SELECT * FROM table WHERE 1=1 AND parameter1 = @parameter1 AND …

And a parameters collection with ready to use values.