[Secure Coding] 0x01 - SQL Injection

TL;DR

SQL Injection (SQLi) attacks remain a significant threat even in modern web applications. They exploit vulnerabilities in how web apps interact with databases, often through improperly handled user input. While ORMs (Object Relational Mappers) reduce the risk, they don’t eliminate it entirely— especially when developers bypass them for manual queries. The best protection is using prepared statements and implementing input filtering. Additionally, you should always store sensitive data, like passwords, securely, as a way to prevent any successful attacks to read it.

SQL Injection Attacks

Web applications typically store and manage data in a database. To interact with this data — whether it’s retrieving user information, storing customer details, or processing orders — SQL (Structured Query Language) queries are used. These queries allow the app to communicate with the database and perform operations like selecting, updating, or deleting records.

SQL Injection (SQLi) is an attack method that exploits vulnerabilities in these SQL queries. It occurs when an attacker manipulates input fields (like login forms or search bars) to inject malicious SQL code into the query. If the input is not properly sanitized, the database executes the injected code, potentially allowing the attacker to read, modify, or delete sensitive data.

For instance, consider the following SQL query in PHP:

In this example, the query takes the user input ($search) and directly inserts it into the SQL statement. If an attacker submits something like ' OR '1'='1'--, the resulting query becomes:

SELECT * FROM products WHERE name LIKE '%' OR '1'='1';--%';

This query now bypasses the intended functionality and returns all records from the database, potentially exposing sensitive information.

Alternative Examples

This type of vulnerability can be found across any language or framework, such as Node.js and ASP.NET, when user inputs are not properly sanitized, but placed directly into SQL queries. Using the same ' OR '1'='1'-- payload, you can see that an attacker would also be able to manipulate the behavior of the database in the following examples.

ASP.NET Example:

Node.js Example:

SQLi Attacks Impact

Although these were simple example attacks, without any real impact, SQL Injection attacks can have severe consequences for organizations and individuals, as they can lead to complete control over the database and its data, based on the permissions the database user was given.

Following the same examples as before, imagine that an attacker now inserts the following string to the user input field of the application:

' UNION SELECT 1,concat(username,':',password),1,1,1 FROM users -- -

The resulting query would then be:

SELECT * FROM products WHERE name LIKE '%' UNION SELECT 1,concat(username,':',password),1,1,1 FROM users -- -%';

This would result in the extraction of all usernames and passwords from the database, as shown by the example lab below (which corresponds to the PHP example given before):

Beyond this exploitation scenario, SQL Injection vulnerabilities could also lead to the alteration/deletion of crucial database records, or even complete compromise of the system, in cases when it’s possible to abuse permissions given to the database user to elevate privileges and gain control of the server.

SQL Injection in Modern Days

A common misconception is that modern web development practices, particularly the use of ORMs (Object Relational Mappers), automatically protect against SQL Injection. While ORMs can help reduce the likelihood of SQLi by abstracting the database interactions, they are not foolproof.

For example, during some Penetration Testing assessments I performed over the years, I noticed that, although companies often rely on ORMs for simple queries, they revert to manual SQL queries when more complex operations are needed. If not handled with care, this can end up introducing SQLi vulnerabilities all over again.

For instance, here’s an example of vulnerable code inside an application that uses Sequelize, in Node.js:

In this case, if the snippet parameter is unsanitized, an attacker could input malicious SQL, abusing an SQLi vulnerability in your application.

Fixing and Preventing SQL Injection

There are two main recommendations to fix and prevent SQLi vulnerabilities. These techniques should be implemented together in order to provide a better protection for the application.

For each example shown below, there will also be a code snippet available, which you can see and copy if you want by clicking the toggle icon on the side of the language name.

Prepared Statements

The most effective way to prevent SQL injection is to use prepared statements. Prepared statements separate SQL code from the data being inserted, ensuring that user input is treated as data rather than executable code.

The following code snippets demonstrate how the previous vulnerable examples were modified in order to fix the SQL Injection vulnerability using Prepared Statements:

if (isset($_GET['search'])) {
    $search = '%' . $_GET['search'] . '%';
    $stmt = $conn->prepare("SELECT * FROM products WHERE name LIKE :search");
    $stmt->bindParam(':search', $search);
} else {
    $stmt = $conn->prepare("SELECT * FROM products");
}

$stmt->execute();
$result = $stmt->fetchAll();

string name = Request.QueryString["name"];
string query = "SELECT * FROM customers WHERE name=@name";
SqlCommand cmd = new SqlCommand(query, conn);
cmd.Parameters.AddWithValue("@name", name);
SqlDataReader reader = cmd.ExecuteReader();

const name = req.query.name;
const query = "SELECT * FROM customers WHERE name=?";
db.query(query, [name], (error, results) => {
    if (error) throw error;
    res.send(results);
})

function findItems(req, resp)
{
    try {
        sequelize.query(
            "SELECT Desc FROM Items WHERE Desc like ?",
            { replacements: [ '%' + req.params.snippet + '%'],
            type: sequelize.QueryTypes.SELECT }
        )
        .spread(function(results, metadata) {
        });
        catch {
        }
    }
}

Whitelist-Oriented Input Filtering

Additionally, you should implement input filtering. A whitelist approach is recommended, which means only allowing specific characters in user inputs. For example, you might restrict a username field to allow only alphanumeric characters.

Although this is being shown for SQL Injection, this is actually a great way to prevent many types of injection attacks, as it will block most of the possible malicious characters if configured correctly.

The following code snippets demonstrate how the previous vulnerable examples were modified in order to fix the SQL Injection vulnerability using Input Filtering with a charset whitelisting approach:

if (isset($_GET['search'])) {
    $search = trim($_GET['search']);
    if (!preg_match('/^[a-zA-Z0-9]+$/', $search)) {
        echo 'Invalid search parameter';
        exit;
    }
    $search = '%' . $search . '%';
    $stmt = $conn->prepare("SELECT * FROM products WHERE name LIKE :search");
    $stmt->bindParam(':search', $search);
} else {
    $stmt = $conn->prepare("SELECT * FROM products");
}

$stmt->execute();
$result = $stmt->fetchAll();

// You must import the System.Text.RegularExpressions library in order to use Regex
var commentText = context.Request.Form["comment"];

// If Regex.IsMatch not alphanumerical, shows error message
string pattern = @"^[a-zA-Z0-9\s]*$";
if (!Regex.IsMatch(commentText, pattern))
{
    return context.Response.WriteAsync("Invalid comment. Please try again.");
}
else
{
    comments.Add(new Comment { Text = commentText });

// Only allow alphanumerical characters and valid email format
const pattern = /^[a-zA-Z0-9]+@[a-zA-Z0-9]+\.[a-zA-Z0-9]+$/;
const input = '[email protected]'; // User input to validate

if (pattern.test(input)) {
    // Valid email format and alphanumerical characters
} else {
    // Invalid Input
}

Bonus Tips

Here are a few extra security best practices to keep in mind:

• Never store passwords in cleartext or reversible formats. Use secure hashing algorithms (e.g., bcrypt, Argon2) to ensure that even if a database breach occurs, passwords aren’t immediately compromised.
• Minimize database privileges for your application. For example, if your web app only needs to read data, don’t give it permission to write or delete data.
• Regularly update dependencies in your app, especially ORM libraries, database drivers, and security packages. Security flaws are often discovered, and regular updates help patch vulnerabilities.