SQL Injection

SQL Injection is a vulnerability where an application takes input from a user and doesn't vaildate that the user's input doesn't contain additional SQL.

<?php
    $username = $_GET['username']; // kchung
    $result = mysql_query("SELECT * FROM users WHERE username='$username'");
?>

If we look at the $username variable, under normal operation we might expect the username parameter to be a real username (e.g. kchung).

But a malicious user might submit different kind of data. For example, consider if the input was '?

The application would crash because the resulting SQL query is incorrect.

SELECT * FROM users WHERE username='''

Note

Notice the extra single quote at the end.

With the knowledge that a single quote will cause an error in the application we can expand a little more on SQL Injection.

What if our input was ' OR 1=1?

SELECT * FROM users WHERE username='' OR 1=1

1 is indeed equal to 1. This equates to true in SQL. If we reinterpret this the SQL statement is really saying

SELECT * FROM users WHERE username='' OR true

This will return every row in the table because each row that exists must be true.

We can also inject comments and termination characters like -- or /* or ;. This allows you to terminate SQL queries after your injected statements. For example '-- is a common SQL injection payload.

SELECT * FROM users WHERE username=''-- '

This payload sets the username parameter to an empty string to break out of the query and then adds a comment (--) that effectively hides the second single quote.

Using this technique of adding SQL statements to an existing query we can force databases to return data that it was not meant to return.

Preventing SQL Injection

The best way to prevent SQL Injection is to use prepared statements. Prepared statements are a way to execute SQL queries that separates the query logic from the data being passed into the query.

<?php
    $stmt = $pdo->prepare('SELECT * FROM users WHERE username = :username');
    $stmt->execute(['username' => $username]);
?>

In this example, the :username is a placeholder that is replaced with the value of the $username variable. The database driver will automatically escape the value of $username to prevent SQL Injection.

Another way to prevent SQL Injection is to use an ORM (Object Relational Mapping) library. ORM libraries abstract the database layer and allow you to interact with the database using objects instead of raw SQL queries.

<?php
    $user = User::where('username', $username)->first();
?>

ORM libraries automatically escape user input to prevent SQL Injection.