SQL injection?

[Harsh Jha]

Hi everyone.. i have started learnin sql from last week,

i came across a term "SQL injection"..
wondering what is it.

[ October 15, 2007: Message edited by: Harsh Jha ]
[ October 15, 2007: Message edited by: Bear Bibeault ]

[Bear Bibeault]

SQL injection is an attack where a user can exploit weaknesses in your code to "inject" SQL clauses into your statements, yeilding unintended results. The aim is usually to gain access.

Here's a classic example: suppose you accept a username and password and use the following to construct a Statement:

String query = "select * from USERS where username='"+username+"' and password='"+password+"'";    

Now the user enters "admin" for the username and "' or 'a'='a" for the password. What happens?

The user has just gained access to the admin account without knowing the password.

The first step to preventing SQL Injection is to use parameterized PreparedStatements rather than building up statements in strings as shown above.
[ October 15, 2007: Message edited by: Bear Bibeault ]

[Shital Patil]

SQL Inj is the term mostly used in Hacking/Crackin

[Harsh Jha]

@ Bear Bibeault
thanks.. but i still dont got how by using " or 1=1"
user can defy the password check.
Sorry...I m an newbee to sql!!

@ shital bhilawdi
thanks for the hint.

[Bear Bibeault]

Actually that was an error. I changed the example to be correct.

[Bear Bibeault]

If that's still not clear, the SQL query ends up being:

select * from USERS where username='admin' and password='' or 'a'='a'    

Since 'a'='a' evaluates as true, the evaluation of the or evaluates as true.

[Harsh Jha]

@ Bear Bibeault
thanks ..i wil try to get it better by reading and implementing.
God bless.

[akshay gehi]

is PreparedStatement the only thing required to fix SQL Injection. I am using PreparedStatements in my applications so does that gurantee no sql injections?

[Jan Cumps]

Originally posted by akshay gehi:
is PreparedStatement the only thing required to fix SQL Injection. I am using PreparedStatements in my applications so does that gurantee no sql injections?

Yes. Preparedstatements using only bind variables prevent this.
Regards, Jan

[Corey McGlone]

One thing I've always struggled with in terms of SQL Injection attacks is knowing when you're completely safe. A co-worker of mine and I were trying to break one of his applications using SQL Injection. His application doesn't do any client-side Java as it passes all variables to stored procedures for execution. Whatever we tried, we were unable to perform a successful SQL Injection attack.

Now my question is simply, is his application safe or were we simply not clever enough to find the correct solution to break it? Also, in general, are stored procedures generally more or less safe than other methods of accessing databases?

Thanks.

[Jeanne Boyarsky]

Originally posted by Corey McGlone:
One thing I've always struggled with in terms of SQL Injection attacks is knowing when you're completely safe. ... Now my question is simply, is his application safe or were we simply not clever enough to find the correct solution to break it?

The only way to be sure is to look at the code and make sure the prepared statement uses bind variables for all inputs. Even if you were unable to break in NOW, someone could change a validation in the future comprising the app if the prepared statement itself is not secure.

Also, in general, are stored procedures generally more or less safe than other methods of accessing databases?

A stored procedure and prepared statement are at the same level of security if used correctly.

[Harsh Jha]

How different is Blind SQL Injection from plain sql injection?
I have read a bit about it on net.. but can anyone give me example.

[ October 17, 2007: Message edited by: Harsh Jha ]
[ October 17, 2007: Message edited by: Harsh Jha ]

[Bear Bibeault]

Please use real words when posting to the forums. Abbreviations such as "abt" in place of "about" only serve to make your posts more difficult to read and less likely to generate useful responses.

Please read this for more information.

thanks,
bear
JavaRanch sheriff

[Paul Campbell]

This is the best explanation I'm aware of from the best source of DBMS information that I am aware of (with apologies to Ralph Kimball).

Tom Kyte (the chief Oracle Technologist):
This is such an important topic, and not as many people are aware of it as I thought. Before we start with an answer, let's define the term SQL injection. SQL injection occurs when an application program accepts arbitrary SQL from an untrusted source (think "end user"), blindly adds it to the application's SQL, and executes it. It would be similar to the operating system's taking a couple of lines of C code from a user, compiling it on the fly, and just executing it in the operating system kernel. You'd never stand for that�it would subvert security (who knows what code goes there), and it would stand a good chance of crashing something. And yet, many application developers do it every day in their applications. They accept arbitrary input from end users and just concatenate it right into their SQL statements, parse (compile) the SQL, and execute it.

I'm going to use an excerpt from my book Effective Oracle by Design to drive this point home. The excerpt is from a chapter that talks about bind variables (imagine that�I actually wrote about bind variables!) and their importance for scalability and performance. To give extra ammunition to the developers and DBAs who want to use bind variables, I provided this clear example of SQL injection being used to bypass authentication checks:

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.

