Securing Apps Against SQL Injection

We take you through a simple technique to illustrate how hackers can inject SQL code into your database; and we provide tips to secure your apps from such attacks

Sunday, February 01, 2009

Print

Comment

Email

Digg

Del.icio.us

Reddit

Twitter

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.

Page(s)   1