11 Replies Latest reply: Nov 23, 2012 11:16 AM by axvelazq RSS

    How to replace 2 single quotes to single quote

    axvelazq
      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
          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
            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
              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
                >
                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
                  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
                    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
                      >
                      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
                        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
                          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
                            >
                            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
                              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