Skip to Main Content
  • Questions
  • Defending and detecting SQL injection

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, AR.

Asked: September 14, 2004 - 3:09 pm UTC

Answered by: Tom Kyte - Last updated: March 29, 2011 - 3:06 am UTC

Category: Database - Version: 9.2.0.4

Viewed 10K+ times! This question is

You Asked

Tom,
I was in the midst of reading your book where you talk a little about sql injection. That got me curious and led me to search a little on google.

From what I've read in the past hour or so, I guess there isn't much of a defense available against sql injection. How would you recommend that I go about detecting folks that engaged in sql injection in the 1) past 2) are currently doing so, at this point in time 3) detect code/applications susceptible to sql injection - how to fix'em too 4) detect database vulnerabilities (authorization for eg) that will allow a malicious user to engage in it.

I realize that previous injections can be detected through logminer. Current injections thru v$sqlarea, sqlnet logging, tracing, sniffing and parsing sql statements and reading through tons and tons of sql statements to detect it. But that's not really feasible - don't you think? But what can I do to minimize it or detect it proactively in future? If auditing is a way to go - what should I be auditing?

I'm not a security dude/hacker by any means. I realize that it is close to impossible to convert an existing application overnight to become bulletproof against injections. But I would like your opinion on what I can do to minimize it at the very least. (yes, use bind variables for one like you've demonstrated time and again!).

It'll be great if you could share any personal experience that you've had in the area. What you did to fix a susceptible applicaiton/database and what you do to prevent it, during database/application design.

Thank you for your time.

and we said...

oh, defending against SQL injection is trivial!!! really, it is.

Just never accept inputs from the end user that you glue into a sql statement.

That's it -- never have something passed to you from the outside (outside YOUR CODE) that becomes part of the query! SQL injection = thing of the past.

It is not dynamic sql that is the issue (all sql is dynamic in Oracle actually -- even static sql in pro*c/plsql!). It is "the construction" of this sql that is the problem.


If a user gives you inputs - they should be BOUND into the query -- not concatenated. The second you concatenate user input into your SQL -- it is as if you gave them the ability to pass you code and you execute that code. Plain and simple.

Don't concatenate, but rather bind their inputs, and wah-lah, you are done.


The way to prevent it is via coding standards and design review. And -- best of all -- at the end of the day, we achieve that nirvana that is "the applications use binds!"




and you rated our response

  (33 ratings)

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

Reviews

Where do we do this?

September 14, 2004 - 3:41 pm UTC

Reviewer: Yuga from LA,USA

Tom,
I didn't understand what you mean by "concatenate the SQL". When we write queries in any application we write as follows. Unless we dynamically build queries in some applications using Oracle forms(Which allows you to create dynamic queries. But then that is the requirement).

select emp_name into l_var
from emp where emp_no = var1;

Is this SQL not bound. Where is the chance of creating a static SQL without bind parameters?

You could probably do this..

select emp_name into l_var
from emp where emp_no = '12345';

Which is not of much use in any application.

Please explain.

Thanks
Yuga.

Tom Kyte

Followup  

September 14, 2004 - 3:49 pm UTC

that sql is bound - it is not possible for the *static* sql in plsql to be subject to sql injection.

this:
begin
...
   select emp_name into l_var 
     from emp where emp_no = var1; 

....

is perfection.  this:

create or replace procedure p( p_where_clause in varchar2 )
is
   l_var varchar2(250);
begin
    
   execute immediate 'select emp_name from emp ' || p_where_clause 
      into l_var;




