Executing SQL queries is security-sensitive. It has led in the past to the following vulnerabilities:
SQL injection is still one of the top 10 security vulnerabilities. Applications that execute SQL commands should neutralize any externally-provided values used in those commands. Failure to do so could allow an attacker to include input that changes the query so that unintended commands are executed, or sensitive data is exposed. Instead of trying to sanitize data by hand, SQL binding mechanisms should be used; they can be relied on to automatically perform a full sanitization.
This rule checks a variety of methods from different frameworks which are susceptible to SQL injection if not used properly. Frameworks which are covered are Java JDBC, JPA, JDO, Hibernate and Spring. The following specific method signatures are tested. Any SQL query built by concatenating or formatting Strings is considered susceptible. The goal is to guide security code reviews.
org.hibernate.Session.createQuery org.hibernate.Session.createSQLQuery java.sql.Statement.executeQuery java.sql.Statement.execute java.sql.Statement.executeUpdate java.sql.Statement.executeLargeUpdate java.sql.Statement.addBatch java.sql.Connection.prepareStatement java.sql.Connection.prepareCall java.sql.Connection.nativeSQL javax.persistence.EntityManager.createNativeQuery javax.persistence.EntityManager.createQuery org.springframework.jdbc.core.JdbcOperations.batchUpdate org.springframework.jdbc.core.JdbcOperations.execute org.springframework.jdbc.core.JdbcOperations.query org.springframework.jdbc.core.JdbcOperations.queryForList org.springframework.jdbc.core.JdbcOperations.queryForMap org.springframework.jdbc.core.JdbcOperations.queryForObject org.springframework.jdbc.core.JdbcOperations.queryForRowSet org.springframework.jdbc.core.JdbcOperations.queryForInt org.springframework.jdbc.core.JdbcOperations.queryForLong org.springframework.jdbc.core.JdbcOperations.update org.springframework.jdbc.core.PreparedStatementCreatorFactory.<init> org.springframework.jdbc.core.PreparedStatementCreatorFactory.newPreparedStatementCreator javax.jdo.PersistenceManager.newQuery javax.jdo.Query.setFilter javax.jdo.Query.setGrouping If a method is defined in an interface, implementations are also tested. For example this is the case for
org.springframework.jdbc.core.JdbcOperations , which is usually used as org.springframework.jdbc.core.JdbcTemplate).
You are at risk if you answered yes to this question.
You can also reduce the impact of an attack by using a database account with low privileges.
public User getUser(Connection con, String user) throws SQLException {
Statement stmt1 = null;
Statement stmt2 = null;
PreparedStatement pstmt;
try {
stmt1 = con.createStatement();
ResultSet rs1 = stmt1.executeQuery("GETDATE()"); // Compliant; parameters not used here
stmt2 = con.createStatement();
ResultSet rs2 = stmt2.executeQuery("select FNAME, LNAME, SSN " +
"from USERS where UNAME=" + user); // Noncompliant; parameter concatenated directly into query
pstmt = con.prepareStatement("select FNAME, LNAME, SSN " +
"from USERS where UNAME=" + user); // Noncompliant; parameter concatenated directly into query
ResultSet rs3 = pstmt.executeQuery();
//...
}
public User getUserHibernate(org.hibernate.Session session, String userInput) {
org.hibernate.Query query = session.createQuery( // Compliant
"FROM students where fname = " + userInput); // Noncompliant; parameter binding should be used instead
// ...
}
public User getUser(Connection con, String user) throws SQLException {
Statement stmt1 = null;
PreparedStatement pstmt = null;
String query = "select FNAME, LNAME, SSN " +
"from USERS where UNAME=?"
try {
stmt1 = con.createStatement();
ResultSet rs1 = stmt1.executeQuery("GETDATE()");
pstmt = con.prepareStatement(query);
pstmt.setString(1, user); // Compliant; PreparedStatements escape their inputs.
ResultSet rs2 = pstmt.executeQuery();
//...
}
}
public User getUserHibernate(org.hibernate.Session session, String userInput) {
org.hibernate.Query query = session.createQuery("FROM students where fname = ?");
query = query.setParameter(0,userInput); // Parameter binding escapes all input
// ...