SQL> create table user_table
2 ( username varchar2(30),
3 password varchar2(30) );
Table created.

SQL> insert into user_table
2 values ( 'tom',
3 'top_secret_password' );
1 row created.

SQL> commit;
Commit complete.

SQL> accept Uname -
> prompt "Enter username:"
Enter username: tom

SQL > accept Pword -
> prompt "Enter pass: "
Enter pass: i_dont_know' or
'x' = 'x

Note the password we just used. It incorporates a little SQL, 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:

SQL> 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:

SQL> variable pword varchar2(30);
SQL> exec :uname := 'tom';
SQL> exec :pword := -
> 'i_dont_know'' or ''x'' = ''x';

PL/SQL procedure successfully
completed.

SQL> 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:

SQL> accept Pword -
> prompt "Enter password: "

Enter password: hr_pkg.fire_emp( 1234)

Whoops, this person may have 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.

It is interesting to note that when I wrote the above excerpt in 2003, Google had about 16,000 pages indexed. Today, in 2004, there are close to 250,000! SQL injection is a growing problem.

This problem does not exist solely in Visual Basic Active Server Pages (ASP) applications or JavaServer Pages (JSP)�it is just as easy to fall into this trap with mod_plsql and PL/SQL or, in fact, any language. Think about the "innocent" stored function in Listing 1.

Code Listing 1: The "innocent" stored function

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 /

Procedure created.

SQL> create table t ( x int );

Table created.

SQL> desc t

Name Null? Type
--------- ----- ------------------
X NUMBER(38)

SQL> begin
2 remove_emp
3 ( 'scott',
4 'KING''; execute immediate ''drop table t''; --' );
5 end;
6 /
begin
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at line 4
ORA-06512: at "OPS$TKYTE.REMOVE_EMP", line 11
ORA-06512: at line 2


SQL> desc t
ERROR:
ORA-04043: object t does not exist

Note how even though the block of generic code�written to be schema-independent�in Listing 1 failed, it succeeded. It processed the first delete�DELETE FROM SCOTT.EMP WHERE ENAME = 'KING'�but it then successfully executed the drop table T command (which had the not-so-nice side effect of committing your delete prematurely as well). The next DELETE succeeded, but the second DROP TABLE failed. So, you've endangered transactional consistency here (because an end user providing you with an employee name has taken over transactional control), but you've also given the end user the ability to execute any SQL�or, in this case, PL/SQL�that person wants to, using the privileges of the owner of the procedure (who presumably has lots of access, because it is a "generic" routine able to delete from lots of stuff).

So, how do we do the following:

1. Detect people who have engaged in SQL injection in the past?
2. Detect people still doing so?
3. Detect code/applications susceptible to SQL injection (and determine how to fix them)?
4. Detect database vulnerabilities that will allow a malicious user to engage in SQL injection?

Well, No. 1 is a hard nut to crack; unless you had auditing enabled, know a pattern to look for, and have a particular event you are trying to explain, it will probably not be possible. For example, if a table mysteriously went "missing" and I had auditing enabled (or perhaps I was using logminer if the event was recent enough), I would know the account that was used and I would start looking for SQL injection possibilities.

For No. 2, you might well be able to use V$SQL to find SQL injections. What you are looking for is any set of queries in V$SQL that differ only in the literal SQL they contain. That is a sure indication that the application is just taking string inputs from somewhere and concatenating them into SQL. This is horrible for many reasons, but it would be the first place to look for potential SQL injection attacks.

Unfortunately, if you are using CURSOR_SHARING = FORCE/SIMILAR, this approach will not work so well, because all queries would have their literals removed. You would not simply be able to locate the duplicate queries in the shared pool; you would have to look at each statement and see what you see. But assuming you are using CURSOR_SHARING = EXACT, you can use the technique outlined on asktom.oracle.com/~tkyte/literals.html. It was originally designed to find "bad SQL" that wasn't being bound properly, but because that is what we need to find, it'll work just as well for us here. These unbound SQL statements are the ones that should be considered suspect: They are bad for your system's performance, but they may hold the keys to the kingdom as well.

What you need to do now is find out where the literals in these SQL statements come from. If you discover, "Oh, that comes from that text input field on that screen over there," you are definitely exposed. Anytime the application accepts SQL from an untrusted source (that would be almost anything outside of the application itself), just executing that SQL has a level of danger. Executing SQL from the middle tier (logged in as some superuser) or when passed to a definer rights routine (because a stored procedure executes by default with the privileges of the definer of the routine) is especially dangerous.

No. 3 can be accomplished only through coding standards, code reviews, and adherence to the standards. Any code that accepts a string from an untrusted source and executes it (does not bind the string value) is open to SQL injection. That statement says it all. If you have a middle-tier application that uses a common account (with lots of privileges), if you have a client/server application that uses a common account (with lots of privileges), or if you use dynamic SQL in PL/SQL definer rights routines, you are very much open to this bug�unless your developers used bind variables. You might even consider asking your third-party vendors about their applications. If their application accepts input from you and just puts it into SQL and executes it, you have to trust the application vendors and assume that everything will be OK, because you don't have access to the application code.

