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)
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!
2 comments:
Scott,
Just to be pedantic, I wanted to point out that these aren't flaws in "APEX the product", these are coding flaws that happen to be in an APEX application.
I just wanted to highlight that as I think someone reading this could run away thinking "I can't use APEX, it's full of security holes".
John.
That's fine, John - perhaps I should be re-iterated that point.
Regardless of the tool (IT or otherwise), if used incorrectly you can introduce dangers and vulnerabilities.
Fortunately, usually this means really going out of your way, or going against the grain.
Apex as a tool is secure and robust, as is the underlying database technology.
Post a Comment