What is an SQL injection?

SQL injections represent an important threat to relational database models and the information stored there. Complete protection against these unauthorized external access, made possible by safety flaws, is therefore essential.

What is an SQL injection?

An SQL injection is the exploitation of a security flaw in relational database systems that use the SQL query language for data entry. The attacker uses user inputs unsecured and containing metacarctors Like the double dash, the quotes or the semicolon. These characters have special functions for the SQL interpreter and make it possible to influence the commands executed from the outside. The SQL injection often occurs as part of PHP and ASP programs, based on obsolete interfaces. The entries then sometimes do not receive the necessary masking and are therefore the perfect target for an attack.

With the targeted use of function characters, an unauthorized user can thus introduce other SQL commands and manipulate the inputs so that he can Edit, delete or read the data. In the most serious cases, it is even possible that an attacker obtain in this way Access to the command line of the system performing commands and thus to the entire database server.

As vulnerable database servers are quickly detected and SQL injection attacks are just as easy to execute, this method is one of the most popular. Criminals use Different attack models And exploit both recent flaws as well as vulnerabilities known for a long time in applications involved in data management. To illustrate the exact functioning of an SQL injection, here are two typical methods.

Example 1: Access thanks to insufficiently masked user entry

In order for users to access a database, they usually have to authenticate. For this purpose, there are scripts which present for example a connection form Composed of a username and password. Users fill out the form and script Then check if there are corresponding inputs in the database. By default, a table named users is created in the database, with the columns username And password. For any web application, the script lines concerned (pseudo-code python) for access to the web server could 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 of specifically handling the field SQL injection passwordby entering for example password’ OR 1=‘1which leads to the following SQL request:

sql = "SELECT id FROM users WHERE (username=‘admin’ AND password=‘‘) OR ‘1’=‘1’"

In this way, he obtains full access to the entire table of users of the database, since the password is always true (1=‘1’). Henceforth, if he connects as a administratorhe can make all the changes he wants on the entrances. The field of username is just as vulnerable to this type of manipulation.

Example 2: Spy data by manipulating the ID

Asking for information on an ID authentication database is a common method. However, this is a front door For SQL injections. A web server can then know, thanks to the ID data transmitted by the URL, what information of the database it must call. The corresponding PHP script looks like the next:

query("SELECT * FROM table WHERE id=$id");
    while ($row = $result->fetch_assoc()) {
        echo print_r($row, true);
    }

The expected URL is in the form .../script.php?id=22. In this specific case, the entrance to the table With the ID « 22 » will be called. If an external user has the opportunity to handle the requested URL and send the web server the request .../script.php?id=22+OR+1=1the resulting call leads to reading all the data on the table:

SELECT * FROM table WHERE id=22 OR 1=1;

In general, all web sites and applications that use SQL databases without prepared requests (PREPARED Statements) or other protective mechanisms can be vulnerable to SQL injections. Discovered vulnerabilities do not remain secret in the immensity of the Internet! In particular, there are information sites that have current security flaws and reveal to criminals how to find vulnerable web projects thanks to a simple Google search. If a website returns detailed SQL error messages, criminals can use them to identify potential vulnerabilities. Just add an apostrophe to an url containing an ID parameter, like here:

[Domainname].fr/news.php?id=5’

A vulnerable website will then send a error message similar to it:

Query failed: You have an error in your SQL syntax...

Or in French: « failure of the request: your SQL syntax has an error … ». Similar methods also make it possible to display the number of columns, the names of the tables and columns, the SQL version or even user names and passwords. Various tools also allowAutomate the search for vulnerabilities and the execution of SQL injections.

You can take different measures to prevent SQL injection attacks on your database system. To do this, you must Examine all the components involved : the server, the different applications as well as the database management system.

Step 1: Monitor Automatic Applications of Applications

When processing inputs with external or integrated applications, it is essential to validate and filter the transmitted values ​​in order to avoid SQL injections.

1. Check data types

Each input must correspond to the expected data type. For example, if a digital input is required, a simple validation in PHP may look like this:

if (filter_var($input, FILTER_VALIDATE_INT) === false) {
    throw new InvalidArgumentException("Entrée invalide");
}

Similar checks must be implemented for character strings, date values ​​or other specific formats.

2. Filter special characters

Special characters can cause safety faults, especially in SQL or HTML contexts. A safe method is to use htmlspecialchars() For HTML entries and PDO::quote() For SQL queries.

