Overview

A Structured Query Language (SQL) injection attack consists of an insertion or injection of a SQL query via the input data from the client to the application. SQL commands are injected into data-plane input that affect the execution of predefined SQL commands.

This attack is possible when developers hand-build SQL statements containing user-supplied data without validation or encoding. The goal of such attacks is to force the database to retrieve and output data to which the user would not otherwise have access. Hackers use SQL injection attacks to access sensitive business or personally identifiable information (PII), which ultimately increases sensitive data exposure.

SQL injection attacks are one of the most prevalent among OWASP Top 10 vulnerabilities, and one of the oldest application vulnerabilities. One recent report lists it as the third most common serious vulnerability.

Impact

A successful SQL injection exploit can read sensitive data from the database, modify database data (insert, update or delete), execute administrative operations on the database, recover the content of a file present in the database management system and even issue commands to the operating system in some instances.

One example: An attacker could use SQL Injection on a vulnerable application in order to query the database for customer credit card numbers and other data, even if it wasn't part of the query the developer created.

How is this exploited?

To perform a SQL injection attack, an attacker must locate a vulnerable input in a web application or webpage. When an application or webpage contains a SQL injection vulnerability, it directly uses input in the form of a user’s SQL query. The hacker can execute a specifically crafted SQL command as a malicious cyber intrusion. Then, leveraging malicious code, a hacker can acquire a response that provides a clear idea about the database construction and can thereby access all the information in the database.

An attacker may perform SQL injection with the following approaches:

A SQL statement that is always true. A hacker executes a SQL injection with a SQL statement that is always true. For instance, 1=1; instead of just entering the “wrong” input, the hacker uses a statement that will always be true.

Entering “100 OR 1=1” in the query input box will return a response with the details of a table.

"OR ""="

This SQL injection approach is similar to the above. A bad actor needs to enter "OR ""=" into the query input box. These two signs serve as the malicious code to break into the application. 

Consider the following example:An attacker seeks to retrieve user data from an application and can simply type “OR=” in the user ID or password. As this SQL statement is valid and true, it will return the data of the user table in the database.

Types of SQL injections

SQL injection can be categorized into three categories: in-band, blind and out-of-band.

In-band SQL injection Is the most frequent and commonly used SQL injection attack. The transfer of data used in in-band attacks can either be done through error messages on the web or by using the UNION operator in SQL statements.

There are two types of in-band SQL injection: union-based and error-based.

Union-based SQL injection. When an application is vulnerable to SQL injection and the application’s responses return the results for a query, attackers use the UNION keyword to retrieve data from other tables of the application database.

Error-based SQL injection. The error-based SQL injection technique relies on error messages thrown by the application database servers. Here, attackers use the error message information to determine the entities of the database.

Blind SQL injection Attacks, after sending a data payload, the attacker observes the behavior and responses to determine the data structure of the database.

There are two types of blind or inferential SQL injection attacks: Boolean and time-based.

Boolean based. The Boolean-based technique sends SQL queries to the database to force the application to return a Boolean result — that is, either a TRUE or FALSE result. Attackers perform various queries blindly to determine the vulnerability.

Time based. The time-based SQL injection attack is often used when an application returns generic error messages. This technique forces the database to wait for a specific time. The response time helps the attacker to identify the query returns as TRUE or FALSE.

Out-of-band SQL injections

The out-of-band SQL injection attack requests that the application transmit data via any protocol — HTTP, DNS or SMB. To perform this type of attack, the following functions can be used on Microsoft SQL and MySQL databases, respectively:

MS SQL: master..xp _dirtree

MySQL: LOAD_FILE()

SQL injection in Java

The most effective method of stopping SQL injection attacks is to only use Mapping (ORM) like Hibernate that safely handles database interaction.

If you must execute queries manually, use Callable Statements for stored procedures and Prepared Statements for normal queries.

Both of these application programming interfaces (APIs) utilize bind variables, and both techniques completely stop the injection of code if used properly.

You must still avoid concatenating user-supplied input to queries and use the binding pattern to keep user input from being misinterpreted as SQL code.

Take this unsafe query as an example:

String user = request.getParameter("user");
String pass = request.getParameter("pass");
String query = "SELECT user_id FROM user_data WHERE 
user_name = '" + user + "' and user_password = '" + pass +"'";
try {
  Statement statement = connection.createStatement( 
ResultSet results = statement.executeQuery( query ); // Unsafe!
}

Now let's use  PreparedStatement  to make the above query safe:

String user = request.getParameter("user");
String pass = request.getParameter("pass");
String query = "SELECT user_id FROM user_data WHERE user_name 
= ? and user_password = ?";
try {
  PreparedStatement pstmt = connection.prepareStatement( query );
  pstmt.setString( 1, user );
  pstmt.setString( 2, pass );
  pstmt.execute(); // Safe!
}

There are some scenarios, like dynamic search, that make it difficult to use parameterized queries because the order and quantity of variables is not predetermined.

If you are unable to avoid building such a SQL call on the fly, then validation and escaping all user data is necessary.

Deciding which characters to escape depends on the database in use and the context into which the untrusted data is being placed.

This is difficult to do by hand, but luckily the ESAPI (the OWASP Enterprise Security API) library offers such functionality. 

Here's an example of safely encoding a dynamically built statement for an Oracle database using untrusted data:

Codec ORACLE_CODEC = new OracleCodec();
String user = req.getParameter("user");
String pass = req.getParameter("pass");
String query = "SELECT user_id FROM user_data WHERE user_name = '" +
     ESAPI.encoder().encodeForSQL( ORACLE_CODEC, **user**) + 
     "' and user_password = '" +
     ESAPI.encoder().encodeForSQL( ORACLE_CODEC, **pass**) + 
     "'";{ {/javaBlock} }

MyBatis framework

MyBatis doesn't modify or escape the string when the ${} syntax is used in dynamic SQL queries.

This causes the mapped value to be directly inserted into the query, which can lead to SQL injection attacks.

Applications using MyBatis should use the #{} syntax on untrusted data.

This tells MyBatis to generate a String Substitution, which is an incomplete SQL query with placeholders that, at run-time, are replaced by user input. This treats user input as parameter content instead of as part of a SQL command.

Second-Order SQL injection

With a maliciously crafted input, an end user could change the structure of the SQL query and perform a Second-Order SQL injection attack, despite not being executed directly at runtime.

Second-Order SQL injection is possible when user-supplied data is stored by the application and later triggered and included in an unsafe SQL query.

The goal of such attacks is to force the database to retrieve and output data to which the user would not otherwise have access. For example, an attacker could use Second-Order SQL injection on a vulnerable web application by registering an unsafe username. This would then be stored in the User table, and executed at a later date to retrieve or manipulate data.

Impact

A successful Second-Order SQL injection exploit can read sensitive data from the database. Additionally, it can also extend to privilege escalation, account hijacking and, in some cases, it may be possible for an attacker to gain shell access to the database server.

Prevention

The most effective method of stopping Second Order SQL injection attacks is to only use Mapping (ORM) like Hibernate that safely handles database interaction. If you must execute queries manually, use Callable Statements for stored procedures and Prepared Statements for normal queries.

Both of these APIs utilize bind variables. Both techniques completely stop the injection of code if used properly. You must still avoid concatenating user supplied input to queries and use the binding pattern to keep user input from being misinterpreted as SQL code.

Take this unsafe query as an example:

String user = request.getParameter("user");
String pass = request.getParameter("pass");
String query = "SELECT user_id FROM user_data WHERE user_name = '" 
+ user + "' and user_password = '" + pass +"'";
try {
Statement statement = connection.createStatement( );
}
ResultSet results = statement.executeQuery( query ); // Unsafe!}

Now, let's fix this using PreparedStatement:

String user = request.getParameter("user");
String pass = request.getParameter("pass");
String query = "SELECT user_id FROM user_data WHERE user_name = ? 
and user_password = ?";
try {
PreparedStatement pstmt = connection.prepareStatement( query );
pstmt.setString( 1, user );.setString( 2, pass );
pstmt.execute(); // Safe!
}

Codec ORACLE_CODEC = new OracleCodec();
String user = req.getParameter("user");
String pass = req.getParameter("pass");
String query = "SELECT user_id FROM user_data WHERE user_name = '" 
+ ESAPI.encoder().encodeForSQL( ORACLE_CODEC, **user**) + 
"' and user_password = '" 
+ ESAPI.encoder().encodeForSQL( ORACLE_CODEC, **pass**) + "'";

Congratulations!

You’ve learned what a Java SQL injection is and how to protect your systems from it. We hope you will apply your new knowledge wisely as you code! Feel free to share this with your network. Also, make sure to check out our lessons on other common vulnerabilities.

Want to make a revision on this learning module? Click here to create a pull request!

Featured in:

BLOG: Why SQL Injection Attacks Still Need to be Dealt With

BLOG: The Top 10 app-attack trends in the financial sector in 2022