Project and Portfolio Management Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

Escaping apostrophe in SQL Strings

SOLVED
Go to solution
Highlighted
davensky
Occasional Contributor

Escaping apostrophe in SQL Strings

Hi

I have a problem with SQL strings with apostrophe. Can anyone help how to escape it in the PL/SQL procedure shown below***?

 

I would call the procedure from Request Rules like this:

 

 SELECT PR_FIRST_TOKEN('[REQ.P.xx]'),PR_FIRST_TOKEN('[REQ.VP.xx]') FROM DUAL

 

Given:  p_input is a String with an apostrophe ex. 'O' BRIAN'

 

***

create or replace
FUNCTION PR_FIRST_TOKEN(p_input IN VARCHAR2)  RETURN VARCHAR2 IS
-- return first token, split by ',' or '#@#'

v_retval VARCHAR2(200);
v_separator_pos NUMBER;

BEGIN
 
  v_separator_pos := INSTR(p_input,',');
  IF v_separator_pos <> 0 THEN
    -- comma found, use substring ending there
    v_retval := SUBSTR(p_input,1,v_separator_pos-1);
  ELSE
    -- no comma found, check for #@#
    v_separator_pos := INSTR(p_input,'#@#');
   IF v_separator_pos <> 0 THEN
     -- #@# found, , use substring ending there
     v_retval := SUBSTR(p_input,1,v_separator_pos-1);
   ELSE
     -- no separator found => use entire string
      v_retval := p_input;
   END IF;
  END IF;

  RETURN v_retval;

END PR_FIRST_TOKEN;

 

 

Thank you so much!

 

9 REPLIES
dirkf
Honored Contributor

Re: Escaping apostrophe in SQL Strings

Hi davensky,

 

a long time ago I had a similar ticket where we needed to try and escape quotes and double-quotes. Might be non-identical, but perhaps it gives you clues where to hitch up.

 

Issue:

In non-English laguanges, the use of the apostrophe is very common in standard written text. Also (at least in Germany), the use of square brackets [ ] is also common in normal text.

Take the following scenario;

Source Text: doo'fus

Command:
ksc_store STORE_RESULT=[REQD.P.SOURCE_TXT]

Result: OK

Command:
ksc_itg_run_sql QUERY_STRING="select '[REQD.P.SOURCE_TXT]' from dual" ENV_NAME="[SOURCE_ENV.ENVIRONMENT_NAME]"
ksc_store RUN_SQL_RESULT=[SQL_OUTPUT]"

Result: Fails.

The query fails as the token is translated by PPM and then passed directly to Oracle without the ability to intercept and substiture the apostrophe character. The query that is run is;

select 'doo'fus' from dual

Given that the source is a free text entry, how can we intercept the apostrophe and 'double it up' to give the following valid query;

select 'doo''fus' from dual

The same is true for workflow step conditions. If an apostrophe is in the field used for condition checking, then an error occurs (I assume the same type of SQL error as the ksc_itg_run_sql);

Invalid command condition. Could not evaluate: 'D'oIt' = 'DoIt' (KNTA-10216)

You can see a RT with the necessary workflow steps and fields here;

http://cyclops.gbr.hp.com:10000/itg/web/knta/crt/RequestDetail.jsp?REQUEST_ID=32751

This has 4 fields of interest;

Source fields:
Source Text - The text that will be copied. Try entering (i) no (ii) single and (iii) double apostrophes to see the changes
Condition - If this field contains the string 'DoIt' (without quotes) then the execution steps will run. Change the contents to have an apostrophe and you will get an error.
Result fields:
From ksc_store
From itg_run-sql

 

Solution at the time:

ksc_escape_char ESC_S=[REQD.P.SOURCE_TXT] ESCAPING_CHAR=' CHARS_TO_ESCAPE='
ksc_itg_run_sql QUERY_STRING="select '[ESC_S]' from dual" ENV_NAME="[SOURCE_ENV.ENVIRONMENT_NAME]"
ksc_store RUN_SQL_RESULT=[SQL_OUTPUT]"

Hope this helps you. Most times, this is not a PPM- but rather an Oracle-limitation.

I guess you'll get the same problems if you run the query via SQLPlus or similar tools.

 

Best regards,

Dirk


 

davensky
Occasional Contributor

Re: Escaping apostrophe in SQL Strings

Hi Dirk,

 

Thank you for the quick response!

 

