Avoid misconceptions about SQL injection attacks

You may know that if user input finds its way directly into SQL statements your site executes against the database, hackers may be able to take advantage of that fact and compromise your website.

One typical strategy to prevent such attacks is to put all the queries in stored procedures on the database. What many people don't realize, however, is that just moving queries into stored procedures doesn't necessarily offer protection.

If your application calls a stored procedure by passing a SQL string to the database, you may still be vulnerable. For example, consider the following VBScript using an ADO recordset and connection object to connect to Microsoft SQL Server:

set rs = conn.execute("usp_my_procedure " & strInput)

If the strInput variable contains a string passed from a form or querystring, it may compromise the database. All a hacker has to do is include a semicolon (;) to batch additional commands.

To avoid this problem, you can clean out any semicolons from the input. Or, to be extra safe, you can parameterize the input, such as in this VBScript example:

If strInput = "" Then
strInput = " "
End If
Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conn
cmd.CommandText = "usp_my_procedure"
cmd.Parameters.Append cmd.CreateParameter("@myinput", adVarChar, adParamInput, 50, strInput)
rs.Open cmd

In addition, be aware that database commands, such as SQL Server's execute (or exec) T-SQL command, may similarly create a security hole. Furthermore, even hidden fields or data passed from the database could make you vulnerable. A hacker could spoof your form and put dangerous values into the hidden fields. Even values that a hacker has added to the database through "legitimate" means could come back to haunt you if a page later grabs that value and executes it.