Expand my Community achievements bar.

Dive into Adobe Summit 2024! Explore curated list of AEM sessions & labs, register, connect with experts, ask questions, engage, and share insights. Don't miss the excitement.

Single Quote causing problem in XPATH

Avatar

Level 7
The workflow was working fine until one of the fields had a single quote in the string and caused the update action to stall.



Using the Execute SQL query module I am updating data in an Oracle table. The query looks like this




UPDATE GSI_FEEDBACK_FORM_HEADERS



SET SITE_STREET = '{$ /process_data/xfaform/object/data/xdp/datasets/data/form1/Details/site_street $}',

SITE_STREET2 = '{$ /process_data/xfaform/object/data/xdp/datasets/data/form1/Details/site_street2 $}'




One of the forms had the street value
"Connah's Quay Deeside". I figured out that the single quote is causing the module to read it as the end of the XPATH, hence causing a "SQL command not ended properly" error.



Any suggestions to oversome this issue.



Aditya
6 Replies

Avatar

Level 7
Nope - No luck



Error :




ORA-00904: "1302 n 19th s't": invalid identifier



Aditya

Avatar

Former Community Member
I searched the web and found a forum that discusses how to build a SQl statement with a quote in the query. Most DBs will expect you to escape it with a /. There are other techniques discussed here:



http://it.toolbox.com/wiki/index.php/How_do_I_escape_single_quotes_in_SQL_queries%3F



In either case it looks like you wll have to search you input string for such chars beforehand.

Avatar

Level 9
Hi

Just use a parameterized query, this solves the problem.

Something like:

UPDATE GSI_FEEDBACK_FORM_HEADERS

SET SITE_STREET = ?,

SITE_STREET2 = ?



Then put the xpath expressions into the table below as parameters 1 and 2.

Howard

http://www.avoka.com

Avatar

Level 7
Howard - Thank you so much for the awesome suggestion. It worked perfectly !!



You have saved me a lot of work.



Aditya

Avatar

Level 9

No problem

It also:

  • Makes your SQL look much neater.
  • You can type in "test" values for the parameters, so that you can test your SQL statement and see the results.

Howard

http://www.avoka.com