3. Avoid error messages

Direct error messages detailing technical information on the database or the system must be avoided. Instead, it is recommended to use a generic output like:

echo "Une erreur est survenue. Veuillez réessayer plus tard.";
error_log("Une erreur inattendue s’est produite. Pour plus de détails, consultez le journal système.");

4. Use prepared requests

A sure way to avoid SQL injections is to use the prepared requests mentioned above. With this approach, the SQL commands and the parameters are processed separately, which prevents the execution of malicious code. Here is an example adapted in PHP with PDO (PHP Data Objects):

$stmt = $pdo->prepare("SELECT * FROM users WHERE id = :id");
$stmt->bindParam(‘:id’, $user_id, PDO::PARAM_INT);
$stmt->execute();

The database management system automatically ensures secure processing of inputs.

Step 2: Ensure complete protection of the server

The safety of the server on which you execute your database management system plays an important role in prevention of SQL injections. The first step is to Strengthen your operating system According to the established scheme:

• Install or activate only applications and services relevant for the operation of the database.
• Delete all the user accounts you don't need.
• Make sure all the relevant system and programs updates are installed.
• Apply the principle of the smallest privilege to ensure that users and services have only strictly necessary authorizations.

Depending on the safety requirements of your web project, additional protective measures can be envisaged:

• Intrusion detection systems (IDS) and intrusion prevention systems (IPS) : these systems use different detection methods for Detect attacks on the server at an early stageemit alerts and, in the case of the IPS, automatically trigger the appropriate countermeasures.
• Application Layer Gateway (ALG) : an ALG monitors and filters traffic between applications and web browsers directly at the application level.
• Web application Firewall (WAF) : a waf protects web applications targeted against SQL injection and Cross-site-written (XSS) by blocking or neutralizing suspicious requests.
• Zero Trust approach : This modern safety approach guarantees that each access, whatever its source, is controlled and verified before being authorized.
• Firewall regulations and network segmentation : These measures are essential to minimize the long -term attack surface.
• Regular IT security audits and intrusion tests : They make it possible to identify and fill vulnerabilities at an early stage.

Step 3: harden the database and use secure codes

Like your operating system, the database must be rid of all the superfluous and updated elements regularly. Delete all unused stored procedures and deactivate all unnecessary user services and accounts. Create an account dedicated to the databaseexclusively reserved for web access, with limited access rights.

In the spirit of prepared requests, it is strongly recommended not to use the PHP module mysql (deleted from PHP 7) and choose instead mysqli or pdo. These alternatives allow you to write more secure code. Here is an example of a secure request with mysqli ::

$mysqli = new mysqli("localhost", "user", "password", "database");
if ($mysqli->connect_error) die("Echec de la connexion");
$stmt = $mysqli->prepare("SELECT password FROM users WHERE username = ?");
$stmt->bind_param("s", $_POST[‘username’]);
$stmt->execute();
$stmt->bind_result($hashedPassword);
if ($stmt->fetch() && password_verify($_POST[‘password’], $hashedPassword)) {
    echo "Connexion réussie";
} else {
    echo "Identifiants de connexion erronés";
}
$stmt->close();
$mysqli->close();

In addition, passwords should never be stored directly in a database or requested in raw text. Rather use a hash method as password_hash() In combination with password_verify() To protect them effectively, for example as follows:

$mysqli = new mysqli("localhost", "user", "password", "database");
$stmt = $mysqli->prepare("SELECT password FROM users WHERE username = ?");
$stmt->bind_param("s", $_POST[‘username’]);
$stmt->execute();
$result = $stmt->get_result();
$row = $result->fetch_assoc();
if ($row && password_verify($_POST[‘password’], $row[‘password’])) {
    echo "Connexion réussie !";
} else {
    echo "Nom d’utilisateur ou mot de passe erroné.";
}

Bobby Tables: SQL injections explained in comics

On the site bobby-tables.comthe XKCD comic tackles the problem of Unsecured user inputs in databases. She stages a mother who receives a phone call from her son's school (affectionately nicknamed the little Bobby Tables). She confirms that her son is called Robert’); DROP TABLE Students;-- And includes the reason for the call: after the school has tried to create an entry for Robert in the database, all the existing data has been erased. Robert's mother does not move, but rather expresses his wish that the school learns of this error and takes measures to clean up the entries of their databases in the future.

The comic strip clearly illustrates the dramatic consequences that users can have unaccounts in the databases.