That, that is dangerous if p_where_clause comes from OUTSIDE YOUR SPHERE of control.  (not only that, but hey -- no binds :(  )

What if the end user submits the string


where function_you_can_run_but_they_cannot( ..... ) = 0


they just ran code *as you*.  Or more insidous, here is the example I used in the book:

<quote>
Without Bind Variables, Your Code Is Less Secure

Another concern when you don't use bind variables revolves around security, specifically the risk of SQL injection. To understand how it works, suppose that we have an application that asks a user for a username and password. We execute this query:

select count(*) from user_table where username = THAT_USER
and password = THAT_PASSWORD


This seems innocent enough right? Well, let's use SQL*Plus to test that theory and see what happens with and without bind variables.


ops$tkyte@ORA920> create table user_table 
  2 ( username varchar2(30), password varchar2(30) );
Table created.

ops$tkyte@ORA920> insert into user_table values 
  2 ( 'tom', 'top_secret_password' );
1 row created.

ops$tkyte@ORA920> commit;
Commit complete.

ops$tkyte@ORA920> accept Uname prompt "Enter username: "
Enter username: tom
ops$tkyte@ORA920> accept Pword prompt "Enter password: "
Enter password: i_dont_know' or 'x' = 'x


Notice the password we just used. It incorporates a little SQL there doesn't it? Since we are just gluing strings together, not binding, the end user can actually type in arbitrary SQL and have it executed! Our application takes this string now and continues on:


ops$tkyte@ORA920> select count(*)
  2    from user_table
  3   where username = '&Uname'
  4     and password = '&Pword'
  5  /
old   3:  where username = '&Uname'
new   3:  where username = 'tom'
old   4:    and password = '&Pword'
new   4:    and password = 'i_dont_know' or 'x' = 'x'

  COUNT(*)
----------
         1

Look at that. Apparently, the password `i_dont_know' or `x' = `x' is our password. But if we use bind variables instead and accept the exact input from the end user, we see this:


ops$tkyte@ORA920> variable uname varchar2(30);
ops$tkyte@ORA920> variable pword varchar2(30);
ops$tkyte@ORA920> exec :uname := 'tom'; 
ops$tkyte@ORA920> exec :pword := 'i_dont_know'' or ''x'' = ''x';

PL/SQL procedure successfully completed.

ops$tkyte@ORA920>
ops$tkyte@ORA920> select count(*)
  2    from user_table
  3   where username = :uname
  4     and password = :pword
  5  /

  COUNT(*)
----------
         0

We get the correct answer. 

Think about this the next time you put an application out on the Internet. How many hidden vulnerabilities might you have lurking in there if you develop your application using string concatenation instead of bind variables? Think of the "neat" side effects someone could have on your system. Consider this password:


ops$tkyte@ORA920> accept Pword prompt "Enter password: "
Enter password: hr_pkg.fire_emp( 1234 )


Whoops, this person just executed a stored function as the user who is connected to the database. While he might not get logged on, he nonetheless got connected to your system and fired someone. Is this unbelievable? Absolutely not. Search www.google.com for SQL Injection, and you'll see results 1 through 10 of about 15,800. Just consider the implications.

If you don't believe the performance-related arguments for using bind variables in your system, maybe this last bit will be enough to persuade you. Bind variables add security.
</quote>



So, if you do not "concatenate" (glue in, make part of the sql) inputs from outside of what you control -- you cannot be subject to "sql injection".  

As soon as you let what the end user type into a field be used in your SQL statement (not bound into, used as part of the sql) -- you are 100% vunerable to SQL injection.


<b>(i'll find any argument I can to further the "just bind will you" crusade), this one is awesome actually -- because

a) it is true
b) it happens
c) people are concerned about it

that I get nicely bound applications out of it just makes me happy :)</b>

 

Remove constants and concatenations to rid sql injections?

September 14, 2004 - 4:54 pm UTC

Reviewer: AR

Tom,
Thank you for your response.

In short - would you say, running (and addressing the output of) your custom "remove_constants" function to identify frequently executed sqls in v$sqlarea that don't use bind variables will pretty much nail any sql injection that is going on as well?

Tom Kyte

Followup  

September 14, 2004 - 7:07 pm UTC

no, only:

a) coding standards enforced by
b) code reviews coupled with
c) people serious about security and doing it right


will do it. i could easily subvert your scheme by using cursor_sharing :)

(but hey, if you want to go on a search and destroy mission to zap that non-shareable sql -- I am 100% behind you!)

I don't understand what SQL injection is

September 14, 2004 - 5:26 pm UTC

Reviewer: Mikito Harakiri

Initially databases were developed for end user to enter *arbitrary sql* from the SQL command line. The whole topic of database security was designed to prevent user from not being able to execute the statement beyond his authority. Therefore design your system as if end user have the power to execute arbitrary sql from *any* client and your application would be safe, right? Or designing database properly today is something too much to ask for?

Tom Kyte

Followup  

September 14, 2004 - 7:18 pm UTC

<quote>
Initially databases were developed for end user to enter *arbitrary sql* from
the SQL command line.
</quote>

where did you make that comment up from?

SQL was designed as a programming tool to store data and allow programmers flexible access to it.

SQL is a language (that is what the L stands for)

SQL was no more designed for a command line interface than C, Java, PL/I, REXX, <whatever> was.

A command line interface is a user friendly program that just happens to collect strings and do stuff -- csh is a command line interface NO DIFFERENT really at its roots from sqlplus. heck even command.com is.



But Mikito - -when the end user is a MIDDLE TIER APPLICATION, connected to the database and trying to verify via a database table that your "password" or credentials "are ok" (did you even read the example???) -- that user (the middle tier) has the authority to query the password table. the PROGRAMMER wrote a program that takes input from the end user and executed it. THAT IS SQL INJECTION.

