Read all the articles, tried most everything but cannot find a solution other than faith that users will not enter malicious data but:
I am trying to create a user entered query filter but when I use string.escapeSingleQuotes()
as suggested it all breaks with:
no viable alternative at character '\'
Yet if I use the debug of the constructed query, put it in execute anon and wrap it in ' then the query works....
So how then do I take a user entered where clause and append it to an dynamic query string and perform the query safely.
Reproducible example
Class
public with sharing class Example_Controller {
public string whereClause {get;set;}
public string results {get;set;}
public void doQuery(){
results = null;
String tmp = whereClause == null ? 'Where Id != null' : (' Where ' + string.escapeSingleQuotes(whereClause));
integer d;
try{
d = database.countQuery('Select Count() From Contact ' + tmp);
results = 'Found ' + d + 'Contact(s)';
}catch(Exception e){
results = e.getMessage();
}
}
}
VF Page
<apex:page id="Example_Page" controller="Example_Controller">
<apex:form id="tmp">
<apex:inputText value="{!whereClause}"/>
<apex:commandButton action="{!doQuery}" value="Click Me"/>
<apex:outPutPanel layout="block" rendered="{!!isBlank(results)}">
{!results}
</apex:outPutPanel>
</apex:form>
</apex:page>
Using the user input:
Name Like 'Liz%'
I get the following on the screen
Now, if I remove the string.escapSingleQuotes()
it works fine but it is vulnerable:
String tmp = whereClause == null ? 'Where Id != null' : (' Where ' + whereClause); //string.escapeSingleQuotes(whereClause));
How do I construct the dynamic query string to safely perform this count query as well as regular SOQL queries......
The GOAL here is so an advance user can type in a query where clause that could be anything (validated prior to execution) so that a specific query can be filtered according to their needs. It needs to be generic and simple....
Initial Result
based on comments below I contracted this hacked together quickly test and it seems to work....Any comments on making it better?
public static String parseWhereClause(String whereClause){
if(whereClause == null) return '';
String[] tmp = whereClause.replace('\\','').split('\'');
String result = tmp[0];
if(tmp.size() > 1){
for(integer x=1;x<tmp.size();x++) {
result += '\'' + string.escapeSingleQuotes(tmp[x]) + '\'';
if(tmp.size() > x+1){
x++;
result += tmp[x];
}
}
}
return result;
}
The above at least throws an error when trying to use an injection query string:
%' or Performance_rating__c<2 or name like '%
And work fine for both
Name Like 'Liz%'
Name Like \'Liz%\'
Although like sfdcfox says allowing the string to be typed in essentially is in itself an SOQL injection. But in this case I expect the user to specify the records returned and the class respects sharing...