What's the correct way to escape single quote character

Posted by mzorbis on 14-Mar-2018 07:18

Hi,

I seem to have a problem with using two single quote characters ('') to escape a single quote character (') in a string literal when calling selectQuery SOAP API function.

The escaped string used for query argument of selectQuery call is:

SELECT id FROM test1 WHERE externalReference1 = 'TEST''DATA' 

This produces a MySQL error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''DATA)' at line 1

Can you help, please? Thanks.

All Replies

Posted by Anvi Jain on 22-Mar-2018 06:14

Hi Marcin,

The query is working fine for me. However, to investigate further, I need some information from your end. For that purpose, I am sharing a list of field types whose information would help me to deduce the problem that you are facing. If you have configured any fields that belong to the following list, please share all the details (especially Integration Code) of those fields.

Group of Checkboxes

Dependent Picklist

Email Template

Radio Buttons

Picklist

Picklist (multiple)  

Workflow Status

Document Template

Along with the above details, please share the webapi.log file as well.

I will get back with further information once I have the above details from you.

Regards,

Anvi

Posted by Ricardo Rafols on 23-Mar-2018 06:17

Hi mzorbis,

You can espace single quotes using backslash.

I.e 'TEST\'\'DATA'


Regards,
Ricardo

Posted by mzorbis on 26-Mar-2018 03:02

Hi Anvi,

At the moment I've got the webapi.log only (replaced actual IP address with 0.0.0.0 intentionally):

[2018-03-23 08:30:53,666] [RID=01edb035-00f8-44de-8140-0017cd16e3ba] [Comp=webapi] [CustId=630468] [IP=0.0.0.0] selectQuery query=SELECT id FROM locationAddress WHERE externalReference1 = 'BB''S BABY DONUTS' AND addressAccount = '949184'

[2018-03-23 08:30:53,670] [RID=01edb035-00f8-44de-8140-0017cd16e3ba] [Comp=webapi] [CustId=630468] [IP=0.0.0.0] ===> Error in thread http-apr-8080-exec-9 at 03/23/2018 08:30 AM: selectQuery error: SELECT A.OBJ_ID FROM RB_OBJ_DATA A WHERE A.OBJ_DEF_ID=640129 AND (A.STR5 = 630488'S BABY DONUTS' AND A.INTG3 = '949184')

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''S BABY DONUTS' AND A.INTG3 = '949184')' at line 1

Cheers,

Marcin.

Posted by Manooj Murali on 26-Mar-2018 03:07

Can you check if you have a picklist in your locationAddress object which has the picklist item code as ''BB'? If yes, then this is a known issue at the moment.

Posted by HosnyA on 14-May-2018 07:03

Hi Manooj,

Initially we though that this was an issue with the phrase BB as our SOAP calls continued working one we changed the picklist code from BB to BB1. However, we figured that this issue is related to any picklist codes, and dependant picklist codes and values.

For example if you have a state called Victoria and do a SOAP selectQuery call (select id from address where reference='Victoria''s doughnuts'). That fails too.

Like I mentioned, the issue is related picklist and dependant picklist codes and values too.

Has this been fixed in any recent versions or do you have any plans to fix the same?

Thanks,

Hosny

This thread is closed