If I've got a valid SQL string can there be anyway I'm able to carry it out during my PL/SQL - but guarantee that it's a Choose statement only...without having done complex parsing to make sure it does not have escape figures/nested instructions or any one of that jazz?


What I am really attempting to accomplish is really a generic, built-directly into my application, querying tool. It features a friendly, domain specific GUI and allows a really non-tech user create reasonably complex queries. The tool handles versioning from the searches, adds innerjoins where needed plus some other application specific items you wouldn't look for a typical SQL DEV type tool.

The applying effectively produces a SQL Query. However , I additionally allow customers to directly enter their very own SQL. I am concerned about potential SQL injection type issues.

I am unsure if this sounds like the right place but, additionally towards the question - if anybody could recommend a great Oracle book that will get me up to date on things of the character - I'd greatly be thankful.

One option would be to GRANT your user only SELECT privilege if that is the only real factor the consumer is approved to complete.

See "Oracle Database Security Guide: Introduction to Privileges"

However, I do not believe that the application is always secure simply because you restrict the queries to SELECT. You will find good examples of mischief that may be perpetrated whenever you allow unsafe utilization of SELECT queries.

Re your clarified question: I have analyzed SQL injection and discussed it a great deal. Things I can advise typically is: Never execute user input as code. That's how SQL injection happens.

You are able to design a website-specific language and map user input to SQL procedures, but make certain there is a layer that translates user options towards the database schema. Should you separate user input out of your SQL code by presenting a mapping layer, then you ought to be okay.

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

Oracle comes with many different execute rights granted to public. As a result a user without any explicit place/update/remove/execute rights can perform mischief.

Talking about mischief, despite a Choose a person might cause trouble. A "Choose * FROM table FOR UPDATE of column" would lock the whole table. Choose...FOR UPDATE only requires Choose rights.

Dumb queries (eg cartesian joins) could bring a database to the needs (though Resource Manager should have the ability to block many of them by only permitting queries that will do under a particular quantity of IOs or CPU).

What about providing them with a listing of approved SQLs to complete along with a process to allow them to nominate SQLs for inclusion ?

In oracle, you can easily determine the first word is "choose" or "with". This really is because of PL/SQL's Ada heritage, which requires compound claims to stay in begin/finish blocks, to ensure that the typical SQL injection techniques just cause syntax errors.

Obviously, the very best response is to get this done by granting permissions and staying away from if at all possible directly evaluating unknown input. But it's interesting the begin/finish syntax removes lots of SQL injection attack vectors.

If you are giving the consumer a text area to allow them to type anything they want, hey, SQL injection is what you would like.

I would not leave the doorway so open like this, but when We had to get it done, then I'd run an explain intend on regardless of the user really wants to do. The optimizer will parse the query and set all the details concerning the SQL statement within the plan_table table, which you'll then query to see if it's actually a choose operation, which tables/indexes that schemas are now being utilized, when the where clause is one thing you agree to, if there's any "bad" procedures, for example Cartesian joins or full table scans, etc.