When a stored procedure (runs with definers rights) dynamically executes some inputs from an end user - it is running with the "proper authority" -- just doing something the programmer clearly didn't think about, didn't anticipate, didn't design for.


Middle tiers with common user/pass into the database....
dynamic sql in stored procedures.....

they all execute in an authorization domain the "end user" might not have access to. that is the problem.


Has *NOTHING* to do with the database design.

It has everything to do with PROGRAM design as the program many times is running as "middle tier" (super user) or "definers rights"

September 14, 2004 - 9:05 pm UTC

Reviewer: mikito

> <quote>
> Initially databases were developed for end user to enter > *arbitrary sql* from
> the SQL command line.
> </quote>

> where did you make that comment up from?



From </code> http://www.mcjones.org/System_R/SQL_Reunion_95/sqlr95-System.html <code>
<quote>What we thought we were doing was making it possible for non-programmers to interact with databases. We thought that this was going to open up access to data to a whole new class of people who could do things that were never possible before because they didn't know how to program. This was before the days of graphical user interfaces which ultimately did make that sort of a revolution, and we didn't know anything about that, and so I don't think we impacted the world as much as we hoped we were going to in terms of making data accessible to non-programmers. It kind of took Apple to do that. The problem that we didn't think we were working on at all - at least, we didn't pay any attention to it - was how to embed query languages into host languages, or how to make a language that would serve as an interchange medium between different systems - those are the ways in which SQL ultimately turned out to be very successful, rather than as an end-user language for ad hoc users.</quote>



Tom Kyte

Followup  

September 15, 2004 - 7:36 am UTC

sql was not invented to enable people to enter sql from a command line.

sql was invented in order to make access to data flexible, easy, accessible to all. (that I said)

the words "command line" don't even appear in that article.

SQL is *a language*

In any case -- do you understand "sql injection" and how it happens and the dangers presented by it.

sql injections

September 14, 2004 - 9:49 pm UTC

Reviewer: AR

Tom,
Thank you for your response.

<quote>
no, only:
a) coding standards enforced by
b) code reviews coupled with
c) people serious about security and doing it right
will do it. i could easily subvert your scheme by using cursor_sharing :)
</quote>

That was kinda my original question. As a DBA, I am serious about point "c". Given that I've inherited some applications, how should should I go about it ('c') - if not use something like remove_constants for starters? Can you elaborate a little more? Should I be looking into privileges? I'm not sure what exactly needs to be addressed when it comes to privileges. Where should I begin?

Note: I wasn't involved in the design/development phase of many of these applications. I just support many of these canned applications now.

Yup, you could subvert the remove_constants scheme with cursor_sharing..you've explained that in your book as well.

Greatly appreciate your time.

