News

What is it?
SQL injection is the illegal insertion of malicious code into text fields on forms, with the intention of manipulating or stealing data from a database. Knowing that the value will be processed as part of an SQL statement, an attacker can structure the string they enter in order to alter the end statement that is processed. Not knowing any different, the SQL server will execute the statement regardless. Let’s take a look at an example:

 

 

This is a very simple form with one input. The user is expected to enter their name into the text input. Once submitted, its value is passed to an SQL statement that will search a table called customers. In my example this would look like the following:

SELECT * FROM customers WHERE firstname = ‘Peter’;

An attacker would look to manipulate this opportunity with SQL injection, inserting a statement into the textbox that could look like the following:

 

 

The generated SQL statement would appear as:

SELECT * FROM customers WHERE firstname = ‘Peter’; drop table customers- –‘;

You can see from the two semi-colons that two separate SQL statements would be executed – the first selecting data from the customers table as expected, the second then deleting the customer table. The double hyphen on the end represents a comment, which means any additional SQL added to the end of the statement will be ignored (if the attacker didn’t include this, the additional SQL could mean an error is thrown if it messes up the structure of the statement).

Of course, the attacker needs to know the name of specific tables. But for an experienced hacker, this would be fairly easy to acquire.

What can attackers do?
Expert attackers can damage your database in a number of ways. The following are a few examples:
– Create new super users with maximum rights. With this, they can login to your site as an admin and damage/steal further.
– Get or reset passwords of existing registered users. They can then login as this user and view their private information.
– Wipe all data from specific tables/all tables. This would be done for malicious reasons rather than personal gain.
– Steal sensitive information – adding their own select statement and specifying * (all) rather than specific columns, they can pull all data back from it.

How can it be prevented?
Thankfully there are a number of precautions developers can take. The main, which should be considered normal practice when developing software, is to validate everything – even fields expecting simpler data such as postcodes or telephone numbers. By ensuring all fields’ values are in the expected format and do not exceed their expected length, the amount of SQL injection attackers can perform is limited. Validation must be performed on the server side, as client-side languages such as JavaScript can easily be turned off.

As well as validating inputs, developers should also consider the automatic removal of common SQL injection characters. When possible, entered values containing semi-colons, single and double quotes (unless they are escaped), and double hyphens should be rejected, because, as highlighted in the example above, they have special meanings in SQL.

Developers should also consider using stored procedures to manipulate the database, rather than doing so at code level. Values inserted into form fields would need to meet a certain format in order to be accepted as a parameter for a stored procedure. Those including SQL Injection values would be rejected.