As a Java Architect at Sun Microsystems, Carol McDonald has spoken at various conferences including JavaOne, Java University, Sun Tech Days, Sun Network, Sun Code Camps, JDJEdge, and JUGs including Machester, Boston, Maine, Cologne, FAA, Richmond, Memphis, D.C... Carol blogs about the latest technologies that she is speaking about at http://weblogs.java.net/blog/caroljmcdonald/. Before returning to Sun in 2007, Carol worked 2 1/2 yrs as an Architect on massive OLTP Spring/hibernate application to manage > 10 mill loans for the consumer credit division of a leading automoblile manufacturer and a leading bank. Before joining Sun the first time in 1999 Carol worked on Pharmaceutical Intranet applications for Roche in Switzerland, a Telecom Network Management Application for Digital (now HP) in France, a X.400 Email Server for IBM in Germany, and as a student intern for the National Security Agency. Carol holds a M.S. in Computer Science from the University of Tennessee, a B.S. in Geology from Vanderbilt University, and is a Sun Certified Java Architect and Java Language Programmer. Carol is also Fluent in French and German. Carol has posted 11 posts at DZone. View Full User Profile

Top 10 Web Security Vulnerabilities Number 2: Injection Flaws

10.05.2009
| 8437 views |
  • submit to reddit

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:


Published at DZone with permission of its author, Carol Mcdonald.

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)