Imagine if someone could access or change any data in your application’s database without permission. What if they could delete records, manipulate information, or even steal sensitive data? If your application is vulnerable to SQL injection, this could happen to you. SQL injection is a type of attack where hackers trick your database into running harmful commands by sending specially crafted queries. In the worst cases, they can gain full control of the database, and suddenly, they have all the power.

Even though SQL injection has been a known threat for decades, it still happens today. In fact, it ranks as the third most critical security risk in the OWASP Top Ten list. In this article, I'll explain what makes an application vulnerable to SQL injection attacks and how to prevent them.

How SQL Injection vulnerabilities occur

Imagine you've built an application with a simple login form that looks like this:

Here, the user is expected to enter their username and password. When the "Sign In" button is pressed, these values are sent to the server, and if they match what's in the database, the user is logged in.

The SQL query that handles this might look like this once the username and password are inserted into the query:

SELECT * FROM users WHERE username='johndoe' AND password='johndoe';

In this case, if there is a matching record for the username johndoe and the password johndoe, the system logs in the user successfully. The database is simply checking if the provided values match what it has stored.

Everything works fine when the user provides regular input, such as letters and numbers. But what happens if they insert special characters, like single quotes (')? For example, what if the user enters johndoe' as the username?

The SQL query would now look like this:

SELECT * FROM users WHERE username='johndoe'' AND password='johndoe';

This will cause a syntax error because the extra single quote breaks the SQL query.

Now, imagine a hacker wants to break into the system by logging in as johndoe, but they don’t know the password. Ideally, they would want the query to look like this:

SELECT * FROM users WHERE username='johndoe';

This would let them log in without needing to verify the password. While they can't directly change how the server writes its queries, they can manipulate the input to achieve this result.

One way to do this is by using SQL comments to ignore the password check. In SQL, two dashes (--) indicate that the rest of the line should be treated as a comment, meaning it won’t be executed. So, the hacker might try entering this as the username:

johndoe'--

This would modify the SQL query to:

SELECT * FROM users WHERE username='johndoe'--' AND password='johndoe';

Because everything after -- is ignored, the password check is skipped, and the hacker is logged in as johndoe without knowing the password.

Now that the hacker can control parts of the query, they can do more than just log in. For example, they could add a new user to the database by injecting an additional SQL command. Here’s how they could do it:

johndoe'; INSERT INTO users (username, password) VALUES ('attacker', 'attackerpassword'); --

By entering this into the username field, the query would become:

SELECT * FROM users WHERE username='johndoe'; INSERT INTO users (username, password) VALUES ('attacker', 'attackerpassword'); -- ' AND password='johndoe';

Now, a new user with the username attacker and password attackerpassword has been added to the system. The hacker can now log in as the attacker user.

The hacker could also use SQL injection to change the password of an existing user. For example, to change the password of the administrator account, they could enter this into the username field:

johndoe'; UPDATE users SET password='attackerpassword' WHERE username='administrator'; --

The resulting query would be:

SELECT * FROM users WHERE username='johndoe'; UPDATE users SET password='attackerpassword' WHERE username='administrator'; -- ' AND password='johndoe';

Now, the password for the administrator account has been changed to attackerpassword, allowing the hacker to log in as the administrator.

As you can see, if an attacker successfully exploits an SQL injection vulnerability, the consequences can be devastating. They can:

• Bypass authentication and log in as other users.
• Add new users to the system.
• Change passwords or modify data.
• Even delete or steal data.

SQL injection attacks can undermine your entire system’s security, allowing unauthorized access to sensitive information. Luckily, there are ways to protect your application from these attacks, which we’ll cover in the next section.

Defending against SQL Injection

The attack in the previous section worked because the user was able to include a character (the single quote ') that had special meaning to the SQL server. Instead of treating the single quote as just another letter, the server interpreted it as part of the SQL command itself, allowing the user to manipulate the query.

To prevent this type of attack, we need to ensure that characters entered by users, like those in a login form, are treated as plain text and not as part of the SQL command. This is done through input sanitization.

Sanitizing input is like checking food before you eat it to ensure it's safe. If something is harmful, it’s either removed or made safe before being served. In the same way, user input is cleaned up before it is sent to the database, ensuring it won’t cause any problems.

For example, when dealing with a single quote in the input, sanitization ensures that the database knows it’s just a character in the input (like a, b, or c) and not a command to end the SQL statement. This is done by escaping the special characters, which tells the database to treat them as normal text.

A better way to protect against SQL injection is by using parameterized queries. In parameterized SQL, the user’s input is treated as a separate parameter, not as part of the SQL command. This means the input can never break out of the query and be interpreted as code. Every character entered by the user will always be treated as plain text, even if it includes characters like single quotes, which are often needed for valid input (e.g., storing text with apostrophes).

By using parameterized queries, we ensure the database treats all input safely, and users can still enter special characters when needed.

Frameworks for mitigating SQL Injection

To make input sanitization and SQL parameterization easier, we can use a framework. Different frameworks exist for various programming languages, and they help handle this process efficiently. Here are some examples:

The exact implementation will vary depending on the language and framework you choose, but here’s an example using Dapper for .NET:

string usernameInput = "johndoe'--";
string passwordInput = "safepassword'";

using (IDbConnection db = new SqlConnection(connectionString))
{
  // Parameterized SQL query
  string sqlQuery = "SELECT * FROM Users WHERE Username = @Username AND Password = @Password";

  // Parameters to prevent SQL injection
  var parameters = new { Username = usernameInput, Password = passwordInput };

  // Execute query
  var user = db.QueryFirstOrDefault<User>(sqlQuery, parameters);
}

In this example, the framework ensures that the username and password inputs are safely handled as parameters in the SQL query, rather than being inserted as plain text. This prevents them from being treated as part of the SQL command.

Using a framework makes parameterization simple because it takes care of most of the hard work for you. The framework manages the proper handling of user input behind the scenes, ensuring your application stays safe from SQL injection attacks.