How do I create a parameterized SQL query? Why Should I?

Question

I've heard that "everyone" is using parameterized SQL queries to protect against SQL injection attacks without having to vailidate every piece of user input.

How do you do this? Do you get this automatically when using stored procedures?

So my understanding this is non-parameterized:

cmdText = String.Format("SELECT foo FROM bar WHERE baz = '{0}'", fuz) 

Would this be parameterized?

cmdText = String.Format("EXEC foo_from_baz '{0}'", fuz) 

Or do I need to do somethng more extensive like this in order to protect myself from SQL injection?

With command     .Parameters.Count = 1     .Parameters.Item(0).ParameterName = "@baz"     .Parameters.Item(0).Value = fuz End With 

Are there other advantages to using parameterized queries besides the security considerations?

Update: This great article was linked in one of the questions references by Grotok. http://www.sommarskog.se/dynamic_sql.html

Answer 1

The EXEC example in the question would NOT be parameterized. You need parameterized queries (prepared statements in some circles) to prevent input like this from causing damage:

';DROP TABLE bar;--

Try putting that in your fuz variable (or don't, if you value the bar table). More subtle and damaging queries are possible as well.

Here's an example of how you do parameters with Sql Server:

Public Function GetBarFooByBaz(ByVal Baz As String) As String     Dim sql As String = "SELECT foo FROM bar WHERE baz= @Baz"      Using cn As New SqlConnection("Your connection string here"), _         cmd As New SqlCommand(sql, cn)          cmd.Parameters.Add("@Baz", SqlDbType.VarChar, 50).Value = Baz         Return cmd.ExecuteScalar().ToString()     End Using End Function 

Stored procedures are sometimes credited with preventing SQL injection. However, most of the time you still have to call them using query parameters or they don't help. If you use stored procedures exclusively, then you can turn off permissions for SELECT, UPDATE, ALTER, CREATE, DELETE, etc (just about everything but EXEC) for the application user account and get some protection that way.