What are SQL-injections? How can they affect my site? How does it happen and how can I avoid it?

Your site may already be under attack, but the attacker is only using your site to attack your users! This is done using something called SQL-injections.

Since Firefox (2 and 3) and MSIE 7 started using Google’s (and others) system for blocking sites that produce harmful web pages the problem with SQL-injections have been put on the spot.

What happens is that an attacker hacks a site by placing their own SQL-code into the database of the victim system. A system open to SQL-injections may be attacked in basically two ways. Either the attacker performs a DOS (denial of service) attack. This could be done by deleting all the tables or doing something else harmful to the site, effectively bringing the whole site down.

The other form of attack that can be performed on systems open to SQL-injections is far more sneaky and may not be detected at all by the site owner or the site visitors. This form of attack consists of planting client side browser code in the database making all visitors run client side code that will infect their computer with malware or viruses. This malicious software may do everything from listening in on traffic between the client (web browser) and bank sites, to connecting the client system to a botnet.

Needless to say, attacks using SQL-injections has become a problem not so much for the owner of the originally defunct site as for the visitors to said site. Although users of the web should not underestimate the consequence of a good virus protection, system update policy and secure browsing policy.

Since the owner of the vulnerable site won’t notice any detour from business as usual and neither will most infected clients, nobody is the wiser to the problem.

This is why Google (and others) have started evaluating (and flagging) sites with bad content, and why Firefox and MSIE (and probably others) have started blocking them.

What are SQL-injections?

The short explanation (hinted above) is that an SQL-injection is an attack where the attacker inserts their own SQL code into the attacked system. This alters the database content of the attacked system. The attack either wrecks havoc by destroying part of or the whole system, or by inserting virus spreading code onto the pages of the attacked site if their content is stored in the database.

How is an attack using SQL-injections performed?

There are two major ways of doing this.

The first method of performing an SQL-injection is by manipulating the input to different kinds of forms on the site. This could be search forms, contact forms, and other places where the user can put information into the system.

To understand this attack you need a basic understanding of SQL.

Imagine we have a database table with products, each having a color. We want to list the name of all products that are ‘green’. This would be done with the following SQL statement:

SELECT name FROM products WHERE color = 'green';

Each database command is ended with a semicolon (;). All literal strings are enclosed by single quotes (‘). So issuing two commands in a row would look something like:

SELECT name FROM products WHERE color = 'green';
UPDATE products SET hasBeenSearchedFor = 'true'
    WHERE color = 'green';

Notice the semicolons and the single quotes. You do not need to understand what the commands do in order to understand the rest.

Now imagine that we get the value ‘green’ from an application the user can input data into. As long as the user types things like green, red, blue everything is safe, but what would happen if the user typed in the following as a color?

'; do harmful thing to database; '

In an unprotected system the SQL-string is usually tacked together one part after the other without checking if the search parameter is valid or sane. Imagine the search command is built in the following way. (Double quotes are used in most systems to create strings, they are not to be confused with the single quotes of the database query):

sql = "SELECT name FROM products WHERE color = '"
      + colorValue + "'";

If a user would put the above value into the color search, the result can be seen below.

"SELECT name FROM products WHERE color = '" +
          "'; do harmful thing to database; '" + "'"

What has happened here is that the variable “colorValue” has been replaced with the value from the user.

This would in turn be interpreted and sent to the database as the following commands:

SELECT name FROM products WHERE color = '';
do harmful thing to database;
''

I.e. select all products with an empty color value, then “do harmful thing to database”. After that the SQL-server will encounter the empty quotes and issue an error.

If the database question is executed in a transaction all changes will be rolled back. Unfortunately this is a rather weak defense against SQL-injections since it is possible to input values that will create three valid commands easily. (I’ll leave that up to the fantasy of the reader!)

Using SQL-injections with numerical input

The second way of performing an SQL-injection attack is to tack on values to numerical input to the system. Pages that display products, user profiles, or any other information easily mapped to a row in a database table are usually candidates for this kind of attack.

Imagine a page that displays a product profile. It might be called with the following URL:

http://www.mysite.com/showProduct?productid=123

The “productid” value will then be passed on to make the following database question:

sql = "SELECT name FROM products WHERE id = " + productid;

Here the attacker does not even have to add a single quote to get an extra SQL-command. All they need to do is add a trailing “;” and they’re in. The only complicating factor is that the URL used to call the system will have to be URL-encoded in order for this attack to work. However, that’s far from a problem even for an unskilled attacker.

How do you protect against SQL-injections?

The key concept is: never, NEVER, NEVER, trust input from a user!

That’s right. NEVER TRUST THE USER.

Don’t ask “what-if a user tries to gain access to our system?”

Say “WHEN the user tries to gain access to our system, then we’re going to….”

The short, technical answer (for Java, .NET and similar languages) is to NEVER build SQL-strings like shown above. Use “prepared statements” or similar constructs where the input variables can be tested by JDBC (or ADODB or LINQ or similar framworks).

Another safe way to go is to use stored procedures. These are not just optimized for speed and performance, they are also, almost always strongly typed, disallowing the kind of problems displayed above. Although beware, you can use string concatenation in stored procedures as well. So, simply using stored procedures will not do the trick unless they are used correctly.

If neither of these are feasible always make sure the input’s single quotes are filtered away or “escaped”. Escaping a single quote means that all databases have support for using single quotes as values in a table column, if you prepend them with a backslash () or use double single quotes (”, that’s two single quotes). Do this programatically before sending the data on to the database.

Protecting against attacks on pages with numerical input is even simpler. If there’s a number input value, make sure it is numerical by translating it into a number before passing it on to the database. Input from web applications are almost always sent as strings, but if a value should be a number, make sure it is!

Part from this a sound backup and restore policy is a sure way to protect hours of time invested in putting information into the system, as well as users trust in the system. It will also cut down the time it will take to get a blocked site back up on its feet.

Read more about SQL-injections here: http://en.wikipedia.org/wiki/SQL_injection