How do parameterized queries help against SQL injection?

sqlchild picture sqlchild · Mar 29, 2011 · Viewed 41.6k times · Source

In both queries 1 and 2, the text from the textbox is inserted into the database. What's the significance of the parameterized query here?

  1. Passing txtTagNumber as a query parameter

    SqlCommand cmd = new SqlCommand("INSERT INTO dbo.Cars " +"VALUES(@TagNbr);" , conn);
    cmd.Parameters.Add("@TagNbr", SqlDbType.Int);
    cmd.Parameters["@TagNbr"].Value = txtTagNumber.Text;
    
  2. Converting txtTagNumber to an integer before constructing the query

    int tagnumber = txtTagNumber.Text.ToInt16(); /* EDITED */
    INSERT into Cars values(tagnumber.Text); /* then is it the same? */
    

Also, here I would use Regular Expression validation to stop insertion of illegal characters.

Answer

OJ. picture OJ. · Mar 29, 2011

Parameterized queries do proper substitution of arguments prior to running the SQL query. It completely removes the possibility of "dirty" input changing the meaning of your query. That is, if the input contains SQL, it can't become part of what is executed becase the SQL is never injected into the resulting statement.