In this article I'll explain you what SQL injection attack is and then I'll show you ways how to prevent it on your own website.
SQL injection is very common form of an attack and it's relatively easy to perform. There are many actual examples in history where attack using this technique was successful and caused considerable losses. That is why it's important that you learn what it is and how to prevent it.
SQL injection takes advantage of incorrect user input filtering or lack of strong typing. Basically all you need to perform this attack is a simple web browser. Many web pages take user input as parameter for SQL query and if this input is not checked before executing query on SQL database it's very easy to use it in a way that was not indented. The most common example of injecting SQL code is through log-in form.
Basic examples of SQL injection
Suppose that we have on a website log-in form and that after logging in we show our users some information that is only for them. The way we construct SQL query on such a website could look like this:
Then if we type login like john and password like pass123 our query will look like this:
So far so good, the website works just the way it was intended - user john provides valid password and gets requested information. But consider the situation when another user wants to get confidential information from user with login "john". In a system that is not well designed and vulnerable to SQL injection all he has to do is to type in log-in form password like:
Yeah, I know that this password is very strange but let's take a look at SQL query that is created:
Thus we have created completely valid SQL query that will allow us to view confidential content meant for user with login "john" without knowing his password.
There's also another, equally simple way to obtain the same result. If you type the following user name then the password is even not important and it can be whatever you like (it will be omitted while executing SQL query):
In SQL two dashes mean commentary so the text that follows these two dashes will be ignored. Let's take a look at the SQL query that will be produced:
Because of the commentary this query can be reduced to the one like this:
Thus we are able again to gain unauthorized access to information that was meant only for user with login "john".
Destroying unprotected database
In previous examples I presented the most basic ways of getting access to confidential data but the attacker might also have other intentions. Using SQL injection you can destroy some data from database or even delete whole database.
If database is not protected good enough it could be possible to input as many sql queries as you like just by using semicolon separator - ";". Let's take again our previous website with log-in form as an example. You could input password like the following one:
Then whole SQL query will look like:
The first query will probably return nothing unless the password of user john is aaa, but that doesn't matter. What's important is second query that will delete whole users data table from database.
As you can see altering unprotected databases can be very easy using simple SQL injection techniques and this above is just an example, using it you can do a whole bunch of other ugly things.
If data that is submitted by the user is not validated before executing SQL query it's very easy to perform attack using non-string fields like for example numeric field.
Suppose that we have query like this one:
If productId isn't validated before it is used in SQL query you can type there a text like for instance this one:
The whole SQL query will look like this:
And the result will be of course deleting whole products table. As you can see validating input is very important, because thus you can prevent performing SQL injection attack on your database.
There are many more various techniques to use SQL injection attack. I covered in this article only the most common and simple ones so you could just get an idea how does it work. The purpose of this article is not to learn you how to attack a website but how to protect one. So in the next part of this article I'll describe how you can protect your web site against this kind of attacks. However please note that you can never be sure if your website or any other product is fully protected because there can always be a glitch that somebody can exploit.
How to protect your website against SQL injection
As you probably noticed in the previous part of this article SQL injection attack can be very harmful, you can even destroy whole database just by inserting some SQL code into user input fields on a website. Generally there is no one foolproof way of avoiding SQL injection and you can never be 100% sure that your website is secure but if you follow few basic rules and strategies that I'll describe you can minimize risk of being successfully attacked to minimum.
Filter and validate user input
One of the most important things to remember is never to trust user input. That's why you should always and I mean every single time validate input. Sometimes it's relatively easy when you have only a very limited set of characters that are allowed in a given input. Good example of such an input is phone number. Generally there can be allowed only numbers and depending on the convention maybe dashes, white spaces or plus sign. All other characters should be discarded and an error should be displayed to users informing that the format of a phone number is incorrect.
There are situations however when validating an input is not as straightforward as in the previous example. In input fields where the set of allowed characters is not very limited you should at least try if it's possible not to accept characters such as:
- ; that is used as query separator in SQL
- -- that indicates beginning of the commentary in SQL
- ' that is used for strings in SQL
You can also use escape functions such as mysql_real_escape_string from PHP. It translates unwanted characters that have special meaning in SQL to its proper form. For example ' character has to be replaced with two quote characters '' in order to work properly. In C# you can use code like this to escape quote ' character:
Think about a form of possible input strings and try to restrict it as much as possible. If you know that an input can't have more than a certain number of characters (like postal code) than validate also length of input and discard it if it's bigger than a certain value. Always try to validate and filter as much as possible.
Use bound parameters
This technique can significantly improve security of your web application. Generally you create a SQL query with special placeholders like ? for parameters used in your query so SQL code can look for instance like this one:
Then to use it all you have to do is just bind parameters. In our example there is only one parameter - id but of course there can be more parameters.
The advantage of binding parameters is very simple - even if you use characters that are potentially harmful like semicolons, dashes or quotes it doesn't matter because it's only a parameter and it can't be treated as SQL code even if you use SQL injection techniques described in this article.
Binding parameters depends of course on language and database that you use but general scheme is similar. Take a look how to do this in pseudocode:
It's even better if in some languages you have also option to specify type of parameter that you're binding.
Remember that using bound parameters is one of the best techniques of preventing SQL injection attack so I strongly advise using it.
Use stored procedures
In some cases you can even completely get rid of SQL code from you program and just use stored procedures instead. Stored procedure is simply a piece of code that will be executed after calling this procedure. Take a look at this sample to get the general idea how it works:
Now to execute it all you have to do is call it with id parameter:
Another advantage of using stored procedures is performance, in many cases depending on the database you're using it can be faster than a standard query.
The most important thing is that you remember never to use admin rights for web based application. Give your user as little rights as possible in other words user rights should allow him to do only what's necessary and nothing more.
You can create a few levels of users in your application - for example one level for all users who entered your web site and another level for users who are already logged-in adding certain right steadily if there's need for it.
Securing web server
There is also an option of securing web server that hosts your application like for example installing special modules such as mod_security for Apache. These modules can filter for instance potentially harmful requests on server. There are however some drawbacks of this strategy like possible decrease in performance or filtration of requests that are not harmful.
SQL injection attack is a threat that cannot be disregarded because results of such an attack can be devastating. What's more as you can see in the first part of this article SQL injection attack is relatively easy to perform. If you follow security guidelines described in the second part of this article you can significantly reduce risk of being successfully attacked.