Pete Finnigan's Oracle Security Weblog

I was aware of this new package called DBMS_ASSERT that Oracle have added to 10g Release 2 to check and sanitize input to PL/SQL packages to prevent SQL Injection or rather make it less likely. This package has also been back ported to previous versions for fixes for CPU October 2005. I noticed this evening that David Litchfield has posted a note titled "Oracle DBMS_ASSERT and the October 2005 CPU" on the bugtraq list about a paper NGS have written documenting this DBMS_ASSERT package. The paper is titled "Securing PL/SQL Applications with DBMS_ASSERT". This is a good paper describing the 6 exported functions in this package with examples of their use and well worth a read.

I also agree with David that this is definitely a move in the right direction for Oracle. At last they seem to be tackling problems at the root. This is good. I don't know the extent of the use of this package yet but I would hope that eventually every PL/SQL package function and procedure parameters are protected or sanitized by DBMS_ASSERT if that input ends up in an SQL statement either directly or not. In fact I would use it for all parameters at source. I would hope Oracle is working towards this. My next step would also to be to use bind variables in every case possible to again eliminate SQL Injection and get rid of all concatenated SQL statements. i.e. SQL that is built by concatenation should be removed - binds should be used instead in all cases as any concatenation potentially can be injected.

This is a good paper but contains one small error that does not detract from reading it. The example for QUALIFIED_SQL_NAME should use that function and not SIMPLE_SQL_NAME. no matter though the intent is obvious.