Understanding and Preventing SQL Injection Attacks

Introduction

In this article, you'll learn about an attack that targets a very common application vulnerability, an injection attack. I'll demonstrate such an attack and, most importantly, show you how you can protect yourself. An injection attack happens when an attacker tries to inject malicious code or commands into an application. This typically happens if the application doesn't validate user input before processing it. The injected code can then be executed, leading to a wide range of potential security issues.

One common type of injection attack is SQL injection. Using a SQL injection, an attacker can manipulate the SQL statements your application sends to a database, enabling them to perform unauthorized actions or access sensitive data. To demonstrate this, I will use the OWASP Juice Shop, an intentionally vulnerable web application designed to help users learn about various application security risks. The OWASP Juice Shop is available on GitHub.

Exploiting a SQL Injection vulnerability

Users can log in to this shop using their email address and password. In this scenario, a typical injection attack would be to manipulate the input and try to log in as a different user.

A website like this often uses a relational database like MySQL to manage its users, and if this is the case, the login page probably sends a select statement to that database to see if the email address exists and if the password is correct. These statements usually look similar to this:

SELECT * FROM users WHERE email = 'email' AND password = 'password'

Now, let's try logging in to an account (hopefully an admin account). The problem is, we don't know the email or password. To bypass this, we can modify the query so that it doesn't validate the email or password. One way to achieve this is by adding two dashes (--) to comment out the rest of the SQL query.

SELECT * FROM users WHERE email = 'email' -- AND password = 'password'

Notice that after adding (--) to the query, everything following it is treated as a comment. This means the query will ignore the password check.

SELECT * FROM users WHERE email = 'email' OR 1=1 -- AND password = 'password'

Furthermore, by adding OR 1=1, the query will evaluate whether 1 equals 1, which is always true. As a result, it is not required to know the email address to log in.

But how can we modify the data we send so that it adds these dashes? If we just add them to the email address, it would look like this:

SELECT * FROM users WHERE email = 'email or 1=1 --' AND password = 'password'

The dashes are inside the code, and the database will think it's part of the email address and not part of the query itself, so this doesn't work.

Now, how do we get the dashes outside of this code? We can achieve this by adding another single quote (') as part of the email address we provide. For example: email' OR 1=1 --.

SELECT * FROM users WHERE email = 'email' or 1=1 --' AND password = 'password'

We've got a valid query that only looks for the email address and ignores the password. Let's try this in the app.

We are, in fact, logged in as the admin and have access to all their data, including addresses, payment options, and a digital wallet.

Fixing a SQL injection vulnerability

It actually looks like a serious security risk. Let's see how we can fix this. Here's the login function from this shop.

module.exports = function login () {
    return (req: Request, res: Response) => {
        const email = req.body.email;
        const password = security.hash(req.body.password);

        const query = `
            SELECT * FROM Users WHERE email = '${email}' AND password = '${password}'
        `;

        models.sequelize.query(query, {
            model: UserModel,
            plain: true
        }).then((authenticatedUser: { data: User }) => {
            const user = utils.queryResultToJson(authenticatedUser);

            if (user.data?.id) {
                returnSuccessfulLogin(res, user);
            } else {
                returnUnauthorized(res, "Invalid email or password.");
            }
        });
    };
};

As you can see, it takes the email and adds it to the query, and also the password, hashes it for security and adds it to the query too. Then, it sends the query using sequelize and transforms the result into a user model. If the user exists, they will be logged in. Otherwise, the app will return the message that we've already seen, "Invalid email or password."

We can fix this by not directly using the user input and using query parameters instead. Let's replace the email and the password with question marks and add them as parameters to the Sequelize query using the replacements attribute. This way, the query isn't just built from whatever the user enters into the fields. It is parameterized, and the database server makes sure that none of these replacements can be executed as part of the query.

module.exports = function login () {
    return (req: Request, res: Response) => {
        const email = req.body.email;
        const password = security.hash(req.body.password);

        const query = `
            SELECT * FROM Users WHERE email = ? AND password = ?
        `;

        models.sequelize.query(query, {
            replacements:[email,password],
            model: UserModel,
            plain: true
        }).then((authenticatedUser: { data: User }) => {
            const user = utils.queryResultToJson(authenticatedUser);

            if (user.data?.id) {
                returnSuccessfulLogin(res, user);
            } else {
                returnUnauthorized(res, "Invalid email or password.");
            }
        });
    };
};

Conclusion

This was a straightforward but classic example of an injection attack. I hope it effectively demonstrated the importance of never trusting user input and ensuring that nothing a user sends to your application is executed directly. To protect your application against such risks, always validate and sanitize all inputs, or leverage built-in mechanisms like query parameters, as shown in the demo.