Why am I getting an oracle exception when doing a SOQL query

Question

the error I get

caused by: System.UnexpectedException: common.exception.SfdcSqlException:
ORA-01424: missing or illegal character following the escape character

select /*gatherSlowStats*/ count(1)
from core.custom_index_value ind
where (ind.string_value like ? ESCAPE '\')
and (ind.organization_id = ?)
and (ind.key_prefix = ?
and (ind.deleted = '0'))
and (ind.index_num = ?)
and rownum <= ?

The supposed culprit code according the the exception

List<Account> records = [Select custom_field__c From Account where Name
    like :(term + '%') order by Name limit 5];

Is there some special sanitising of strings that I need to do to prevent this error?

edit: Updated my code with the suggestions and ran it with full debug logs

String term = '123123\t22%';
System.debug('term: ' + term);
List<Account> records = [Select custom_field__c From Account where Name
    like :term order by Name limit 5];

16:43:42.047 (47502000)|SYSTEM_METHOD_ENTRY|[16]|System.debug(ANY)
16:43:42.047 (47519000)|USER_DEBUG|[16]|DEBUG|term: 123123\t22%
16:43:42.047 (47526000)|SYSTEM_METHOD_EXIT|[16]|System.debug(ANY)
16:43:42.051 (51810000)|SOQL_EXECUTE_BEGIN|[17]|Aggregations:0|select custom_field__c from Account where Name like :tmpVar1 order by Name limit 5
16:43:42.078 (78747000)|EXCEPTION_THROWN|[17]|System.UnexpectedException: common.exception.SfdcSqlException: ORA-01424: missing or illegal character following the escape character


select /*Apex.Class.myController.doSearch: line 17*/ /*+ ordered use_nl(cft) */
cft.val132 "custom_field__c",
nest."Name",
nest."Id",
nest."RecordTypeId"
from (select *
from (select *
from (select t.name "Name",
upper(t.name) "_SORT_COL_0",
t.account_id "Id",
t.record_type_id "RecordTypeId",
t.account_id repl__0
from sales.account t
where (t.organization_id = '00DN00000003ALJ')
and (t.deleted = '0')
and (t.account_id != '000000000000000'))
where (upper("Name") like ? ESCAPE '\')
order by "_SORT_COL_0" asc nulls first)
where rownum <= ?) nest,
sales.account_cfdata cft
where (cft.account_cfdata_id = repl__0)
and (cft.organization_id = '00DN00000003ALJ')
16:43:42.078 (78880000)|SYSTEM_MODE_EXIT|false
16:43:42.078 (78964000)|FATAL_ERROR|System.UnexpectedException: common.exception.SfdcSqlException: ORA-01424: missing or illegal character following the escape character

update:

salesforce replied with: Please engage your technical team to overcome this issue

Answer 1

I've tried this in my development org

String term = 'Burlington Textiles Corp of America';
List<Account> records = [Select Active__c From Account where Name like :(term + '%') order by Name limit 5];

It works fine for me, is there something odd in your 'term' value?

Some things to try...

Avoid the concatination in the SOQL...

String termForSOQL = term + '%';
List<Account> records = [Select Active__c From Account where Name like :termForSOQL order by Name limit 5];

Add the following to see 'term' value...

System.debug(term);

Also try ....

List<Account> records = 
   [Select Active__c From Account where Name like :'Burlington Textiles Corp of America%') 
     order by Name limit 5];

(e.g. without the variable)

Bottom Line. Basically this should not be happening normally, you've encountered a Salesforce platform bug. You should make a small peace of Apex like the above with the value of 'term' hard coded and report the issue to Salesforce support. My guess is there is something special, a special character or so? Also tell Salesforce what your Org Id is.

Answer 2

Try doing that concatenation in a variable instead of doing it in the query.

Answer 3

Based on your updated answer where you specify term to be '123123\t22%'. If you are looking for a literal backslash in the Account Name then you will need to escape slashes twice in the term:

String term = '123123\\\\t22%';
System.debug('term: ' + term); // DEBUG|term: 123123\\t22%

This will result in an escaped backslash when passed to the query:

List<Account> records = [
    SELECT custom_field__c 
    FROM Account
    WHERE Name LIKE :term
    ORDER BY Name
    LIMIT 5
];

Answer 4

I was getting the same error. The problem was that in my SOQL, I referenced a bad formula field. The formula was something like IF(ISNULL(GEOFIELD),False,True). If I removed that field from the query, everything was fine (NOTE: This was not in the where clause...just a selected field). My solution was to change the formula field to IF(ISBLANK(GEOFIELD.latitude__s) || ISBLANK(GEOFIELD.longitude__s,False,True).

The general issue I'm suggesting here is that I had a formula problem that the formula syntax check did not catch...or did not anticipate would cause a problem if used in a SOQL query. So check your formulas!

Hope this helps somebody debug their issues.