0

I have a stored procedure which has:

execute immediate 'SELECT COUNT(S_NM) FROM '||lc_s_d_tb_nm||' WHERE S_NM IN('''||in_s_nm||''') AND '||lc_s_t_col||'='''||in_s_type||''' ' into lc_s_count;

The parameter in_s_nm is being sent by another function and one of the value is - Test - Mother's Identifier

Because of ' (apostrophe), my sql is not working.

How can I fix it so it works?

dang
  • 2,342
  • 5
  • 44
  • 91
  • 1
    Possible duplicate of [Escaping single quote in PLSQL](https://stackoverflow.com/questions/6678478/escaping-single-quote-in-plsql) – Ori Marko Jun 04 '19 at 08:45

3 Answers3

2

You should stop right there.. Don't use, I repeat, don't ever use concatenation of values in a dynamic SQL ( except for Table and column names or for educational purposes :-; ). It is vulnerable to SQL Injection and it could become a security threat.

Your query should be rewritten as

EXECUTE IMMEDIATE 'SELECT COUNT(S_NM) FROM '||lc_s_d_tb_nm||' 
   WHERE S_NM = :s_nm
AND '||lc_s_t_col||'= :s_type'  into lc_s_count  USING in_s_nm,in_s_type;

DEMO

Kaushik Nayak
  • 30,772
  • 5
  • 32
  • 45
  • 2
    It could only be that you want to use SQL Injection as tool, where it makes sense to use concatenation. BTW you can also chance `WHERE S_NM IN (:s_nm)` to `WHERE S_NM = :s_nm` - that's what got me confused a little at first, cause i thought you wanted to pass multiple values to the dynamic sql. – Radagast81 Jun 04 '19 at 11:15
  • It's been removed as well. I should familiarize myself more in using the SO mobile app: – Kaushik Nayak Jun 04 '19 at 13:45
1

Just use replace(in_s_nm, '''', '''''') instead of in_s_nm.

Radagast81
  • 2,921
  • 1
  • 7
  • 21
0

In 10g Oracle introduced the Quote Operator as an alternative or rather an extension that eliminates, at least most if not all, of that double quotation problem. For example try:

select q'/this is a single quote ', and this a double '' and even a triple '''/' from dual
union all 
select 'this is a single quote '', and this a double '''' and even a triple ''''''' from dual;

I think you'll find the first much easier to deal with than the second although they produce the same result.

Belayer
  • 13,578
  • 2
  • 11
  • 22