Click below link to read Mitigation Strategies 1 to 4
https://community.veracode.com/s/article/How-to-fix-CWE-89-SQL-Injection-flaws


Mitigation Strategy 5] Dynamic Table names and Columns names

We may come across flaws that flagged for CWE 89 SQL Injection on perfectly parameterized PreparedStatement. Because we realized that, the SQL queries use variables for their table name or column name.
For example.

Class EmployeeDaoImpl{
Public Employee insertEmp(Employee employee){

.. .. .. 
//String query = "INSERT INTO EmployeeTbl(id, name, email) VALUES(’?’,’?’,’?’)"; 
String query = "INSERT INTO " +tblName + " (id, " +col1+ "," +col2+ ") VALUES(’?’,’?’,’?’)"; 
PreparedStatement pstatement = connection.prepareStatement(query); 
Pstatement.setString(1,employee.getId());
Pstatement.setString(2,employee.getName());
Pstatement.setString(3,employee.getEmail());
pstatement.executeUpdate(); 
.. .. .. 
}
Public int updateEmployee(Employee emp){
.. .. .. 
//String query = "UPDATE EmployeeTbl SET name = '?', email = '?' WHERE id =’?’)"; 
String query = "UPDATE + tblName + SET " +col1+ "= '?', " +col2+ "= '?' WHERE id =’?’)"; 
PreparedStatement pstatement = connection.prepareStatement(query); 
Pstatement.setString(1,employee.getName());
Pstatement.setString(2,employee.getEmail());
Pstatement.setString(3,employee.getId());
pstatement.executeUpdate(); 
.. .. .. 
}
}

Here we see that table names and column names are dynamic and cannot be parameterized the way we parameterized the values of the columns against records.
So depending on how & from where these variables are initialized will help us evaluate if these variables can be trusted. 
They either are retrieved from a property file, hardcoded elsewhere in application code, or derived from untrusted sources. 
Based on the source, we will put in place appropriate mitigation. 

Scenario 1. Hardcoded elsewhere in the application code.
We can use the Whitelist approach. Refer to this link on how the whitelist works. 
https://community.veracode.com/s/article/allowlist-helps-fix-cwe

