Favorite Quote
A reader, January 12, 2005 - 7:47 pm UTC
I was reading the link in the original question. I found this line quite amusing:
"There also may be some performance benefits if this prepared query is reused multiple times (it only has to be parsed once), but this is minor compared to the enormous security benefits"
Although completely true (that one statement isn't likely to bog up the shared pool), it makes it seem like the only real benefit to prepared statements is their security.
Logs
Martin Guillen, January 12, 2005 - 7:52 pm UTC
Tom: I think that you may have logs of what people search in the home page of your site. Are some people trying to do sql injection? I know they can't.
January 12, 2005 - 8:26 pm UTC
I've never looked (i bind, all the time)
funny thing is -- I'm expecting people to put "sql/plsql" stuff in there. they could do sql injection purely *by accident* if I didn't bind!!!
If I get some time, I'll take a peek.
fine grained access control
max, January 14, 2005 - 7:29 am UTC
hi, Tom.
how do you think, can (or even should) we user FGAC to avoid SQL injections in php/perl/isapi applications? I mean all that query rewrite stuff with additional where claues etc.
or just 'bind variable' (kinda magic spell :-) and forget FGAC?
January 14, 2005 - 8:16 am UTC
I don't see the connection between FGAC (security feature) and the prevention of SQL injection.
I can suffer from sql injection just as easily with FGAC as without.
as for the very first link: is the information valid, does it apply?
A reader, January 14, 2005 - 10:34 am UTC
apart from running SCRIPTS i've never been able to successfully execute "statements" such as:
SELECT email, passwd, login_id, full_name
FROM members
WHERE email = 'x'; DROP TABLE members;
SELECT email, passwd, login_id, full_name
FROM members
WHERE email = 'x';
INSERT INTO members ('email','passwd','login_id','full_name')
VALUES ('steve@unixwiz.net','hello','steve','Steve Friedl');--';
SELECT email, passwd, login_id, full_name
FROM members
WHERE email = 'x';
UPDATE members
SET email = 'steve@unixwiz.net'
WHERE email = 'bob@example.com';
"stringing our own unrelated command at the end of the query" would introduce a SECOND statement instead.
January 14, 2005 - 7:54 pm UTC
read the second link (my article), i demo it.
Just because you haven't stumbled upon it doesn't mean it cannot happen.
In Oracle -- think "sql injection in PLSQL blocks"
A reader, January 15, 2005 - 3:45 pm UTC
yes, your article made it very clear to me. thanks.
the only thing i didn't get there was the following sample you gave:
<quote>
SQL> accept Pword -
> prompt "Enter password: "
Enter password: hr_pkg.fire_emp( 1234)
</quote>
i didn't get stored code executed in such a way :o(
(what did i miss here?)
January 15, 2005 - 4:10 pm UTC
if you had a hr_pkg, with a function fire_emp, you would have.
it can occur even when you use binds
Menon, January 15, 2005 - 4:51 pm UTC
Well, in somewhat contrived scenarios...
e.g.
------------------
benchmark@ORA10G> set echo on
benchmark@ORA10G> drop table t1;
Table dropped.
benchmark@ORA10G> drop table t2;
Table dropped.
benchmark@ORA10G> create table t1 as
2 select rownum as x from all_objects where rownum <= 10;
Table created.
benchmark@ORA10G> create table t2 as select * from t1;
Table created.
benchmark@ORA10G> select count(*) from t1;
10
benchmark@ORA10G> select count(*) from t2;
10
-
Now the procedure below takes a table name and
deletes all rows from it. The table name can not
be bound so we just concatenate it ( if
you have a where clause then this example wont
work)
benchmark@ORA10G> create or replace procedure delete_from_table( p_table_name in varchar2 )
2 is
3 l_sql_str long := 'begin delete from ' || p_table_name ||'end;';
4 begin
5 dbms_output.put_line( l_sql_str );
6 execute immediate l_sql_str;
7 end;
8 /
Procedure created.
benchmark@ORA10G> show errors;
No errors.
Now using the SQL input, we make it delete from t2 as
well - of course you can insert whatever you want
instead of the code to delete from t2...
benchmark@ORA10G> exec delete_from_table( 't1; begin delete from t2; end;');
begin delete from t1; begin delete from t2; end;end;
PL/SQL procedure successfully completed.
benchmark@ORA10G> select count(*) from t1;
0
benchmark@ORA10G> select count(*) from t2;
0
benchmark@ORA10G> spool off
-------
So bottom line is when you construct SQL, use binds (of course) and be careful of what comes in...
did you use SQL*PLUS?
A reader, January 15, 2005 - 5:11 pm UTC
well, i 've typed in a procedure's name when i was prompted for a variable' s value and nothing happened at all.
January 15, 2005 - 5:57 pm UTC
*function*
but here 's what i've observed (with SQL*PLUS on 9.2.0.6.0)
A reader, January 17, 2005 - 11:27 am UTC
... using a function ...
SQL>drop table Test ;
Tabelle wurde gelöscht.
SQL>create table Test( Num number ) ;
Tabelle wurde angelegt.
SQL>
SQL>insert into Test( Num ) values( 1 ) ;
1 Zeile wurde erstellt.
SQL>commit ;
Transaktion mit COMMIT abgeschlossen.
SQL>
SQL>create or replace package MyPck
2 is
3 function DoIt( p_nPrm number )
4 return number ;
5 end MyPck ;
6 /
Paket wurde erstellt.
SQL>
SQL>create or replace package body MyPck
2 is
3 function DoIt( p_nPrm number )
4 return number
5 is
6 begin
7 delete Test ;
8 commit ;
9 end DoIt ;
10 end MyPck ;
11 /
Paketrumpf wurde erstellt.
SQL>
SQL>accept MyVar prompt "Please don't type in a function's call here': "
Please don't type in a function's call here': MyPck.DoIt(123)
SQL>
SQL>select * from Test ;
NUM
----------
1
am i just to blind to see it?
January 17, 2005 - 5:13 pm UTC
where are you concatenating &myvar into the sql text anywhere?!?
to be honest ...
A reader, January 18, 2005 - 2:50 am UTC
i don't even know HOW TO :o(
(i really didn't get that point)
January 18, 2005 - 9:55 am UTC
that is the point of sql injection.
sql injection is when
a) you accept input from an END USER
b) you glue that input into a SQL statement (which may be considered "code", it is code)
c) you execute that SQL statement
meaning, the end user just gave you code to run at will.
ops$tkyte@ORA9IR2> create table t as select * from all_users where rownum < 5;
Table created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace function do_something return number
2 as
3 pragma autonomous_transaction;
4 begin
5 delete from t;
6 commit;
7 return 0;
8 end;
9 /
Function created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> accept input
do_something
ops$tkyte@ORA9IR2> select * from t where user_id = &input;
old 1: select * from t where user_id = &input
new 1: select * from t where user_id = do_something
USERNAME USER_ID CREATED
------------------------------ ---------- ---------
SYS 0 02-SEP-04
ops$tkyte@ORA9IR2> select * from t;
no rows selected
Another reason...
Bob B, January 18, 2005 - 10:36 am UTC
Just another reason why Autonomous Transactions are evil.
January 18, 2005 - 11:39 am UTC
yes, I agree -- but I've seen this used in many places because people want to "select f(x) from dual" instead of "begin f(x); end;" -- typically in environments where the "tool" they are using can only do "sql" because it is "database independent"
SQL Injection and Dynamic SQL
Yuan, January 31, 2005 - 9:51 am UTC
I learned a lot from reading your article on Injection in Oracle magazine. I'd like some clarification though. Isn't this in essence an argument against using much of Dynamic SQL? For example, in the same issue, your reply to "Selective System Grants" uses Dynamic SQL without binding.
special injection considerations for ctxsys?
Peter Köhler, January 31, 2005 - 11:19 am UTC
Hi Tom,
are there any special considerations for domain indices where You can bind text queries into contains queries like:
select * from t_contact c
where contains(c.fulltext, &query)>0;
and You set &query = 'abc% near defg'.
I dont´t oversee if there may be special security problems.
Maybe one can set &query to '\'select user from dual\'' or something similar.
Greetings and thanks in advance
Peter
January 31, 2005 - 11:41 am UTC
yes, think of a &query string in the form:
'x' ) or 1=1
or....
'x' ) or 1=1 union all select ..... from another_table_you_arent_supposed_to
Dynamic SQL in Article
Yuan, January 31, 2005 - 12:34 pm UTC
Just read the online "patch." Couldn't you have bound that variable?
execute immediate 'alter system set user_dump_dest = :1 scope=memory' using p_udump;
January 31, 2005 - 12:42 pm UTC
you cannot bind in DDL.
ops$tkyte@ORA9IR2> variable x varchar2(30);
ops$tkyte@ORA9IR2> exec :x := '/tmp';
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> alter system set user_dump_dest = :x scope=memory;
alter system set user_dump_dest = :x scope=memory
*
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM
Thanks!
Yuan, January 31, 2005 - 1:54 pm UTC
Did not know that. Thanks for yet another piece of info.
re: special injection considerations for ctxsys?
Peter Köhler, February 01, 2005 - 3:42 am UTC
Hi Tom,
what i meant with the example
>>select * from t_contact c
>>where contains(c.fulltext, &query)>0;
>>and You set &query = 'abc% near defg'.
was really binding the query in jdbc
with a prepared statement.
Can this be dangerous?
Greetings
Peter Köhler
February 01, 2005 - 8:46 am UTC
if you BIND
then
it is safe
else if you don't BIND
then
it is not safe
END IF
if you were "binding"
<quote>
select * from t_contact c
where contains(c.fulltext, &query)>0;
and You set &query = 'abc% near defg'.
</quote>
that was a bad "example", that appears to be a demo using SQLPlus's substitution -- NOT binds.
A reader, June 12, 2005 - 12:31 pm UTC
Assume the application is NOT binding BUT the application does not allow for the ' character to be entered. And all numeric columns only allow INTs for value inputs. How can one do SQL injection? This is the way an App I am reviewing works. I would love to show them there is a way around this "so called" injection fix and make them use Binding. A security hole would force this issue.
June 12, 2005 - 1:57 pm UTC
if it only allows ints and they verify that, it probably isn't subject to sql injection.
It just sucks the performance out of your database, consumes many times (5, 10, 15 times) the amount of cpu a real application would, uses more memory than you ever needed to buy and so on.
So, in short, it might not, might not suffer from sql injection but it is the worst thing to do still.
funny thing is, they probably spent 10x the effort proving this to you (that they aren't sql injection afraid) and probably rewrote code to make it so -- then it would have taken to simply bind and fix the issue once and for all.
But, we'd have to see their validation code to bless this implementation fully.
A reader, June 12, 2005 - 7:18 pm UTC
Thanks for your help.
Another waste of time and resource...
Franco, August 16, 2005 - 7:02 am UTC
SQL injection
sam, July 21, 2006 - 6:34 pm UTC
Tom:
my authentiation query is:
SELECT COUNT(*) FROM user_table
WHERE user_id = i_user_id and active_status = 'Y'
AND password = hash(i_password);
WHen I input
user= tom
password=i' or 'x'='x
it does not let me in. Since I am not using bind variables why it is not working. Is the hash function securing the system here?
2. Should i be doing this to use bing variables:
SELECT COUNT(*) FROM user_table
WHERE user_id = :user and active_status = 'Y'
AND password = hash(:password) using i_user_id,i_password;
July 23, 2006 - 8:16 am UTC
sure looks like bind variables?????
I'm assuming
a) plsql
b) i_user_id is a plsql variable
plsql variables are "bind variables" in sql, this is one of the thousands of reasons why plsql rules.
sql injection
sam, July 21, 2006 - 6:41 pm UTC
Tom:
On your example in the oracle magazine issue, you dont show how the bind variables actually protect against sql injection.
Can you show us how the actual SQL statement is being executed with and without bind vairables.
select count(*) from user_table where username='&uname' and password='&Pword';
select count(*) from user_table where username=:uname and
password = :pword;
July 23, 2006 - 8:18 am UTC
sure the article does show you.
when you use &uname - the value you type in becomes PART OF THE SQL STATMENT. so, if uname is:
x' or 'a' = 'a
the predicate physically becomes the ext:
where username = '&uname'
where username = 'x' or 'a' = 'a'
^^^^^^^^^^^^^^ <<<=== &uname
but when the bind is used:
where username = :uname
the string "x' or 'a' = 'a" is sent to the server, you cannot CHANGE the predicate, you can only supply an input to be compared to username, you cannot add an "OR" condition for example, you cannot CHANGE the syntax of the query at all.
sql injection
sam, July 23, 2006 - 7:35 pm UTC
Tom:
AS a followup to my previous message "i_userid" and "i_password" are not plsql variables. THey are input paramters to the plsql procedures. Does that make it not bound?
SELECT COUNT(*) FROM user_table
WHERE user_id = i_user_id and active_status = 'Y'
AND password = hash(i_password);
2. I am little confused on how you determine whether the quey uses bind variables or not? Is this a true statement.
Always use plsql varaibles in queries instead of the input parameters. FOr example like this:
procedure test (
i_userid varchar2 default null,
i_password varchar2 default null)
begin
v_userid := i_userid;
v_password := i_password;
--Is this how you do it to protect against SQL injection
select count(*) from user_table where userid=v_userid and v_password := v_password;
end;
3. How do you use the :var in the above query instead of plsql variable.
July 24, 2006 - 9:46 am UTC
....
"i_userid" and "i_password" are not plsql
variables. THey are input paramters to the plsql procedures.
.....
if they aren't plsql variables - what are they then??? Indeed - they ARE plsql variables. parameters are "variables".
this is well bound. anytime you reference a plsql variable in a sql statement - you are binding.
anytime you use variables in sql (not LITERALS), you are binding. parameters are just PLSQL VARIABLES!!
You would not use :var in the above instead of plsql variables (implicit binds). In order to use :var in PLSQL, you must resort to dynamic sql and we would NEVER do that if we can use static sql. And you can so :var would be totally wrong.
sql injection
sam, July 24, 2006 - 11:14 am UTC
Tom:
I am confused. ALL mod_plsql application pages are based on this format.
PROCEDURE do_something (
p_user_id IN VARCHAR2 DEFAULT NULL,
p_xxx_xxx IN VARCHAR2 DEFAULT NULL)
begin
end;
Based on what you just said (imput parameters are plsql variables) then everything is bound. The only way you can pass a user data is through those parameters and what you are saying there is no way a user can type something to do SQL injection using
select * from table where col1 = p_data;
When we tried running a watchfire web app testing software we got hundreds of sql injection vulnerabilities. Do you think the software may be buggy or doing inacurate checks.
2. How do you sanitize user input in oracle to prevent sql injection. I mean before reaching the query?
July 24, 2006 - 12:11 pm UTC
create procedure I_am_sql_injected( p_cname in varchar2,
p_value in varchar2 )
as
....
begin
l_query := 'select * from t where ' || p_cname || ' = ' ||
p_value;
open ref_cursor for l_query;
that is SQL Injection - user supplies columns to apply predicate to, and the values and you just blindly build a query based on their inputs.
I don't know how watchfire qualifies a sql injection - I don't know how they "find them" so I cannot comment as to whether you have a problem or not.
2) bind it, don't concatenate - that is the simple answer. See:
</code>
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1288401763279 <code>
for an example of how you might do the above without being subject to sql injection.
sql injection
sam, July 24, 2006 - 3:59 pm UTC
Tom:
Are you saying that as long as all "p_parameters" are values and not "column names" they are plsql variables. However if one of these is a "column" name then it qualifies as a sql injection.
in other words if the parameters are only used on the right side of the query where clause then we are OK.
p_value in your example is a name of the HTML form element and getting submitted like all html form elements.
July 24, 2006 - 4:21 pm UTC
no, did not say that at all.
IF you use dynamic sql
AND
you use string concatenation
THEN
you are subject to sql injection
END IF
that is what I am saying.
you cannot make any assumptions about what p_value is - I can type IN ANYTHING I WANT on the browser url field.... ANYTHING I WANT.
No verification will take place on the browser. It can be anything the user wants to type in.
sql injection
sam, July 24, 2006 - 5:30 pm UTC
Tom:
1. I hardly use dynamic sql but i use IMPLICIT cursros with parameter concatentaion such as
for x in (select * from table where x = p_xvalue and y = p_yvalue)
Loop
OR
select * from table where col1 = p_value;
I ASSUME these are are SQL injection proof. Correct?
2. Based on what you said is that if i have a form that asks for P_userid, p_password and I submit this page to another page that has this query:
select count(*) from user_table where userid=p_userid and password=p_password;
No one can ever try to log in using SQL injection. So even if user passed a password of "x' or '1'='1'" IT WILL NEVER run it as
select count(*) from user_Table where userid='tom' and
password='x' or '1'='1';
Is this correct?
July 24, 2006 - 6:38 pm UTC
1) using static sql in plsql - cannot think of a possible case whereby sql injection would even be fathomable. Just cannot happen. You have to use dynamic sql.
2) correct, that query you show BINDS p_userid and p_password. You cannot change the syntax of the sql statement at all.
Helena Marková, July 25, 2006 - 9:22 am UTC
sql injection
sam, July 25, 2006 - 10:47 am UTC
Tom:
Thanks for the clarification.
I wish you can show your two small sql*plus examples in pl/sql procedure to see how this in real life happens. You will always have two fields on authentication form and you will always submit form data to a query select count(*) from user_table where user_id=p_userid and and password = p_password;.
I just do not see any other way of doing it that allows SQL injection on an authentication form unless someone does it this way.
V_query=select count(*) from user_table where user_id=||p_userid||and password=||p_password;
Execute v_query;
But this is a stupid way of doing it. I dont know why someone would do it this way.
2. The article and everyones solution to SQL Injection seems to be:
a. Filter out characters like single quotes, double quotes, slash, back slash, semi colon, extended character like NULL ,carry return, new line in all strings from inout from users, parameters from URLs and values from cookies.
b. For numeric value, convert it to integer or make sure it is an integer.
My question is whether do we need to do those in ORACLE plsql apps if we use bind variables or we do not?
3. The watchfire tool I was telling you about reads the HTML code generated after sending some dummy date. However, I cant see how can you check for SQL injection vulnerability by looking at HTML unless they see if http server generates an error. My understanding is that you have to look at the plsql code to check vulnerabilites.
July 25, 2006 - 11:57 am UTC
...
But this is a stupid way of doing it. I dont know why someone would do it this
way.
......
because people DO. and not everyone uses plsql - if this was java or VB, they would be using dynamic sql since that is pretty much what those languages use. As I said - if you use static SQL in plsql - sql injection is pretty hard to come by. PLSQL rocks.
2) my approach is to BIND BIND BIND BIND. If you BIND, you don't need to "filter". so so so much easier. So much easier. In ANY language. So much more correct too.
In PLSQL, using static sql, you won't find sql injection being an issue. It is when you use || (concatenation) to build a string and the string contains user inputs that you have to worry.
3) you are correct, if that is what they do, you cannot tell anything. It would be a tool that gives a false sense of security as it would miss the problem entirely.
sql injection
sam, July 25, 2006 - 3:19 pm UTC
Tom:
1. Is it correct to say that in VB, C, java, ASP etc you ALWAYS have to build the sql query as a string (because they send the query as a string to the database). They cant use static SQL like plsql and that is why they can do sql injections with those.
while plsql allows you to do static sql in most queries which prevents sql injection.
2. The only place where it can happen with oracle is when you use dynamic sql? and you handle that using BINDING
July 25, 2006 - 4:04 pm UTC
1) pretty much - there is "sqlj" for java, a precompiler like Pro*C, that gives static sql - but not many people use it.
</code>
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:439619916584 <code>
has an example of static sql in java
2) the time it happens is when you use dynamic sql, correct.
If you bind, the end user CANNOT change the meaning, the intent of your query.
(no: cursor sharing force/similar is not good enough, that binds AFTER the user rewrites your logic! it happens too late)
sql injection
sam, July 26, 2006 - 11:24 am UTC
Tom:
Doe SQL injection also apply to "INSERT" and "UPDATE" statements or it is only applicable to "SELECT"? Would you also bind those.
July 26, 2006 - 11:58 am UTC
it applies to ANYTHING YOU PARSE. Everything you parse.
When you parse, yo uare compiling a program.
If you allow the end user to write the program, you are subject to sql injection.
dynamnically executed ANYTHING is subject to sql injection if you use string concatenation.
web security
sam, November 02, 2006 - 3:58 pm UTC
Tom:
1. Is there any other things for web application security you do when you write applications in addition to SQL injection, encrypting passwords, SSL etc.
2. I keep seeing that I should do data validation at the server i.e making sure that a user enters numbers only for a numeric field. Does Oracle have a package speicifc for that kind of validation?
3. Also a popular requirement is to sanitize user input from special characters and HTML tags. How do you do that and is that for XSS crss scripting attacks.
Thank you,
November 02, 2006 - 7:36 pm UTC
1) tons of stuff, identification, authorization, design the schema to be securable, everything is affected from day 1.
2) we call it insert?
3) your needs are your needs, I'm not aware of anything in particular.
sql injection
sam, November 02, 2006 - 11:25 pm UTC
Tom:
1. Do you have a good reference, article, book on how to implement security in coding web applications using mod_plsql?
2. I was told to do data input validation at the server before user hits the database (using insert). Do you agree?
3. Do you use any specific tool for checking vulnerabilities in your web apps? Would you recommend any.
thanks,
November 03, 2006 - 9:19 am UTC
1) the david knox book is good (see links I like)
it is not about mod_plsql, it is about security - period.
2) you have to do it in both places, you cannot JUST do it in the middle tier, the database needs to do the validation, it is the source of truth.
3) i do not have any personal recommendations, no
web security
sam, November 03, 2006 - 5:47 pm UTC
Tom:
When you code your web applications:
1. Don't you use javascript for form validation? For security does not this means that you have to do all validation on the server so why need javascript unless you want to do it both places?
2. Do you read the data the user enters and do checks and balances on it, like checking for HTML tags (XSS check) and checking the length of text against field size, and checking data type (numeric) etc.?
November 04, 2006 - 12:23 pm UTC
1) i use apex.
you do validation in the client application in order to make the application more user friendly (think about it, do you want to submit a form AND THEN be told "hey, you missed this field")
you do validation in the client application to increase the probability the transaction succeeds the first time around (hence decreasing the workload on the server itself)
I do not know why the word "security" was used in this context, not really relevant to this discussion.
2) when appropriate, you do the right thing. If the text should NOT have html in it, I encode the text (using htf.escape_sc - turns < into < and & into & and so on. So that when it is displayed, it looks like TEXT and is NOT interpreted as html.
For example, everything you type into this site has that happen, I don't REJECT the text, I make it "safe"
web
sam, November 03, 2006 - 5:56 pm UTC
Tom:
David Knox books is for 10g and we are using 9i would it work?
It also seems to be targeting the database instead of the HTML web application. Would it cover things like SQL injection, XSS crosscripting, buffer overflows, etc.
November 04, 2006 - 12:24 pm UTC
90% of what is covered in there exists in 9i as well.
it covers DATABASE APPLICATIONS
but no, it won't do cross site stuff and buffer overflows are just BUGS - period, you really don't need a book on buffer overflows do you??
Saw this and thought of you
Scott Mattes, November 22, 2006 - 4:44 pm UTC
November 24, 2006 - 6:01 pm UTC
indeed, anytime you
a) take input from outside
b) use string concatenation to build a bigger string from it
c) "compile" and "execute" as code the string
are you "injectable", you just gave your end users access to a compiler, be it an xpath compiler, sql compiler, whatever.
Quick Question
Chris Seymour, March 26, 2007 - 10:49 am UTC
Hey Tom - I have a question about one of the code examples in the article you reference several times. You show a way a stored procedure could be injected, but dont expand on how you would handle it.
SQL> create or replace
2 procedure remove_emp( p_schema in varchar2,
3 p_ename in varchar2 )
4 is
5 l_str long;
6 begin
7 l_str := '
8 begin
9 delete from ' || p_schema ||
10 '.emp where ename = ''' || p_ename || ''';
11 delete from ' || p_schema ||
12 '.bonus where ename = ''' || p_ename || ''';
13 end;';
14 execute immediate l_str;
15 end;
16 /
I have a strong feeling your answer going to be as simple as "Dont do this dynamically because you can do it statically" Lets just say for the sake of argument you wanted to do something similar to this (dynamically), but safely so it couldnt be injected. Could you give an example of a stored procedure that uses bind variables to safely accomplish the above dynamically?
March 26, 2007 - 11:13 am UTC
you cannot bind schema's - hence you would need to write code to verify that what you were sent was in fact a schema prior to concatenating it.
you would just bind the P_ENAME however
where ename = :x ....' using p_name, ...
One More Quick Followup
Chris Seymour, March 26, 2007 - 6:12 pm UTC
One followup to your response - I do follow what you're saying, but have one quick question. Assuming this is a stored procedure, the :x bind variables wouldnt compile correctly unless you were using Forms or sql*plus...right?(I would expect a PLS-00049: bad bind variable message). Sometimes I get confused by your examples because they all use the :x bind variable notation which I dont believe fly's in stored procedures.
So if you were not using sql*plus/Forms, but rather another tool to compile, would you do something like this?
CREATE OR REPLACE PROCEDURE remove_emp(p_schema IN VARCHAR2,
p_ename IN VARCHAR2) IS
l_str LONG;
BEGIN
--some code here to check to make sure p_schema exists
--it exists so run the code
l_str := 'begin
delete from ' || p_schema || '.emp where ename = ''' || p_ename || ''';
delete from ' || p_schema || '.bonus where ename = ''' ||p_ename || ''';
end;';
EXECUTE IMMEDIATE l_str USING p_schema,p_ename;
END;
I appreciate your time. I guess my question is now switching from the SQL Injection to making sure everything is bound well.
March 27, 2007 - 9:23 am UTC
begin
execute immediate 'insert into t values (:x)' using 'hello world';
end;
is perfectly valid.
:x is used in dynamic sql - execute immediate, open using, dbms_sql...
status of reference cursors
Herb Harrell, April 13, 2007 - 12:20 pm UTC
Follow-up question: Is the use of a reference cursor an instance of dynamic SQL and therefore just as susceptible to SQL injection as an EXECUTE IMMEDIATE?
PL/SQL example:
TYPE PersCurTyp IS REF CURSOR;
co_cfv PersCurTyp;
OPEN co_cfv FOR sql_stm; -- sql_stm a varchar2
FETCH co_cfv BULK COLLECT INTO lastnames,emails;
rowct := co_cfv%ROWCOUNT;
CLOSE co_cfv;
When the code in the example is executed, does the Oracle runtime system check whether sql_stm is a proper select statement before passing it to the SQL engine for execution?
It seems to me the runtime system could easily perform that check by executing the equivalent of:
uniquename varchar2 := REPLACE(TO_CHAR(newtime,'HH24:MI:SS'),':','') || userid_in;
EXECUTE IMMEDIATE 'CREATE OR REPLACE PACKAGE ' || uniquename ||' IS
CURSOR testcursor IS ' || sql_stm || '; END;';
EXECUTE IMMEDIATE 'DROP PACKAGE ' || uniquename;
COMMIT;
I guess I could find the answer experimentally, but I would appreciate expert guidance.
If it turns out that the runtime system does not perform such a check, then the check can obviously - thanks to dynamic SQL - be built into any PL/SQL procedure where it is needed.
This simple precaution would ensure that any maliciously injected SQL will be detected, because the attempt to create the throw-away PL/SQL package dynamically will certainly cause a server error and terminate processing; the reference cursor will not be opened and the malicious SQL will not be executed.
Building SELECT statements dynamically allows for much more flexible queries than variable binding. If I am right, then the precaution suggested here would preserve that flexibility.
Please let me know if I'm missing something.
April 13, 2007 - 7:07 pm UTC
a dynamically opened refcursor is absolutely subject to sql injection - since you are building a sql statement in a strnig.
whether it is a proper sql statement or not is not relevant. You can sql inject regular SQL to gain access to data you should not, tables you should not, execute functions you should not and so on.
I fail to see how you would detect "maliciously injected SQL" - a SELECT is just as subject to sql injection as anything else.
Is anyone listening?
A reader, April 28, 2008 - 3:00 pm UTC
April 29, 2008 - 8:09 am UTC
I think they are just beginning to listen now...
When I do a seminar, I always poll the room during my "All About Binds" talk. The question I ask:
"How many of you, not that I'll make you do this, feel they could get up at a whiteboard and describe to their peers what sql injection is - how it happens - what it means?"
I consistently get between 0 and 5% of hands raised, very disheartening.
sql inject
A reader, May 09, 2008 - 12:15 pm UTC
Tom:
we have a few oracle web apps that use mod_plsql. someone is recommending to strip all user input from html tags to prevent possibility of javascript or active x injection too.
1. I explained to him that bind variable prevent any possibility of sql injection. what do you say the above statement?
2. can this really happen?
3. how do you prevent it in oracle?
4. do you have any function that does the stripping for input parameters or some regular expression function?
May 12, 2008 - 12:03 pm UTC
1) I say "you are talking sql injection", they are not.
They are saying "if you accept input from an end user, and that input is later used ON A PAGE without any encoding - then the end user can change our HTML"
Meaning, if you have:
<input type=text value="user input">
and the user inputs:
hello"> You smell funny <input type=text value="
Your html would be:
<input type=text value="hello"> You smell funny <input type=text value="">
Now, you may notice on this site, I let you post whatever you want - html, javascript, whatever.
But only because on the way OUT of the database - I use htf.escape_sc
ops$tkyte%ORA10GR2> select htf.escape_sc( 'hello"> You smell funny <input type=text value="' ) from dual;
HTF.ESCAPE_SC('HELLO">YOUSMELLFUNNY<INPUTTYPE=TEXTVALUE="')
-------------------------------------------------------------------------------
hello"> You smell funny <input type=text value="
I store whatever you type in in the table.
I produce "safe" html by escaping special characters on the WAY OUT.
that way I do not lose your text, I do not change your text, but you cannot change my page.
Your "someone" is recommending the wrong approach, store what the user gives you - just produce a safe HTML page.
2) I just demonstrated how, sure.
3) It is not an Oracle issue, it is general "HTML 101 coding" - period, escape the html entities before putting them on the page.
4) nope, because it would be wrong...
SQLJ and SQL Injection
developer, October 17, 2008 - 1:28 pm UTC
Hi Tom,
On July 25, 2006 - 4pm US/Eastern you wrote:
1) pretty much - there is "sqlj" for java, a precompiler like Pro*C, that gives static sql - but not many people use it.
Does SQLJ effectively mitigate against SQL Injection? If not, any pointers (web resources) on how to prevent SQL injection when using SQLJ?
Thanks much
October 18, 2008 - 9:46 pm UTC
if you use static sql (sqlj does that), it is not possible to sql inject.
with static sql, the sql is fixed - cannot be changed - is IMMUTABLE - as of compile time.
for sql injection to happen - the sql must be dynamically generated at runtime - that allows the end user of your routine to perhaps influence what the generated sql looks like.
If you use static sql - whereby the SQL is fixed at runtime (via sqlj of by using plsql), you are not sql injectable.
if you use straight jdbc yourself, whereby you build the sql at runtime, you are subject to sql injection anytime you do not use a bind variable to bind in inputs. Anytime you do not, Everytime you do not.
SQLJ and SQL Injection
developer, October 28, 2008 - 1:03 pm UTC
Tom, thanks for the clarification. We are exploring SQLJ. Surprisingly, there aren't many resources on it out there.
In any case, thanks again for the valuable feedback.
Regards
sql injection using case
A reader, August 18, 2010 - 1:22 pm UTC
August 19, 2010 - 1:55 am UTC
if people just used bind variables - you wouldn't need to sanitize inputs at all :(
it could be so simple
A security study
A reader, August 19, 2010 - 9:37 am UTC
"More than half of the malware infections came from direct installation (injection) by the attacker, and SQL databases led the list of subverted systems. SQL injections frequently rely on well-known quirks in SQL systems; craftily assembled SQL database queries, for example, can install programs that pluck data and send it to the requester."
http://windowssecrets.com/2010/08/19/02-New-analysis-of-stolen-data-brings-surprises