Thread: 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.

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.

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!

A hacker could try s0mething as shown in the above screenshot. This is what was typed into the username text box.

After the concaten'ation, this is what SQL gets to execute:

Never mind the "Login failed!" message, the hacker would have been suc¬cessful in adding a new record to your table, as below.

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 riot have contai ned 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 Jess 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.