Question

How to escape single quote (') in SQLExecutor

  • 25 June 2020
  • 2 replies
  • 60 views
nmartin
Rank

Is there an escape character for the SQL text box in the SQLExecutor?

If I try the following SQL against and Oracle database - select OFFICER from officer where lower(email) = '@Value(userName)'

where userName value is in the format of it will fail because FME turns @Value(userName) into a string (evidenced by highlights in this picture).

If I format my userName field to 'myemail@address.com' and remove the single quotes in the query as per this picture, the query will succeed

 

Obviously I can run this through extra transformers to format my values beforehand but this is adding unnecessary processing and workbench bulk.

Here https://knowledge.safe.com/questions/36933/how-to-prevent-single-quotes-turning-my-parameter.html @daleatsafe has stated that FME doesn't touch single quotes, but it appears from my first picture that it does? Or I have misunderstood something in that post.

Also select OFFICER from officer where lower(email) = '''@Value(userName)' runs without causing a rejected message, but it does not output anything.

Every other combination of quotes I've tried other than those shown above results in a rejected message (e.g. wrapping the value in "'" or ''' etc).

 

2 replies

david_r
Rank
Userlevel 4

Since the SQL entered into the SQLExecutor is simply forwarded to the underlying database, it depends on the SQL dialect used in the database.

For Oracle you can use the alternative quote operator, e.g.

select OFFICER from officer where lower(email) = q'#@Value(userName)#'

Change the #-character to something that isn't present in any of the usernames.

More info here: https://livesql.oracle.com/apex/livesql/file/content_CIREYU9EA54EOKQ7LAMZKRF6P.html

nmartin
Rank

Since the SQL entered into the SQLExecutor is simply forwarded to the underlying database, it depends on the SQL dialect used in the database.

For Oracle you can use the alternative quote operator, e.g.

select OFFICER from officer where lower(email) = q'#@Value(userName)#'

Change the #-character to something that isn't present in any of the usernames.

More info here: https://livesql.oracle.com/apex/livesql/file/content_CIREYU9EA54EOKQ7LAMZKRF6P.html

I'm not sure what I was doing wrong initially because in the end I went and put a space after the last ' and it started working, then I went back and copied pasted my "select OFFICER from officer where lower(email) = '@Value(userName)'" from the original post and it continued working.

Thank you for the response, good to know that it does just forward the SQL as is and about the alternative operator. Perhaps this will be of some help to another who so they won't spend an hour in frustration due to their own stupidity. :D

Reply


Community Stats

28,731
Posts
109,530
Replies
36,565
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.