Injecting SQL into the New Year

No Oracle blog would be complete without a comment regarding SQL Injection.

This is a way to exploit a potential security vulnerability in the database layer of your application. Depending on the programming language used, and the way your SQL is written - this vulnerability could extend from viewing rows you're not supposed to; to viewing data from other columns/tables; to dropping/manipulating other database objects.

There are some security gurus out there, and I'd suggest you read some work by either Pete Finnigan or Peter Lorenzen.

For now I'll give a brief demo on an example that could be demonstrated in Oracle Application Express.

Let's say you have a simple report region on the EMP table, with a field allowing you to search by employee name:

You may wish to define this report region as SQL Query (PL/SQL function body returning SQL query), which allows you to dynamically create the SQL  - a common requirement in many applications.

The correct way to define this SQL would be to use bind variables. The following image demonstrates a simple example of this (using the Oracle 10g q-quote mechanism)

Now if this SQL was modified slightly, we introduce the possibility of SQL injection.

 

For those not familiar with the q-quote syntax, it may read:


v_sql := v_sql||'WHERE UPPER(ename) LIKE '''||:P3_ENAME||'%''';


You see, if we performed the following search, we could illicitly obtain all the rows from the query:

This happens because our SQL actually forms the following:


SELECT empno, ename, job, hiredate from emp WHERE UPPER(ename) LIKE 'S' or 1=1--%'


Where the OR 1=1 ensures all rows are returned. So not only are you taking a performance hit, you are creating vulnerabilities in your application. Lorenzen's paper describes some other potential threats within Apex.

If the region used bind variables, the same search criteria would just return no rows.

A simple example completed.

I hope everyone enjoyed any time off they may have had over the new year, I certainly did!