PS : You typed a whole page to explain the sql injection concept to Mikito and just a para in response to my question. Boohoo :(. Just kidding!

Tom Kyte

Followup  

September 15, 2004 - 7:44 am UTC

you need to start with (a) though.

non-shareable sql would be an indication that perhaps sql injection is a POSSIBILITY (doesn't prove it, doesn't disprove it)

all "apparently shareable sql" would be an indication that PERHAPS sql injection is not a problem (doesn't prove it, doesn't disprove it)

(a) and (b) are the only bullet proof way I can think of.

if cursor_sharing is exact
and the applications never muck with it
and you have lots of non-shareable sql
that might be a good indication you are vunerable (but the theory has too many holes to be 100% sure)

finding the non-shareable sql is not just a case of finding literal SQL sometimes, my remove_constants and then query is looking for "low hanging fruit" binding issues (eg: look for the same query once literals are removed with a count of 100 or more or N or more). The sql injection query might be totally unique (there would be 1 of them). But queries with a count of one are going to be -- well, every other query in they system pretty much (you won't see the forest for the trees).


Hopefully the answer to Mikito was of general purpose use -- to explain why sql injection is relevant to systems that execute SQL in a different privilege domain from the end user and allow the end user to submit arbitrary sql to it.



wah-lah?

September 15, 2004 - 8:04 am UTC

Reviewer: Joel from Columbus, OH USA

Hmmmmm....you mean voila? ;)

You've got to be kidding

September 15, 2004 - 9:11 am UTC

Reviewer: Jeff Hunter from greenwich.ct.us

Joel from Columbus, OH USA, you're a DBA, right? Only a DBA could be so picky about a detail that is so irrelevant and off topic.

No I'm not kidding

September 15, 2004 - 10:09 am UTC

Reviewer: Joel from Columbus, OH USA

Jeff Hunter from greenwich.ct.us, I'm not a DBA. But one suggestion, have a sense of humor about you dude. That's the way it was intended, and I'm sure Tom took it that way as well.

sql injections

September 15, 2004 - 12:08 pm UTC

Reviewer: AR

Tom,
Many thanks for your response.

I doubt that a significant % of vendors / web developers are even aware of something called sql injection! Hopefully that will change.

Generally speaking - would you say all of the Oracle database's internal code is sql injection-proof? Just curious - Is there a QA/security group within Oracle corp that reviews every piece of code/patch that is churned-out for such holes?

<quote>
The sql injection query might be totally
unique (there would be 1 of them). But queries with a count of one are going to be -- well, every other query in they system pretty much (you won't see the forest for the trees).
</quote>

Based on your response, it is going to be a looong time before I can bullet proof some of our applications.

Are you aware of any tools which would help detect such stuff on a previously deployed application? Like you say, if I were to do this manually - I would pretty much have to look at almost *EVERY* sql that gets executed. Doesn't seem feasible.

Thank you again.

PS : Am I the only one over-reacting or being panicky about this? I would think anyone with an application that allows a user to provide inputs on the web ought to be concerned..

Tom Kyte

Followup  

September 15, 2004 - 12:53 pm UTC

is there a QA/security group -- yes, each and every product is sent through them.

Also, we submit our code to independent 3rd parties for evaluation as well.



I don't think you are over-reacting -- if the developers take end user input and just execute that in the database (eg: no binds), they could inadvertantly do something bad and a malicious "smart" end user could do bad things.

When I was writing effective Oracle by design with WROX (before they went out of business) and was writing this chapter (on the binding issue), I actually used the google search on "sql injection" and since WROX was hosting the discussion forum for my Oracle book on a SQL server ASP web site -- I used one of the "hey, try this and see if it works" techniques from a web page. Using that, I was able to run OS commands from their database as the "sql server admin". It took me about 15 minutes to figure out how to exploit it. The exploit relied on the fact the developer would take my search string as input and simply concatenated it to the query. so I passed in ; sp_xxxxx( .... ) and ran a stored procedure that does host commands that many people had installed by default.


you'd have to look at the techniques the developers use to build their sql queries. securing code starts at design -- when securing code AFTER the fact, only a brute force "read it all" can answer many questions.



I am not panicky

September 15, 2004 - 12:39 pm UTC

Reviewer: Mark from NY

I actually did investigate SQL injection when our net admin first informed me about it. But do you want to know why I'm not panicky? Tom's books drove into my head long ago that bind variables = good, lack of bind variables = bad. :D (99% of the time anyway, and when you're not using them you want to use hard-coded constants, not concatenated user input.)

Few things in Oracle are that close to black and white... Like Tom always says, there's no fast=true. But using bind variables is as close to that as you can get (it's also good=true).

Command line

September 15, 2004 - 4:21 pm UTC

Reviewer: mikito

Sorry, I meant possibility of the end user to execute ad-hock SQL, be it command line SQL*Plus or other client. Once again, the RDBMS security design is supposed to permit safe execution of ad-hock SQL by the end user. If end user is allowed to execute any SQL statement (within his authorization scope, of course), then what danger SQL injection can possibly present? In all SQL injection examples that I saw, an intruder injects SQL statement that should trivially be rejected by user permissions.

Tom Kyte

Followup  

September 15, 2004 - 8:29 pm UTC

if the user is

a) logged in as themself
b) using THEIR privilege domain

