Introduction

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

Background

As ASP.NET developers, we often write dynamic SQL to perform some database operations. These dynamic SQL is 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 SQL injection.

SQL injection is the attack in which the user of the website will input some SQL code as input which would result in
creating a SQL statement 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 SQL injection attacks. Let us create a simple table that contains username and password of the user for
authentication.

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

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 SQL that is being created by concatenating
strings. This code will return true if the userid and password are found in the database otherwise false.

For all the normal users this code will work fine. I can even test it using userid as sampleuser and password as 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 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 SQL return 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 SQL injection 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.

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 sampleuser the resulting query would be:

select * from Products where AssignedTo = 'sampleuser'

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.

Now we saw that how string concatenated dynamic SQL is prone to SQL injection. There are many other problems that
could be created by injecting SQL. Imagine a scenario where the injected SQL is 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 SQL injection. There are some thumb rules that should
be followed in order to prevent injection attacks on our websites.

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

Dynamic SQL should never be created using string concatenations.

Always prefer using Stored Procedures.

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

All sensitive and confidential information should be stored in encrypted.

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 SQL characters and SQL command 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 SQL being created using string concatenations then we are always at the risk of
getting some SQL that 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 SQL is being generated if we are using stored procedures. Let us create a Stored procedure for the
database access required for our login page and see what is the right way of doing the database operation
using stored procedure.

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

If we still find our self needing the dynamic SQL in code then parametrized commands are the best way of performing
such dynamic SQL business. This way we can always be sure of that no
bad SQL is 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 SQL is 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 SQL injection and
how to curb them using parametrized commands and stored procedures.

Point of interest

This is a very basic article on SQL injection. 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 SQL injection and making the applications SQL injection proof. I hope this has been informative.

Share

About the Author

I Started my Programming career with C++. Later got a chance to develop Windows Form applications using C#. Currently using C#, ASP.NET & ASP.NET MVC to create Information Systems, e-commerce/e-governance Portals and Data driven websites.

My interests involves Programming, Website development and Learning/Teaching subjects related to Computer Science/Information Systems. IMO, C# is the best programming language and I love working with C# and other Microsoft Technologies.

Comments and Discussions

You list 5 rules, but there is only 1 rule to prevent SQL Injection. You only need to use parameters for the user values. You do not need to validate user input if you use parameters. Since the injection is prevented by parameters, it does not matter if you use stored procedures or dynamic sql. If you concatenated values inside your stored procedure, you would still have SQL Injection. If a table contains SQL Injection code and you concatenate those values you would also have SQL Injection. If you parameterize in your stored procedure you will prevent SQL Injection.

Encryption and Administrator privileges are good advice, but not required to prevent SQL Injection.