SQL injections are one of the most common web application security threats used by attackers. SQL injection attacks, or SQLi attacks, have remained one of the top three most critical security risks on the Open Worldwide Application Security Project (OWASP) Top 10 list since 2007. 

A SQL injection attack is a web application attack in which the attacker “injects” SQL statements with malicious SQL commands to manipulate or access application data, whether sensitive or public. These attacks leverage areas in web applications that ask for user input. If user inputs in an app are not properly sanitized through input validation, an attacker can use a SQL injection attack to gain access to the associated app datastore. 

How SQL Injection Works

Attackers commonly use SQL injections to infiltrate web applications through user input. This includes form fills for usernames, user IDs, first and last names, and more. If you do not sanitize these inputs before accepting them or make strong use of parameterized SQL statements, an attacker can pass SQL statements through that input that unknowingly run on your database.

For example, say you are taking the input of a user ID in from a user. When your application fetches information about a user, the URL may look something like this:

SELECT * FROM users WHERE id = '42'

They enter their user ID; you take in their input, use it to find their information in your database, and then display their data to them.

But, consider this: instead of inputting their user ID, they input what can be interpreted as a SQL string query as in the following example:

'42' OR '1'='1'

If you take their input as-is, without sanitizing, this will result in something like the following SQL query:

SELECT * FROM users WHERE id = '42' OR '1'='1';

Since 1=1 is always true, this statement (and other syntactically valid queries like it, including complex string concatenations) could return every data field for all users in the users table. This is a classic example of a SQL injection; it does not trigger a warning due to incorrect syntax or a modified query, but instead utilizes the system against itself.

It’s important to note that this is the output the database is designed to provide for this type of query. In this instance, the attacker is not looking to break the application you’ve made…just use what’s already available to access things they shouldn’t. When developing an application, try to consider what things might be accessible that shouldn’t be, and then implement ways to prevent that access from happening. 

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.

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 Query

Use 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.