PREVIEW

SQL injection

Improper handling of input during SQL query generation

SQL injection: the basics

What is SQL injection?

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.

About this lesson

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.

Fun fact about sql injection Even Star Trek suffers

Did you know that, according to Star Trek, SQL injection will still plague humanity 300 years from now? In "If Memory Serves", a 2019 Star Trek Discovery episode, a probe used SQL injection when attacking a datastore on one of the ship's shuttlecraft. The attack was discovered by Commander Airiam. Sadly, in the 24th century, people are still writing insecure SQL queries.

Our interactive widgets are optimized for larger screens. To access the complete Snyk Learn experience please switch to tablet or desktop.

SQL injection in Action DO THIS

Vulnerable app

To see how easy it is to perform a SQL injection attack, let's play with a vulnerable web application–the login screen of a fictional company called startup.io. We will attempt to bypass their login screen by supplying malicious input to the password field.

SQL injection under the hood

What happened when we hacked the login page?

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 the checkLogin function, which aims to verify the user's credentials.

Our interactive widgets are optimized for larger screens. To access the complete Snyk Learn experience please switch to tablet or desktop.

The req parameter comes directly from the input form.

How does string manipulation work?

The key here is the construction of sqlQuery 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 email
  • 1=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

A SQL injection attack illustration where a malicious input is injected into a login window of a web application

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.

What is the impact of SQL injection?

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.

Why is SQL injection common?

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.

Fun fact about sql injection Naming is hard

SQL injection has been used by creative people in many unexpected ways. For example, in 2014, an individual in Poland legally renamed his business to "Dariusz Jakubowski x'; DROP TABLE users; SELECT '1" to disrupt the operation of spammers' harvesting bots. Wonder what this peculiar name means? Keep reading to find out!

SQL injection mitigation

How do you mitigate Java SQL injection?

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 a PreparedStatement which almost all SQL databases support.

Consider a possible fix to our SQL injection problem, which uses a PreparedStatement. You can see the broken code on the left and the fixed version on the right.

Our interactive widgets are optimized for larger screens. To access the complete Snyk Learn experience please switch to tablet or desktop.

Prepared statement explained

Notice the creation of a PreparedStatement from the Connection object in the fixed code. This call sends our SQL statement to the database, that is, it "prepares" the statement for future execution. Also, notice the usage of placeholders ? as query parameters. In the broken code example (left), we inject parameters directly into the query. In the fixed code example (right), we put placeholders in all places where we previously injected input parameters. This way, we tell the SQL database to expect input parameters in all locations where it sees ?.

When we send the prepared statement to the database, the statement does not contain any user-supplied data. We supply the input parameters separately by first calling the statement.setString method with the parameter value and later calling the executeQuery method. When the executeQuery method runs, a second request is sent to the database. The request instructs the database to execute the prepared statement with the input parameters we set using the setString method.

Most importantly, the second request does not contain the SQL statement itself. The SQL statement was already sent to the database by our first request (preparedStatement). The second request (executeQuery) sends the parameters and asks the database to execute the query. This way we keep the parameters of the query and the query itself cleanly separated. The job of combining the two falls into the database engine, which eliminates the SQL injection vulnerability.

A SQL injection mitigation illustration where the malicious input injection is prevented by prepared statement

Keep learning

To learn more about SQL injection, check out some other great content produced by Snyk:

Congratulations

You’ve learned what SQL injection is and how to protect your systems from it. We hope you will apply your new knowledge wisely and make your code much safer.

Feel free to rate how valuable this lesson was for you and provide feedback to make it even better! Also, make sure to check out our lessons on other common vulnerabilities.

Try Snyk. Be Secure

Are you sure that you don't have this vulnerability in your codebase?

Quick Start - Start For Free Chevron Right icon
Snyk Learn - Try Snyk