SQL injection

In large and medium-sized companies, database systems are indispensable: Practical storage systems are structured to store and manage banking and insurance account information and bookings. Many companies plan their resources using enterprise resource planning software that can’t function without a database. Even the majority of systems offered online would not be possible without database systems. Setting up and maintaining an electronic index is very complex – but the biggest challenge is ensuring their safety. Back-up methods and using failure-resistant hardware is just as important as using comprehensive safeguards against external access. So-called SQL injections are one of the greatest dangers to traditional relational database models and the implemented information.

What is an SQL injection?

SQL injections are the exploitation of a security flaw in a relational database systems that rely on the SQL language in their data input. The attacker makes use of user input in the database interfaces that isn’t sufficiently masked and contains metacharacters such as the double dash, quotes, or semicolons. These characters represent special functions for the SQL interpreter, allowing external commands to be influenced internally. An SQL injection occurs often in cooperation with PHP and ASP programmes that rely on old interfaces. Inputs sometimes don’t get the necessary masking, making them the perfect target for an attack.

With the targeted use of function symbols an unauthorised user can enter additional SQL commands and manipulate the entries in such a way that enables them to modify, delete, or read data. In extreme cases it’s even possible that the attacker can access the command line of the system and so gain access to the entire database server.

SQL injection example: How a database attack works

Since vulnerable databases are quickly and easily tracked and SQL injection attacks are just as easy to run, the method is one off the most popular in the world. Criminals can carry out various different attack patterns and make use of current as well as familiar security gaps of the data management processes used by applications. To explain how exactly an SQL injection works we’ve put together examples of some of the most popular methods for you.

Example 1: Access via inadequately masked user input

For a user to access a database they usually need to authenticate first. There are scripts for this that, for example, present a log-in form consisting of username and password. The user fills out the form and the script verifies whether a corresponding entry exists in the database. Usually there’s a table for this in the database with the name “users” as well as the row “username” and “password”. For any web application, the appropriate script lines (pseudocode) for web server access might be as follows:

uname = request.POST['username']
passwd = request.POST['password']
sql = "SELECT id FROM users WHERE username='" + uname + "' AND password='" + passwd + "'"
database.execute(sql)

An attacker now has the possibility to manipulate the password field by means of an SQL injection, for example, by typing password' OR 1='1 which leads to the following SQL query:

sql = "SELECT id FROM users WHERE username='' AND password='password' OR 1='1'

In this way the attacker gains access to the entire user table of the database, because the password is always true (1='1'). If they log in as the administrator they can make any changes to the entries that they want. The username field can also be manipulated in this way.

Example 2: Data spying by means of ID manipulation

Querying information from a database by ID is a practical and common method, but also opens a possible gate for SQL injection. For example, a webserver knows through a transmitted ID detail in a URL which information it should recall from the database. The corresponding PHP script looks like this:

<?php 
    #Datenbankabfrage anhand einer ID 
    $id = $_REQUEST['id'];
    $result = mysql_query("SELECT * from tabelle WHERE id=$id");
    
    # Anzeige des Ergebnisse ...
     ?>

The expected URL has the form …/script.php?id=22. In the specified case, the table entry with the ID 22 will be called up.  If an outside user has the chance to manipulate this requesting URL, they can send the request …/script.php?id=22+or+1=1 to the web server instead, and the resulting msyql_query call reads out not only the entry with the ID 22, but instead all of the data:

SELECT * FROM tabelle WHERE id=22 or 1=1

How attackers find vulnerable database systems

In principle, every website and every web application is susceptible to an SQL injection, provided SQL is used as the language of the database – because all too often the manufacturers of the programmes that communicate with the database don’t ensure a sufficient degree of security. Discovered weak spots don’t remain a secret in the vast world of the internet, and, for example, there are pages that display information on current security gaps reveal to criminals how they can find suitable web projects using Google search. Thanks to standard error reports, it doesn’t take long to verify whether the indicated hits are actually a potential attack target. You can simply append an apostrophe to a URL that contains the ID parameter (as in the following example):

[Domainname].de/news.php?id=5‘

A vulnerable website sends an error message back in the following form:

“Query failed: You have an error in your SQL syntax…”

Similar methods can also be used to output column number, table and column names, SQL version, or even users and passwords. The majority of free tools allow you to automate searches and subsequent SQL injections. In any case, the exploitation of known security gaps is punishable, in the respect that the database system doesn’t belong to itself and can be submitted to a security check.

How to protect your database from SQL injection