Class DbMaster{

Public static String tbl_Employee = “EmployeeTbl”;
Public static String tbl_Employee_test = “EmployeeTestTbl”;
Public static String tbl_Employee_QA = “qa.EmployeeTbl”;
Public static String tbl_Employee_prod = “prod.EmployeeTbl”;

Public static String tbl_Department = “DepartmentTbl”;
Public static String tbl_Department_test = “DepartmentTestTbl”;
Public static String tbl_Department_QA = “qa.DepartmentTbl”;
Public static String tbl_Department_prod = “prod.DepartmentTbl”;

Static List allTablesWhiteList= new LinkedList<String>();
Static{
allTablesWhiteList.add(tbl_Employee);
allTablesWhiteList.add(tbl_Employee_test);
allTablesWhiteList.add(tbl_Employee_QA);
allTablesWhiteList.add(tbl_Employee_prod);
allTablesWhiteList.add(tbl_Department);
allTablesWhiteList.add(tbl_Department_test);
allTablesWhiteList.add(tbl_Department_QA);
allTablesWhiteList.add(tbl_Department_prod);
.. .. ..
}

public static String cleanTable(String tblName){
    for(String tname: allTablesWhiteList){
    if(tblName!=null && tblName.equalsIgnoreCase(tname))
    return tname;
    }
return null;
}

Public static String col_name = “name”;
Public static String col_fName = “firstname”;
Public static String col_lName = “lastname”;
Public static String col_email = “email”;
Public static String col_location = “location”;

Static List allColsWhiteList= new LinkedList<String>();
Static{
allColsWhiteList.add(col_name);
allColsWhiteList.add(col_fName);
allColsWhiteList.add(col_lName);
allColsWhiteList.add(col_email);
allColsWhiteList.add(col_location);
.. .. .. 
}

public static String cleanColumn(String colName){
    for(String cname: allColsWhiteList){
    if(colName!=null && colName.equalsIgnoreCase(tname))
    return tname;
    }
return null;
}

So we can modify our Dao implementation to use cleanTable() and cleanColumn()  to sanitize the variables used.

Class EmployeeDaoImpl{
Public Employee insertEmp(Employee employee){

.. .. .. 
//String query = "INSERT INTO EmployeeTbl(id, name, email) VALUES(’?’,’?’,’?’)"; 
String query = "INSERT INTO " + DbMaster.cleanTable(tblName) + " (id, " + DbMaster.cleanColumn(col1)+ "," + DbMaster.cleanColumn(col2) + ") VALUES(’?’,’?’,’?’)"; 
PreparedStatement pstatement = connection.prepareStatement(query); 
Pstatement.setString(1,employee.getId());
Pstatement.setString(2,employee.getName());
Pstatement.setString(3,employee.getEmail());
pstatement.executeUpdate(); 
.. .. .. 
}
Public int updateEmployee(Employee emp){
.. .. .. 
//String query = "UPDATE EmployeeTbl SET name = '?', email = '?' WHERE id =’?’)"; 
String query = "UPDATE + DbMaster.cleanTable(tblName) + SET " + DbMaster.cleanColumn(col1)+ "= '?', " + DbMaster.cleanColumn(col2)+ "= '?' WHERE id =’?’)"; 
PreparedStatement pstatement = connection.prepareStatement(query); 
Pstatement.setString(1,employee.getName());
Pstatement.setString(2,employee.getEmail());
Pstatement.setString(3,employee.getId());
pstatement.executeUpdate(); 
.. .. .. 
}
}

Scenario 2 & 3 ] Retrieved from a property file or other sources including untrusted data.
Here, we may not build up a whitelist of table names or column names but define a validation utility to validate if the given table name or column name is adhering to specific regular expressions (Pattern checking)
A table name & column is usually ALPHANUMERIC and has symbols like _ Underscore character. It must begin with an alphabetic character.
A table name that holds DB schema can have a single (.) period operator as well.
A table name or column name usually are not very lengthy, so a length check can also be implemented for e.g. maximum of 20 characters are allowed.

 public class RegexValidationUtil {
     public static final String RGX_TableName = "^[a-zA-Z][a-zA-Z0-9_]*\\.?[a-zA-Z][a-zA-Z0-9_]*$";
    public static final String RGX_ColName = "^[a-zA-Z][a-zA-Z0-9_]*$";
//column name can also have Period operator, use RGX_TableName same as ColName.
    public static final String RGX_Alphanumeric = "^[a-zA-Z0-9]+$";
    
    public static boolean validate(String str, String regex){
         Pattern pattern = Pattern.compile(regex);
         Matcher matcher = pattern.matcher(str);
         return matcher.matches();
    }
   public static boolean validateTbl(String str){return validate(str, RGX_TableName); 
    }
    public static boolean validateCol(String str){return validate(str, RGX_ColName);}
    
    public static boolean validateRgxAndLength(String str, String regex, int maxlength){
         if(str!=null && str.length() <= maxlength){
             return validate(str,regex);
         return false;
    }
}

Class EmployeeDaoImpl{
Public Employee insertEmp(Employee employee){

.. .. .. 
//String query = "INSERT INTO EmployeeTbl(id, name, email) VALUES(’?’,’?’,’?’)"; 
if(RegexValidationUtil.validateTbl(tblName) 
&& RegexValidationUtil.validateCol(col1)
&& RegexValidationUtil.validateCol(col2)){
String query = "INSERT INTO " +tblName+ " (id, " +col1+"," +col2+ ") VALUES(’?’,’?’,’?’)"; 
PreparedStatement pstatement = connection.prepareStatement(query); 
Pstatement.setString(1,employee.getId());
Pstatement.setString(2,employee.getName());
Pstatement.setString(3,employee.getEmail());
pstatement.executeUpdate(); 
}//if
.. .. .. 
}
}

The Veracode SAST scanner would continue to flag this for CWE 89.
This is mitigation by design. We have to manually acknowledge the fact that the table name and column names used are safe can be trusted.

In cases where ORDER BY or GROUP BY clauses are dynamic as well, we shall accordingly define a suitable pattern for it.
The whitelist approach can be used to ensure the comma-separated column names are valid and can be trusted.

Below we will see an example of Regex based validation utility for the same.
Example 1:

The below code uses a variable for the ORDER BY clause. 

Pattern for Comma-separated Column names would be :
 

public class RegexValidationUtil {
.. ..
public static final String RGX_CSV_ColName = "^\\s*[a-zA-Z][a-zA-Z0-9_]*(\\s*,?\\s*[a-zA-Z][a-zA-Z0-9_]*)*\\s*$";
.. .. 
}    

Example 2: where it specifies ASC or DESC sorting order.

The modified pattern would be :

public class RegexValidationUtil {
.. ..
public static final String RGX_CSV_OrderBy = "^\\s*[a-zA-Z][a-zA-Z0-9_]*\\s*(asc|desc|ASC|DESC)?(\\s*,\\s*[a-zA-Z][a-zA-Z0-9_]*\\s*(asc|desc|ASC|DESC)?)*\\s*$";
.. .. 
}    
.. .. 
If(RegexValidationUtil.validate(orderbystr,RegexValidationUtil.RGX_CSV_OrderBy)){
String query = "SELECT * FROM Customers ORDER BY" + orderbystr;
}

Mitigation Strategy 6] Setting up for PreparedStatement using IN Clause

