This discussion is archived
11 Replies Latest reply: Nov 23, 2012 9:16 AM by axvelazq RSS

How to replace 2 single quotes to single quote

axvelazq Newbie
Currently Being Moderated
Hi guys,

I have an script.sql that receives as a parameter an string.
example:

@C:/myscript.sql "o'connor"
user_account_value varchar2(120) := '&1';

EXECUTE IMMEDIATE "Select * from Table where column = :1 "  USING user_account_value 
I am not sure how to deal with string that contains single quotes.

If the parameter were passed as : "o''connor" this will work
If the parameter is pass as: "o'connor" this will not work.

so my question is what options do I have to deal with dynamic queries and single quotes.

I tried replacing replace(myParameter,'''',''''''); but not working well.
  • 1. Re: How to replace 2 single quotes to single quote
    rp0428 Guru
    Currently Being Moderated
    Welcome to the forum!

    Whenever you post provide your 4 digit Oracle version.
    >
    I am not sure how to deal with string that contains single quotes.
    >
    You did not post enough code for anyone to test and you also did not post any error message or exception that you are getting.

    Your code has at least two major problems that have NOTHING to do with the value of the variable. First you can't just do a SELECT in PL/SQL. You have to give Oracle some place to put the results. That means a SELECT INTO is required.

    Second a string literal has to be enclosed in single quotes and your query does not do that.
    EXECUTE IMMEDIATE "Select * from Table where column = ':1' "  USING user_account_value 
  • 2. Re: How to replace 2 single quotes to single quote
    GauravBhide Newbie
    Currently Being Moderated
    Hi,

    Try this.
    SQL> with test_table as
      2  (
      3    select 100 as id , 'o''connor' as name  from dual
      4    union all
      5    select 101 as id , 'abc' as name from dual
      6  )select * from test_table where name = q'[&1]';
    Enter value for 1: o'connor
    old   6: )select * from test_table where name = q'[&1]'
    new   6: )select * from test_table where name = q'[o'connor]'
    
            ID NAME
    ---------- --------
           100 o'connor
    
    SQL>
    Replace '&1' by q'[&1]'.

    Inside q'[  ]' , single quote will be treated as a part of data.
    SQL> select q'[a'b'c'd'e']' as test_quotes from dual;
    
    TEST_QUOTE
    ----------
    a'b'c'd'e'
  • 3. Re: How to replace 2 single quotes to single quote
    jeneesh Guru
    Currently Being Moderated
    rp0428 wrote:
    Second a string literal has to be enclosed in single quotes and your query does not do that.
    EXECUTE IMMEDIATE "Select * from Table where column = ':1' "  USING user_account_value 
    Looks incorrect..

    No need to use single quotes when the variable is binded abd passd using USING clause.

    But the OP is using double quotes for execute immediate, which is anyhow wrong..
  • 4. Re: How to replace 2 single quotes to single quote
    rp0428 Guru
    Currently Being Moderated
    >
    No need to use single quotes when the variable is binded abd passd using USING clause.
    >
    String literals have to be enclosed in single quotes. This syntax is invalid if 'column' is a VARCHAR2
    Select * from Table where column = abc; 
    You need to construct this query where 'abc' is the result of replacing the bind variable.
    Select * from Table where column = 'abc'; 
  • 5. Re: How to replace 2 single quotes to single quote
    axvelazq Newbie
    Currently Being Moderated
    Let me expand the explanation of my problem. I am using Oracle 11g

    I have an script that receives an string as a parameter, for example:
    @C:/myScript.sql "AXEL";
    @C:/myScript.sql "AXEL DAVID";
    @C:/myScript.sql "o'neal";

    my scripts basically constructs a Parameterized query an execute it. That's all. It works most of the time, except when the parameter contains apostrophes
    DEFINE myparameter = &&1
    declare results varchar2 (100);
    ...
    myquery := 'Select columnX from myTable where x = :p1';
    
    EXECUTE IMMEDIATE myquery into results USING myparameter;
    ...
    When trying the following;
    @C:/myScript.sql "o'neal";
    I get the following error:
    Bind Variable "p1" is NOT DECLARED
    anonymous block completed
    I also tried to as follow;
     myquery := 'Select columnX  from myTable where x = ':p1'''';
    but I get the following error:
    Error report:
    ORA-06550: line 5, column 40:
    PLS-00103: Encountered the symbol "CONOR" when expecting one of the following:
    
       * & = - + ; < / > at in is mod remainder not rem
       <an exponent (**)> <> or != or ~= >= <= <> and or like like2
       like4 likec between || multiset member submultiset
    06550. 00000 -  "line %s, column %s:\n%s"
    *Cause:    Usually a PL/SQL compilation error.
    *Action:
    Of course if I change the parameter as "o''neal" It will work, but I will end up with other issues later on, so I would like to know how can I deal with apostrophe on dynamic queries.

    I also tried to use the following:
    ...
       myquery := "Select ....  where x = " || Q'#:p1#';
    ...
    not working:
    Bind Variable "p1" is NOT DECLARED
    anonymous block completed
    any hint will be appreciated =)

    Edited by: user13679988 on Nov 22, 2012 3:54 PM

    Edited by: user13679988 on Nov 22, 2012 6:11 PM
  • 6. Re: How to replace 2 single quotes to single quote
    orafad Oracle ACE
    Currently Being Moderated
    user13679988 wrote:
    myquery := "Select columnX from myTable where x = :p1";

    EXECUTE IMMEDIATE myquery into results USING myparameter;
    Try surrounding the select with single quotes instead of double quotes, in the myquery assignment.
  • 7. Re: How to replace 2 single quotes to single quote
    rp0428 Guru
    Currently Being Moderated
    >
    any hint will be appreciated
    >
    This works for me
    declare
    v_dname varchar2(100) := '&&1';
    v_deptno number;
    myquery varchar2(4000);
    begin
    myquery := 'Select deptno from dept where dname = :1';
    EXECUTE IMMEDIATE myquery into v_deptno USING v_dname;
    dbms_output.put_line(v_deptno);
    end;
    /
    
    SQL> @E:\document1.txt "ACCOUNTING"
    old   2: v_dname varchar2(100) := '&&1';
    new   2: v_dname varchar2(100) := 'ACCOUNTING';
    10
  • 8. Re: How to replace 2 single quotes to single quote
    axvelazq Newbie
    Currently Being Moderated
    the problem comes when the parameter contains apostrophes
    For example,

    try

    @myscript.sql "o'connor"


    Of course if there is no apostrophes, this will always work, this is about How to escape apostrophe in Oracle PL/SQL dynamic queries
  • 9. Re: How to replace 2 single quotes to single quote
    sb92075 Guru
    Currently Being Moderated
    user13679988 wrote:
    the problem comes when the parameter contains apostrophes
    For example,

    try

    @myscript.sql "o'connor"


    Of course if there is no apostrophes, this will always work, this is about How to escape apostrophe in Oracle PL/SQL dynamic queries
    You should be concerned about SQL injection in addition to accepting apostrophes

    above is only one of many reasons why Dynamic SQL should be avoided;
    since it scales as well as my pet goat can fly.
  • 10. Re: How to replace 2 single quotes to single quote
    rp0428 Guru
    Currently Being Moderated
    >
    Of course if there is no apostrophes, this will always work, this is about How to escape apostrophe in Oracle PL/SQL dynamic queries
    >
    Just use alternative quoting (Q) syntax. This works for me.
    INSERT INTO DEPT VALUES (11, 'ACC''OUNTING', 'NEW YORK')
    
    declare
    v_dname varchar2(100) := q'[&&1]';
    v_deptno number;
    myquery varchar2(4000);
    begin
    myquery := 'Select deptno from dept where dname = :1';
    EXECUTE IMMEDIATE myquery into v_deptno USING v_dname;
    dbms_output.put_line(v_deptno);
    end;
    /
    
    SQL> set serveroutput on
    SQL> @e:\document2.txt "ACC'OUNTING"
    old   2: v_dname varchar2(100) := q'[&&1]';
    new   2: v_dname varchar2(100) := q'[ACC'OUNTING]';
    11
    
    PL/SQL procedure successfully completed.
    
    SQL>
  • 11. Re: How to replace 2 single quotes to single quote
    axvelazq Newbie
    Currently Being Moderated
    Q['&&1'] was the key!

    Thank you so much!



    I understand that SQL Injection could take place sometimes when working with dynamic queries, but in my case I really needed to automate some scripts.


    thanks in deed


    Axel

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points