then "all is safe" (assuming you've only given them the privs they should have)


In all SQL Injection examples you saw on this page, the sql is being executed by a middle tier application (eg: check this guys password -- we must already be LOGGED IN no?) - the the middle tier applications are usually logged in as "super user" or a user with lots more privs then the end user is to have (the middle tier is supposed to regulate what the user can and cannot do).

OR the user is executing a stored procedure, the stored procedure executes with DEFINERS RIGHTS and the end user "tricks the stored procedure" into doing something the programmer DID NOT INTEND.

sql injection is called "a serious programmer design error, aka a bug in the code"

sql injection happens, as stated with mid tier applications that use a common user id or in any application that executes code in another privelege domain.


it would be sort of like have "sh" in unix owned by root with SETUID privs...

Tom's example of SQL injection

September 15, 2004 - 4:36 pm UTC

Reviewer: mikito

In Tom's example it's the database application that manages the end-users. Well, this kind of database application design, when the end users are managed by application instead of the database, is certainly not secured. However, it has been emphasised "use RDMS features instead reinventing them on application side" maybe hundred times on Tom's site already, and this is just one example of the troubled design not following this simple principle.

Tom Kyte

Followup  

September 15, 2004 - 8:31 pm UTC

not really

think "definers rights procedures"

I call them a security bonus, they are awesome -- but when programmed to do dynamic sql -- any sql -- passed in by an end user, they become "a problem"

Unbound Object Names and SQL Injection

September 15, 2004 - 6:08 pm UTC

Reviewer: NN from Arlington, VA USA

My application, for reasons that make no sense, has many tables with identical columns, and accesses them through dynamic SQL along the lines of:

EXECUTE IMMEDIATE 'select * from '||v_table_name||' where id = :1' USING v_id;

The table name is concatenated because Oracle won't let you bind object names, apparently only values.

Here are some tables that fit the pattern:
create table cust(id number(2) primary key, timestamp date, name varchar2(30));
create table vend(id number(2) primary key, timestamp date, name varchar2(30));
insert into cust values(1,SYSDATE,'Peoplesoft');
insert into cust values(2,SYSDATE,'SAP');
insert into vend values(3,SYSDATE,'Oracle');
insert into vend values(4,SYSDATE,'Sun');

The database spends 95% of its cycles in parses and waits, but performance problems are insufficient grounds for changing the design. What WOULD constitute sufficient grounds is a security breach doing something active, like dropping a table or changing a password. My attempts have failed to parse, or got a WNDS exception - is there a more nefarious possibility? I don't know that I want to see an example posted, but it would be good to know if it is possible.


Tom Kyte

Followup  

September 15, 2004 - 9:06 pm UTC

if v_table_name is 100% controlled by YOU -- it won't be subject to sql injection.

It is when you glue in something the end user types in.

(binding is so you can reuse a query plan. if you change the table, the plan would CHANGE - hence, you cannot bind thing that would necessarily change the plan!)



I feel better already!

September 17, 2004 - 10:01 am UTC

Reviewer: Stew Stryker from Hanover, NH USA

>if v_table_name is 100% controlled by YOU -- it won't be
> subject to sql injection.
>
>It is when you glue in something the end user types in.

I was really beginning to worry about a little work I did recently that used Dynamic SQL and concatenated in the values. But your comment above reminded me that I'm controlling the input for all the values that are pasted in.

(I *think* the following is directly related to this discussion, though slightly on the edge.)

On a slight tangent, I wanted to ask about *when* it's valid to use Dynamic SQL. I used the above Dynamic SQL in a stored procedure for an end-user report which had 5 parameters, 4 of them optional. The optional ones were of the type: either enter a value or use all.

I *really* didn't want to build and maintain 16 different versions of an already-complex query, so I wrote a little something to parse the query string and drop the clauses that didn't have parameters provided for them. Then it dynamically executed.

I thought that the dynamic SQL would run faster than a static query like:

SELECT ...
FROM ..., ..., ...
WHERE (dept = :dept_in OR :dept_in IS NULL)
and (state = :state_in OR :state_in IS NULL)
...

My experience with the above is that they're terribly slow.

So, was I nuts, or was this a valid use of Dynamic SQL?

Tom Kyte

Followup  

September 17, 2004 - 10:30 am UTC

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1288401763279

perfectly valid -- as long as you use bind variables!  (in one way shape or form)

if you don't like sys_context, you can use decode

http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1288401763279#4988761718663 <code>

sql injection

September 17, 2004 - 3:58 pm UTC

Reviewer: Dave from St.Louis MO

Very consise answer to solve this problem.

SQL Injection in PRO*C

January 09, 2005 - 11:21 pm UTC

Reviewer: Giridhar from India

Hi tom,
After reading your book and this thread, i would like to explore further if our system has sql injection issues.
I would like to know if sql injection is possible in pro*c.
(As most of the times we use ":" host variables, which are bound). Would you pls provide an example if sql injection is possible in pro*c as it would help me to learn more about sql injection in pro*c applications.

Thanks in advance,
Giridhar

Tom Kyte

Followup  

January 10, 2005 - 8:36 am UTC


// for all languages, the following is true
IF (you do dynamic sql) AND (you accept input strings from the end user)
THEN
you are subject to sql injection
END IF


method 2 and method 3

January 10, 2005 - 9:05 am UTC

Reviewer: Giridhar from India

Hi Tom,
it means even method 2 and method 3 in pro*c are also vulnerable for sql injection?
regards,
Giridhar

Tom Kyte

Followup  

January 10, 2005 - 9:08 am UTC

dynamic sql

if you

a) accept input from an end user
b) execute it as code in the database

you are sql injection candidates


if you use bind variables -- no sql injection.

if you use concantenation and just "glue the user inputs into your sql", you are subject to sql injection.


method 1, 2, 3, 4, 5, 6, 7, 8, 999, whatever


the language is not relevant
the 'method' is not relevant


if you do dynamic SQL and you take input from the end user and just glue it in there, you have sql injection issues to worry about.

