I have a pretty complex stored procedure that handles a variety of
queries against a table, and the WHERE criteria of the stored procedure
is passed into it as a varchar argument.

I have variables set for limiting the number of rows returned for
pagination, along with the WHERE and ORDER BY parts.

I use the FOR EXECUTE STATEMENT command to construct the query like so:

for execute statement
'SELECT ' || V_LIMIT_PART || ' DISTINCT
CONTACT.C_ID,
CONTACT.FULL_NAME,
CONTACT.COLLEGE,
C1.COUNTRY_NAME,
CONTACT.PHONE1,
CONTACT.EMAIL_ADDRESS,
STATUS_TYPE.ST_DESC,
CONTACT.FK_STATUS_ID
FROM CONTACT
LEFT OUTER JOIN STATUS_TYPE ON (STATUS_TYPE.ST_ID =
CONTACT.FK_STATUS_ID)
LEFT OUTER JOIN COUNTRY C1 ON (COUNTRY.COUNTRY_ID =
CONTACT.FK_COUNTRY_ID)
' ||
V_WHERE_PART || '
' || V_ORDER_PART
into
:R_ID,
:R_FULLNAME,
:R_COLLEGE,
:R_COUNTRY,
:R_PHONE1,
:R_EMAIL,
:R_STATUS,
:R_STATUS_ID
do
begin
suspend;
end

Its working perfectly in 99% of all cases. However the problem with it
is when the user wishes to query on a name containing a quotation (ie.
O'Reilly). In those cases, I cannot find a simple way to construct the
incoming query string to include the quotation.

For example, a typical query value passed in, that becomes the
V_WHERE_PART variable would look like this:

FK_STATUS_ID IS NOT NULL AND (SRCH_LAST_NAME = ''OREILLY'' )

Note that I pass in double quotations around the value. This is handled
fine when concatenated with the EXECUTE STATEMENT string. However when
I need to include the single quotation mark in the name O'Reilly, then
it fails. I've tried using double quotations but all that does is
prematurely end the string.

How can I do this with any value that contains a quotation in the
content? I've tried replacing it with some unusual string, and doing a
string replace on the incoming value before passing it to the EXECUTE
STATEMENT for concatenation, but this doesn't solve anything since the
problem is the use of quotations in the resulting string that is executed.

If anyone has an idea for dealing with this, I'm all ears. Thanks in
advance for anyone's time looking at this.

Myles

--
=======================
Myles Wakeham
Director of Engineering
Tech Solutions USA, Inc.
Scottsdale, Arizona USA
http://www.techsolusa.com
Phone +1-480-451-7440