Scenario:
While updating some code to satisfy SOQL injection issues brought up by security review report, I stumbled across a code that was not brought up. I thought it was similar to the code in the report, so I proceeded to apply the same change. In this case, the recordId originated from a URL parameter while the objName is not. This is the code before:
private static Boolean valId(String recordId, String objName) {
String id = String.escapeSingleQuotes(recordId);
if((id.length() == 15 || id.length() == 18) && Pattern.matches('^[a-zA-Z0-9]*$', id)) {
return (database.countQuery('SELECT count() FROM ' + objName + ' WHERE Id = \'' + id + '\'') > 0) ? true : false;
}
return false;
}
This is the code after making the changes:
private static Boolean valId(String recordId, String objName) {
String id = String.escapeSingleQuotes(recordId);
if((id.length() == 15 || id.length() == 18) && Pattern.matches('^[a-zA-Z0-9]*$', id)) {
return (database.countQuery('SELECT count() FROM :objName WHERE Id = :id ') > 0) ? true : false;
}
return false;
}
Problem:
I am getting the following error:
FATAL_ERROR System.QueryException: unexpected token: ':'
I get the same error when I simplified the code in an Execute Anonymous Window
String objName = 'User';
system.debug(database.countQuery('SELECT count() FROM :objName'));
Other Observations:
It seems countQuery() returns an error when a colon-prefixed variable is used in the FROM section of the query. I was able to run the query successfully by hard-coding the Object name. And after trying the alternative (see Attempted Fixes), AggregateResult also has the same restriction.
Attempted Fixes:
I've looked at the question posted Return type of count() versus count(fieldName) and the alternative seems to be using an AggregateResult list.
Question:
How do I keep my code dry for this particular method without relying on SOQL injection code practice?
Update:
So here's the workaround I used... sigh
private static Boolean valId(String recordId, String objName) {
String id = String.escapeSingleQuotes(recordId);
if((id.length() == 15 || id.length() == 18) && Pattern.matches('^[a-zA-Z0-9]*$', id)) {
if (objName == 'User') {
return (database.countQuery('SELECT count() FROM User WHERE Id = :id') > 0) ? true : false;
} else if (objName == 'Report') {
return (database.countQuery('SELECT count() FROM Report WHERE Id = :id') > 0) ? true : false;
}
}
return false;
}