How about jbo:ExecuteSql...?

February 04, 2005 - 5:37 pm UTC

Reviewer: Dave from Richmond, VA USA

Is there a simple way to convert:
 
      <jbo:ExecuteSQL appid="SomeBc4jModule" >
        DELETE FROM emp WHERE empno = <%=s_empno%>
      </jbo:ExecuteSQL>

to use a bind variable? 
Best I've thought of so far is to create a stored procedure that accepts the empno as a parameter and performs the delete as follows:

In the database:
  create procedure emp_delete(s_empno number) is
  begin
     DELETE FROM emp WHERE empno = s_empno;
  end; 

In my jsp:

      <jbo:ExecuteSQL appid="SomeBc4jModule" >
        BEGIN 
           emp_delete(<%=s_empno%>);
        END;         
      </jbo:ExecuteSQL>

I think this might work but is there a better way? 


 

Tom Kyte

Followup  

February 05, 2005 - 5:22 am UTC

but then you just moved the problem, instead of having an unbound delete statement, you have an unbound plsql block.

that is, you would not have fixed anything.


I know nothing about jbo and what you can/cannot do with it. Maybe someone reading this does and will followup.

You lost me...

February 07, 2005 - 11:39 am UTC

Reviewer: Dave from Richmond, VA USA

I'm not all that great with the JBO stuff myself. I thought that by creating a procedure I would at least limit the possiblity for injection by only accepting a single number (anything else should cause an exception right?). I also thought that sql statements in plsql procedures where always bound.

Tom Kyte

Followup  

February 07, 2005 - 11:47 am UTC

*that* would be even *worse*


 <jbo:ExecuteSQL appid="SomeBc4jModule" >
        BEGIN 
           emp_delete(<%=s_empno%>);
        END;         
      </jbo:ExecuteSQL>


consider this input:

s_empno = 55 ); execute immediate 'drop table emp';
s_empno = 55 ); update emp set sal = sal*2 where ename = 'TOM';

consider what the resulting block of code would look like....



sql statements in the procedure emp_delete are BOUND, but the statment you are executing -- IT is not.  

I'm sure if you check out the programmers guide, it might be revealed in there! (howto bind)


 

SQL Injection

February 07, 2005 - 12:32 pm UTC

Reviewer: A reader

The database procedure that Dave has (reproduced below) has no dynamic SQL. It only as a static SQL that expects a number bind variable value.

create procedure emp_delete(s_empno number) is
begin
DELETE FROM emp WHERE empno = s_empno;
end;

Any non-number passed to the procedure would error out in the call to the procedure itself (would not even reach the SQL statement).
How can this be subject to SQL injection ?

Tom Kyte

Followup  

February 07, 2005 - 1:19 pm UTC

if binds are not being used, think about it

whatever string the end user types in will be glued into the statement. Ok, we have:

begin
emp_delete( %%%YOUR_INPUTS_HERE%%% );
end;


say, instead of a number, I provide you:

