SQL Injection Prevention

From: Shabam (blislecp_at_hotmail.com)
Date: 09/28/04 

Date: Tue, 28 Sep 2004 00:35:32 -0700

I've read a few articles on ways to prevent SQL injection. I'd like your opinion as to which is better. Or, if there's another way I've not heard of, please suggest it. Thanks!

#1.
http://www.sitepoint.com/article/sql-injection-attacks-safe/5

Run user input through a function which strips quotes, so that user input of "' or 1=1 --'" (minus the outside double quotes) turns into "'' or 1=1 --'". Also, strip any user input that contains "select", "drop", ";", "--", "insert", "delete", or "xp_".

Downside here is obviously that users will no longer be able to enter such characters in the application.

#2.
http://www.uberasp.net/getarticle.aspx?id=46

Use parametized SQL queries. This seems to me to be a better and more elegant solution, as you don't have to actively look for strings to watch for, and also, frequent use of them gives you the same benefit as with stored procedures (only if the same parameters are re-used).

SqlConnection objConnection = new SqlConnection(_ConnectionString);
objConnection.Open();
SqlCommand objCommand = new SqlCommand(
   "SELECT * FROM User WHERE Name = @Name AND Password = @Password",
   objConnection);
objCommand.Parameters.Add("@Name", NameTextBox.Text);
objCommand.Parameters.Add("@Password", PasswordTextBox.Text);
SqlDataReader objReader = objCommand.ExecuteReader();
if (objReader.Read())
{
 ...

Relevant Pages

  • SQL Injection Prevention
    ... I've read a few articles on ways to prevent SQL injection. ... I'd like your opinion as to which is better. ... SqlConnection objConnection = new SqlConnection; ... SqlCommand objCommand = new SqlCommand( ...
    (microsoft.public.sqlserver.server)
  • Re: SQL Injection Prevention
    ... I've read a few articles on ways to prevent SQL injection. ... Run user input through a function which strips quotes, ... SqlConnection objConnection = new SqlConnection; ... SqlCommand objCommand = new SqlCommand( ...
    (microsoft.public.sqlserver.server)
  • Re: SQL Injection Prevention
    ... I've read a few articles on ways to prevent SQL injection. ... Run user input through a function which strips quotes, ... SqlConnection objConnection = new SqlConnection; ... SqlCommand objCommand = new SqlCommand( ...
    (microsoft.public.dotnet.security)