LACNIC Blog > Cibersecurity > How to prevent SQL injection attacks

How to prevent SQL injection attacks

30/03/2022

By Graciela Martínez and Guillermo Pereyra – LACNIC CSIRT

SQL injection attacks are a very popular web hacking technique. Known as SQLi, these attacks occur when one or more valid SQL statements are “injected” into an input field to be processed by an underlying database.

Structured Query Language (SQL) is a command-and-control language for relational databases.

How is an SQL injection attack performed?

An attack occurs when an application accepts data from unreliable sources — data that has been modified to be interpreted as code — and does not perform a proper validation before using the data to perform a dynamic query on the database.

Attack vectors

When designing an application, it is important to keep in mind that any user-entered data can be modified arbitrarily.

Attacks on web applications are usually performed by modifying parameters in the URL, using the HTTP GET method or, as we will see in the examples below, modifying any type of data input with the HTTP POST method. Depending on how the web application is designed, this vulnerability can be exploited either by modifying HTTP headers such as User Agent, cookies, referrer, or the system’s own headers.

Other potential attack vectors should also be considered, for example, bar code readers, QR readers, or video cameras with text recognition.

It is important to note that attackers may be either external or members of the affected organization.

What are the possible consequences of an SQL injection attack?

Any unwanted action executed on a database as a result of an SQLi attack can affect one or more information security pillars.

A successful attack can allow the attacker to perform different actions that affect the confidentiality, integrity, and/or availability of the information contained in the database.

Examples:

Confidentiality can be affected if sensitive information is accessed without the required authorization.
Integrity can be affected if information is deleted or altered without the required authorization.
Availability can be affected if the information is not available when it is needed, either because it cannot be accessed or because it has been previously modified without the required authorization.

Other vulnerabilities that an SQLi attack might exploit include authentication failures and/or the modification of a user’s profile authorization to perform certain actions on a resource. For example, in case of an attack against a database where user keys are stored, obfuscating the keys with robust cryptographic hashes would prevent unauthorized access to the keys and their potential use by an attacker.

For all the above, a successful SQLi attack simultaneously affects several different pillars of information security.

How to prevent an SQL injection attack

There are different types of measures that can be considered when a system needs to communicate with a relational database using SQL.

Some of the basic measures that should be implemented include:

Use of parameterized queries: prepared statements
Use of procedures: stored procedures
Validation of user data entry
Escaping all authorized user entries

Use of parameterized queries: prepared statements

Preparing SQL statements and storing them in a variable before their execution is a simple and secure way for developers to work. Doing this ahead of time ensures that an attacker will not be able to insert statements into our database, as shown in the second example below.

The most popular languages have methods to securely parameterize statements requiring user-supplied input. These languages include:

Java EE – use PreparedStatement() in our bind variables
.NET – use parameterized queries such as SqlCommand() or OleDbCommand() in our bind variables
PHP – PDOs can be used for generic databases with a strong parameterization of queries or, if using a specific database driver, it is necessary to find a secure function to prepare our statement. For example, in the case of MySQL, bind_param() should be used.

Use of procedures: stored procedures

When executing procedures directly on the database, care must be taken not to include the generation of unsecure dynamic SQL statements. These procedures must have their inputs validated and an adequate “escape” functionality.

Validation of user-supplied data

User-supplied data must always be properly validated, not only to prevent SQL injections.

The use of dynamic variables for table or column names is not recommended, neither is specifying sort order (ASC or DESC). If necessary, prior validations should be used, such as converting the inputs to Boolean variables or using SWITCH, Sort of, or other functions.

Escaping all authorized user entries: escape characters

Escaping user-supplied inputs to convert them to another format such as strings should be used with care, as this does not prevent every injection. The escape characters technique depends on each database engine, so it is important to implement further controls and validations to keep attackers from bypassing this measure.

Examples

Below are some examples of code that would enable SQLi attacks.

Example 1: Separating results into pages, using PHP and Postgres.

     
        <?php
        $índice    = $argv[0]; 
        $consulta  = "SELECT id, name FROM products ORDER BY name LIMIT 20 OFFSET $índice;";
        $resultado = pg_query($conexión, $consulta); 
        ?>

A user typically clicks the “next” and “back” buttons to browse between results, which would change the decimal value of the “index” variable in the URL.

This behavior would not cause any issues, but if a malicious agent were to decide to add the following code to the URL:

0;
insert into pg_shadow(usename,usesysid,usesuper,usecatupd,passwd)
    select 'crack', usesysid, 't','t','crack'
    from pg_shadow where usename='postgres';
--

the $consulta variable would look like this:

$consulta  = "SELECT id, name FROM products ORDER BY name LIMIT 20 OFFSET 0;
insert into pg_shadow(usename,usesysid,usesuper,usecatupd,passwd)
    select 'superusuario', usesysid, 't','t','password'
    from pg_shadow where usename='postgres';
--;"

As a result, a “superuser” user would be created with privileges, who would then be able to perform malicious activities such as those described earlier in this article.

One way to fix this problem would be to use PDOs (PHP Data Objects):

<?php 
$stmt  = $pdo->prepare("SELECT id, name FROM products ORDER BY name LIMIT 20 OFFSET :índice;");
$índice = $argv[0]; 
$stmt->bindParam(':indice', $indice);
$stmt->execute()
?>

Example 2: Authentication bypass

Suppose a web application has an authentication form that accepts a username and password as inputs.

This form is being processed by code containing the following SQL statement:

consulta = "SELECT * FROM users WHERE username = "'" + username + "' AND password = '" + password + "'"

As we can see, the query to the database is built using SQL statements and the user-supplied values are assigned directly to the variables.

In this case, a malicious user could enter admin as the user and pass’ OR ‘1’=’1 as the password.

The final query to the database would look like this:

consulta = SELECT * FROM users WHERE username = 'admin' AND (password = ' pass' OR '1'='1 ')

Given that the Boolean condition will always be true, this query would fetch all the data associated with privileged user ‘admin‘.

Conclusion

SQL injection attacks are preventable if the necessary controls are implemented during the application development phase.

Developer teams should have a procedure that includes best practices for secure software development, including a prudent time for the testing phase prior to going into production.