SQL Injection

OWASP Top 10 - A1 (Injection)




What is SQL Injection?

A SQL injection attack consists of insertion or "injection" of a SQL query via the input data from the client to the application.

A successful SQL injection exploit can read sensitive data from the database, modify database data (Insert/Update/Delete), execute administration operations on the database (such as shutdown the DBMS), recover the content of a given file present on the DBMS file system and in some cases issue commands to the operating system. SQL injection attacks are a type of injection attack, in which SQL commands are injected into data-plane input in order to effect the execution of predefined SQL commands.

owasp.org




Example of a SQL Injection Attack

Here is a basic HTML login form with two inputs: username and password.

      <form method="post" action="/login">
      <input name="username" type="text" id="username">
      <input name="password" type="password" id="password">
      </form> 

The common way for the /login to work is by building a database query. If the variables $request.username and $request.password are requested directly from the user’s input, this can be compromised.

      SELECT id
      FROM Users
      WHERE username = '$request.username'
      AND password = '$request.password'

For example, if a user inserts admin' or 1=1 -- as the username, he/she will bypass the login form without providing a valid username/password combination.

      SELECT id
      FROM Users
      WHERE username = 'admin' or 1=1--
      AND password = 'request.password'

The issue is that the ' in the username closes out the username field, then the -- starts a SQL comment causing the database server to ignore the rest of the string. As the inputs of the web application are not sanitized, the query has been modified in a malicious way.




How to prevent SQL Injection

The source of the problem of SQL Injection (the most important injection risk) is based on SQL queries that use untrusted data without the use of parametrized queries (without PreparedStatement in Java environments).

First of all Hdiv minimizes the existence of untrusted data thanks to the web information flow control system that avoids the manipulation of the data generated on the server side. This architecture minimizes the risk to just the new data generated legally from editable form elements. It's important to note that even using PreparedStatement if the query is based on untrusted data generated previously at server side (for instance the identification id of an item within a list) it's possible to exist a SQL Injection risk.

Although PreparedStatement solves the most of the cases, there are some SQL keywords that can not be used with PreparedStatement, such as ORDER BY. In these cases, you have to concatenate the column name and the order to the SQL query but only after verifying that the column name and order are valid in this context and sanitising them to counter any attempt of SQL Injection attack.





How do I prevent SQL Injection attacks?

Use parametrized queries

Variables passed as arguments to prepared statements will automatically be escaped by the JDBC driver.

      conn = pool.getConnection( );

      PreparedStatement stmt = conn.prepareStatement(
        "SELECT id FROM Account WHERE username=? AND password=?");

      stmt.setString(1, username);
      stmt.setString(2, password);

      rs = stmt.executeQuery(sql);
      if (rs.next()) {
        loggedIn = true;
        out.println("Successfully logged in");
      } else {
        out.println("Username and/or password not recognized");
      }
      

Input validation

Custom validation

Validate all input data to the application using whitelist (what is allowed) for type, format, length, range, reject if invalid. For example, validate an email address and a phone number:

      private Pattern regexPattern;
      private Matcher regMatcher;

      public String validateEmailAddress(String emailAddress) {
          regexPattern = Pattern.compile("^[(a-zA-Z-0-9-\\_\\+\\.)]+@[(a-z-A-z)]+\\.[(a-zA-z)]{2,3}$");
          regMatcher   = regexPattern.matcher(emailAddress);
          if (regMatcher.matches()) {
              return "Valid Email Address";
          } else {
              return "Invalid Email Address";
          }
      }

      public String validateMobileNumber(String mobileNumber) {
          regexPattern = Pattern.compile("^\\+[0-9]{2,3}+-[0-9]{10}$");
          regMatcher   = regexPattern.matcher(mobileNumber);
          if (regMatcher.matches()) {
              return "Valid Mobile Number";
          } else {
              return "Invalid Mobile Number";
          }
      }
      

Hdiv Protection

Although the above defense strategies are important, it depends on the developer to apply them or not. However, Hdiv secures automatically without a developer interaction.

Risk Covered

  • Hdiv is able to perform syntactic analysis of all SQL queries and apply similar techniques used in compilers to deterministically detect successful SQL Injection attacks – resulting in no false positives being generated.
  • Hdiv detects and prevents if any tainted data (data from an external source) contained additional ANSI SQL Syntactical elements and hence a SQL Injection attempt.
  • Hdiv also prevents SQL Injection in legacy applications where the source code may not be available. It does indeed dramatically improve the security of vulnerable legacy applications.