There are various different methods that you can employ to prevent SQL injection attacks on your database system. You should deal with all of the components involved – the server and individual applications as well as the database management system.

Step 1: Monitor the automatic entries of the application

Test and filter the methods and parameters that the connected applications use when entering the database. The transferred data should always be in the expected data type. If a numeric parameter is required you can check it using a PHP script with the is_numeric() function. When filtering, it’s necessary to ignore corresponding special characters. Another important point is to make sure that the applications don’t output any external error messages that reveal information about the system or the structures of the database.

Meanwhile, so-called prepared statements are popular practices as they can be used with many database management systems. These pre-defined statements were originally used to perform more frequent queries but because of their structure they also reduce the risk of SQL injection. Because the parameter statements transfer the actual SQL command from the parameters separately to the database. It’s only the database management system itself that links them together and automatically masks the crucial special characters.

Step 2: Provide comprehensive server protection

The security of the server on which you run your database management system also obviously plays a large role in SQL injection prevention. The first step is the solidification of the operating system according to the established pattern:

  • Install or enable only those applications and services that are relevant to the operation of the database.
  • Delete all user accounts that aren’t needed.
  • Make sure that all relevant system and programme updates are installed.

The higher the demands of your web project’s security, the more likely you are to consider the use of intrusion detection (IDS) systems or intrusion prevention (IPS) systems. These work with various different detection systems to detect attacks on the server at an early stage, issue warnings, and, in the case of IPS, initiate corresponding countermeasures automatically.  An application layer gateway, which monitors the traffic between applications and the web browser directly at the application level, can also be an effective safeguard.

Step 3: Solidify the database and utilise secure codes

Like your operating system, the database should be cleared of all irrelevant factors and updated on a regular basis. To do this, remove all the stored procedures that you don’t need and disable all unnecessary services and user accounts. Set up a special database account, designed to be accessed from the web, which has minimal access rights. Store all sensitive data such as passwords in an encrypted form in your database.

When it comes to prepared statements, it’s strongly recommended to not use the PHP module mysql and instead choose mysqli or PDO. In this way, you can also protect yourself with secure codes. For example, the mysqli_real_escape_string() function in PHP scripts prevents special characters from being passed to the SQL database in the original form and instead masks them. If you, for instance, extend the following codes lines

$query = "SELECT * FROM users 
WHERE username= '" . $_POST['username'] . "' 
AND password= '" . $_POST['password'] . "'";

by the following function

$query = "SELECT * FROM users 
WHERE username= '" . mysqli_real_escape_string($_POST['username']) . "' 
AND password= '" . mysql_real_escape_string($_POST['password']) . "'";

then problematic characters of user input are replaced by the secure SQL variant (\').

What Bobby Tables have to do with SQL injection

The homepage of bobby-tables.com features an xkcd webcomic devoted to the topic of inviting database user input. The comic shows a mother who’s received a call from her son’s school – affectionately called Little Bobby Tables. She confirms for the school that her son is actually called Robert'); DROP TABLE Students;– – and then learns the reason for the call – while trying to create an entry for Robert in the student database, the school accidentally caused the entire data set to be deleted. Robert’s mother simply replies that she hopes the school has learned its lesson and will structure their databases better in the future. The comic is intended to clarify the fatal consequences of unintended or untested user input into a database. In the case of the school database, the situation is probably as follows: Student names are saved in a table with the name Students. As soon as a new student comes to the school, they will be entered into the table, using corresponding code that looks something like this: 

$sql = "INSERT INTO Students (Name) VALUES ('" . $studentName . "');";
execute_sql($sql);

This is an ordinary SQL-INSERT command that inserts the contents of the $studentName variable into the Students table. The second part of the code is used to pass the assignment to the database (execute_sql). For a student named Paul, the SQL assignment will work as desired. The respective code

INSERT INTO Students (Name) VALUES ('Paul');

inserts Paul into the Students table. Now the same statement should make the entry for Bobby Tables, which leads to the following code line:

INSERT INTO Students (Name) VALUES ('Robert'); DROP TABLE Students;––');

Robert’s entry was added to the table in this way but the DROP TABLE statement contained in the name had the result that the entire table was subsequently deleted. Because of the non-masked entry into the database the SQL injection of the mother (hiding behind the name of little Bobby Tables) was successful.

But the solution presented in the comic – to clean up the code by hand – isn’t really recommended. Because manual character masking is very error-prone, solutions such as parameterised statements or masking functions like mysqli_real_escape_string() should be given preference to protect your database from malicious attacks and prevent SQL injection.

Was this article helpful?