SQL Injections [SQLi]

1. Introduction:

SQL Injections happen when developers create dynamic database queries that include user-supplied input. For this reason, developers have to:

1. Stop writing dynamic queries for their applications.

2. Filter and escape the user-supplied input.

The techniques presented here technically apply to most other programming languages and/ or types of databases.

2. Typical vulnerable code:

JavaScript

function authenticate(req, res, next) {
    var email = req.query.email,
        password = req.query.password,
        sqlRequest = new sql.Request(),
        sqlQuery = "select * from users where (email='" 
        + "' and password = '" + password + "')";
    
    sqlRequest.query(sqlQuery)
        .then(function (recordset) {
            if (recordset.length == 1) {
                loggedIn = true;
                // Auth successful
            } else {
                // Auth failure
            }
        })
        .catch(next);
}

Python

def authenticate(request):
    email = request.POST['email']
    password = request.POST['password']
    sql = "select * from users where (email ='" 
        + email 
        + "' and password ='" + password + "')"
    
    cursor = connection.cursor()
    cursor.execute(sql)
    row = cursor.fetchone()
    if row:
        loggedIn = "Auth successful"
    else:
        loggedIn = "Auth failure"
    return HttpResponse("Logged In Status: " + loggedIn)

PHP

<?php
$email = $POST['email'];
$password = $POST['password'];

$stmt = mysql_query("SELECT * FROM users WHERE (email='$email' 
                    AND password='$password') LIMIT 0,1"
                    );

$count = mysql_fetch_array($stmt);
if($count > 0) {
    session_start();
    // Auth successful
}
else
{
    // Auth failure
}
?>

sqlQuery executes an SQL Query without any input validation whatsoever. (i.e. no checking of legal characters, minimum/maximum string lengths, or removal of "malicious" characters).

The attacker can inject raw SQL syntax within both the username and the password input fields to alter the meaning of the underlying SQL query responsible for authentication, resulting in a bypass of the application's authentication mechanism. An example of such bypass could be the query: ' or 1=1)#

3. Mitigation:

SQL Injection attacks are unfortunately very common, and this is due to two factors:

1. The significant prevalence of SQL Injection vulnerabilities, and

2. The attractiveness of the target (i.e., the database typically contains all the interesting/critical data for your application).

It's rather shameful that there still are so many successful SQLi occurring because it is really simple to avoid this type of vulnerability in the first place. In this article, you will discover the top 4 most reliable techniques for developing a robust and secure application.

3.1. Prepared statements:

The use of prepared statements with variable binding (aka parameterized queries) is how all developers should write their queries to begin with. They are simple to write and easier to understand than dynamic queries. Parameterized queries force the developer to first define all the SQL code, and then pass in each parameter to the query later. This coding style allows the database to distinguish between code and data, regardless of what user input is supplied.

Prepared statements ensure that an attacker is not able to change the intent of a query, even if SQL commands are inserted by an attacker. In the safe example below, if an attacker were to enter the email of [e-mail]' or '1'='1, the parameterized query would not be vulnerable and would instead look for a username that literally matched the entire string [e-mail]' or '1'='1.

JavaScript

function authenticate(req, res, next) {
    var email = req.query.email,
        password = req.query.password,
        ps = new sql.PreparedStatement(),
        sqlQuery = "select * from users where (email = @email and " 
                 + "password = @password)";
    
    ps.input('email', sql.VarChar(50));
    ps.input('password', sql.VarChar(50));
    ps.prepare(sqlQuery)
    .then(function () {
        return ps.execute({email: email, password: password})
        then(function (recordset) {
            if (recordset.length == 1) {
                loggedIn = true;
                // Auth successful
    
            } else {
                // Auth failure
            }
        })
    })
    .catch(next);
};

Python

def authenticate(request):
    email = request.POST['email']
    password = request.POST['password']
    
    cursor = connection.cursor()
    cursor.execute("select * from users where(email = %s and password = %s)"
    , [email, password])
    row = cursor.fetchone()
    if row:
        loggedIn = "Auth successful"
    else:
        loggedIn = "Auth failure"
    return HttpResponse("Logged In Status: " + loggedIn)

PHP

<?php
$email = $POST['email'];
$password = $POST['password'];

$stmt = $db->prepare("SELECT * FROM users WHERE 
                    (email=:email AND password=:password) LIMIT 0,1"
                    );
$stmt->bindParam(':email', $email);
$stmt->bindParam(':password', $password);
$stmt->execute();

$count = mysql_fetch_array($stmt);
if($count > 0) {
    session_start();
    // Auth successful
}
else
{
    // Auth failure
}
?>

Wherever executing SQL queries is necessary, make sure to always use prepared statements and query parametrization.

3.2. Stored procedures:

Stored procedures are not always safe from SQLi. However, certain standard stored procedure programming constructs have the same effect as the use of parameterized queries when implemented safely.

