Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
amarcuteamarcute 

escapeSingleQuotes method making the query not to return any results

Hi,

 

String.escapeSingleQuotes method making the query not to return any results if string contains a single quote

 

I'm using the method String.escaapeSingleQuotes() to make the user entered input to be safe from external Injections.

Here is the code snippet I'm using:

 

        param = String.escapeSingleQuotes(param);

        return Database.query('SELECT Id, Name FROM Account WHERE Name LIKE \'%'+param+'%\'');

 

This works fine if name does not contain any single quotes. Some of the Account names are having single quotes. For Example: L'oreal, O'connor, O'reilly etc

 

When I pass "L'ore"  as the param, String.escapeSingleQuotes() methods adds some / and ' and makes it as bellow (got it from Debug statements). I tried running the bellow query from workbench/force.com Explorer, it returns 0 results. Is it the expected behaviour?

 

SELECT Id,Name FROM Account WHERE Name LIKE '%L\\\'ore%'

 

Best Answer chosen by Admin (Salesforce Developers) 
Peter_sfdcPeter_sfdc

I tried the following tests: 

 

String str = 'L\'Oreal'; //<-- get the escaped name

System.debug(str); // <--debug output: L'Oreal

String strEsc = String.escapeSingleQuotes(str); //<-- pass through escape method

System.debug(strEsc); //<-- output: L\'Oreal

String wQuotes = '\'%'+strEsc+'%\''; //<-- try to create just the like string

System.debug(wQuotes);  //<--output: '%L\'Oreal%'

List<Account> accts = Database.query('Select Id FROM Account WHERE Name like \'%'+strEsc+'%\'');
//debug of SOQL statement reads: Select Id FROM Account WHERE Name like '%L\'Oreal%'

 So everything I tried seems normal to me. 

 

I'm sure you have, but I have to ask: have you verified that the string you're passing in from the URL param doesn't have extra characters in it? 

All Answers

Peter_sfdcPeter_sfdc

I tried the following tests: 

 

String str = 'L\'Oreal'; //<-- get the escaped name

System.debug(str); // <--debug output: L'Oreal

String strEsc = String.escapeSingleQuotes(str); //<-- pass through escape method

System.debug(strEsc); //<-- output: L\'Oreal

String wQuotes = '\'%'+strEsc+'%\''; //<-- try to create just the like string

System.debug(wQuotes);  //<--output: '%L\'Oreal%'

List<Account> accts = Database.query('Select Id FROM Account WHERE Name like \'%'+strEsc+'%\'');
//debug of SOQL statement reads: Select Id FROM Account WHERE Name like '%L\'Oreal%'

 So everything I tried seems normal to me. 

 

I'm sure you have, but I have to ask: have you verified that the string you're passing in from the URL param doesn't have extra characters in it? 

This was selected as the best answer
amarcuteamarcute

Thank you for the clue. IT was getting escaped twice. Once in the UI and again in the Apex code. Now my code is corrected.