Thanks for the question, Brian.

Asked: August 31, 2009 - 5:42 pm UTC

Last updated: July 05, 2012 - 9:09 am UTC

Version: 10g

Viewed 10K+ times! This question is

You Asked

After running a proc I wrote I get this error:
ORA-01756: quoted string not properly terminated

I am writing SQL as a string so that I can embed variables into the the string,
like: v_sql := 'insert into... select... etc';

I then execute this with

execute immediate v_sql;

I'm having trouble setting a field to a string in the where clause:

' and z.keyword_campaign = ''' || v_sc || ''

Where v_sc is a variable. The end result should be <...and z.keyword_campaign = 'xxx_yyy' and ...>

If I use 3 single quotes the proc won't compile. If I use 2 or 4, it throws the above error.

I haven't found much covering this on my google searches. So here I am -

Thanks !
Brian

and Tom said...

glad it failed.

You do NOT want to hard code it, you WANT to use bind variables

' and z.keyword_campaign = :v_sc';

and then execute immediate v_sql using v_sc;

do that for all of your inputs, no muss, no fuss, no security (sql injection) issues, no hard parsing problems.

I could answer your question, but I choose not to - I need you to use bind variables here.

Rating

(20 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

followup on "embedding a variable in a string"

Brian Farrell, September 01, 2009 - 4:27 pm UTC

Thank you. I'm going to followup on your advice. But I need to look into using bind variables within a proc.

Perhaps you could have briefly explained why bind variables are better... or reference a link that explains it?

Here is the correct code for what I'm trying to do

' and z.keyword_campaign = || '''' || v_sc || ''''

:-)

September 01, 2009 - 6:06 pm UTC

... But I need to look into using
bind variables within a proc.
...

I just demonstrated that? It is as *simple* as demonstrated. it will make your life SO MUCH EASIER. It is harder to code without bind variables.

that is the wrong code still - it doesn't use bind variables.

and it is wrong:

' and z.keyword_campaign = || '''' || v_sc || ''''

would be

