Introduction to SQL Injections

SQL injection attacks are a type of cybersecurity threat that target databases through malicious SQL code injection. In a SQL injection attack, malicious actors exploit vulnerabilities in an application's input validation mechanisms, typically in web forms or user inputs, to insert malicious SQL statements into the application's SQL queries.

In this blog, readers will learn about the insidious threat of SQL injection attacks, understanding how attackers exploit input fields to manipulate SQL queries. We'll explore real-world examples of SQL injection vulnerabilities. Additionally, readers will discover essential techniques to prevent SQL injection, including input validation, parameterized queries, and prepared statements.

What are the underlying vulnerability that makes SQL injection attacks possible?

When web applications accept user input without proper validation, attackers can inject malicious SQL code into input fields. This malicious code is then executed by the application's backend database server, allowing attackers to manipulate SQL queries and access sensitive data.
In the below image we have input field which takes our email/username but exploiters can use it to run malicious SQL queries which may corrupt the databases when the application isn't designed with proper input validation, parameterized queries, and prepared statements.

Common SQL Injection Techniques

Let's start with inserting some values in a table. Now let's say that we need a function that delete's a tuple taking the username as a arugument.

async function deleteUser(username) {
    try {
        const query = `DELETE FROM users WHERE username = '${username}'`;
        const result = await client.query(query);
        console.log(`Deleted user with username: ${username}`);
        return result.rowCount;
    } catch (error) {
        console.error('Error deleting user:', error);
        throw error;
    }
}
const deleteUser= "user1";
deleteUser(maliciousInput)
    .then(() => client.end())
    .catch(() => client.end());

User1 has been deleted. Now let's again simulate the same deletion operation but with a malicious input for the argument.

async function deleteUser(username) {
    try {
        const query = `DELETE FROM users WHERE username = '${username}'`;
        const result = await client.query(query);
        console.log(`Deleted user with username: ${username}`);
        return result.rowCount;
    } catch (error) {
        console.error('Error deleting user:', error);
        throw error;
    }
}
// Simulate SQL injection
const maliciousInput = "'; DROP TABLE users; --";
deleteUser(maliciousInput)
    .then(() => client.end())
    .catch(() => client.end());

When i run this piece of code this happens:- Behind the scenes-

• ';: This is a single quote followed by a semicolon. It's used to close any open SQL string that might be in progress.
• DROP TABLE users;: This is the SQL statement that attempts to drop the users table from the database. This is a destructive operation, and if executed, it will delete the entire users table from the database.
• --: This part represents a SQL comment. In SQL, -- signifies a single-line comment. Everything following -- on the same line is treated as a comment and ignored by the SQL engine. This part is added to comment out any subsequent characters in the SQL query string, preventing syntax errors.

Now, when the vulnerable code executes deleteUser(maliciousInput), it constructs a SQL query using the maliciousInput variable without proper sanitization or parameterization. In the vulnerable code, the SQL query is constructed via string concatenation, so the entire string value of maliciousInput will be included in the SQL query.

DELETE FROM users WHERE username = ''; DROP TABLE users; --';

This illustrates the danger of SQL injection vulnerabilities and emphasizes the importance of using parameterized queries or other methods to prevent unauthorized SQL code execution.
Congrats now your database is vulnerable to easy SQL injections.

Fix

To prevent SQL injection, you should use parameterized queries. Here's how you can modify the code to use parameterized queries:

async function deleteUser(username) {
    try {
        const query = 'DELETE FROM users WHERE username = $1';
        const result = await client.query(query, [username]);
        console.log(`Deleted user with username: ${username}`);
        return result.rowCount;
    } catch (error) {
        console.error('Error deleting user:', error);
        throw error;
    }
}

// Simulate SQL injection (will not work due to parameterized query)
const maliciousInput = "'; DROP TABLE users; --";
deleteUser(maliciousInput)
    .then(() => client.end())
    .catch(() => client.end());

In the secure version, we use a parameterized query where the SQL query string remains static and the user input is passed as a separate parameter. This ensures that user input is treated as data rather than executable SQL code, thus preventing SQL injection attacks.
Great now you know how to build you applications the right way.