For No. 4, I recommend a fairly new book authored by David Knox, Effective Oracle Database 10g Security by Design (Oracle Press, 2004). It does not have a checklist of "run this script, and it'll tell you if you are OK" (there are such security checklists out there) but rather espouses the concept of "you need to design security into your system from Day 1," in much the same fashion as I espouse the concept of "you must design performance, scalability, and maintainability into your system from Day 1."

Normally, I do pretty much a cut-and-paste from the Ask Tom site into this column and edit it. To make the preceding question and answer more readable, however, I actually wrote this more or less from scratch, using the material from the thread on Ask Tom. I encourage you to read the original discussion at asktom.oracle.com/~tkyte/sqlinj.html for further proof that SQL injection is real, it exists, and using bind variables is the solution that solves it pretty much entirely.

Comparing the Contents of Two Tables

I have two tables named A and B. They have identical columns and have the same number of rows via select count(*) from A and from B. However, the content in one of the rows is different, as shown in the following query:

SQL> select * from A where C1=1;
C1 C2 C3
------ ------------ --------
1 AAAAAAAAAAAA 100

SQL> select * from B where C1=1;

C1 C2 C3
------ ------------ --------
1 AAAAAAAAAAAB 100

The only difference is the last character in column C2. It is an A in table A and a B in table B. I would like to write SQL to compare or see if tables A and B are in sync with respect to their content rather than the number of rows, but I don't know how to do it.

OK, we'll do the specific solution to this problem with columns C1, C2, and C3, and then we'll see how to generalize this to any number of columns. The first and immediate answer I came to was this:

(select 'A', a.* from a
MINUS
select 'A', b.* from b)
UNION ALL
(select 'B', b.* from b
MINUS
select 'B', a.* from a)

That is, just take A minus B (which gives us everything in A that's not in B) and add to that (UNION ALL) the result of B minus A. In fact, that is correct, but it has a couple of drawbacks:

* The query requires four full table scans.
* If a row is duplicated in A, then MINUS will "de-dup" it silently (and do the same with B).

So, this solution would be slow and also hide information from us. There is a better way, however, that uses just two full scans and GROUP BY. Consider these values in A and B:

SQL> select * from a;

C1 C2 C3
---------- -- --
1 x y
2 xx y
3 x y

SQL> select * from b;
C1 C2 C3
---------- -- --
1 x y
2 x y
3 x yy

The first rows are the same, but the second and third rows differ. This is how we can find them:

SQL> select c1, c2, c3,
2 count(src1) CNT1,
3 count(src2) CNT2
4 from
5 ( select a.*,
6 1 src1,
7 to_number(null) src2
8 from a
9 union all
10 select b.*,
11 to_number(null) src1,
12 2 src2
13 from b
14 )
15 group by c1,c2,c3
16 having count(src1) <> count(src2)
17 /

C1 C2 C3 CNT1 CNT2
--- -- -- ---- ----
2 x y 0 1
2 xx y 1 0
3 x y 1 0
3 x yy 0 1


Now, because COUNT(<expression>) returns a count of the non-null values of <expression>�we expect that after grouping by all of the columns in the table�we would have two equal counts (because COUNT(src1) counts the number of records in table A that have those values and COUNT(src2) does the same for table BCNT1 and CNT2, that would have told us that table A has this row twice but table B has it three times (which is something the MINUS and UNION ALL operators above would not be able to do).

To give credit where credit is due, you'll want to read the original Ask Tom discussion that got us to this answer: asktom.oracle.com/~tkyte/compare.html. What I found interesting in that thread was the back and forth we had to go through in order to come to the final query. Ultimately, a combination of Marco Stefanetti's technique with a minor addition I made led to this query, but you'll see the genesis of a pretty good idea there.

[ October 17, 2007: Message edited by: Paul Campbell ]

[Paul Campbell]

Originally posted by Jeanne Boyarsky:

A stored procedure and prepared statement are at the same level of security if used correctly.

Taking that concept a bit further... a stored procedure has the ability to run as definer and can be utilized as a security mechanism to prevent SQL injection.

Ideally for a secure application environment you would have three schemas following the philosophy of "least privilege".

The first schema owns application data and grants only the bare minimum of needed privileges to the second schema. The second schema uses those privileges to create stored code that act as "methods" on the data objects, as well as any views that might be desired. This second schema grants EXECUTE privileges on the stored code to the third schema. The third schema is for user sessions; this schema can only access the data by going through the approved path provided by the stored code and views of the second schema.

[Harsh Jha]

nice take paul..
as a beginner i really got a lot of hint about sql injection.
the starting para of your post is good enough.
thanks.