5 Replies Latest reply: Feb 11, 2013 8:50 AM by Frank Kulash RSS

    unquoting a condition

    malky
      Hi there,

      I'm passing some parameters from java code to a query as follows:

      select * from table1
      where col1 = 'yes'
      --want to make this part dynamic
      *$[variable]*

      where the $[variable] = 'and col2 like ''some%'' and col3 = ''good'''

      So this query is giving me an error saying that it cannot construct such query because i guess what it's trying to run is:

      select * from table1
      where col1 = 'yes'
      *'and col2 like ''some%'' and col3 = ''good'''*

      I need to remove the first and last single quote... I tried using double quote for my string and replace all single quotes with empty but the "LIKE" function only works with single quotes (like 'some%')..
      I also tried using:

      select * from table1
      where col1 = 'yes'
      and trim (both '''' from 'col2 like ''some%'' and col3 = ''good''')
      but i get a "ORA-00920: invalid relational operator"...

      so... any suggestions?
        • 1. Dynamic SQL
          Frank Kulash
          Hi,
          malky wrote:
          Hi there,

          I'm passing some parameters from java code to a query as follows:

          select * from table1
          where col1 = 'yes'
          --want to make this part dynamic
          *$[variable]*

          where the $[variable] = 'and col2 like ''some%'' and col3 = ''good'''

          So this query is giving me an error saying that it cannot construct such query because i guess what it's trying to run is:

          select * from table1
          where col1 = 'yes'
          *'and col2 like ''some%'' and col3 = ''good'''*

          I need to remove the first and last single quote...
          Actually, it's a lot more complicated than that. The first character of the string is not single-quote, but the character a in the word "and".
          To do what you want requires Dynamic SQL , where you assemble a string containing a SQL statement, and the use the PL/SQL command EXECUTE IMMEDIATE to run it. Search for "Dynamic SQL". (There are other, perhaps easier, ways to do dynamic SQL in SQL*Plus, but it sounds like they don't apply here since you're using a Java front end.)

          You should realize that this opens you to SQL Injections , that is, a malicious user essentially will have the power to issue any SQL command, such as DROP TABLE.
          • 2. Re: unquoting a condition
            Etbin
            Something to play with (assuming the select returns a single row - if not check [url http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/executeimmediate_statement.htm#LNPLS01317]EXECUTE IMMEDIATE Statement and proceed accordingly)
            declare
            
              the_sql varchar2(4000) := q'{select col4,col5,col6 
                                             from table1
                                            where col1 = 'yes'
                                              and col2 like ':bind1'
                                              and col3 = ':bind2'
                                          }';
            
              l_col2 varchar2(100) = 'some%';
              l_col3 varchar2(100) = 'good';
              l_col4 varchar2(100);
              l_col5 number;
              l_col6 date;
              
            begin
            
            /*  dbms_output.put_line(the_sql); -- just for debugging to check the_sql is wellformed */
            
              execute immediate the_sql 
                           into l_col4,l_col5,l_col6
                          using l_col2,l_col3;
            
              dbms_output.put_line(l_col4 ||','|| to_char(l_col5) ||','|| to_char(l_col6,'yyyymmdd'));
            
            end;
            Regards

            Etbin
            • 3. Re: unquoting a condition
              malky
              Sorry but none of those answers applies to my situation.. Frank, why would you say "The first character of the string is not single-quote, but the character a in the word "and"?

              Maybe I didn't explain myself.. As I said I'm passing the parameter in Java to a pre-fabricated UPDATE statement, I'm not using Java's prepareStatement.. I'm using a service we already have which passes all parameters in single quotes to the sql statement:

              select * from table1 where param1 = $[paramValue1] and param2 = $[paramValue2]

              where the $[]s are replaced with a single quoted value coming from java..

              I want to be able to pass conditions to make my query dynamic.. that's why if i have 'and here my condition' I want to be able to remove the single quotes so that the condition can be taken. So in the case above if i have:

              select * from table1 where param1 = $[paramValue1]
              $[paramValue2]

              where I expect $[paramValue2] will be passed as 'and col2 like ''some%'' and col3 = ''good'''
              therefore I want some sort of quote remover so that when this runs:

              select * from table1 where param1 = $[paramValue1]
              replaceQuotes($[paramValue2])

              The query will look something like this:

              select * from table1 where param1 = 'val1'
              and col2 like ''some%'' and col3 = ''good''

              I hope that was clearer.
              • 4. Re: unquoting a condition
                6363
                malky wrote:
                Sorry but none of those answers applies to my situation..
                Sorry but they do, you just don't understand them.
                where I expect $[paramValue2] will be passed as 'and col2 like ''some%'' and col3 = ''good'''
                That is not a valid parameter value and will require dynamic SQL

                http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/dynamic.htm#LNPLS01101

                And will result in the infamous SQL Injection security vulnerability

                http://en.wikipedia.org/wiki/SQL_injection

                http://thedailywtf.com/Articles/Oklahoma-Leaks-Tens-of-Thousands-of-Social-Security-Numbers,-Other-Sensitive-Data.aspx
                • 5. Re: unquoting a condition
                  Frank Kulash
                  Hi,
                  malky wrote:
                  Sorry but none of those answers applies to my situation.. Frank, why would you say "The first character of the string is not single-quote, but the character a in the word "and"?
                  This shows why:
                  {code}
                  SELECT SUBSTR ( 'and col2 like ''some%'' and col3 = ''good'''
                       , 1
                       , 1
                       )          AS first_char
                  FROM dual;
                  {code}
                  Output:
                  {code}
                  FIRST_CHAR
                  ----------
                  a
                  {code}
                  Maybe I didn't explain myself.. As I said I'm passing the parameter in Java to a pre-fabricated UPDATE statement, I'm not using Java's prepareStatement.. I'm using a service we already have which passes all parameters in single quotes to the sql statement: ...
                  Are you saying that you are already doing some kind of dynamic SQL in Java, and you are having trouble creating the dynamic SQL statement the way you want it? If so, the question concerns Java and/or your service, and so a SQL forum like this is not a good place to ask it.