55); execute immediate 'drop table emp'; dbms_output.put_line(null


your block suddenly becomes:

begin
emp_delete( 55); execute immediate 'drop table emp'; dbms_output.put_line(null );
end;


that is the very definition of "sql injection"


so, if his code was not using binds and you change it from simple SQL to PLSQL that takes a string - well, the problem gets to be on the scale of using SQL Server all of a sudden (Oracle is less prone to SQL Injection than sql server is by far, but if you stick plsql blocks in there without binds -- letting end users provide arbitrary PLSQL code -- then you make us just as vunerable!)





sys_context & sql injection

February 07, 2005 - 1:20 pm UTC

Reviewer: Rich from Chicago, IL

I am creating a stored proc which will open a refcursor
based on a dynamic query depending on the params passed
from an external program (e.g. Java).

I used your suggestion in:
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1288401763279 <code>

as my template. It works fine but I wanted to know if this
is also vulnerable to SQL injections. I tried to test it
out by entering bogus conditions (e.g. i_dont_know' or 'x'
= 'x) and (so far) the query executes as expected (i.e.
the dynamic query doesnt return records when bogus
condition is used).

I would appreciate your thoughts on this. Thanks a lot.



Tom Kyte

Followup  

February 07, 2005 - 1:23 pm UTC

sql injection means the end user supplies a value which you concatenate into sql or plsql and execute.

sys_context is *identical* to binding (it is binding!)

so, it is safe...



where x = sys_context('my_ctx', 'x' )

is just like:

where x = :x




sys_context & sql injection

February 07, 2005 - 1:44 pm UTC

Reviewer: Rich from Chicago, IL

Superb! Many (and more) thanks!

PL/SQL Injection

February 07, 2005 - 3:04 pm UTC

Reviewer: Jay

Thanks for the explanation on how Dave's anonymous PL/SQL block is subject to SQL injection -- oops PL/SQL injection!

Bet nobody talks about PL/SQL injection (SQL injection is keyword most used). It is as scary as SQL injection!

Tom Kyte

Followup  

February 07, 2005 - 4:23 pm UTC

scarier and I talked about it in the article...

</code> https://www.oracle.com/technetwork/issue-archive/2005/05-jan/o15asktom-084959.html <code>


How do you have flexibility and security?

February 07, 2005 - 3:13 pm UTC

Reviewer: Bruce from Edmonton, Alberta, Canada

Hi,

The examples you list for binding and preventing SQL injection use hard-coded operators (like col1 = :var).

I would like to know how to accept input from users where they need to have the ability to choose an operator (e.g. =, like, in, not in, . . .) as well as the value? We have many search forms where this type of flexibility in searching is a business necessity.

Thank-you.

Tom Kyte

Followup  

February 07, 2005 - 4:25 pm UTC

where col1 like :var;
where col1 in ( :var1, :var2, :var3 );

where col1 in ( select * from table(str2tbl(:var)) );

(search this site for str2tbl for more.

where col1 between :x and :y;

where col1 <ANY OPERATOR> :bind_variable;

anywhere you could put a literal, you can put a bind.....

correct me if i am wrong...

July 27, 2006 - 4:45 am UTC

Reviewer: Ramachandran from India


A statement like :
select * from table_name where column_name = 'somevalue';
will be parsed each time it is executed even though its the same query except for the where clause parameter.

If something like:
select * from table_name where column_name = :bindvariable;
is used where the actual value is bound to the bind variable, then it will be parsed once and reused upon subsequent invocations.
I think I am right so far.

In your example above, you bound 'i_dont_know'' or ''x'' = ''x' for the password but that did not return any rows.

So how did it "actually" bind or what is the exact query that gets executed ?
Does this mean that it actually tried finding a password column that had the value of 'i_dont_know'' or ''x'' = ''x' as a complete string and thats why it failed ?

please clarify and thanks a lot in advance.

Tom Kyte

Followup  

July 27, 2006 - 11:52 am UTC

when you bind the string 'i_dont_know....'

It is not any different than binding the string 'tiger' or 'password'
it was just a string.


the actual query executed was:

select * from password_table where password = :x;

and :x was set to that string.

July 27, 2006 - 7:41 pm UTC

Reviewer: Su Baba

CREATE TABLE x (
domain VARCHAR2(30)
);


INSERT INTO x VALUES ('yahoo.com');
INSERT INTO x VALUES ('google.com');
INSERT INTO x VALUES ('microsoft.com');
commit;

SELECT * FROM x WHERE domain = 'yahoo.com';

DOMAIN
---------
yahoo.com

-- Potential SQL Injection
SELECT * FROM x WHERE domain = 'yahoo.com' or 1 = 1;

DOMAIN
-------------
yahoo.com
google.com
microsoft.com

In the UI, we allow users to enter domain. So if they enter "yahoo.com", the resulting SQL will be

SELECT * FROM x WHERE domain = 'yahoo.com';

DOMAIN
---------
yahoo.com


If they enter "yahoo.com' or 1 = 1", the resulting SQL will be

SELECT * FROM x WHERE domain = 'yahoo.com'' or 1 = 1';

no rows selected


In the application, we always turn a single quote into 2 single quotes.

Would this be good enough to prevent against SQL injection?


Tom Kyte

Followup  

July 27, 2006 - 8:29 pm UTC

JUST BIND

why is that so hard?!?!>?!>!>!


I just don't get it


JUST BIND AND YOU DON'T EVEN NEED TO THINK ABOUT IT.

the blindingly simple answer is:

JUST BIND

for performance
for scalability
for memory utilization
for security


JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND

re: just bind

July 27, 2006 - 8:34 pm UTC

Reviewer: Mark A. Williams from Indianapolis, IN USA

JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND
JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND
JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND
JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND
JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND
JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND
JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND
JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND
JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND
JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND
JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND JUST BIND

Jack Nicholson does SQL?

Tom Kyte

Followup  

July 27, 2006 - 8:38 pm UTC

Hi honey, I'm home.

All work and no play makes Jack a dull boy....

Here's Johnny!


:)

</code> http://www.cruisegazing.com/RPG_Motivational/chaoticneutral.jpg <code>

just had to add that link :)

July 27, 2006 - 9:13 pm UTC

Reviewer: Su Baba

