Formatting strings used as SQL queries is security-sensitive. It has led in the past to the following vulnerabilities:
SQL queries often need to use a hardcoded SQL string with a dynamic parameter coming from a user request. Formatting a string to add those parameters to the request is a bad practice as it can result in an SQL injection. The safe way to add parameters to a SQL query is to use SQL binding mechanisms.
This rule raises an issue when an SQL query is built by formatting Strings, even if there is no injection. This rule does not detect SQL injections. The goal is to guide security code reviews and to prevent a common bad practice.
The following method signatures from Java JDBC, JPA, JDO, Hibernate and Spring are tested:
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 may be 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()"); // No issue; hardcoded query
stmt2 = con.createStatement();
ResultSet rs2 = stmt2.executeQuery("select FNAME, LNAME, SSN " +
"from USERS where UNAME=" + user); // Sensitive
pstmt = con.prepareStatement("select FNAME, LNAME, SSN " +
"from USERS where UNAME=" + user); // Sensitive
ResultSet rs3 = pstmt.executeQuery();
//...
}
public User getUserHibernate(org.hibernate.Session session, String data) {
org.hibernate.Query query = session.createQuery(
"FROM students where fname = " + data); // Sensitive
// ...
}
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); // Good; PreparedStatements escape their inputs.
ResultSet rs2 = pstmt.executeQuery();
//...
}
}
public User getUserHibernate(org.hibernate.Session session, String data) {
org.hibernate.Query query = session.createQuery("FROM students where fname = ?");
query = query.setParameter(0,data); // Good; Parameter binding escapes all input
org.hibernate.Query query2 = session.createQuery("FROM students where fname = " + data); // Sensitive
// ...