Web Applications Security - Understanding SQL Injection In Depth

In the previous article, we learned about?injection vulnerabilities?and explored some common examples and techniques for safeguarding web applications against them. Nevertheless, this article will delve further into the specifics of?SQL injection, examining the steps and measures required to minimize the threat posed by this type of injection.

What is SQL Injection?

SQL injection is a type of security vulnerability that can occur in?web applications?that use?SQL databases. It happens when an attacker is able to insert?malicious SQL code?into a?database query?through an input field on a website.

SQL injection?attacks can be very dangerous because they can allow an attacker to bypass authentication and access sensitive information or make unauthorized changes to the database.

How does an application become vulnerable?

When a developer creates?dynamic database queries?using?string concatenation?to incorporate user-supplied input without proper sanitization, it creates an opportunity for?SQL injections?to occur within an application. To illustrate this in the context of PHP, consider a basic?HTML form?designed to search for users based on their names or IDs. The corresponding?PHP code?for this functionality might resemble the following:

$servername =?"localhost"
$username =?"username";
$password =?"password";
$dbname =?"my_project";

// Acquire the E-mail address from the input 
$input = $_GET["input];

// Create DB connection
$conn =?new?mysqli($servername, $username, $password, $dbname);

// Construct the query
$query = "SELECT id, name FROM users WHERE name = '$input' OR id = '$input'";

// Run the query
$users = $conn->query($query);

// Print matching users from results set
while ($user = $user->fetch_assoc()) {
    echo?"id: "?. $row["id"].?" - Name: "?. $row["name"] .?"<br>";
}        

The user inputs either a name or an ID and receives the corresponding search results, making the process straightforward. As an illustration, if the?name field?were populated with the name "John," the DBMS would run the subsequent query:

SELECT id, name FROM users WHERE name = 'john' OR id = 'john'        

Unfortunately, life is rarely so straightforward, and there are often obstacles that can complicate matters.

Consider a scenario in which an individual attempt to search for the name "Darya," which is written in Belarusian as "дар'я." The problem arises from the fact that the name includes a single quote, causing the DBMS to attempt to execute the subsequent query when it is passed as input:

SELECT id, name FROM users WHERE name = 'дар'я' OR id = 'дар'я';        

You can already see the problem here: this part (я') isn't interpreted as a part of the name, and you will end up having an error message similar to the following one:

PHP Fatal error:?Uncaught mysqli_sql_exception: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'я''

Thankfully, this has prevented any harmful consequences from occurring during execution. However, if an individual were attempting to exploit this vulnerability, they may input a malicious code. For instance, if their input consisted of (' OR 1=1 --), the DBMS would attempt to carry out the subsequent query:

SELECT id, name FROM users WHERE name = '' OR 1=1 --' OR id = '' OR 1=1 --'        

This basically means "Give me all users whose name is either empty or 1=1", and obviously 1 equals 1, so the comparison is TRUE always. The rest of the query is ignored since the double dash is used.

Always remember, the double dash (--) is how you declare a single line comment in MySQL.

Now some harm was done here, the user's data has been leaked and returned. But that's not all and things can still get worse.

Imagine if our bad guy used this input (' OR 1=1; DROP TABLE users; -- ),. In this case, the poor DBMS will execute the following query:

SELECT id, name FROM users WHERE name = '' OR 1=1; DROP TABLE users; -- ' OR id = '' OR 1=1; DROP TABLE users; -- '        

Here we have 2 important results to discuss:

1. You will probably lose your job and get sued
2. The users' data are lost forever (unless a regular backup is being done frequently)

Do we really need to sleep in graveyards and experience nightmares? It seems unnecessary.

Suggested Procedures and Steps to Consider

There are several options and defenses you could set up to avoid having your application vulnerable to this dangerous security hole. Let's talk about them in detail.

Prepared Statements, Prepared Statements, and Prepared Statements

I just wanted to emphasize the importance of prepared statements. This technique is combined with parameterized queries to form a safe mechanism for database query execution.

The idea is very simple: the developer defines the full SQL query and then fills in dynamic values. This allows the DBMS to distinguish between the code and data and ensures that a poisoned input cannot change the meaning of an SQL query.

We can think about it in a really simple way. Just imagine that you're taking the dynamic values and substituting them with a question mark (?), so our query becomes like this:

SELECT id, name FROM users WHERE id = ? OR name = ?        

Here, the DBMS analyzes the query and optimizes it, and knows exactly what this query will do. After that, the data is bound to the placeholders, and the query is executed.

Thanks to this technique, SQL injection isn't possible because no matter what the input is, it won't be able to change the meaning of the query.

Whitelist Input Validation

Prepared statements have an important limitation: some parts of an SQL query cannot be labeled with (?), like table names and columns, and the sort order indicator (ASC and DESC). In this case, we have to establish a whitelist input strategy.

Let's assume that we have a CRUD table that displays all the users, and has a sort functionality according to the ID field. Normally, the PHP code for this logic would look something like this:

$servername =?"localhost
$username =?"username";
$password =?"password";
$dbname =?"my_project";

// Acquire the sort direction
$sort_indicator = $_GET["sort_indicator];

// Create DB connection
$conn =?new?mysqli($servername, $username, $password, $dbname);

// Construct the query
$query = "SELECT id, name FROM users ORDER BY id $sort_indicator";

// Run the query
$users = $conn->query($query);

// Print matching users from results set
while ($user = $user->fetch_assoc()) {
    echo?"id: "?. $row["id"].?" - Name: "?. $row["name"] .?"<br>";
}        

This code is vulnerable to SQL injection because if the user tampered with the value of $sort_indicator variable he will be able to do harmful actions. Also, this part cannot be used as a bind variable in a prepared statement.

The solution here is to validate the value which the user sends in the $sort_indicator variable. You can simply write:

$servername =?"localhos
$username =?"username";
$password =?"password";
$dbname =?"my_project";

// Acquire the sort direction
$sort_indicator = $_GET["sort_indicator];

// Validate the sort direction
$sort_indicator = match($sort_indicator) {
    "ascending" => "ASC",
    "descending" => "DESC",
    default => "DESC"
};

// Create DB connection
$conn =?new?mysqli($servername, $username, $password, $dbname);

// Construct the query
$query = "SELECT id, name FROM users ORDER BY id $sort_indicator";

// Run the query
$users = $conn->query($query);

// Print matching users from results set
while ($user = $user->fetch_assoc()) {
    echo?"id: "?. $row["id"].?" - Name: "?. $row["name"] .?"<br>";
}        

If the value sent is "ascending", then the indicator is "ASC", otherwise it's "DESC". The resultant value is completely safe to be passed because there's no way that a value other than these two will be passed to the query.

In a future article, we will come back to the topics of whitelisting and blacklisting due to their importance.

The Concept of Least Privilege

Your application uses a database account to authenticate with the DBMS. Most of the time, developers use a database account that has super (or root) privileges.

Here lies the danger, because a highly-privileged DB account is capable of performing all kinds of operations that may not be needed in the production stage. For example: what are the chances that you need to programmatically execute a DROP TABLE or a TRUNCATE statement on a production server?

Speaking strictly of MySQL, it has a number of privileges you can assign to each database account to restrict. The following image shows the default privileges for the root user.

Let's be honest, for a production environment, most of the time you only need the 4 basic operations on data: SELECT - INSERT - UPDATE - DELETE.

These privileges can be set either in a global manner (for all databases), per database, or even per table. And here I would even go as far as saying that if you don't need a specific operation, just disable it. For example, a logs table that tracks users logging in may only need INSERT and SELECT operations, other operations aren't required so don't activate them.

So always reduce the privileges of a database account to the minimal amount of privileges needed to work.

Select Only The Data You're Interested In

The idea here is to only select the columns that you will be using in your code, don't just (SELECT *) the whole table unless you really need it. This radicalness?has a very good reason: you're not retrieving potentially sensitive information from the database, which reduces the possibility of data exposure.

On a side note, this also has a performance advantage since you're reducing the amount of data that is being retrieved from the database.

If you have a SELECT query that's occurring in your code, you may consider wrapping it in a VIEW. Inside a view, you can store the results of a SELECT query for later usage. A view can also be used to hide columns that may contain sensitive information.

Prepared Statements In Action

Fortunately, PHP has a good invention called PDO.

PHP PDO (PHP Data Objects) is a database abstraction layer that provides a consistent interface for accessing and manipulating databases in PHP. PDO defines a set of classes and methods that allow you to perform common database operations, such as executing queries, fetching results, and managing transactions. Using PDO can help to improve the security and maintainability of your PHP code, as it can help prevent SQL injection attacks.

Using PDO is very simple, all you need to do is just to grab an instance of the PDO class, prepare your query, bind your parameters, and grab the results. Here's a simple example:

<?php


$host = "127.0.0.1";
$dbname = "test";


// retrieve the email from the form
$email = $_GET["email"];


$pdo = new PDO("mysql:host=$host;dbname=$dbname", "root", "123");


// set the PDO error mode to exception
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);


$query = "SELECT id, name FROM users WHERE email = :email";


// prepare sql and bind parameters
$stmt = $pdo->prepare($query);
$stmt->bindParam(':email', $email);


$stmt->execute();


$users = $stmt->fetchAll();


print_r($users);        

This is the preferred way to write SQL queries inside your PHP code.

However, if you're using a PHP framework like Laravel, things are much simpler.

Prepared Statements In Laravel Framework

Laravel framework provides several ways to write and execute SQL queries:

• Eloquent ORM
• Query Builder Layer
• Raw SQL Statements

Both eloquent ORM and query builder is invulnerable to SQL injection. However, the third option is vulnerable to SQL injection if it's used improperly.

This code is written using Eloquent ORM:

$email = $request->email;
$users = App\Models\User::where("email", $email)->get();        

And this code is written using the query builder:

$builder = Illuminate\Support\Facades\DB::table("users");
$builder->where("email", $request->email);
$results = $builder->get();        

And both are completely safe because under the hood they build prepared statements and bind the values. However, the following code which uses raw SQL statements isn't safe:

$name = $request->name;
$results = DB::select("SELECT * FROM users WHERE name = '$name'");        

In order to make it safe, we have to use bound parameters as follows:

$name = $request->name;
$results = DB::select('SELECT * FROM users WHERE name = ?' , [$name]);        

One important thing to notice here is the second parameter of the select() method, it takes an array of values to be bound to the query.

Conclusion

In conclusion,?SQL injection?is a type of attack in which an attacker injects malicious SQL code into a?web application's input fields, allowing them to manipulate the application's database.?SQL injection attacks?can be devastating, as they can give an attacker access to sensitive data, allow them to modify or delete data, and even grant them?administrative privileges?on the system.

Preventing SQL injection requires a multi-layered approach, including?input validation?and sanitization, using?parameterized queries?or?prepared statements, and implementing?least privilege?access controls.?