Re: Validating dynamic SQL

Hi Jeremy

Dynamic PL/SQL is far more dangerous than dynamic SQL as it makes it simpler for a user/hacker/whatever to exploit this tool for any purpose. He could just insert a call to DBMS_SQL and execute anything the user he is logged in as has privileges to execute.

You might be interested in two papers i wrote for securityfocus last year on SQL injection and Oracle at:

http://online.securityfocus.com/infocus/1644 - "SQL injection and Oracle
- part one"

http://online.securityfocus.com/infocus/1646 - "SQL injection and Oracle
- part two"

one way to protect your code is to set up one user who is allowed to use it, grant only the privileges needed (least privilege principle) and then add a block to the code / app that checks you are logged in as that user, if not it throws you out. use audit to monitor use of DDL.

parsing the SQL is another option but if the SQL is complex and you need to check for a few things as you say, then you are on your way to writing a parser and it becomes less trivial.

one question though, this sounds like it could be done by just using SQL*plus and a hand coded script for each customer. wouldn't that be simpler?

hth

kind regards

Pete

-- 
Pete Finnigan
email:pete_at_petefinnigan.com
Web site: http://www.petefinnigan.com - Oracle security information and services
Book:Oracle security step-by-step Guide - see http://store.sans.org for details.