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

In oracle, you can just check to see that the first word is "select" or "with". This is due to PL/SQL's Ada heritage, which requires compound statements to be in begin/end blocks, so that the usual SQL injection techniques just cause syntax errors.

Of course, the best answer is to do this by granting permissions and avoiding if possible directly evaluating unknown input. But it is interesting that the begin/end syntax eliminates a lot of SQL injection attack vectors.

Answer 2

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 3

If you're giving the user a text area so they can type whatever they want, hey, SQL injection is what you want.

I wouldn't leave the door so open like that, but if I was forced to do it, then I'd run an explain plan on whatever the user wants to do. The optimizer will parse the query and put all the information about the SQL statement in the plan_table table, which you can then query to check if it's really a select operation, which tables/indexes from which schemas are being accessed, if the where clause is something you approve of, if there's any "bad" operations, such as Cartesian joins or full table scans, etc.

Answer 4

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 ?

Answer 5

Take a look at Oracle's paper on writing injection proof pl/sql. The DBMS_ASSERT built-in package should help you test your SQL for appropriateness.

Even with those tests, I'd be extremely reluctant to give people an open text window for building their queries especially on the public net or in a large organization where you don't know everybody. There are very creative people just looking for opportunities like that.