Yes, yes, I hear you. That's what I would do if I get to make the call, but I don't! I'm trying to come with some benchmarking numbers in all 4 areas that you mentioned (performance, scalability, memory utilization, and security) that would convince the managegment/developers the need to use bind variables in our application. I can't even come up with anything that's convincing at all!

- performance: performs about the same with or without bind.
- scalability: not too many concurrent users in the system.
Hard to make the case.
- memory utilization: probably the easiest to prove.
- security: couldn't break the application code with the
example above.

In terms of security, I just need to show one simple case that would make our application look vulnerable, but I couldn't come up with one.

Given the wealth of information out there on bind variables, it may sound silly that I have a hard time making my case, but a generic benchmarking won't do, it has to be specific to our application.

Tom Kyte

Followup  

July 27, 2006 - 9:15 pm UTC

you cannot?

You cannot use runstats to show the latching that takes place?
</code> http://asktom.oracle.com/~tkyte/runstats.html <code>

You cannot just run 1,000 parses and time it and show how 1,000 soft parses take less time?

You cannot show how 1,000 stupid copies of the same sql statment in the shared pool consume 1,000 times the memory of 1 copy?



do they ever "not bind" numbers?

(try hitting them with a buffer overflow and see if you can just crash the thing - put in as many single quotes as they let you, let them double it up)

dbms_assert

July 28, 2006 - 4:11 am UTC

Reviewer: Sokrates

why is dbms_assert not documented ?


Tom Kyte

Followup  

July 28, 2006 - 7:57 pm UTC

was supplied as a "patch" after the documentation was written and distributed.

July 28, 2006 - 4:02 pm UTC

Reviewer: Su Baba

do they ever "not bind" numbers?

=> They do, but the UI checks if the input value is a number or not.

(try hitting them with a buffer overflow and see if you can just crash the thing - put in as many single quotes as they let you, let them double it up)

=> The UI also limits the field the user can enter to certain size (< 40). No possibility of buffer overflow.



Tom Kyte

Followup  

July 28, 2006 - 8:55 pm UTC

then they likely are not subject to sql injection, unless they forget to do it somewhere.

Isn't this just silly though. They wrote lots of validation code, must remember to invoke this code all over the place, just so they can build applications that refuse to scale, look like a child wrote them, perform not at the level they could and eat up the shared pool.

When all they needed to do was....

bind

Some days I just give up.

example of costly sql injection

August 17, 2009 - 8:00 pm UTC

Reviewer: Jim from Oregon

http://news.cnet.com/8301-27080_3-10311336-245.html?part=rss&subj=news&tag=2547-1_3-0-5

I guess those companies just didn't filter the strings carefully enough. Oh wait if they used bind variables they would have been fine.
Tom Kyte

Followup  

August 24, 2009 - 4:32 pm UTC

I liked this "expert coverage" of it better:

http://news.bbc.co.uk/2/hi/business/8206305.stm

read it, when I got to quotes like:

"involved extremely well researched, especially configured codes, not standard attack codes downloaded from the internet".

I just get chills - the people reporting on this, investigating this - know NOT of what they are talking about.

But the quote does sound so cool "not just any old standard attack code - especially (sic) configured codes" - how cool is that.

March 04, 2011 - 9:57 pm UTC

Reviewer: Sean from Atlanta

ops$tkyte@ORA920> accept Uname prompt "Enter username: "
Enter username: tom
ops$tkyte@ORA920> accept Pword prompt "Enter password: "
Enter password: i_dont_know' or 'x' = 'x

the hecker does not have to figure out the username correctly. any char (less 30 charaters) would do, because:
(username ='junk' and password ='alsojunk') or ('x'='x')
is true.
Tom Kyte

Followup  

March 07, 2011 - 9:47 am UTC

it would be best in some cases to "know" the username.

Many developers, upon having someone "validated" in the login process then use the username to figure out what you should be able to "see".


It would be nice to log in as the President of a company - without knowing their password - wouldn't it :)

but yes, you can enter any string you want.

Mysql.com hacked by SQL Injection

March 28, 2011 - 9:19 am UTC

Reviewer: Mark from NY

This might have been simple sloppiness by one or two individuals, but I can't help but feel this proves a point about the attitudes of the folks behind MySQL - http://techie-buzz.com/tech-news/mysql-com-database-compromised-sql-injection.html
Tom Kyte

Followup  

March 29, 2011 - 3:06 am UTC

sql injection can happen to anyone if they are not thinking about it. it has happened to me, it will happen ultimately to anyone that

a) does not use bind variables
b) and then does not have their code reviewed by at least five people that do not like them.

The last point is important, you have to make sure the people reviewing your code will be very very very critical of it - that is best done by people that would like to see you made fun of :)