Dynamic SOQL, LIKE and Apostrophes

Question

I want use an Aura component to perform a lookup on Accounts. For example I want to search for an Account named O'Neills.

The query is:

query = 'SELECT Id, Name FROM Account WHERE Name LIKE :value';

The value variable is passed to the controller and modified like so:

value = '%'+value+'%';

The documentation (https://developer.salesforce.com/docs/atlas.en-us.pages.meta/pages/pages_security_tips_soql_injection.htm) quite generally dictates that in dynamic SOQL I should also use:

value = String.escapeSingleQuotes(value);

However at runtime this appears to be evaluated by Database.query() as:

DEBUG: SELECT Id, Name FROM Account WHERE Name LIKE '%O\'Neills%'

Which does not return the desired Account.

If we do not use String.escapeSingleQuotes(value) the query is evaluated as:

DEBUG: SELECT Id, Name FROM Account WHERE Name LIKE '%O'Neills%'

This returns the desired Account.

Why is the ' being treated as a String literal (as opposed to part of the SOQL command), is it because it is a bound variable or because it is between the %s or is it because it is dynamic SOQL?

If the ' is being treated as a String literal, then surely there is no use in escaping single quotes and no risk of injection.

Answer 1

If you are using : to embed the value into the SOQL query, then it handles the escaping of special characters already.

If you build an actual string query and call it using Database.query then you would need to escape the single quotes.