Single Quote causing problem in XPATH

[Aditya_S_]

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

[Community user]

Try using double quotes in your expression.

[Aditya_S_]

Nope - No luck



Error :




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



Aditya

[Community user]

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.

[HowardTreisman]

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

[Aditya_S_]

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



You have saved me a lot of work.



Aditya

[HowardTreisman]

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