Database systems are essential for large and small businesses. These practical storage systems are used by banks and insurance companies to store account information and manage reservations in a structured way. Many companies use enterprise resource planning (ERP) systems to integrate and manage their operations, which would not work properly without databases. In the same way, most offers on the Internetthey would not be possible without database systems. Creating and maintaining these electronic filing cabinets implies a great effort, but guaranteeing their security represents the greatest challenge. The procedures for backing up and the implementation of fail-safe hardware are as important as the protection measures against external attacks. The so-called SQL injections, especially, represent a great threat to the traditional relational database model and the information implemented there.
What is SQL injection?
An SQL injection is defined as the exploitation of a vulnerability in relational database systems by accessing their data through the SQL language . The attacker takes advantage of those security flaws on the surface of the database that have not been correctly masked and that contain metacharacters such as the double hyphen, the quotes or the semicolon. These characters represent special functions for the SQL interpreter and allow external influence on executed instructions. It is common for SQL injections to be performed in conjunction with PGP and ASP programs, which depend on older interfaces.. There, some of the entries do not have the correct masking, thus becoming the perfect target for these attacks.
The specific use of metacharacters allows unauthorized users to access other SQL commands and manipulate entries to modify, delete or read data. In the worst case, it is possible that, through a direct injection of SQL commands, an attacker gains access to the command line of the command execution system and, therefore, to the entire server database.
SQL injection examples: this is how database attacks work
Because database servers can be easily tracked, in the same way that it is also easy to execute SQL injection attacks, this method has quickly become one of the preferred by cybercriminals around the world. These act with different attack patterns and take advantage, above all, of the traditional and current vulnerabilities of the applications that participate in the data management process. Next, and to explain how these attacks work, we have collected some examples of the most common methods to apply an SQL injection and the preventing SQL injection attacks.
Example 1: Access through a badly masked user entry
Usually, before a user can access a database, they need to authenticate. For this purpose there are scripts that, for example, create a login form composed of a username and password. Thus, the user fills in the form and the script is responsible for verifying if said entry exists within the database. By default, the database has a table with the name “users” as well as the lines for “username” and “password”. For any web application, the command lines (pseudocode) that allow access to the web server would be:
uname = request.POST['username']
passwd = request.POST['password']
sql = "SELECT id FROM users WHERE username='" + uname + "' AND password='" + passwd + "'"
database.execute(sql)sql = "SELECT id FROM users WHERE username='' AND password='password' OR 1='1'Example 2: Spying data through ID manipulation
Requesting information from a database with an identifier is a practical and common method, as well as a potential gateway for an SQL injection attack. For example, the identifier transmitted in a URL allows the web server to identify what information should be retrieved from the database. The PHP script for this will look like this:
<?php
#Datenbankabfrage anhand einer ID
$id = $_REQUEST['id'];
$result = mysql_query("SELECT * from tabelle WHERE id=$id");
# Anzeige des Ergebnisse ...
?>SELECT * FROM tabelle WHERE id=22 or 1=1Because it is common that the manufacturers of the programs that communicate with the databases do not offer a sufficient level of security, mainly, any page and web application that uses SQL as a database language is vulnerable to SQL injection attacks . Additionally, the vulnerabilities discovered are not usually a secret for the user community on the Internet, which has generated the creation of web pages that collect information about these vulnerabilities and that, at the same time, explain to cybercriminals how to find web projects for it through Google search. The error messages Standard allow you to check quickly if the specified results represent a potential target. Below we show you how:
[Domainname].de/news.php?id=5‘“ Query failed: You have an error in your SQL Syntax…”
In Spanish: “Error in the request: the syntax of your SQL is incorrect …”. Using similar methods it is also possible to obtain information such as the number of columns, the names of the tables and columns, the SQL version and even the user names and their passwords. There are a large number of free tools that allow you to search and automate the direct injection of malicious commands in SQL. In any case, those who take advantage of these vulnerabilities can incur legal penalties, unless they are their own databases and their manipulation is done for preventive purposes and to control their security.
How to protect your database from an SQL injection
There are different measures to prevent SQL injection attacks and protect the database. For this, it is necessary to familiarize yourself with all the components involved: the server, the individual applications and the database management system.
Step 1: Monitor automatic modifications to applications
Check and filter the methods and parameters used by applications to access the database. The transferred data must always be in the expected data format. If, for example, a numerical parameter is required, you can check it using a PHP script including the is_numeric () function . The filtering process is responsible for ignoring special characters. Another important point is to ensure that applications do not generate any external error messages that can reveal information about the system or the structure of the database used.
The so-called Prepared Statements have become a common practice that can be used with many database management systems. These predefined instructions were originally created to execute frequent requests, reducing the risk of SQL injection attacks due to their structure. This is mainly because the instructions with parameters transmit the actual SQL command to the database, separated from the parameters. Thus, it is the database management system itself that, in the end, executes them together and automatically masks the special characters.
Step 2: Provide complete protection to the server
Logically, the security of the server on which the database management system runs plays a fundamental role in preventing SQL injection attacks. The first security measure is the hardening of the operating system according to the following pattern:
- Install and activate only those applications and services that are really relevant to the database.
- Remove all user accounts that are not necessary.
- Be sure to install all updates relevant to the system and program.
The higher the requirements related to the security of your web project, the greater the need to install intrusion detection systems (IDS) or intrusion prevention systems (IPS). These work with different recognition systems and are aimed at detecting attacks against the server on time and alerting your administrator and, in the case of IPS, taking appropriate measures automatically. Another recommended protection measure is also the implementation of a port in the application layer (Application Layer Gateway) that directly monitors, at the application level, traffic between applications and the web browser.
Step 3: Shield the database and use more secure codes
In the same way as an operating system, a database should be free of irrelevant external factors and be updated regularly. To do this, it is advisable to deactivate all inactive user services and accounts, as well as to eliminate all stored procedures that are not necessary. Set up a database account that is intended only for access from the web and that has minimal access rights. Remember to store all confidential information, for example, passwords, in an encrypted way in the database.
As regards Prepared Statements, it is not recommended to use the PHP mysql module, but rather mysqli or PDO. This way you can protect your system with security codes. Thus, for example, the PHP script function mysqli_real_escape_string () prevents special characters from being transmitted to the SQL database in its original form and masks them by itself. If you expand, for example, the following code:
$query = "SELECT * FROM users
WHERE username= '" . $_POST['username'] . "'
AND password= '" . $_POST['password'] . "'";$query = "SELECT * FROM users
WHERE username= '" . mysqli_real_escape_string($_POST['username']) . "'
AND password= '" . mysql_real_escape_string($_POST['password']) . "'";problematic characters will be replaced with the secure SQL variant (\ ').The relationship between Bobby Tables and SQL injection attacks
The bobby-tables.com website has addressed the issue of information infiltration by an unauthorized user in a database in a very peculiar way: with the xkcd web comic [Visit the xkcd webcomic about SQL injections] (http://bobby-tables.com/). The comic shows a mother who receives a call from her son’s school, lovingly named Bobby Tables. The supposed teacher asks him if, in fact, his son is called “ Robert ‘); DROP TABLE Students; – – ” and, in the end, understands the reason for the call: the attempt to create an entry in the student database with Robert’s name resulted in the total loss of the information in the database. Robert’s mother can only convey a few words of encouragement and expresses her hope that the school has learned from her mistakes and that, in the future, she will clean the entries in her database.
The comic is intended to illustrate the fatal consequences of unauthorized or uncontrolled access by a user to a database. In the case of the school database, the following happened more or less:
Student names are stored in a table called Students . Each time a new student arrives at school, his or her name is entered in that table using the following code:
$sql = "INSERT INTO Students (Name) VALUES ('" . $studentName . "');";
execute_sql($sql);INSERT INTO Students (Name) VALUES ('Paul');INSERT INTO Students (Name) VALUES ('Robert'); DROP TABLE Students;––');
Although in this way the Roberts entry was added to the table, the DROP TABLE instruction made sure that the table was completely removed . Due to this unmasked entry in the database, the mother (who is hiding behind the name of little Bobby Tables) was able to carry out a successful SQL injection.
However, the solution of “keeping the code clean manually” proposed in the comic is not foolproof. Because manual masking of characters is prone to errors , to protect your database from deceptive attacks and SQL injections it is advisable to use some of the solutions mentioned above, whether they are instructions with masked parameters or functions such as mysqli_real_escape_string ().
