How to prevent SQL injections

Many applications include code that looks like:

   1:  string sqlStmt = "SELECT * FROM USERS WHERE UserName= '" + un + "' AND Password='" + pwd + "'";

Admit it...it's ugly, but you constructed SQL statements like this one.

The variables un,pwd are provided by the user. The problem with this SQL string is that the attacker can piggyback SQL statements in one of them.

What if the attacker enters this:

un = maor, pwd = 123456' OR 1=1. The following malicious statement is built:

   1:  string sqlStmt = "SELECT * FROM USERS WHERE UserName= 'maor' AND Password='123456' OR 1=1";

The statement will return all columns for all rows...Bad!

And what if the attacker enters this:

un=maor , pwd = 123456' DROP TABLE Users. The following malicious statement is built:

   1:  string sqlStmt = "SELECT * FROM USERS WHERE UserName= 'maor' AND Password='123456' DROP TABLE Users";

This builds SQL statement that queries for a user and then drops the users table.

What can you do prevent these attacks?

1. Quoting the input

Quoting the input is not a remedy, but its often proposed to solve the problem.

if we use the statement of:

   1:  string pwd;

   2:  pwd = pwd.Replace("'","''");

The code replaces single quotes with 2 single quotes in the input. The single quote is escaped and its render to invalid SQL statement. However its not perfect. If the statement has a integer field the attacker can use it to attack.

2. Use stored procedures

Many of us probably believe that the application is immune to SQL injection if we use stored procedures. WRONG!

When we enter the 123456' OR 1=1 to a parameter the sp will fail cause we cannot perform join across a stored procedure call. However, performing data manipulation is valid.

   1:  exec sp_getUser 'maor','123456' INSERT INTO Users Values('123','123')

This command will fetch data about the user and then insert a new row into the users table! What we can do? secure the stored procedure. How?

1. Use quotename function for object names. It's built in T-SQL function that adds delimiters to object names to help nullify invalid characters.
   
2. Use sp_executesql to execute sql statements built dynamically, instead of just concatenating a string. This makes sure no malformed parameters are passed along to the database server.

3. Never connect as sysadmin

If you see that your web application connects to the database as sysadmin account - its a BUG. Most of the web applications don't need the capabilities of a sysadmin to run; If there is a bug in the SQL statements and the application connects as sysadmin account, the attacker can: delete any database or table in the server; delete any table data; change data; alter tables; deletes log; and more... The potential damage is unlimited.

4. Build secure SQL statements

Instead of dynamically building a string, as shown in the bad examples above, use parameters. Anything placed into a parameter will be treated as field data, not part of the SQL statement, which makes your application much more secure.

Using parameterized queries is a three step process:

1. Construct the SqlCommand command string with parameters.
   
2. Declare a SqlParameter object, assigning values as appropriate.
   
3. Assign the SqlParameter object to the SqlCommand object's Parameters property.

   1:  // 1. declare command object with parameter

   2:  SqlCommand cmd = new SqlCommand(

   3:      "SELECT * FROM USERS WHERE UserName= @UN AND Password= @PWD", conn);

   4:   

   5:  // 2. define parameters used in command object

   6:  SqlParameter param1 = new SqlParameter();

   7:  param1.ParameterName = "@UN";

   8:  param1.Value = userName;

   9:   

  10:  SqlParameter param2 = new SqlParameter();

  11:  param2.ParameterName = "@PWD";

  12:  param2.Value = password;

  13:   

  14:   

  15:  // 3. add new parameter to command object

  16:  cmd.Parameters.Add(param1);

  17:  cmd.Parameters.Add(param2);

Summary

• Don't trust the user's input.
  
• Be strict about what represent valid input and reject everything else. RegEx are your friend!!!
  
• Use parameterized queries not string concatenation.
  
• Connect to the database server by using a least-privilege account, not the sysadmin account.

Code secure!!!!

Technorati Tags: .NET, C#, Security, SQL Injection