They require the developer to just build SQL statements with parameters that are automatically parameterized unless the developer does something largely out of the norm. The difference between prepared statements and stored procedures is that the SQL code for a stored procedure is defined and stored in the database itself, and then called from the application.

Both of these techniques have the same effectiveness in preventing SQL injection so your organization should choose which approach makes the most sense for you.

This is a short example of how a stored procedure can be implemented, in SQL.

CREATE PROCEDURE SafeAuth(@username varchar(50),  @password varchar(50))
AS
BEGIN
DECLARE @sql varchar(150)
    SELECT Username,Password FROM dbo.Login
    WHERE Userame=@username AND Password=@password
end

Note: 'Implemented safely' means the stored procedure does not include any unsafe dynamic SQL generation. Developers do not usually generate dynamic SQL inside stored procedures. However, it can be done but should be avoided. If it can't be avoided, the stored procedure must use input validation or proper escaping as described in this article to make sure that all user-supplied input to the stored procedure can't be used to inject SQL code into the dynamically generated query.

Although stored procedures are an efficient way to safe proof against SQLI, it is crucial to also combine them with another technique, such as prepared statements.

3.3. Input validation:

There are two types of input validation: syntactical and semantical.

Syntactical validation enforces syntax correctness of structured fields. (eg. SSN, date, currency symbol, etc).

Semantic validation imposes the correctness of the inputted values in the specific business context. (eg. start date before the end date, price within attributed range, etc)

Input validation can be implemented using any programming technique that allows effective enforcement of syntactic and semantic correctness, for example:

It is important to note however that any JavaScript input validation performed on the client-side can be bypassed by an attacker that disables JavaScript or uses a Web Proxy. Ensure that any input validation performed on the client-side is also performed on the server-side.

• Data type validators available natively in web application frameworks (such as Django Validators, Apache Commons Validators, etc).

• Validation against JSON Schema and XML Schema (XSD) for input in these formats.

• Type conversion (e.g. Integer.parseInt() in Java, int() in Python) with strict exception handling.

• Minimum and maximum value range check for numerical parameters and dates, minimum and maximum length check for strings.

• The array of allowed values for small sets of string parameters (e.g. days of the week).

• Regular expressions for any other structured data covering the whole input string (^...$) and not using "any character" wildcard (such as . or \S). Developing regular expressions can be complicated, thus, it is recommended to follow a comprehensive resource when developing such a validation.

As input validation is a widely used technique, ideally found in every other user interaction, you can learn more about it here.

3.4. Escaping user-supplied input:

This technique should only be used as a last resort when none of the above are feasible. Input validation is probably a better choice as this methodology is frail and we cannot guarantee it will prevent all SQLi in all situations.

Imagine the following scenario: each DBMS supports one or more character escaping schemes specific to certain kinds of queries. If you then escape all user-supplied input using the proper escaping scheme for the database you are using, the DBMS will not confuse that input with SQL code written by the developer, thus avoiding any possible SQL injection vulnerabilities.

Usually, escaping the user input is on top of the already existing layer of defense, such as the prepared statements or stored procedures. In the following snippets, we make use of language-specific commands or specific database adapters that take care of escaping the query.

JavaScript

var SqlString = require('sqlstring');

var userId = 1;
var sql = SqlString.format('SELECT * FROM users WHERE id = ?', [userId]);
console.log(sql); // SELECT * FROM users WHERE id = 1

Python

from psycopg2 import sql
# here one cam make use of database adapters, such as psycopg2
def authenticate(request):
    email = request.POST['email']
    password = request.POST['password']
    with connection.cursor() as cursor:
        stmt = sql.SQL("""
            select 
                * 
            from 
                users 
            where
                (email = {email} and password = {password})
            """).format(
                email = sql.Identifier(email),
                password = sql.Literal(password),
        )
        cursor.execute(stmt)
        row = cursor.fetchone()
    
        if row:
            loggedIn = "Auth successful"
        else:
            loggedIn = "Auth failure"
        return HttpResponse("Logged In Status: " + loggedIn)

PHP

$stmt = $pdo->prepare('SELECT * FROM employees WHERE name = :name');
$stmt->execute(array('name' => $name));
foreach ($stmt as $row) {
    // do something with $row
    // PDO is the universal option; if you connect to databases other than MySQL
    // you can use driver-specific options.
}

4. Takeaways:

SQLI SHOULDNT EXIST! Is what everyone says. And they are right! If every developer would:

1. Prepare their SQL queries.

2. Validate & escape the user input.

3. Write clean efficient code.( =D )

SQLIs will eventually go extinct!

You can find more details about this topic here:

• OWASP Cheatsheet against SQLI

• Preventing python SQLI

• Query parametrization Cheatsheet

• Injection prevention Cheatsheet