5 Answers
5

Your EXEC example 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 your 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.

Performance: If you regularly invoke the same query just with different parameters a parametrized query might allow the database to cache your queries which is a considerable source of performance gain.

Extra: You won't have to worry about date and time formatting issues in your database code. Similarly, if your code will ever run on machines with a non-English locale, you will not have problems with decimal points / decimal commas.

You want to go with your last example as this is the only one that is truly parametrized. Besides security concerns (which are much more prevalent then you might think) it is best to let ADO.NET handle the parametrization as you cannot be sure if the value you are passing in requires single quotes around it or not without inspecting the Type of each parameter.

Becareful with this: .Net strings are unicode, and so the parameter will assume NVarChar by default. If it's really a VarChar column this can cause big performance problems.
–
Joel CoehoornFeb 12 '09 at 19:43