1

Explained: SQL injection

Even though SQL injection is a type of attack that is relatively easy to prevent, it is one of the most common web hacking techniques. So, what’s it all about?

The basics

SQL is short for Structured Query Language and usually pronounced as “sequel.” SQL is a standard language used to query and change the content of databases. It was originally designed to perform business analyses. But with the implementation of product-specific application programming interfaces (API) and the growth of online applications, it quickly became more widely used.

Consider, for example, searching for a certain item in a big online store. What happens behind the scene is an SQL query on the databases containing products, pricing, and stock. And if you’re logged in as a customer, it might even include some of your preferences.

example SQL query

Example of a SQL query in a webstore

SQL injection

SQL injection is something that can happen when you offer the website visitors the option to initiate a SQL query without applying validation of the input. The effects are potentially horrible, since SQL injection might destroy your database or give the attacker access to parts of the database that you do not want publicly known. Attackers could be after personally identifiable information of your customers or the list of your suppliers.

While the most common use of SQL injection is for web applications, this is certainly not the only type of application that is vulnerable to these attacks. Basically, anything that asks for user input and uses a SQL-based database could be compromised this way without proper validation of the input, regardless of whether the input is stored in the database or initiates a query.

Validation

SQL injection is possible when the attacker applies any kind of code injection technique. These possibilities are called vulnerabilities because it makes the application vulnerable to nefarious SQL statements being inserted into an entry field and executed as commands. To execute a SQL injection, the attacker has to find and exploit a security vulnerability in an application, such as when user input is incorrectly filtered for string literal escape. This filtering is what we call validation. The input can be expected to have a certain format and should be rejected or sanitized if it does not match our expectations.

unsanitized php code

Above is a greatly exaggerated example of completely unfiltered php code. The input from the “name” field on the website goes straight into the SQL query. In this code, we can’t see what happens with the result of the query, but often it will be displayed in some form on the site. And with a little bit of trial and error, the attacker could retrieve the administrator’s username and change his password just by entering a string of valid SQL commands in the “name” field. That is why we call it SQL injection. An attacker can squeeze in his own strings of code.

Possible goals of the attack

There are several reasons why an attacker would use SQL injection.

  • Destruction: For whatever reason, the attacker wants to put the application or site out of business. You may have seen developers use the “drop table” when making fun of SQL-related accidents. The “drop table” command followed by the name of one of the tables in the database will make it delete the entire table with that name. Rebuilding such a table will be time-consuming—if it is possible in the first place.
  • Stealing information: Data breaches, anyone? The impact to your company is, at a minimum, the loss of trust of your customers and could completely put you out of business.
  • Feeding false information: An attacker could raise his credit or lead you to make business decisions based on false information. Both could cost you dearly.
  • Taking over control: An attacker that has control over your database may want to feed you false information, deny your access, or remove valuable information.

Encryption

Knowing what the attackers are after and which methods are used to attack should help you to prevent successful attacks.

For example, a common method to steal passwords is to trick your search results into displaying them. The only thing the attacker needs to do is see if there are any submitted variables used in SQL statements that they can pass unfiltered. These filters can be set to customize WHERE, ORDER BY, LIMIT, and OFFSET clauses in select statements. The union operator is used to combine the result-set of two or more select statements. If your database supports this construct, the attacker might try to add an extra query to the original one. This query could be used to list passwords or usernames. On top of sanitizing input, using encrypted password fields is another defensive weapon you can use.

Encrypting important data and building some filters to validate the input goes a long way. Obviously, the method of validation depends on the application itself and the coding language. Methods of attack that work in PHP might fail in ASP, for example. Excluding certain characters that are unexpected and/or irrelevant in a text field is a good start.

Is it more important to accommodate the customer who wants to be addressed as Mr. & Mrs. Jones or to avoid the risk of an attacker happily being able to use the “&&” symbols that are a valid command in SQL queries and many coding languages. It doesn’t have to be one or the other, by the way. You can accept the input of such characters as long as you make sure they are dealt with before they are added to the query commands.

Summary

SQL injection is the placement of unauthorized code into SQL statements and is one of the many web attack mechanisms used by hackers to steal data. It is perhaps one of the most common application layer attacks. Knowing what attackers are after and what methods they are using can help you protect your business from these types of attacks.

ABOUT THE AUTHOR

Pieter Arntz

Malware Intelligence Researcher

Was a Microsoft MVP in consumer security for 12 years running. Can speak four languages. Smells of rich mahogany and leather-bound books.