The procedure looks like this when you pass the two names:

 

 SELECT PR_FIRST_TOKEN('34971#@#35308'),CUS_FIRST_TOKEN('John Doe#@#Randy O'Brien')

 

My question is: How can I add an ESCAPE before the apostrophe in the PL/SQL Procedure PR_FIRST_TOKEN? For example: I  may use  one of the string manipulations functions in PL/SQL. I would say I must check whether there is an apostrophe in the variable p_input...

 

I would be very happy if somebody could complete the code with the given information.

 

Thank you

Best Regards

 

 

 

 

 

 

erik_c
Regular Collector

Re: Escaping apostrophe in SQL Strings

Hi,

 

I don't think you need to escape it in your procedure, but in the calling query because that's where the token's value is injected. Try using (if 10g or later) the q function to change the string identifier to something else, like a ? character:

 

select q'?Here's an O'Brien?' from dual

 

So your function call would be something like

 

SELECT PR_FIRST_TOKEN('34971#@#35308'),CUS_FIRST_TOKEN(q'?John Doe#@#Randy O'Brien?') from dual

davensky
Occasional Contributor

Re: Escaping apostrophe in SQL Strings

Hi Eric,

 

It did not work. Look the error below:

 

 Fehler in SQL in Regel 15 - 'Zugewiesenen Tester Setzen':SELECT PR_FIRST_TOKEN('34971#@#35308'),PR_FIRST_TOKEN(q'?Pxxx Frigerio#@#Cxxxxxo D'Ambrogio?') FROM DUAL (KNTA-10521)
Fehler in SQL in Regel 15 - 'Zugewiesenen Tester Setzen':SELECT PR_FIRST_TOKEN('34971#@#35308'),PR_FIRST_TOKEN(q'?Pxxx Frigerio#@#Cxxxxxo D'Ambrogio?')
FROM DUAL (KNTA-10521)
at com.kintana.core.util.FLSRuleUtils.executeRules(FLSRuleUtils.java:629)
at com.kintana.core.util.FLSRuleUtils.evaluateRules(FLSRuleUtils.java:603)
at com.kintana.core.util.FLSRuleUtils.handleRulesForFLS(FLSRuleUtils.java:296)
at com.kintana.core.util.FLSRuleUtils.handleRulesForFLS(FLSRuleUtils.java:56)
at com.kintana.crt.web.ctrl.RequestUpdateController.update(RequestUpdateController.java:340)
at org.apache.jsp.web.knta.crt.RequestUpdate_jsp._jspService(RequestUpdate_jsp.java:122)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:334)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:314)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:264)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.ajaxanywhere.AAFilter.doFilter(AAFilter.java:46)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at com.mercury.itg.servlet.I18NFilter.doFilter(I18NFilter.java:34)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at com.mercury.itg.servlet.HibernateSessionFilter.doFilter(HibernateSessionFilter.java:77)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.

 

THANK YOU

 

Regards

erik_c
Regular Collector

Re: Escaping apostrophe in SQL Strings

Post your full rule SQL?

 

I tested with a simple string function, a page load rule:

 

select LOWER(q'?Here's an O'Brien?') , UPPER(q'?Here's an O'Brien?') from dual

 

and it works fine. The issue might be your custom function or db version...

davensky
Occasional Contributor

Re: Escaping apostrophe in SQL Strings

Here is the SQL Rule:

 

SELECT CUS_FIRST_TOKEN(p'?[REQ.P.CUS_REALISIERER]?'),CUS_FIRST_TOKEN(q'?[REQ.VP.CUS_REALISIERER]?')
FROM DUAL

erik_c
Regular Collector

Re: Escaping apostrophe in SQL Strings

Have you tested the rule sql in sqlplus to see if it errors there?
davensky
Occasional Contributor

Re: Escaping apostrophe in SQL Strings

Hi Erik

 

I have already tested the rule sql, but it did not work. error message is still there.

 

Regards

 

davensky
Occasional Contributor
Solution

Re: Escaping apostrophe in SQL Strings

Hi all,

I have a good news and a fairly bad news....

The good news is that HP has already found a workaround for the problem using the q operator:
 

select (select CUS_FIRST_TOKEN(q'+[REQD.VP.ORIG_FIELD]+') from dual),(select CUS_FIRST_TOKEN(q'+[REQD.VP.ORIG_FIELD]+') from dual) from dual

 
The fairly bad news, I quote Dirk(HP) "

is that this operator escapes the issue but then also adds the quote to the field value, meaning that if you have

Source-Field value ‘D’Amacourt’, you’ll have

Destination-Field value ‘D’’Amacourt’.

Thus, if you plan or actually already DO use this field as a source for yet another field, then we’re in for trouble.

We’ve also not tested this issue against the possibility of the destination field also being a multi-value or list-field, instead testing against a text-field."
 

I have already tested it and it works:-)
 

Thank you Dirk + all.

Davenksy

//Add this to "OnDomLoad" event