Preventing SQL Injection in Your Applications

Hi,

SQL Injection is the unwanted addition of “other” logic into the database via the SQL language.  Malicious attacks pass additional syntax either in hopes of gaining additional information (lets them in, in another way, possibly with better permissions) or to cause mayhem while in there with the access already they have. 

A lot of this problem can be eliminated via careful coding.  For one thing, DBAs really need to pay attention to the login permissions assigned to the web access accounts (the account assigned to the DAD…database access descriptor).

There are three categories of SQL injection attacks to databases:

1. SQL Manipulation
2. Code Injection
3. Function Call Injection

SQL Manipulation adds syntax to an existing SQL statement; using the UNION clause, for instance.  SQL manipulation can also alter the WHERE clause to produce different results.  I have some interesting examples in which you can make the WHERE clause return true then add additional items.  For example, WHERE …  and you add ‘OR 1 = 1’…this will always return true to the original SQL and allows you to add additional WHERE clause items of your choosing.

SELECT * FROM users
WHERE username = 'bob'
and PASSWORD = 'mypassword'
…can be changed to…
SELECT * FROM users
WHERE username = 'bob'
and PASSWORD = 'mypassword' or 1 = 1 

I have seen Oracle Apps users code their large UNION clauses with each section having a 1 = 1 technique.  This isn’t SQL manipulation, but a clever way to test one side of the UNION clause and then the other.  You simply change one to a ‘OR 1 = 0’ and that side tests false and does not run!  In this code, it is a way to get the ‘SELECT * FROM users’ to work no matter what.  By adding the ‘or 1 = 1’, it’s as if this SQL has no WHERE clause at all, returning anything selected, including the manipulation of adding a UNION clause. 

SELECT product_name
FROM all_products
WHERE product_name like '%Chairs%'

By adding a UNION clause, this SQL manipulation can gain other information useful to gain additional access or expose sensitive information.  In this example, the query will return all the accounts on this particular database, if the access account has permissions to see the DBA_USERS information.

SELECT product_name
FROM all_products
WHERE product_name like '%Chairs' 
UNION SELECT username FROM dba_users 
      WHERE username like '%'

Code Injection appends additional SQL to the existing SQL or inserts new SQL.  This is similar to the above.  Any dynamic SQL is subject to this kind of attack.

SELECT * FROM users
WHERE username = 'bob'
and PASSWORD = 'mypassword'; DELETE FROM users 
                     WHERE username = 'admin';

This example injects in a DELETE clause, causing a mess if the user has the privileges to do DML to this ‘users’ table.  I hope they have a good backup!  This kind of code does not work in PL/SQL or Java.

BEGIN ENCRYPT PASSWORD('bob', 'mypassword'); END;

…becomes…

BEGIN ENCRYPT PASSWORD('bob', 'mypassword'); 
DELETE FROM users 
WHERE upper(username) = upper('admin'); END;

This kind of code would work in Oracle.

Function Call Injection is where someone replaces an existing function call with code of their choosing.  One would have to have the ability to compile; and if these permissions were available via the Internet access account…someone needs to have a serious chat with the people in charge of security at your site!  This would be very difficult to do with Oracle because function calls are PL/SQL and they would need to be compiled for sure.

SELECT TRANSLATE('user input',
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
'0123456789') FROM dual;

…becomes…

SELECT TRANSLATE('' || 
myappadmin.adduser('admin', 'newpass') || '',
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
'0123456789') FROM dual;

This code could add a new user.  Privileges for the account being used could easily prevent this kind of attack.

SELECT TRANSLATE('user input',
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
'0123456789') FROM dual;

…becomes…

SELECT TRANSLATE('' || 
UTL_HTTP.REQUEST('http://192.168.1.1/') || '',
 '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', '0123456789')
FROM dual;

This code could give the user access to the database via the web, or be used to present more useful information.

In Oracle, to change a function to different code, you would have to have the ability to compile code.  Again, limited privileges to the accounts available to other would help.

Oracle12 allows for ‘run faster’ functions.  This is similar to multiple SQL statements’ being treated as a single unit (the WITH … AS syntax), but you can now include function code.  IF the function has the same name as an existing function, this function gets run in its place.  I just wanted to let you know that using the WITH … AS syntax in Oracle allows for all kinds of substitutions, and in Oracle12, you can now run PL/SQL code without having compile permissions.

What is vulnerable is open-ended SQL where the end user or app is supplying the remainder of the SQL.  I’ve done this via a forms app in the past, but the end user never had direct access to the SQL.  They were always making choices I had supplied; I pasted in the rest of the WHERE clause and ran the SQL dynamically (execute immediate).

In Oracle, what isn’t vulnerable is bind variables.  The contents of bind variables are treated as data, not as syntax; no matter what the data is, it will always be treated as data.

BEGIN
...
sqlstr := 'SELECT postal-code
FROM states
WHERE state-name = ''' || name || '''';
EXECUTE IMMEDIATE sqlstr INTO code;
IF code = 'IL' THEN ...
...
END;

A couple of ways to prevent a lot of damage here is to make sure the ‘name’ field (that contains the additional syntax) is only large enough for the largest expected value, AND (most important) validate to make sure what is in that field is what you were expecting to be in that field!

BEGIN
...
sqlstr := 'SELECT postal-code
FROM states WHERE state-name = :name';
EXECUTE IMMEDIATE sqlstr USING name INTO code;
IF code = 'IL' THEN ...
...
END;

The above example has been converted to use bind variables instead of concatenating/substituting the contents of the ‘name’ variable into the SQL.  Using bind variables instead of code substitution prevents the problem altogether.  Bind variables are always treated as data, not syntax.

EXECUTE IMMEDIATE
'BEGIN updatepass(''' || value || '''); END;';
Here is another example of changing code substitution over to bind variables. 
cmd := 'BEGIN updatepass(:1); END;';
EXECUTE IMMEDIATE cmd USING value;

So, a clever use of bind variables will prevent a lot of these kinds of problems altogether.  If you must allow users to submit dynamic code/dynamic data, make sure the field isn’t any larger than what is needed for the data **AND** that you verify that the input is what you were expecting.  Allow the users to select from a list of features and you code the dynamic SQL yourself, not allowing anything dynamic in from the users. 

Permissions, permissions, permissions!  I can’t harp on this enough.  Accounts available to the public and over the web should be VERY limited in what they can do and see.  Oracle12 also allows for functions and procedures to have read access to tables and not giving this read access to the tables to the user.  So, the only way the user can get to the data is through the pre-programmed function.

I hope you find this information useful.  Coders…use bind variables and limit the use of dynamic anything to your applications.  DBAs…permissions, permissions, permissions!  

Dan Hotka

Author/Instructor/Oracle Expert

www.DanHotka.com

Dan@DanHotka.com