A SQL Injection (SQLi) attack is a malicious attack directed at a web application’s database by exploiting a coding flaw. The goal of SQLi is to expose or change the information the database contains which could be sensitive information. The exposure of sensitive information can lead to credit card fraud, identity theft, and other crimes. The manipulation of the database can void transactions or change account balances.

The coding flaw that permits SQLi occurs when untrusted data can be inserted into a dynamically constructed SQL query. This will trick the interpreter into executing unintended commands.

It is one of the most common attacks in web and Software As a Service (SaaS) products.1

Impacts of SQL Injection

There are several impacts that result from successful SQL Injection (SQLi) attacks for web based applications.

These include:

  • Confidentiality
  • Integrity
  • Authentication
  • Authorization
Confidentiality

If untrusted input is inserted into a dynamically crafted SQL statement used to query a database table, then it is possible that instead of returning the intended data, and attacker can retrieve the entire table. In addition, using a specially crafted SQL statement, that attack can read other tables. This information is then returned to the browser where the original data was intended.

If these tables contain sensitive data such as personal information, or credit card data, then several crimes can result from identity theft to credit card fraud.

Integrity

Untrusted input inserted into a dynamically crafted SQL statement can modify tables in a SQL database resulting in a loss of integrity. This could mean:

  • a change in account balances
  • a change of the price of a sale item
  • a change in the financial results of an organization
  • that the data in that database can be corrupted
Authentication

If software performs user authentication from a SQLi vulnerable query then an attacker can potentially authenticate as any user including administrators.

Authorization

If software performs user authorization check from a SQLi vulnerable query then an attacker can potentially modify the authorization privileges of any user.

Testing for Java SQL Injection

Testing for SQLi by developers can be performed during development and from a blackbox(using the feature) and whitebox(reviewing code) perspective. Developers have a larger focus on whitebox.7

Developer testing can consist of:

  • code review
  • using automated and manual-assist static code analysis tools
  • recognizing untrusted input sources
  • recognizing data flow from untrusted sources to a consumption endpoint (sinks)
  • ensuring that from source to sink that validation occurs
  • ensuring that any data from an untrusted source is validated prior to consumption by code accessing the database
  • ensuring that any code accessing a database follows best practices using parameterized queries.
Recognizing Untrusted Input

Anything from the browser/client must be treated as untrusted including parameter values, parameter names, and HTTP headers.

Recognizing Vulnerable Code

Recognize where code performs database actions and identify any untrusted input that may be injected into the string construction. The following example is vulnerable to SQLi:

public static ResultSet getReviews(Connection con, String dbName, HttpServletRequest Request) {

  String sqlquery = "SELECT * FROM Reviews WHERE ProductID = '"+ request.getParameter("id") + "'";
  Statement statement = connection.createStatement();
  ResultSet results = statement.executeQuery(query);

  return results;
}
  • The HttpServletRequest object request contains a parameter id that is inserted into a string concatenation whose source is untrusted. An attacker recognizing this may insert an attack payload in the id parameter to change the contents of the string used to query the database.
  • An example of a potential attack would be if the attacker provided:
    • http://example/com?id='%20or%20'1'='1'--
  • This would cause the sqlquery string to become:
    • “SELECT * FROM Reviews WHERE ProductID = ‘' or '1' = '1'--
    • Although we are searching for an empty ProductID in the WHERE clause, the or '1' = '1' means every case since 1 always equals 1. This result will return the entire contents of the database.
  • A safer version of this code using parameterized queries would look like this:
    :
    PreparedStatement statement = connection.prepareStatement("SELECT * FROM Reviews WHERE ProductID = ?");
    statement.setString(1, request.getParameter("id"));
    ResultSet results = statement.executeQuery();
    :
    
Tools

Download and install the free intercepting proxy tool known as OWASP ZAP. ZAP contains an intercepting proxy and automated scanner. As you follow the happy path it will capture the traffic between the browser and application and you can perform passive and active scanning against that traffic to find vulnerabilities such as SQLi. It has an additional feature called the HUD for “heads up display” which has a simplified UI.

Another open-source tool is SQLMap which enables multiple attack payload to determine if the parameter is vulnerable to SQLi.

Want to test your own code?

Get started with reshift. Connect with Github , Bitbucket, or Gitlab.

Java SQL Injection Examples

String Concatenation SQLi Example
  • Consider the follow example where a web application attempts to authenticate a user. The user input from the browser consists of a username and a password and these are presented to the SQL query in the form of input variables $username and $password respectively.

    String query = "SELECT * FROM Users WHERE Username=" +
                  request.getParameter("username") +
                  " AND Password=" +
                  request.getParameter("password");
    
  • The general idea here is that the Users table is queried for the given username and if the password field for that username (if it exists in the table) matches the given password then the query returns a value.

  • But the content of username and password come from the users browser, presumably from an input form. This is an untrusted source.

  • This means that anyone on the internet can provide any input for username and password. To an attacker this means that they can provide an attack payload.

  • Consider the following input for username: 1' or '1' = '1'--

  • and password is given as: whatever

  • The resulting SQL query string in code will be:

    String query = "SELECT * FROM Users WHERE Username=1' or '1' = '1'-- AND Password=whatever"
    
  • The -- are comments and the SQL parser will ignore anything after the --

  • This leaves us with:

    SELECT * FROM Users WHERE Username='1' or '1' = '1'--
    
  • Will there be a user in the Users table with a username of 1? We do not care since immediately after that we have the boolean statement OR ‘1’ = ‘1’. Since ‘1’ = ‘1’ is always true then the statement result is true for every row in the Users table. This means the statement will return every row from the users table.

  • If the code returns results of sql statements directly to the browser then the attacker will see the contents of users table including username and passwords.

  • This would occur for any table that a string concatenation SQL statement is attempting to query.

Correction

The OWASP SQLi cheatsheet lists the following primary defences against SQL Injection. They include:

  • Use of Prepared Statements (with Parameterized Queries)
  • Use of Stored Procedures
  • Whitelist Input Validation
  • Escaping All User Supplied Input

Fixes for Java SQL Injection

An unsafe example below takes untrusted user input and concatenates or inserts directly into a SQL command string.

String sqlQuery = "SELECT password FROM user WHERE user_name = "
             + request.getParameter("username");
try {
    Statement statement = connection.createStatement( ... );
    ResultSet password = statement.executeQuery( sqlQuery );
}

Using Prepared statements to fix this vulnerability would look as follows:

String username = request.getParameter("username");
// Perform input validation on username detect attack strings

String sqlQuery = "SELECT password FROM user WHERE user_name = ? ";
PreparedStatement prepedStmt = connection.prepareStatement( sqlQuery );
prepedStmt.setString( 1, username);
ResultSet results = prepedStmt.executeQuery( );

References

OWASP Cross-site Scripting (XSS)
CSO Online: The Biggest Data Breach Fines, Penalties and Settlements So Far
F5: Breach Costs Are Rising with the Prevalence of Lawsuits
Trip Wire: IBM Study Shows Data Breach Costs are on the Rise
Price Waterhouse Cooper: Consumer Intelligence Series: Protect.me
Varonis Blog: Analyzing Company Reputation After a Data Breach: Varonis
OWASP: Testing for SQL Injection (OTG-INPVAL-005)
Cheat Sheet Series: SQL Injection Prevention Cheat Sheet

Thanks for reading 🙂

Want more content like this?