Number 2 in the Top 10 most critical web application security vulnerabilities identified by the Open Web Application Security Project (OWASP) is Injection Flaws. Injection happens whenever an attacker's data is able to modify a query or command sent to a database, LDAP server, operating system or other Interpreter. Types of injections are SQL, LDAP, XPath, XSLT, HTML, XML, OS command... SQL injection and Cross-Site Scripting account for more than 80% of the vulnerabilities being discovered against Web applications (SANS Top Cyber Security Risks).

SQL Injection Example

Use of string concatenation to build query: SQL Injection can happen with dynamic database queries concatenated with user supplied input, for example with the following query:

"select * from MYTABLE where name=" + parameter

if the user supplies "name' OR 'a'='a' " as the parameter it results in the following:

"select * from MYTABLE where name= 'name' OR 'a'='a'; 

the OR 'a'='a' causes the where clause to always be true which is the equivalent of the following:

"select * from MYTABLE; 

if the user supplies "name' OR 'a'='a' ; delete from MYTABLE" as the parameter it results in the following:

"select * from MYTABLE where name= 'name' OR 'a'='a'; delete from MYTABLE;

the OR 'a'='a' causes the where clause to always be true which is the equivalent of the following:

"select * from MYTABLE; delete from MYTABLE;

some database servers, allow multiple SQL statements separated by semicolons to be executed at once.

SQL Injection can be used to:

• create , read , update, or delete database data

Protecting against SQL Injection

• Don't concatenate user input data to a query or command!
  • Use Query Parameter binding with typed parameters, this ensures the input data can only be interpreted as the value for the intended parameter so the attacker can not change the intent of a query.
• Validate all input data to the application using white list (what is allowed) for type, format, length, range, reject if invalid. (see previous blog entry)
• don't provide too much information in error messages (like SQL Exception Information, table names..) to the user.

Java specific Protecting against SQL Injection

Don't concatenate user input data to a query or command:

• Don't do this with JDBC:
  

  String empId= req.getParameter("empId") // input parameter
  String query = "SELECT * FROM Employee WHERE 
                       id = '" + empId +"'";  

  
  
• Don't do this with JPA:
  
  

  q = entityManager.createQuery(“select e from Employee e WHERE ”
  		+ “e.id = '” + empId + “'”);

Use Query Parameter binding with typed parameters

• With JDBC you should use a PreparedStatement and set values by calling one of the setXXX methods on the PreparedStatement object, For example:
  

  String selectStatement = "SELECT * FROM Employee WHERE id = ? ";
  PreparedStatement pStmt = con.prepareStatement(selectStatement);
  pStmt.setString(1, empId);

  This sets the first question mark placeholder to the value of the input parameter empId in the SQL command. Any dangerous characters - such as semicolons, quotes, etc.. should be automatically escaped by the JDBC driver.
• With JPA or Hibernate you should use Named Parameters. Named parameters are parameters in a query that are prefixed with a colon (:). Named parameters in a query are bound to an argument by the javax.persistence.Query.setParameter(String name, Object value) method. For example:
  

  q = entityManager.createQuery(“select e from Employee e WHERE ”
               + “e.id = ':id'”);
  q.setParameter(“id”, empId);

  This sets the id to the empId in the SQL command, again any dangerous characters should be automatically escaped by the JDBC driver.
• With JPA 2.0 or Hibernate you can use the Criteria API. The JPA 2.0 criteria API providies a typesafe object-based Query API based on a metamodel of the Entity classes, rather than a string-based Query API. This allows you to develop queries that a Java compiler can verify for correctness at compile time. Below is an example using the Criteria API for the same query as before :
  
  

  QueryBuilder qb = em.getQueryBuilder();
  CriteriaQuery<Employee> q = qb.createQuery(Employee.class);
  Root<Employee> e = q.from(Employee.class);
  ParameterExpression<String> id = cb.parameter(String.class);
  
  TypedQuery<Employee> query = em.createQuery(
  q.select(e).where(cb.equal(e.get(Employee_.id), id) );
  query.setParameter(id, empId);

  
  

References and More Information:

• Top 10 most critical web application security vulnerabilities
• Open Web Application Security Project (OWASP)
• SQL Injection
• SQL Injection cheat sheet
• Dynamic, typesafe queries in JPA 2.0
• JPA 2.0 spec
• OWASP Enterprise Security API