Executing Dynamic SQL in Oracle (PL/SQL) and Ensuring Security

Question

If I have a valid SQL string; is there anyway I can execute it in my PL/SQL - but guarantee that it is a SELECT statement only...without doing complex parsing to ensure it doesn't have any escape characters/nested commands or any of that jazz?

EDIT:

What I'm really trying to accomplish is a generic, built-in to my application, querying tool. It has a friendly, domain specific GUI and lets a very non-tech user create reasonably complex queries. The tool handles versioning of the searches, adds innerjoins where needed and some other application specific stuff you wouldn't find a typical SQL DEV type tool.

The application successfully creates a SQL Query. The problem is that I also allow users to directly enter their own SQL. I'm worried about potential SQL injection type issues.

I'm not sure if this is the appropriate place; but, in addition to the question - if anyone could recommend a good Oracle book that would get me up to speed on things of this nature - I'd very much appreciate it.

Answer 1

One solution is to GRANT your user only SELECT privilege if that's the only thing the user is authorized to do.

See "Oracle Database Security Guide: Introduction to Privileges"

However, I don't think that your application is necessarily secure just because you restrict the queries to SELECT. There are examples of mischief that can be perpetrated when you allow unsafe use of SELECT queries.

---

Re your clarified question: I've studied SQL injection and written about it quite a bit. What I can advise as a general rule is: Never execute user input as code. That's how SQL injection occurs.

You can design a domain-specific language and map user input to SQL operations, but make sure there's a layer that translates user choices to the database schema. If you separate user input from your SQL code by introducing a mapping layer, then you should be all right.

See also my answer to "How do I protect this function from sql injection."

Answer 2

Oracle comes with a lot of execute privileges granted to public. As such even a user with no explicit insert/update/delete/execute privileges can do mischief.

Speaking of mischief, even with a SELECT a user could cause trouble. A "SELECT * FROM table FOR UPDATE of column" would lock the entire table. SELECT...FOR UPDATE only requires SELECT privileges.

Dumb queries (eg cartesian joins) could bring a database to its needs (though Resource Manager should be able to block most of them by only allowing queries that would do less than a specified amount of IOs or CPU).

How about giving them a list of approved SQLs to execute and a process for them to nominate SQLs for inclusion ?