Parameterize Java JPA ALTER SESSION SQL

Question

I am having a problem with setting a parameter in an SQL Query statement created from a JPA EntityManager. I am working in an EJB and the EntityManager object for the session is valid.

import javax.persistence.EntityManager;
import javax.persistence.Query;

pubic void methodWorks(EntityManager em, String schema) {
    String sqlStmt = "ALTER SESSION SET CURRENT_SCHEMA = " + schema;
    try {
        em.createNativeQuery(sqlStmt).executeUpdate();
    } 
    catch(Exception ex) {
        ex.printStackTrace();
    }
}
pubic void methodFails1(EntityManager em, String schema) {
    String sqlStmt = "ALTER SESSION SET CURRENT_SCHEMA = ?";
    try {
        em.createNativeQuery(sqlStmt).setParameter(1, schema).executeUpdate();
    } 
    catch(Exception ex) {
        ex.printStackTrace();
    }
}
pubic void methodFails2(EntityManager em, String schema) {
    String sqlStmt = "ALTER SESSION SET CURRENT_SCHEMA = ?1";
    try {
        em.createNativeQuery(sqlStmt).setParameter(1, schema).executeUpdate();
    } 
    catch(Exception ex) {
        ex.printStackTrace();
    }
}
pubic void methodFails3(EntityManager em, String schema) {
    String sqlStmt = "ALTER SESSION SET CURRENT_SCHEMA = :inputSchema";
    try {
        em.createNativeQuery(sqlStmt).setParameter("inputSchema", schema).executeUpdate();
    } 
    catch(Exception ex) {
        ex.printStackTrace();
    }
}

The problem is that a Fortify Scan (which this must pass) identifies the sqlStmt in the methodWorks method as being vulnerable to an SQL Injection Attack (from Fortify). The failed methods all report

Internal Exception java.sql.SQLSyntaxErrorException: ORA:-02421 mission or invalid schema authorization identifier.
Error Code 2421
Call: ALTER SESSION SET CURRENT_SCHEMA = ?
   bind => [1 parameter bound]

Merely sanitizing the input parameter "isn't good enough" to pass the Fortify and QA. Setting it as a parameter (which hint-hint: CAN be easily fooled) will pass the Fortify scan and QA requirements.

Answer 1

This query is indeed open to SQL injection because you're using string concatenation. The safe way to handle a query like this is to use parameters.

String sqlStmt = "ALTER SESSION SET CURRENT_SCHEMA = ?";
Query updateQuery = em.createNativeQuery(sqlStmt);

updateQuery.setParameter(0, schema);
updateQuery.executeUpdate();

Parameter values are automatically escaped for you. This saves you time as you don't need to worry about SQL injection any longer. This is solved in the Query/EntityManager class. Also, it makes the query a lot easier to read.