What's Wrong with Dynamic SQL

So, what's wrong with dynamic SQL?

The traditional way that most Visual Basic 6 programmer use to build SQL strings has got some serious limitations that have thankfully been addressed in ADO.Net. Most of us used to build "dynamic" SQL string like this:

Dim SQLString, NameString as string
NameString = "Joe Blow"
SQLString = "Select * from Table1 where Name='" & NameString & "'"

What?... Do you don't see anything wrong with this? Does this looks familiar to you? Trust me, this not the preferred way... consider the example above with the following value for NameString:

NameString =  "James O'Brian"
SQLString = "Select * from Table1 where Name='" amp; NameString amp; "'"

You'll get a nasty error that says: Syntax Error (missing operator) in query expression 'Name='James O'Brian''

So, you're an enterprising programmer and you know that your underlying database also uses double quotes as valid string delineators. So, you modify the example above to use double quotes and you're now happy

NameString =  "James O'Brian"
SQLString = "Select * from Table1 where Name=""" & NameString & """"

Hooray!, you fixed the problem... or did you? Consider the following example:

NameString =  "Jane ""Doe"" Smith"
SQLString = "Select * from Table1 where Name=""" & NameString & """"

You'll get that same ole nasty error that says: Syntax Error (missing operator) in query expression 'Name="Jane "Doe" Smith"'

Ok, are you convinced yet?

One of the first rules of programming is "Never trust the input from the user". The input could be benign, like in the examples above, or it could be malicious like a SQL Injection attack.

Now, let me show a better way... let's use a "parameterized query". What?, I hear you say... I'm not using stored procedures! Well, parameterized queries are not just for stored procedures, you can use them in ordinary SQL strings too. Consider the following example:

SQLString = "Select * from Table1 where Name=?"
' The first argument is used to find the place in the SQL string (by name or by order)
cmd.Parameters.Add("Name", OleDbType.VarChar, 50).Value = NameString

Now the example will work for Joe Blow, James O'Brian, and Jane "Doe" Smith!

But wait, there's more! There is yet another way to parameters in ADO.Net. You can use "Named Parameters" in an SQL string. I actually prefer this method, because it makes things consistent with the way we do things with stored procedures.

SQLString  = "Select * from Table1 where Name=@NamedParameter"
' This time we use a "named" parameter to find the spot in the SQL string above.
cmd.Parameters.Add("@NamedParameter", OleDbType.VarChar, 50).Value = NameString 

Downloads/Links

Download the complete VB.Net Source code example used in this article: ADONet_Queries.zip