Securing Apps Against SQL Injection

The principles and dangers of SQL Injection are technology agnostic. I have

used Microsoft SQL Server and ASP.NET in my code samples here, and want to

remind you of the security threats of SQL Injection in your applications

irrespective of the technology you use to build it. I would also list out what

you can do to make your applications more secure.

Let me start with doing what you do always — build an application with user

management features built in it. Just to make it easier, if you are trying this

out along with me, I would hard code my user database instead of building the

user management screens.

USE payroll



GO


CREATE TABLE ApplicationUser


(


UserName nvarchar(25),


Password nvarchar(25)


)


GO


INSERT INTO ApplicationUser VALUES('Amit', 'Password4Amit')


INSERT INTO ApplicationUser VALUES('Aparna', 'Password4Aparna')


GO

First I have made a screen for my users to log in (see below).

Here is the code I have used to verify that the user name and password are

correct.

protected void btnSubmit_Click(object sender, EventArgs

e)



{


string strSQL = "SELECT Password FROM ApplicationUser WHERE UserName = '"


+ txtUserName.Text + "'";


string strConnection = "Data Source=(local); "


+ "Integrated Security = SSPI; Initial Catalog=payroll";


string strPassword;


bool blnValidUser = false;


SqlConnection conPayroll = new SqlConnection(strConnection);


SqlCommand cmdUserValidate = new SqlCommand(strSQL, conPayroll);


conPayroll.Open();


try


{


strPassword = cmdUserValidate.ExecuteScalar().ToString();


if (txtPassword.Text == strPassword)


{


blnValidUser = true;


}


}


catch (NullReferenceException)


{


}


catch (SqlException)


{


}


if (blnValidUser)


{


lblMessage.Text = "Congratulations. Successfull login!";


}


else


{


lblMessage.Text = "Login failed!";


}


}

Now we have the perfect system and no one would be able to get in without

having a valid user name and password, right? Wrong!

This is the kind of code that a hacker would type in the username text box.

A hacker could try something as shown in the above screenshot.

This is what was typed into the username text box.

dummy'; INSERT INTO ApplicationUser VALUES('Hacker',

'Password4Hacker'); --

After the concatenation, this is what SQL gets to execute:

SELECT Password FROM ApplicationUser WHERE UserName =

'dummy'; INSERT INTO ApplicationUser VALUES('Hacker', 'Password4Hacker'); --'

Never mind the “Login failed!” message, the hacker would have been successful

in adding a new record to your table, as below.

By injecting SQL code into login screens, the hacker would be successful in making entries into your table.

How would a hacker guess the name of the table you use to store your users?

That is a valid point, but would that be your only line of defense against the

hacker? The point is our hacker can type not only that INSERT statement I

illustrated, but can type anything!

What the hacker has been trying to do here is injecting code into the SQL,

taking advantage of the fact that you have been concatenating strings to

construct your SQL. This kind of attack is known as SQL injection.

Here is what I suggest you do to reduce the chances of an SQL injection

attempt succeeding.

• Inspect user input thoroughly. In the above example, the user name input
  
  should not have contained any spaces. It should not have exceeded 25
  
  characters. If the input looks suspicious, do not run the code. And alert an
  
  administrator immediately.
• One trick in security is to give the executing user the minimum set of
  
  privileges that are required for her to carry out her task. The less the
  
  privileges the executing user has, the less the damage a hacker can do.
• Avoid constructing your SQL by concatenating user input strings, if that
  
  is possible. Static SQL is safe.

The three points above are by no means exhaustive. The methods and techniques

used for SQL injection have unfortunately matured and have reached a level of

sophistication. The hacking technique I have shown here is elementary. The three

points I mentioned above should protect you from basic attacks, but please do

more research on the subject to build security into your applications.