Here we will see an example of how to make use of a dynamic list that can be used in IN Clause :

We will try to use parameterized Prepared statement. 

Since the number of cities could be dynamic, we will have to figure out a way to form the SQL query.
Assume we have a list (ArrayList) of cities to use. We can define the SQL query this way:

 
java.util.ArrayList<String> cities;
//read the variables emprole, citites, stat.
String query = "SELECT * FROM employees WHERE emp_role= ? AND office_location IN "+ DBUtil.makeINClause(cities.size())+" AND status = ?";
//System.out.println(query) 
//SELECT * FROM employees WHERE emp_role= ? AND Office_location IN (?,?,?,?,?) AND status = ?; 
//. . . 
PreparedStatement pstmt = con.prepareStatment(query);
int que_mark_no =1;
pstmt.setString(que_mark_no++, emprole);
que_mark_no = DBUtil.setINClauseParameters(pstmt, cities, que_mark_no); 
pstmt.setString(que_mark_no++, stat); 
. . . 

The utility functions are defined here:

class DBUtil {
    public static String makeINClause(int size){
        StringBuffer csqm = new StringBuffer("(");
        for(int i=0;i<size;i++) if(i<size-1) csqm.append("?,");else csqm.append("?");
        csqm.append(")");
        return csqm.toString();
    }
    
     public static int setINClauseParameters(PreparedStatement pstmt, List<String> list, int que_pos){
         for(String element: list){
             {
                pstmt.setString(que_pos, element);
                que_pos++;
             }    
            return que_pos;
      }
 }
    

Mitigation Strategy 7] ESAPI Escaping using Database-specific codec.

There is an ESAPI database codec available that can be used as one of the acceptable mitigation strategies.

class DBUtil {
    public static String encodeSqlParam(String param){
    Codec ORACLE_CODEC = new OracleCodec();
    return ESAPI.encoder().encodeForSQL( ORACLE_CODEC, param );
    }
.. .. 
}

 
So, if you had an existing Dynamic query being generated in your code that was going to Oracle that looked like this:

We would rewrite to ensure encoding is done on those variables. 

And it would now be safe from SQL injection, regardless of the input supplied.
Read more from references.

Mitigation Strategy 8] Encoding the untrusted data.

