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

by

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.

More Related Contents:

  1. Syntax not valid on word ‘ON’ in SQL join statement [closed]
  2. How to use parameters “@” in an SQL command in VB
  3. The parameterized query expects the parameter which was not supplied
  4. Split data of columns in (if of is not null data of is null) [closed]
  5. SQL Sum of Field [closed]
  6. SQL Data models
  7. Table Naming Dilemma: Singular vs. Plural Names [closed]
  8. Simplest way to do a recursive self-join?
  9. generate days from date range
  10. How many rows in a database are TOO MANY?
  11. Oracle Joins – Comparison between conventional syntax VS ANSI Syntax
  12. Is there ever a time where using a database 1:1 relationship makes sense?
  13. ActiveRecord Arel OR condition
  14. SQL Error: ORA-01861: literal does not match format string 01861
  15. Can a sql server table have two identity columns?
  16. mysql – how many columns is too many?
  17. SQL Server 2008 – IF NOT EXISTS INSERT ELSE UPDATE
  18. SQL query to find Nth highest salary from a salary table
  19. Determine Oracle null == null
  20. SELECT INTO using Oracle
  21. Why is iterating through a large Django QuerySet consuming massive amounts of memory?
  22. alternatives to REPLACE on a text or ntext datatype
  23. Update a column of a table with a column of another table in PostgreSQL
  24. Check if NULL exists in Postgres array
  25. What does collation mean?
  26. How can I solve ORA-00911: invalid character error?
  27. Try_Convert for SQL Server 2008 R2
  28. SQL Server variable scope in a stored procedure
  29. converting int to real in sqlite
  30. What is difference between foreign key and reference key?

Leave a Comment