Prevent SQL Injection in your application

I found many web sites where easily inject the SQL and logged in there, so what is wrong with these web site code. Are they not authenticating the user information? They are, so how can anyone can logged in without creating the account?

Let’s say you have written code to authenticate the user from your database like this:

SqlCommand cmd = new SqlCommand();
cmd.CommandText = "Select UserID, UserName, ….  From Users  " +
  "Where UserName = ‘" + txtUserName.Text + "’ And Password = ‘" +txtPassword.Text +"’";

Suppose user entered the username = admin and password = xxxxx so what would be the where class criteria in above statement, it will become like this

Where UserName = ‘amdin’ And Password = ‘xxxxx’

Till now it will work as it should. But now I am entering the password like this

Password = x’ OR 1=1 –

Now the where class will become like this

Where UserName = ‘amdin’ And Password = ‘x’ OR 1=1 –

So if there is any user name Admin I will be logged in, right? Let me explain the above line:

Above query will check for user name admin and password = x, it may or may not exist in database and finally will check 1 = 1 which will always be true and I commented out whatever is after that.

Is it dangerous? Till now the user can only logged in to your application with admin and do whatever admin can do. I can do the worse without going to your pages. Let’s say I try to guess the table name in your database say products and trying to login with these criteria in your password text box.

Password = x’ Go Drop table Products Go –‘

You are right; anyone can delete your table if he/she can guess the table name. If there is not custom error page and you are showing the error whatever it is coming then easily any one can guess your entire database so never show the exact error but the custom error page.

How to Prevent:

Just change the command text to use the parameter and no one can inject, see the command text

SqlCommand cmd = new SqlCommand();
cmd.CommandText = " Select UserID, UserName, ….  From Users  " +
            "Where UserName = @UserName  And Password = @Password";
Cmd.Parameters.AddWithValue(“@UserName “, txtUserName.Text);

Cmd.Parameters.AddWithValue(“@Password “, txtPassword.Text);

Now whatever the user will enter in text boxes, will be treated as a single value. Let me show when the user will enter UserName = admin and Password= x’ OR 1=1 –, then what it would be treated in SQL

@UserName = N'admin',
@Password = N'x'' OR 1 = 1'

As you can see SQL automatically converting the single quote (‘) to double quote so there is no way to inject.

To counter SQL injection attacks, you need to:

  1. Constrain and sanitize input data: Check for known good data by validating for type, length, format, and range.

  2. Use type-safe SQL parameters for data access: You can use these parameters with stored procedures or dynamically constructed SQL command strings. Parameter collections such as SqlParameterCollection provide type checking and length validation. If you use a parameters collection, input is treated as a literal value, and SQL Server does not treat it as executable code. An additional benefit of using a parameters collection is that you can enforce type and length checks. Values outside of the range trigger an exception. This is a good example of defense in depth.

  3. Use an account that has restricted permissions in the database: Ideally, you should only grant execute permissions to selected stored procedures in the database and provide no direct table access.

  4. Avoid disclosing database error information: In the event of database errors, make sure you do not disclose detailed error messages to the user.

Note: Use of Secure Socket Layer (SSL) and IP Security (IPSec), do not protect your application from SQL injection attacks.

A K Sinha
  • sql server
By A K Sinha On 14 Jul, 12  Viewed: 527

Other blogs you may like

Adding identity to an existing column in SQL Server

We can not directly change a column to identity columns, say we have a primary key column or any normal column in our table and want to make it identity column then we cannot do it directly by altering the table but there are only two way to change column to identity column 1. Create a new... By Ali Adravi   On 02 May 2013  Viewed: 1,084

some useful sql tricks

There are many ways which can help us to save our time to write some repeated code or to do some repeated action, in this post we will see some very common code which we need very frequently in our development life. I am not saying that after this post you will save your half say work every day but... By Ali Adravi   On 31 Mar 2013  Viewed: 924

Get precision and decimal value in SQL Server by using PARSENAME

In SQL server there are decimal and money data type to store precision and scale both together or say decimal values. Suppose we want to get precision and scale separately then how to get it. There are many ways to do this but there is a function in SQL named PARSENAME which can be used to get... By Myghty   On 26 Mar 2013  Viewed: 3,311

Available wildcards in sql server with examples

To write a good performing procedure or function we should be aware about available wildcards in SQL Server. I found most developer depends on ‘%’ wildcard only while we have others as well. In this article we will discuss all the available wildcards in detail with examples. First of all let’s see... By Dev D   On 25 Feb 2013  Viewed: 666

Concatenate multiple columns in SQL Server with NULL value

When we need to concatenate two columns simply we can use `+` sign and that is correct, but what if any of them is null, Will it return what we want, NO, it will return null. So let's discuss how we should concatenate two or more columns without creating extra space or extra comma problem. Let's... By Ali Adravi   On 13 Feb 2013  Viewed: 12,555