String.Format and SQL requests

Question

In my application I use string.Format() for the SQL like this

Dim strSQL As String = "SELECT X FROM MY_TABLE WHERE Y <> {0} AND Z = '{1}'"
    strSQL = String.Format(strSQL, otherObj.Y, myObj.Z)

one day I did a global "stress" test using SQL injections (params with quotes, and stuff like this) and discovered a lot of bugs...

What is the best way in .NET to "fight" against it? Is there a String.Format or other common way to correctly(and safely) use the SQL parameters in sql queries.

With what would you'll recommend to replace String.Format?

Justin Niessner · Answer 1 · 2011-03-14T18:01:41.423

8

Use a parameterized query instead:

Using conn as new SqlConnection(connString)

    Dim command As new SqlCommand("select x from my_table " + & _
        "where y <> @param1 and z = @param2", conn);

    command.Parameters.Add(new SqlParameter("@param1", otherObj.Y));
    command.Parameters.Add(new SqlParameter("@param2", myObj.Z));

    ' Execute the command and get results

End Using

edited Mar 14 '11 at 18:01

answered Mar 14 '11 at 17:54

Justin Niessner

242,243
40
408
536

score 3 · Accepted Answer · answered Mar 14 '11 at 17:55

The only completely safe way to prevent SQL injection is not to allow user-supplied data into your actual SQL statement. Instead, supply variable data (such as the tokens you have in your format string) through parameters.

For example,

Using cmd As yourConnection.CreateCommand()
    cmd.CommandText = "select x from my_table where y <> @y and z = @z"

    cmd.Parameters.AddWithValue("@y", otherObj.Y)
    cmd.Parameters.AddWithValue("@z", myObj.Z)

    // etc.
End Using

(The use of AddWithValue assumes that this is a SqlConnection, but the code doesn't look much different for other providers, and the concepts are the same)

String.Format and SQL requests

2 Answers2