1

Apologies if this seems a daft question but how does using parameters defends against SQL injection and what are the best practices in relation to T-SQL:

For example: Is this best practice? 

SqlCommand SqlCmd = new SqlCommand("SQL Command @X ....... @Y");
SqlCmd.CommandType = CommandType.Text;
SqlCmd.Parameters.AddWithValue("@X", SqlDbType.VarChar).Value = X;
SqlCmd.Parameters.AddWithValue("@Y", SqlDbType.date).Value = Y;
SqlCmd.Connection = ConnectionString;
ᗩИᎠЯƎᗩ
  • 2,122
  • 5
  • 29
  • 41
TheIdiot
  • 307
  • 1
  • 5
  • 12
  • Thanks but I am also interested in general best practices and as the comments mention what about overflow. Etc.? – TheIdiot Apr 24 '13 at 22:19

2 Answers2

2

Yes, this code is safe from SQL injection.

The reason is that when working with parameters your passing the items as a value to your server and they will be interpreted as such.

If you would construct a string, SQL cannot know what the value is and what the query is and has to rely purely on syntax.

Kenneth
  • 28,294
  • 6
  • 61
  • 84
1

The best practice is to use SQL Parameters. Using the SqlParameterCollection (as in your example: SqlCmd.Parameters) automatically provides you with:

  1. Type checking
  2. Length validation
  3. Input is treated as a literal value rather than as executable code
  4. Handling of special characters normally involved in SQL injection attacks

There are some additional best practices, including:

  • Constrain and sanitize input data
  • Use an account that has restricted permissions in the database
  • Avoid disclosing database error information

You'll find those best practices further described at this OWASP SQL Injection Prevention Cheat Sheet.

schellack
  • 10,144
  • 1
  • 29
  • 33