If you google "Java SQL injection," you'll see that people ask questions like "Is SQL injection possible in Java?" Or even "Do SQL injections still work in 2021?" Well, I hate to be the bearer of bad news, but the answer to both questions is yes.
On a brighter note, know that SQL injection (SQLi) isn't inevitable. Despite being very common and causing a lot of damage, when successful, SQLi attacks are avoidable. In this post, you'll learn what a SQL injection really is, how it works, how to avoid it, and how to make your app more resilient in the chance an attack manages to succeed.
Let's begin.
What Is a Java SQL Injection?
Well, technically speaking, there's no such thing as a "Java SQL injection." When we say that, we're simply referring to SQL injection attacks to Java codebases. The question then becomes, "What is a SQL injection?"
While we do have an entire post dedicated to that, here comes the TL;DR: a SQL injection is an attack where a person manages to inject unauthorized SQL—structured query language—code into an application. As a result, the attacker gains the ability to change the behavior of a legitimate query before it hits the database.
Thus, they might be able to access and steal unauthorized information or, perhaps worse, insert, change, or delete data from the database.
How does someone perform a SQL injection attack? In short, the attacker abuses vulnerabilities in web applications. They enter information that they know—or believe—the app will concatenate, untreated, into a database query.
Now let's look at some examples of SQL injection.
SQL Injection in Java: A Basic Example
Consider the following line of code:
String pw = "123456"; // this would come from the user
String query = "SELECT * from users where name = 'USER' " +
"and password = '" + pw + "'";
It might not seem that bad, right? But now suppose someone entered this as the password:
'; DROP TABLE users --After the concatenation, that single quote at the beginning would match with the one already in the query. The two dashes at the end mean anything after them would be interpreted as comments. So the resulting query would be this:
SELECT * from users where name = 'USER' and password = ''; DROP TABLE users --'Instead of selecting a user, we would be dropping the whole table!
Sure, this is an extreme example. For this attack to work, the attacker would have to know that the table is called users—and they can use another type of SQLi attack to learn that information. Also, the database connection used in this part of the application would have to have drop table privileges, which is strongly recommended against—more on this later.
An Additional Example
Another classic example of SQL injection is what's called boolean SQL injection.
Suppose you have a query like this:
SELECT * FROM projects WHERE user_id = 10This will obviously return projects belonging to the user with an ID equal to 10. Nice. But what about the following one?
SELECT * FROM projects WHERE user_id = 10 OR 1 = 1The query above has an additional condition, which tests whether 1 equals to 1. The comparison doesn't have to be that one, of course. Any comparison that evaluates as true suffices.
Since the injected condition always returns true, everything after WHERE will be evaluated as true, which means the query will retrieve all projects.
How to Prevent Java SQL Injections
The top advice you can adopt to avoid SQL injections—and also other security threats—is to never trust user input.
In practice, that means never concatenating data you get from users, be it from form fields, URL parameters, or other sources.
What should you do then?
Use the Type System in Your Favor
Java is a statically typed language. This means that, unlike languages like JavaScript, data types are known in development time. So use that functionality in your favor.
In practice, that means always using the most specific and restrictive data type. For instance, if your web app has a method for retrieving an instance based on its integer ID, don't have the method accept String as a parameter. Just by using the correct type, you already reduced the likelihood of exploitation. Consider the following code:
@GetMapping("/employees/{id}")
EntityModel one(@PathVariable Long id) { Employee employee = repository.findById(id) //
.orElseThrow(() -> new EmployeeNotFoundException(id)); return EntityModel.of(employee, //
linkTo(methodOn(EmployeeController.class).one(id)).withSelfRel(),
linkTo(methodOn(EmployeeController.class).all()).withRel("employees"));
}The code above comes from Spring Boot's tutorial on how to create a REST API. As you can see, the method accepts a numeric ID, declaring the parameter as Long. Thus, an attempt to pass any non-numeric data—including the kind of text necessary for an SQLi attempt—would result in an error, and the attack attempt would be foiled.
Validate Input Using an Allowlist
Sometimes, the valid values for a given operation are very few. In such scenarios, you might use an allowlist containing all valid values. Like a bouncer in front of a club, you can simply match every entered value against the list, denying entry if a value isn't found.
Suppose your application is a news site. The administrative area allows the site staff to add and manage news stories. Each story can have one of several different statuses, such as draft, published, preview, and so on. It's fair to imagine such an app would have a search feature in which you can filter through the existing stories according to their types.
The user would click on a link, which would redirect to a URL containing, as a parameter, the type of story, like <SOME-URL>&story_status=draft. The "draft" part would then become part of a SQL query.
Since the number of possible statuses is small and previously known, you could use the allowlist approach here. The allowlist could be a simple ArrayList:
List allowList = new ArrayList(4);
allowList.add("draft");
allowList.add("published");
allowList.add("updated");
allowList.add("deleted");Then, performing the input validation would be a matter of a simple check:
if (allowList.contains(urlParam)) {
// proceeds to assemble and execute the SQL QueryUse Parameterized Queries
The best solution for the problem of SQL injections is parameterized queries. That means you don't concatenate user-supplied values. Instead, you use placeholders for those values, ensuring the values themselves are never part of the text of the query. The parameters are then passed to the database through a different mechanism.
The query from our first example could look like this:
String query = "SELECT * from users where name = ? and password = ?";The question marks are placeholders for the actual values. As you can see, the query text never contains the entered values. We do that by creating a prepared statement:
String user = "user"; // comes from user
String password = "password"; // comes from user, gets hashed, etc
String query = SELECT * FROM users WHERE user = ? AND password = ?";PreparedStatement statement = con.prepareStatement(query);myStmt.setString(1, user);
myStmt.setString(2, password);Beware: ORM Can Help, but It's Not Bulletproof
The usage of object-relational mapping (ORM) is encouraged, not only in regards to SQL injection protection but also due to time-saving concerns. It abstracts a lot of the complexities that go into connecting with the database.
However, ORM is in no way bulletproof against SQL injections. Many ORM tools still offer ways for the user to execute raw SQL instructions. If said instructions get compromised, an injection will happen anyway.
Additionally, if you use a framework such as Spring, make sure you educate yourself on the specifics of that framework.
Conclusion
SQL injection attacks exploit the fact that an app concatenates raw, untreated values into SQL queries. So, to avoid this type of attack, you should make sure to avoid concatenating user-supplied data into your queries.
As a general rule, don't trust user input before validating it. This helps not only with SQLi but other injection attacks as well, such as command injections.
As you've seen, fighting SQL injections relies on you using parameterized queries or prepared statements. This is true not only for Java but other languages and tech stacks as well.
Finally, there's another principle you should always have in mind: the principle of least privilege. For example, when generating access tokens for an API, only grant it as few privileges as possible. But what about databases? Remember back at the start of the post when I said that using database users with potentially dangerous privileges, when not necessary, was strongly recommended against? Well, that's why: the principle of least privilege. So, when creating a connection string, don't use the admin user or any user with destructive privileges.
This way, even if an attacker succeeds at injecting malicious code into your app, they won't be able to change or delete data. Sure, just accessing unauthorized data is bad enough, but at least you prevent more damage.
This post was written by Carlos Schults. Carlos is a consultant and software engineer with experience in desktop, web, and mobile development. Though his primary language is C#, he has experience with a number of languages and platforms. His main interests include automated testing, version control, and code quality.
