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.