' and z.keyword_campaign = ''' || v_sc || '''';

Not using binds has two serious drawbacks

Oleksandr Alesinskyy, September 02, 2009 - 4:51 am UTC

Not using binds has two serious drawbacks

a) sql injection - google it if you don't know it

b) hard parsing - all of the sql will be unique, you will KILL the server, literally KILL the server.

The situation with bind variables is somewhat more involved.

a) As for sql injection - if you substitute only values and not the parts of sql statement, then sql injection may be very easily prevented, just quote string properly (duplicate each quote inside the string and then surround it with quotes, write such function once and call whenever you need this functionality - I really wonder why Oracle does not supply such function once and for all).

b) As for performance KILLING - it depends. For the classical OLTP system binds are the MUST. For warehousing their absence is, in many if not the most cases, preferable. But now there are many system lying somewhere in between OLTP and DWH worlds. For such systems decision should be made on case-by-case basis. The problem is that for such a system query results may very seriously differ in cardinality depending on the values of binds and Oracle (at least prior to 11g) cannot supply different plans for different bind variable values. It might kill system even faster then excessive hard parsing. And original question does not mention neither Oracle version, nor an application purpose.

September 02, 2009 - 10:53 am UTC

a) As for sql injection - if you substitute only values and not the parts of sql statement, then sql injection may be very easily prevented, just quote string properly (duplicate each quote inside the string and then surround it with quotes, write such function once and call whenever you need this functionality - I really wonder why Oracle does not supply such function once and for all).

bzzzt, wrong. false, incorrect, misleading - do not every write that again. It leads the neophyte (and experienced) developer into a false sense of security.

and for the record, there is such a function

replace( str, '''', '''''' )

but, it won't cut it. If only it were that simple. sql injection is insidious, it can slip in in the simpliest of cases.

create or replace procedure inj( p_date in date )
as
    l_rec   all_users%rowtype;
    c       sys_refcursor;
    l_query long;
begin
    l_query := '
    select *
      from all_users
     where created = ''' ||p_date ||'''';

    dbms_output.put_line( l_query );
    open c for l_query;

    for i in 1 .. 5
    loop
        fetch c into l_rec;
        exit when c%notfound;
        dbms_output.put_line( l_rec.username || '.....' );
    end loop;
    close c;
end;
/

that routine is definitely subject to sql injection - yet there isn't a string in sight?

this statement however is true:

if you use bind variables, you cannot be subject to sql injection, bind variables make sql inject impossible. If you never concatenate user supplied inputs (parameters to you are all user supplied inputs) and you use bind variables to pass all values to the sql statement it is not possible to be subject to sql injection. You have no worries about it, you do not need to have your code reviewed by at least five people that do not like to you (so they read it critically) to make sure you haven't made a mistake. And even after they do - since sql injection is so insidious, so tricky (see above) you might still not sleep well at night if you use concatenation as you are just waiting for the next exploit to come along

b) I told him to read me, he has a lot to learn. See this list for example:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1922231800346590548

but for a newbie - the rule is "USE BINDS" - learn to use binds first, then come back and we'll learn when you might not want to use binds - but binds should be the knee jerk, first reaction to all sql - especially for someone that writes "Perhaps you could have briefly explained why bind variables are better... or reference a link that
explains it?" - lots to read by that person first.

Oleksandr Alesinskyy, September 02, 2009 - 5:42 pm UTC

and for the record, there is such a function

replace( str, '''', '''''' )

It is not "such a function" - it does only part of the job, namely, duplicate quotes: it does not add surrounding quotes. I agree that it is a "lion share" of the job - but not the whole job.

that routine is definitely subject to sql injection - yet there isn't a string in sight?

There is "a string in sight" - a string to which p_date is implicitly converted. Make this conversion explicit and orderly quote result string - where would be sql injection after this? Anything that is concatenated is a string.

September 03, 2009 - 7:31 am UTC

and you cannot figure out how to add the quotes? anyway.

right - as I said "there is no string in sight", I believe if you asked around Oleksandr - you would find that 99.999999 of the people out there would say "that isn't a string, it is a date"

sql injection is INSIDIOUS - it is beyond the realm of thought to most people that the above code is subject to sql injection. They would not believe it until they see it.

And how many other edge cases are there? Lots - it is not worth the work - JUST USE BINDS and guess what - you cannot be, YOU CANNOT BE, sql injected.

That simple. If you use concatenation of any form - you must subject your code to review to at least five people that do not like you and are much smarter than you to verify "you did it right". it is complex.

Bind variables

Oleksandr Alesinskyy, September 02, 2009 - 5:53 pm UTC

b) I told him to read me, he has a lot to learn. See this list for example:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1922231800346590548

Yes, extremely good list, nobody should be allowed to program anything (save training exercises) related to Oracle DB til he would at least skim over those books.

but for a newbie - the rule is "USE BINDS"
The problem is that he may be working on some project where binds are not 100% appropriate (I have an impression that a good deal of modern projects fall to this class - it was not so say 10 years ago) - what newbie (and many if not most of the not so newbies) should be taught is to determine needs first (or at least give enough information to somebody able to determine them) and only then start to search for a solution.

September 03, 2009 - 7:35 am UTC

... (I have an impression that a good deal of modern projects fall to this class ...

I vehemently disagree with you - strongly, urgently and passionately. MOST 'projects' do NOT fall into that class - MOST.

... what newbie (and many if not most of the not so newbies) should be taught is to determine needs first (or at least give enough information to somebody able to determine them) and only then start to search for a solution. ...

hah, I've started giving up on that - everyone wants to know simply "what is the best way".

If there was a single best way, we wouldn't need software developers would we.

this is why I say

o I wish we didn't have triggers at all
o I wish when others was removed from the language
o I wish autonomous transactions would become deprecated and unsupported

because, much like bind variables, they are used (or not) in entirely INAPPROPRIATE CIRCUMSTANCES.

And regards binds - it is much easier to go back and make a bind a literal than vice versa. Because making the bind a literal is considered "tuning the applications sql" whereas making a literal a bind (for performance reasons, as the hard parse is killing the system) is considered a waste of time (by development staffs)

followup to sql injection vs binds

Bryan, September 03, 2009 - 9:45 am UTC

It's a DWH and there is no OLTP invovled. Oracle 11g.

Would you like to define SQL Injection?

September 04, 2009 - 1:55 pm UTC

If you were the same Brian - we could answer.... But I doubt you spelled your name wrong.

Anyway - if and only if keyword_campaign is a skewed column (eg: we cannot get a good feel for the estimated cardinality) - then you may feel free to use a literal with concatenation

and then submit your code to review to at least five people that do not like you.

You cannot trust the people that call your code to be "friendly", anytime you accept inputs from the outside world - be it another developer you work with or an end user - you must be aware of and protect against sql injection vulnerabilities.

And you need to do this each time you do not bind - and therefore, not binding is much harder than binding - binding is the right approach unless and until you have a good reason NOT to bind.

?

Sokrates, September 03, 2009 - 10:01 am UTC

procedure inj( p_date in date )
...
is definitely subject to sql injection

are you joking ?
I am too blind to see it.
How is inj subject to sql injection ?
Please show us.

It would prove how insiduous sql injection is.

September 04, 2009 - 1:57 pm UTC

alter session set nls_date_format = 'dd-mon-yyyy"'' or ''a'' = ''a"';

then run it.

Not joking, sql injection is insidious - it is very hard to see most of the times. You have to think of every possible outside influence.

Example...

Centinul, September 03, 2009 - 1:05 pm UTC

Sokrates --

Simple example:

SQL > create or replace procedure inj( p_date in date )
  2  as
  3      l_rec   all_users%rowtype;
  4      c       sys_refcursor;
  5      l_query long;
  6  begin
  7      l_query := '
  8      select *
  9        from all_users
 10       where created = ''' ||p_date ||'''';
 11
 12      dbms_output.put_line( l_query );
 13      open c for l_query;
 14
 15      for i in 1 .. 5
 16      loop
 17          fetch c into l_rec;
 18          exit when c%notfound;
 19          dbms_output.put_line( l_rec.username || '.....' );
 20      end loop;
 21      close c;
 22  end;
 23  /

Procedure created.

SQL > CREATE OR REPLACE FUNCTION HACKER_FUNCTION
  2  RETURN DATE
  3  AS
  4          PRAGMA AUTONOMOUS_TRANSACTION;
  5  BEGIN
  6          DBMS_OUTPUT.PUT_LINE('I just really messed up your database by being able to execute an anonymous transaction!!!');
  7          --SOME REALLY BAD DML/DDL!
  8
  9          COMMIT;
 10          RETURN SYSDATE;
 11  END;
 12  /

Function created.

SQL > exec inj(hacker_function);
I just really messed up your database by being able to execute an anonymous transaction!!!

    select *
      from all_users
     where created = '09/03/2009 12:31:09'

PL/SQL procedure successfully completed.

September 04, 2009 - 3:34 pm UTC

yup... another way.

or pass in

decode( any_function, null, sysdate, sysdate );

Grammatical error in my post above

Centinul, September 03, 2009 - 1:45 pm UTC

I just noticed a grammatical error above. Please replace anonymous with autonomous when reading it.

Thanks!

David Litchfield White Paper

Centinul, September 03, 2009 - 2:35 pm UTC

Admittedly, my example of SQL injection wasn't very good.

However, David Litchfield has a white paper on a topic called "Lateral SQL Injection", in which he shows an example procedure almost exactly like Tom's. Here is the link:

http://www.databasesecurity.com/dbsec/lateral-sql-injection.pdf

DWH vs OLTP; bind vs literal

brian, September 03, 2009 - 2:44 pm UTC

The presenting question was for DWH, on Oracle 11g. Here is a quote that fills in more detail on
the DWH vs OLTP question above.

"Lastly, it's worth bearing in mind that there are some instances where bind variables are probably not appropriate, usually where instead of your query being executed many times a second (as with OLTP systems) your query in fact actually takes several seconds, or minutes, or hours to execute - a situation you get in decision support and data warehousing. In this instance, the time taken to hard parse your query is only a small proportion of the total query execution time, and the benefit
of avoiding a hard parse is probably outweighed by the reduction in important information you're making available to the query optimizer - by substituting the actual predicate with a bind variable, you're removing the ability for the optimiser to compare your value with the data distribution in the column, which might make it opt for a full table scan or an index when this isn't appropriate. Oracle 9i helps deal with this using a feature known as bind variable peeking, which allows Oracle to look at the value behind a bind variable to help choose the best execution
plan."

http://www.akadia.com/services/ora_bind_variables.html

September 04, 2009 - 3:45 pm UTC

arg, I hate that site, it is the site of plagiarism gone mad. I've sent emails to the guy, lots of stuff.

do you know who wrote that originally?

Hint: it was no one at akadia

Mark Rittman and I wrote that
http://www.rittmanmead.com/2004/03/24/bind-variables-explained/

Those are our words - verbatim

Adaptive Cursor Sharing, 11g

Centinul, September 03, 2009 - 3:04 pm UTC

Brian --

I can see that article being valid for pre-11g versions of Oracle. However, in 11g there is a neat feature called "Adaptive Cursor Sharing."

A Couple of links:

http://optimizermagic.blogspot.com/2007/12/why-are-there-more-cursors-in-11g-for.html
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:599762300346943686

This feature may (if not now eventually) allow us to "have our cake and eat it too."

September 04, 2009 - 3:48 pm UTC

not in a warehouse/dss system - there the query is typically run once.

In a warehouse, there is a good argument for not binding - but that code - it has to be SAFE

INSIDIOUS !!

Sokrates, September 04, 2009 - 2:44 am UTC

Centinul , thank you very much for pointing me to
http://www.databasesecurity.com/dbsec/lateral-sql-injection.pdf
ALTER SESSION SET NLS_DATE_FORMAT = '"'' and scott.getdba()=1--"';

most useful !!

avoiding sql injection

A reader, September 23, 2009 - 7:45 am UTC

Tom,

The sql injection example posted by centinul would work regardless of whether the inj proc uses bind variables wouldn't it? (aside from the issues of suitable privileges)

Steve

September 28, 2009 - 2:21 pm UTC

only if the PROCEDURE CALL is the point of the sql injection.

I was worried about the sql in the procedure

centinul was worried about the THING that called the procedure. If that thing bound "hacker_function" - the call would have failed (hacker function would not be convertable into a date as a string bind variable).

if the invokers of the function concatenated the hacker_function string it, it would work - centinul was lookup "up a level" from where I was looking.

DBMS_ASSERT

Duke Ganote, September 28, 2009 - 8:12 pm UTC

dbms_assert still advised?

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:677074900346092274

October 02, 2009 - 8:07 am UTC

not sure what you mean?

To :BV, or not to :BV?

Andy, September 30, 2009 - 6:29 am UTC

I have a table with >500K rows. It has a few functional based indexes, using the UPPER() function on a column.

When I query the table, using the following criteria:

WHERE (UPPER(Col1) LIKE :bv5 OR UPPER(Col2) LIKE :bv5)

I get an FTS:

-------------------------------------------------------------------------------------
| Id  | Operation               | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |           |     1 |   187 |  2840   (3)| 00:00:35 |
|*  1 |  COUNT STOPKEY          |           |       |       |            |          |
|   2 |   VIEW                  |           |     1 |   187 |  2840   (3)| 00:00:35 |
|*  3 |    SORT GROUP BY STOPKEY|           |     1 |   187 |  2840   (3)| 00:00:35 |
|*  4 |     TABLE ACCESS FULL   | Tab1      | 57118 |    10M|  2834   (3)| 00:00:35 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=100)
   3 - filter(ROWNUM<=100)
   4 - filter(UPPER("Col1") LIKE :BV5 OR UPPER("Col2") LIKE
              :BV5)

Fair enough - CBO doesn't know how to evaluate the BV in order to use the index.

Hard coding the values instead of using the BV (as per other posts regarding bv's for transactions, direct values
for searches):

WHERE (UPPER(Col1) LIKE 'search value%' OR UPPER(Col2) LIKE 'search value%')

I get better response, presumbaly because the indexes are utilised:

-------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |           |     1 |   187 |    21  (15)| 00:00:01 |
|*  1 |  COUNT STOPKEY                      |           |       |       |            |          |
|   2 |   VIEW                              |           |     1 |   187 |    21  (15)| 00:00:01 |
|*  3 |    SORT GROUP BY STOPKEY            |           |     1 |   187 |    21  (15)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID     | Tab1      |   373 | 69751 |    20  (10)| 00:00:01 |
|   5 |      BITMAP CONVERSION TO ROWIDS    |           |       |       |            |          |
|   6 |       BITMAP OR                     |           |       |       |            |          |
|   7 |        BITMAP CONVERSION FROM ROWIDS|           |       |       |            |          |
|   8 |         SORT ORDER BY               |           |       |       |            |          |
|*  9 |          INDEX RANGE SCAN           | S2_FNC    |       |       |     2   (0)| 00:00:01 |
|  10 |        BITMAP CONVERSION FROM ROWIDS|           |       |       |            |          |
|  11 |         SORT ORDER BY               |           |       |       |            |          |
|* 12 |          INDEX RANGE SCAN           | S1_FNC    |       |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=100)
   3 - filter(ROWNUM<=100)
   9 - access(UPPER("Col2") LIKE 'search value%')
       filter(UPPER("Col2") LIKE 'search value%' AND UPPER("Col2")
              LIKE 'search value%')
  12 - access(UPPER("Col1") LIKE 'search value%')
       filter(UPPER("Col1") LIKE 'search value%' AND UPPER("Col1")
              LIKE 'search value%')

With that in mind, with both BV's and using the indexes, but using the INDEX_COMBINE hint:

SELECT /*+ INDEX_COMBINE(g) */
...
WHERE (UPPER(Col1) LIKE :bv5 OR UPPER(Col2) LIKE :bv5)

I found I can achieve the same plan:

----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |           |     1 |   187 |       |  5560   (1)| 00:01:07 |
|*  1 |  COUNT STOPKEY                      |           |       |       |       |            |          |
|   2 |   VIEW                              |           |     1 |   187 |       |  5560   (1)| 00:01:07 |
|*  3 |    SORT GROUP BY STOPKEY            |           |     1 |   187 |       |  5560   (1)| 00:01:07 |
|   4 |     TABLE ACCESS BY INDEX ROWID     | Tab1      | 57118 |    10M|       |  5554   (1)| 00:01:07 |
|   5 |      BITMAP CONVERSION TO ROWIDS    |           |       |       |       |            |          |
|   6 |       BITMAP OR                     |           |       |       |       |            |          |
|   7 |        BITMAP CONVERSION FROM ROWIDS|           |       |       |       |            |          |
|   8 |         SORT ORDER BY               |           |       |       |   936K|            |          |
|*  9 |          INDEX RANGE SCAN           | S1_FNC    |       |       |       |    21   (0)| 00:00:01 |
|  10 |        BITMAP CONVERSION FROM ROWIDS|           |       |       |       |            |          |
|  11 |         SORT ORDER BY               |           |       |       |   936K|            |          |
|* 12 |          INDEX RANGE SCAN           | S2_FNC    |       |       |       |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=100)
   3 - filter(ROWNUM<=100)
   9 - access(UPPER("Col1") LIKE :BV5)
       filter(UPPER("Col1") LIKE :BV5 AND UPPER("Col1") LIKE :BV5)
  12 - access(UPPER("Col2") LIKE :BV5)
       filter(UPPER("Col2") LIKE :BV5 AND UPPER("Col2") LIKE :BV5)

However, here the Cost is up and the time taken is greater!

Also, note that the stats are exactly the same across all 3 queries:

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        143  consistent gets
          0  physical reads
          0  redo size
       1185  bytes sent via SQL*Net to client
        338  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
          6  rows processed

Why am I not able to achieve the same performance with the bind variables + hint as with the hard coded values?

October 07, 2009 - 8:20 am UTC

you are not showing us everything here

... COUNT STOPKEY ....

you have obviously wrapped your query in a select count(*) with a rownum filter.

Use first_rows(N) as your hint and use bind variables. Set N to what you would be using with your rownum filter.

but, in a generic search like that, I'd probably be looking at union or union all - something like

ops$tkyte%ORA11GR2> explain plan for
  2  select /*+ first_rows(100) */ *
  3    from t
  4   where (upper(col1) like :x)
  5   union all
  6  select /*+ first_rows(100) */ *
  7    from t
  8   where upper(col2) like :x
  9     and rownum <= 100
 10  /

Explained.

ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 96193762

----------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |   200 | 10800 |    42  (50)| 00:00:01 |
|   1 |  UNION-ALL                    |        |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID | T      |   100 |  5400 |    21   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN           | T_IDX1 |       |       |     3   (0)| 00:00:01 |
|*  4 |   COUNT STOPKEY               |        |       |       |            |          |
|   5 |    TABLE ACCESS BY INDEX ROWID| T      |   100 |  5400 |    21   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN          | T_IDX2 |       |       |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access(UPPER("COL1") LIKE :X)
       filter(UPPER("COL1") LIKE :X)
   4 - filter(ROWNUM<=100)
   6 - access(UPPER("COL2") LIKE :X)
       filter(UPPER("COL2") LIKE :X)

22 rows selected.

A reader, October 08, 2009 - 10:48 am UTC

Hello Tom,

I remember you said but unable to find that "OR" is not always equal to "UNION ALL"

as below

with tmp(x,y) as
(
select 1 , 1 from dual
)
select * from tmp where x=1 OR y=1

output
======

X Y
-- --
1 1

where as

with tmp(x,y) as
(
select 1 , 1 from dual
)
select * from tmp where x =1
union all
select * from tmp where y = 1

output
======
X Y
-- --
1 1
1 1

<Qoute from above>

ops$tkyte%ORA11GR2> explain plan for
2 select /*+ first_rows(100) */ *
3 from t
4 where (upper(col1) like :x)
5 union all
6 select /*+ first_rows(100) */ *
7 from t
8 where upper(col2) like :x
9 and rownum <= 100
10 /

<Qoute from above>

(1) Is it not that your suggestion as above differ in the output since "OR" output NOT ALWAYS SAME as "UNION ALL"?
Please correct me if i am wrong?

(2) When converting OR into UNION ALL which factor to consider, could you please explain ?

Many Thanks

Warm Regard's

To :BV or not to :BV

Andy, October 09, 2009 - 11:13 am UTC

Thanks Tom.

I didn't include the ROWNUM <= 100, as I thought the inner query would be performed first before the ROWNUM is used to cut off the records returned. Clearly, the combination of both the inner and outer queries has an impact - ROWNUM doesn't just limit the result set.

I believe the UNION, not UNION ALL would be the choice for unique records.

I think what makes this difficult for me is that there are a number of variations of the columns possibly included in my query. I need to take into account OR's and AND's - using UNIONS for each OR'd indexed column in my final query is possibly going to be MASSIVE!!

To :BV or not to :BV

Andy, October 12, 2009 - 10:11 am UTC

I think my SQL is going to be very large!

SELECT col1, col2, col3, col4 from tab 
WHERE (col1 = X OR col2 = X)
  AND (col3 = Y OR col4 = Y)

Perhaps:

SELECT col1, col2, col3, col4 from tab WHERE col1 = :bv1 AND col3 = :bv2
UNION 
SELECT col1, col2, col3, col4 from tab WHERE col1 = :bv1 AND col4 = :bv2
UNION 
SELECT col1, col2, col3, col4 from tab WHERE col2 = :bv1 AND col3 = :bv2
UNION 
SELECT col1, col2, col3, col4 from tab WHERE col2 = :bv1 AND col4 = :bv2

... and so on

The problem is that I have 3 sets of possible "OR" columns (something like:
col1 OR col2, col3 OR col4, col5 OR col6 OR col7) , combined with 5 possible single "AND" columns.

This would be easier to code and understand as follows:

...
WHERE (col1 OR col2) 
   AND (col3 OR col4) 
   AND (col5 OR col6 OR col7)
   AND col8
   AND col9
   AND col10
   AND col11
   AND col12

But to use the indexes, I need to use UNIONS. Does Oracle really not cope with the logic for this?

Re: Not using binds has two serious drawbacks

Rasto Sadecky, July 05, 2012 - 7:10 am UTC

Tom,

This is related to the first review by Oleksandr Alesinskyy above: ...just quote string properly (duplicate each quote inside the string and then surround it with quotes...

We found out that it's easy to inject SQL into some of our (JSP) LOV's but I couldn't "break" into others.
The LOV's have a search field at the top allowing the user to input their search criteria.

The ones that we could crack fairly easily are in effect building a where clause like this:

' where upper(department_id) = upper('||searchValue||')'

However, I couldn't inject SQL into all of my randomly picked test subjects. The ones that look safe are either using bind variables (and we're going to convert all that don't to use binds) or they do what Oleksandr suggested:
All single quotes in the searchValue are replaced by double single quotes before concatenating the result into the same where clause as above.

While I have no doubts about bind variables being the right approach I'm still curious: Is it possible to inject SQL when the user input is pre-processed as described above?
I tried to come up with something but I just couldn't break the resulting select statement.

Thank you,
Rasto

July 05, 2012 - 9:09 am UTC

However, I couldn't inject SQL into all of my randomly picked test subjects.

that either means

a) the developer did a sanity check and you didn't yet figure out how to defeat it
b) you just didn't try hard enough
c) the code is actually not sql injectable

The fact that you can inject some is the disturbing thing - if you concatenate strings together with inputs passed to you from the outside (another programmer, an end user, whatever) - your code is subject to sql injection and you have to have it reviewed by many people - people that do not like you so they'll actually try to break it as hard as they can.

and then when they cannot, you just hope and pray that no one smarter than they are comes along with evil intent later.

OR

you use bind variables, making your sql safe from sql injection.

your choice.

Re: Not using binds has two serious drawbacks

Rasto Sadecky, July 05, 2012 - 7:22 am UTC

I made a mistake in the previous post when translating from Java.
The where clause should look like this:

' where upper(department_id) = upper('''||searchValue||''')'

Live SQL

Share and learn SQL and PL/SQL; free access to the latest version of Oracle Database!

Dev Gym

Classes, workouts and quizzes on Oracle Database technologies. Expertise through exercise!

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

followup on "embedding a variable in a string"

Not using binds has two serious drawbacks

Bind variables

followup to sql injection vs binds

?

Example...

Grammatical error in my post above

David Litchfield White Paper

DWH vs OLTP; bind vs literal

Adaptive Cursor Sharing, 11g

INSIDIOUS !!

avoiding sql injection

DBMS_ASSERT

To :BV, or not to :BV?

To :BV or not to :BV

To :BV or not to :BV

Re: Not using binds has two serious drawbacks

Re: Not using binds has two serious drawbacks

More to Explore

Live SQL

Dev Gym