We have several request forms, which then (as you would expect) initiate workflows to process the data entered on the form. Within those workflows we execute SQL nodes to call DB stored procedures to insert, update, etc. data on our database.
This all works fine except when someone enters a data value on a form that includes an apostrophe, e.g. O'Brien.
We can get the value from the form field into the workflow, but invoking the stored procedure and including the WF job variable that now contains the value O'Brien causes an error, as the workflow engine does a variable substitution to replace the variable with the value ... and the variable is in quotes to pass the value into the stored procedure ... and the quote in the middle of the value then breaks the SQL.
We have been investigating various options, including use of the Oracle q operator, but that is exhibiting inconsistent behaviour depending on the delimiter used. Other thoughts have been to edit single quote to two quotes, either on the form or when initially retrieving from the form, but we are open to all other options/suggestions.
Has anyone else faced this kind of issue? If so what approach did you adopt to get round it?