Using Hex encoding
If the Hex encoding facility is provided by the Database, this is another mitigation strategy that can be implemented.
Here, We do not compare the Column with value directly i.e. col = $variable. But instead, we compare the hex-encoded value of the column with the hex-encoded value of the user-entered variable.

We would rewrite it like : 

We have to provide the implementation of DBUtil.encodeHex() function.

class DBUtil {
    public static String encodeHex(String param){
    returns Hex converted value
    }
.. .. 
}

Here, hex_encode() used inside the SQL query is the Database provided function. It could be different for different databases.
Dao class implementation should hex-encode the untrusted data before using it in the SQL query using the utility function described above.
It only helps with a WHERE clause used either SELECT queries and UPDATE or DELETE (with WHERE Clause) commands. It cannot be used to add/update a record.
Please note that this method brings in the additional cost of computing hex at the programming side as well as the database side. Hence, this method can be used for limited parameters only.

Using Base 64 encoding
Similar to Hex encoding; We can also use Base64 encoding & decoding.
We would rewrite it like :

We can also change the approach to decode the value and then compare with the DB column.

We have to provide the implementation of DBUtil.encodeBase64() function.

class DBUtil {
    public static String encodeBase64(String param){
    returns Base64 encoded value
    }
.. .. 
}

 
Here, please note that base64encode() and base64decode() are database provided functions. It could be different for different database.
Alternatively, you can use the encoding function which is available on the database and can be implemented within your own code.

If Scanner continues to flag CWE 89 for executing queries, it can be proposed as mitigation by design.
 

Mitigation Strategy 9] Using Stored Procedure

Stored Procedures are equally prone to SQL injection issues. 
Whenever possible please apply sanitization techniques to ensure the untrusted data are safe to be used.
Some database programmers believe that by using stored procedures, their code is safe from SQL injection attacks. That is not true because, if the dynamic query is used inside the stored procedures and the dynamic query is constructed by concatenating the parameters it is at high risk of attack.
The easiest way to prevent SQL injection from happening is to use parameters and to execute the dynamically generated statement.

The following code example uses a CallableStatement, Java's implementation of the stored procedure interface, to execute the same database query. The sp_getAccountBalance stored procedure would have to be predefined in the database and implement the same functionality as the query defined above.

String custname = request.getParameter("customerName");
try {
  CallableStatement cs = connection.prepareCall("{call sp_getAccountBalance(?)}");
  cs.setString(1, custname);
  ResultSet results = cs.executeQuery();
  // … result set handling
} catch (SQLException se) {
  // … logging and error handling
}

Stored Procedure:
In below example, the query is dynamically created allowing SQL injection.

CREATE OR REPLACE PROCEDURE sp_getAccountBalance(custName VARCHAR(50)) AS
BEGIN
execute immediate 'Select * from dbo.Account acc where acc.customerName = '||
custName|| ';';
END

This is runtime-created SQL hence vulnerable for SQL injection.
We will replace it with a compile-time fixed SQL query. Here the command is immutable, therefore safe from the SQL injection.

CREATE OR REPLACE PROCEDURE sp_getAccountBalance(custName VARCHAR(50)) AS
BEGIN
Select * from dbo.Account acc where acc.customerName = custName;
END

The following code example uses a SqlCommand, .NET's implementation of the stored procedure interface, to execute the same database query. The sp_getAccountBalance stored procedure would have to be predefined in the database and implement the same functionality as the query defined above.

 Try
   Dim command As SqlCommand = new SqlCommand("sp_getAccountBalance", connection)
   command.CommandType = CommandType.StoredProcedure
   command.Parameters.Add(new SqlParameter("@CustomerName", CustomerName.Text))
   Dim reader As SqlDataReader = command.ExecuteReader()
   '...
 Catch se As SqlException
   'error handling
 End Try

Mitigation Strategy 10] Veracode Custom cleanser annotation

Mitigation Strategy 11] Defense In-depth.

References