PL/SQL Injection – The Doctor Will See You Now

Following on from my recent post about SQL Injection I had an excellent comment from Gary.

Whilst we’ve been able to establish that the PL/SQL solution we implemented does not suffer the same vulnerability to injection as the concatenated SQL statement, this does lead us to a further question – does using PL/SQL automatically render us immune from injection attacks ?

For the purposes of this post, I’m going to leave PHP to one-side and concentrate on the PL/SQL side of the matter.

The Application

Just to recap, Application Users’ details are held in a table and we have a function to verify a user’s logon credentials. This is all very basic and intended only as an example. Holding password data in a table in clear text is really not a great idea.

Anyway, the table looks like this:

CREATE TABLE app_users(
    user_name VARCHAR2(30),
    password VARCHAR2(30))
/

INSERT INTO app_users( user_name, password)
VALUES( 'MIKE', 'SENSIBLE')
/

The user verification function returns 1 if the user is valid or 0 if not.
Just suppose at this point that we assume that the use of PL/SQL, by it's very nature, will mean that we can't be injected. We could then choose to build the statement dynamically by simply concatenating the user input into the statement. This is the same technique we used originally in the previous post, just moved down a couple of application layers :

CREATE OR REPLACE FUNCTION validate_app_user_fn( 
    i_uname IN app_users.user_name%TYPE,
    i_pwd IN app_users.password%TYPE)
    RETURN NUMBER IS
    l_query VARCHAR2(4000);
    l_count PLS_INTEGER;
BEGIN
    l_query := 
        'SELECT COUNT(*) FROM app_users'
        ||' WHERE user_name = '||CHR(39)||i_uname||CHR(39) 
        ||' AND password = '||CHR(39)||i_pwd||CHR(39);
   EXECUTE IMMEDIATE l_query INTO l_count;
    RETURN l_count;
END;
/

OK, I think it's fair to say that anyone whose used PL/SQL for any length of time would not code the function in this way. However, a PHP developer who is finding their way as they go, may well see this as a reasonable approach.
In any case, it's a valid PL/SQL function and it works :

SQL> SELECT validate_app_user_fn('MIKE', 'SENSIBLE') as valid from dual;

     VALID
----------
	 1

SQL> SELECT validate_app_user_fn('MIKE', 'SILLY') as valid from dual;

     VALID
----------
	 0

SQL> SELECT validate_app_user_fn('SILLY', 'SILLY') as valid from dual;      

     VALID
----------
	 0

Is that a syringe in your pocket?

Now, let's try our original trick to see if we can hack the function :

SQL> select validate_app_user_fn('HARRY', 'A'||CHR(39)||'or 1=1--') as valid from dual;

     VALID
----------
	 1

I think we can safely conclude that use of PL/SQL is not, in itself, a defence against injection.

Nurse, prep for surgery

Let's return to a more conventional solution. Our function is about to go under the knife :

CREATE OR REPLACE FUNCTION validate_app_user_fn( 
    i_uname IN app_users.user_name%TYPE,
    i_pwd IN app_users.password%TYPE)
    RETURN NUMBER IS
    l_count PLS_INTEGER;
BEGIN
    SELECT COUNT(*) INTO l_count 
    FROM app_users 
    WHERE user_name = i_uname 
    AND password = i_pwd;
    
    RETURN l_count;
END;
/

Now, when we try the same thing :

SQL> select validate_app_user_fn('HARRY', 'A'||CHR(39)||'or 1=1--') as valid from dual;

     VALID
----------
	 0

Now, as we are referencing the variables in the query, rather than concatenating them in, they are quarantined – i.e. PL/SQL will only apply them in the context they were intended to be used. In short, they are bound into the query.

If we have a look at the query that's actually issued when we call this function, we can see this clearly :

SQL> SELECT sql_text
  2  FROM v$sqlarea
  3  WHERE UPPER(sql_text) LIKE '%FROM APP_USERS%'
  4  /         

SQL_TEXT
--------------------------------------------------------------------------------
SELECT COUNT(*) FROM app_users WHERE user_name = 'MIKE' AND password = 'SENSIBLE
'

SELECT COUNT(*) FROM app_users WHERE user_name = 'SILLY' AND password = 'SILLY'
SELECT COUNT(*) FROM app_users WHERE user_name = 'HARRY' AND password = 'A'or 1=
1--'

SELECT COUNT(*) FROM APP_USERS WHERE USER_NAME = :B2 AND PASSWORD = :B1
SELECT sql_text FROM v$sqlarea WHERE UPPER(sql_text) LIKE '%FROM APP_USERS%'
SELECT COUNT(*) FROM app_users WHERE user_name = 'MIKE' AND password = 'SILLY'

6 rows selected.

SQL> 

There's no magic here. PL/SQL does all this binding stuff without needing to be told. It's just the way it works by default.
Of course, there will be the odd occasion when you do need to dynamically build a query, but there are ways and means of binding variables in those circumstances. For the most part however, this method will do the job.
If you happen to be new to PL/SQL and want to know a bit more, I've written a programmer's introduction to the language. That post also includes links to other sources of PL/SQL wisdom.
Speaking of which, Tom Kyte has written an article which explores a variation of this particular kind of attack using NLS_DATE_FORMAT. If you're not feeling like a pin-cushion at this point, the gory details are here.

As you've been good, read all the way to the end, and not cried when getting your jabs, here's a lolly.

About these ads

One thought on “PL/SQL Injection – The Doctor Will See You Now

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s