Understanding SQL Injection and Creating SQL Injection Proof ASP.NET Applications

This article talk about what SQLinjection is, how can that effect the security of our websites and what steps should be taken to create an ASP.NET application SQLinjection proof.

Background

As ASP.NET developers, we often write dynamic SQLto perform some database operations. These dynamic SQLis some cases might be created by concatenating strings with user input. If we are not validating the user input and taking every input as is, then this kind of scenario poses a very serious problem of SQLinjection.

SQLinjection is the attack in which the user of the website will input some SQLcode as input which would result in creating a SQLstatement that developers didn’t intend to write. These SQL statements could result in unauthorized access, revealing secret user information and sometimes it could even wipe out the entire data lying on the server.

Using the code

Getting to know SQL Injection

Let us take this discussion a little further by looking into the bad coding practices that will make the application prone to the SQLinjection attacks. Let us create a simple table that contains username and password of the user for authentication.

userTable

Now I will create a small page that lets the user to enter his logincredentials and get them validated against the Users table.

loginSample

Note: Password should never be stored in plain text. This table contains password in plain text just for the sake of simplicity of this article.

The actual code that I will use to authenticate the user contains dynamic SQLthat is being created by concatenating strings. This code will return true if the useridand passwordare found in the database otherwise false.

For all the normal users this code will work fine. I can even test it using useridas sampleuserand passwordas samplepwd and this will work fine. For any other data except this it should say that authentication failed(since this is the only record in the table). The query that will get generated to test this input will be:

Now let us try to inject some SQLinto this page. Let me give hacker' or 1=1-- as usernameand anything in the password(even leave it empty). Now the resultant SQLfor this will become:

Now when we execute this query the 1=1 clause will always return true(and the password check is commented out. Now irrespective of whatever data user has entered this will SQLreturn a row making this function return true and in turn authenticating the user. So What I have done now is that I gained access to the website even when I didn’t knew the valid user credentials.

How can I curb this problem is something we will look into details in some time. But before that let us also look at one more example of SQLinjection just to get little more understanding.

In this second example we will assume that the malicious user somehow got hold of the database schema and then he is trying to manipulate the application to find some confidential information. Lets say we have a page that is supposed to show all the products that are assigned to a user in the organization.

Let us start by looking at the Producttable.

productsTable

Let us now look at the code that is retrieving this data:

Now if I call this function with the proper data(as normal users would do) then this will show me the results. i.e. If I call this page for sampleuserthe resulting query would be:

productsOK

Now let me use this query string with this page: userID=' UNION SELECT 0 AS Expr1, password, userID FROM Users -- . Once this data is used with the current code this will show me all the username and passwords from the database. The reason will be quiet clear once we look into the resulting query of this input.

productsBAD

Now we saw that how string concatenated dynamic SQLis prone to SQLinjection. There are many other problems that could be created by injecting SQL. Imagine a scenario where the injected SQLis dropping tables or truncating all the tables. The problem in such cases would be catastrophic.

How to Prevent SQL Injection

ASP.NET provides us beautiful mechanism for prevention against the SQLinjection. There are some thumb rules that should be followed in order to prevent injection attacks on our websites.

  1. User input should never be trusted. It should always be validated
  2. Dynamic SQLshould never be created using string concatenations.
  3. Always prefer using Stored Procedures.
  4. If dynamic SQLis needed it should be used with parametrized commands.
  5. All sensitive and confidential information should be stored in encrypted.
  6. The application should never use/access the DB with Administrator privileges.

User input should never be trusted. It should always be validated

The basic thumb rule here is that the user input should never be trusted. First of all we should apply filters on all the input fields. If any field is supposed to take numbers then we should never accept alphabets in that. Secondly, All the inputs should be validated against a regular expression so that no SQLcharacters and SQLcommand keywords are passed to the database.

Both this filtration and validation should be done at client side using JavaScript. It would suffice for the normal user. Malicious users cans till bypass the client side validations. So to curb that all the validations should be done at server side too.

Dynamic SQL should never be created using string concatenations.

If we have dynamic SQLbeing created using string concatenations then we are always at the risk of getting some SQLthat we are not supposed to use with the application. It is advisable to avoid the string concatenations altogether.

Always prefer using Stored Procedures.

Stored procedures are the best way of performing the DB operations. We can always be sure of that no bad SQLis being generated if we are using stored procedures. Let us create a Stored procedure for the database access required for our loginpage and see what is the right way of doing the database operation using stored procedure.

And now lets have a good version in our code using this stored procedure.

If dynamic SQL is needed it should be used with parametrized commands.

If we still find our self needing the dynamic SQLin code then parametrized commands are the best way of performing such dynamic SQLbusiness. This way we can always be sure of that no bad SQLis being generated. Let us create a parametrized command for the database access required for our Product page and see what is the right way of doing the database operation.

All sensitive and confidential information should be stored in encrypted.

All the sensitive information should be stored encrypted in the database. The benefit of having this is that even if somehow the user get hold of the data he will only be able to see the encrypted values which are not easy to use for someone who doesn’t know the encryption technique used by the application.

The application should never use/access the DB with Administrator privileges.

This will make sure that even if the bad SQLis being passed to the Database by some injections, the database will not allow any catastrophic actions like dropping table.

Note: Refer the sample application attached to see the working examples SQLinjection and how to curb them using parametrized commands and stored procedures.

Point of interest

This is a very basic article on SQLinjection. I have specifically focused on ASP.NET applications but same concept will apply for any ADO.NET application. This article is meant for the beginner’s who know nothing or too little about SQLinjection and making the applications SQLinjection proof. I hope this has been informative.

Download sample code for this article: SQLInjectionDemo