SQL injection (or SQLi) is one of the most widespread code vulnerabilities. To perform a SQL injection attack, an attacker inserts or "injects" malicious SQL code via the input data of the application. SQL injection allows the attacker to read, change, or delete sensitive data as well as execute administrative operations on the database.
In this lesson, you will learn how SQL injection works and how to protect your code against it. We will begin by using SQL injection to bypass the login screen of a vulnerable web application. We will then dive deeper into the code of that vulnerable application and explain why the SQL injection attack was effective. Finally, we will teach you how to fix and prevent SQL injection in your applications.
We used the quote character ('
) in the password field to inject an unintended string into the query in order to change the query's logic. Using other metacharacters, such as -
or ;
, is also possible.
Let's look at this example in more detail. First, consider the backend logic of startup.io’s login functionality. The following code sample shows a function which aims to verify the user's credentials.
The username and password are passed from the input form to this function and the SQL query is constructed by concatenating the input into the query. This meant we were able to manipulate the query and bypass authentication. The key here is the use of the “%” character placed before our parameters. This will pass the input from the client straight to the database, without performing any sort of checks or validation. You should never use “%” or “+” to merge any user-supplied values into your queries.
The connection object is used to create a “cursor” which allows you to send commands to the database and cursor.fetchone() will fetch the result from the query.
The key here is the construction of the query through string concatenation. Let's try to inject the malicious password which we use to bypass the login into the query. After the string concatenation is done, the WHERE
clause of the query becomes:
WHERE email='user1@startup.io' AND password='idontknow' OR 1=1;
If we consider the following:
AND
has precedence over OR
(true for most SQL implementations)password='idontknow'
is FALSE
for the given email1=1
is always TRUE
we can reduce the WHERE
clause as follows:
-> email='user1@startup.io' AND password='idontknow' OR 1=1-> (TRUE AND FALSE) OR TRUE-> FALSE OR TRUE-> TRUE
By injecting malicious input into the query, we managed to change the query's logic! The query will evaluate as TRUE
even when the supplied password is invalid. Since the query result is used to decide if a given user should be allowed to log in, we’ve bypassed the application's authentication mechanism.
Now that you know how SQL injection works, imagine the implications. By using SQL injection, an attacker can change the logic of the vulnerable query. They can read or modify any data stored in the database. If the vulnerable query is executed as a privileged user, the attacker can run any administrative operations, including deleting the entire database schema.
SQL injection is widespread because it is easily detected and exploited. Any website or application which uses a database is subject to a SQL injection attack. In the past, almost all popular SQL clients and libraries were vulnerable to SQL injection attacks.
To protect your app against SQL injection, you need to prevent user-supplied input from affecting the logic of the executed query. One way to achieve that is through parameterized queries which almost all SQL libraries support. In a parameterized query, the input is "escaped" and handled literally. The database will use the specified type and value when executing the query.
For instance, if our example used a parameterized query, and you were to insert ' OR 1=1'
as a password input to that query, a database client would not interpret that input as SQL. Instead, it would look for a password string ' OR 1=1'
We can fix our vulnerable check_login
function by using a parameterized query. Take a look at the diff below, which compares the vulnerable code with the fixed code. Notice that we removed the % and the ‘ ‘ around the placeholders. The parameters are now passed as an input to the execute
method. Internally, this ensures that the input parameters are escaped by the library and are not treated as literal SQL statements.
To learn more about SQL injection, check out some other great content produced by Snyk:
The SQL injection cheat sheet which lists other methods of preventing SQL injection in your code.
Our blog post on SQL injection in ORM for more information on a different prevention method (ORM) and why sometimes it is not enough to mitigate SQL injection.
Snyk OWASP Top 10 for an overview of other common vulnerabilities present in modern web applications.