Preventing SQL Injection

May - June Issue of SQL Server Standard carried Dinesh Asanka’s great article on SQL Injection. I would like to share some more ways to prevent SQL injection.
SQL Injection is based on injecting code into a user input and makes it interpreted differently by SQL Server. For Example, an html page rendered for user login may have two text fields (one for user name and the other one for password). Now the hacker may try to inject some code into these textboxes. What is the prevention?
This is the principle: Make sure the user input is always treated as user input and not as part of your code. This has two implementations:

1. Always use stored procedures to execute a query. User input will be treated as literal storing if we use parameters. For example ;

CREATE PROCEDURE find_User
@UserName nvarchar(100), @Password nvarchar(100)
AS
SELECT * FROM users
WHERE username = @UserName AND
Password = @Password
GO

Now whatever the user sends through user input has only one meaning; they are literal string. They are NOT part of the SQL code. This prevents the hacker from changing the query.

2. If you are using dynamic SQL inside stored procedures (and execute them using EXECUTE or sp_ExecuteSQL methods) where one or more user input is in string format (char, varchar, nchar and nvarchar) replace all single quotes of the user input with two single quotes.

CREATE PROCEDURE find_User
@UserName nvarchar(100), @Password nvarchar(100)
AS
SET @UserName = REPLACE(@UserName,'''','''''')
SET @Password = REPLACE(@Password,'''','''''')
DECLARE @SQL nvarchar(4000)
Set @SQL = 'SELECT * FROM users
WHERE username = ''' + @UserName + ''' AND
Password = ''' + @Password + ''''
EXEC SP_EXECUTESQL @SQL
GO

I understand this part of code may be a bit difficult to read. Let me explain what is happening here and why should we use this method.
First let me assume that you have used the parameters as it is within your stored procedure. Your procedure will look like this:

CREATE PROCEDURE find_User
@UserName nvarchar(100), @Password nvarchar(100)
AS

DECLARE @SQL nvarchar(4000)
Set @SQL = 'SELECT * FROM users
WHERE username = ''' + @UserName + ''' AND
Password = ''' + @Password + ''''
EXEC SP_EXECUTESQL @SQL
GO

Now, a user sends some malicious code through input box on username
His value is ' or 1=1 --
Password is blank
Remember his first character for is single quote.
The application takes the parameters and replaces each quote with two quotes.
The application code may look like this: (I have used C# for this example)

public Boolean verifyUser(string user, string pass)
{
// Create command
SqlCommand command = new SqlCommand("find_user");
command.CommandType = CommandType.StoredProcedure;

// Attach parameters
SqlParameter UserName = new SqlParameter("@UserName", SqlDbType.string);
username.Value = user;
command.Parameters.Add(username);
SqlParameter password = new SqlParameter("@Password", SqlDbType.string);
password.Value = pass;
command.Parameters.Add(password);

// Execute command
return this.ExecuteReader(command);
}

Your parameters will take the values as they are, and passed to your dynamic SQL
Your @SQL variable will look like this before execution

SELECT * FROM users
WHERE username = '' or 1=1 --' AND
Password = ''

Now the user has injected his SQL code into your code.

Some of you would have implemented the code like this:

public Boolean verifyUser(string user, string pass)
{
// Replace single quotes with two single quotes in parameter(s)
user = user.Replace("'", "''");
pass = pass.Replace("'", "''");

// Create command with parameters

SqlCommand command = new SqlCommand("find_user '"+ user + "', '"+ pass + "'");
command.CommandType = CommandType.Text;

// Execute command
return this.ExecuteReader(command);
}

In the second method even though the parameter replaces each single quote with two single quotes, that is necessary for internal usage only
When it comes to SQL Server, it will recongnize it as one quote only


Your parameters will take the values as they are and passed to your dynamic SQL
Your @SQL variable will look like this before execution

SELECT * FROM users
WHERE username = '' or 1=1 --' AND
Password = ''

Now the user has injected his SQL code into your code.

As you all know, your application won’t know (and shouldn’t know if we follow the best practices) how you have implemented the stored procedure Find_User. It will simply pass the parameter as it is. It is the stored procedure which uses dynamic SQL, should do the validation.
I know it is an extra piece of work. But it is well worth when you compare the price you may pay otherwise.