Taking reserved SQL words out of a Java string

Question

I’m making a view that has an open field (textarea) where the user can write SQL and then run the same SQL.

The point is that the oracle user where it will run SQL has permissions for everything and it is not supposed to do delete's or truncate or insert. Only consultations are permitted (select).

I see no other way than to have words mapped in memory and check whether the string contains those reserved words.

Any idea how to solve otherwise?

Answer 1

Using a read-only connection

The only totally safe way to do this and the simplest way too is to create a second user on Oracle with read-only permissions of the tables and then perform these queries in a different connection to other system queries.

The only job you will get is to modify the program structure a little to allow this, maybe set up a second data source on your application server and so on.

Creating a Sublinguage from Oracle SQL

This alternative is much less safe and much more complicated.

It consists of creating an SQL interpreter (practically a compiler) that does the lexical, syntactic and semantic analysis and thus allows only the SQL sub language you define. Anything else, out of this sublingual, would be accused as a syntax error code, even if it is a valid SQL.

This proposal is feasible if you have the proficiency to define grammar for a formal language (paid attention in the faculty compiler class?) to implement or generate a formal language parser and if the user would be happy to have access to a limited set of functionalities, after all everything that you do not include in your sublinguage cannot be used, even if Oracle gives access.

The good news is that it’s not hard to reuse something that others have already done. A well-known library in Java, called Antlr, is capable of generating a parser based on a formal language and there are several downloadable grammars, including PL/SQL.

Now, all you have to do is remove from the grammar what you don’t want. You’ll need to understand what you’re doing, so you still need to remember some of those compiler lessons.

With the grammar ready, you generate the parser. When executed, the parser interprets the code (SQL in this case) and generates an AST, which is a tree with the tokens found.

In your case, you don’t need to do anything with this tree, just check if the SQL sublingual interpretation has successfully occurred.

Content filter using a "blacklist"

Filtering SQL using a list of forbidden words goes far beyond delete or insert. You will need to include any command you can:

• Changing the structure of the bank
• Change seat or session settings
• Delete data and tables such as truncate
• Perform destructive functions or procedures
• The list continues...

There are procedures in Oracle capable of executing arbitrary SQL commands if the user has the permissions for this.

It may be perfectly possible to reach a reasonable level of security, but the problem with all this is that you may end up filtering a valid SQL by chance, a false positive, if some field or table or string name contains the name of a blocked command.

I’m not leaving it here because I know I couldn’t compile something totally safe and I don’t want to encourage that practice and indirectly cause safety precedents out there.

However, I would say that the best way is to look at the Oracle manual, because it has all the commands and syntax of each one well explained. So just consult one by one and add the ones you don’t want to run in a list.

Once you have the list, simply iterate over it and check if any words are contained in SQL. In comparison, n when forgetting to ignore upper and lower case and also consider character encoding.

Implementing security using fear

An alternative would be to warn the user that the commands typed will be stored and if he is caught doing something wrong he will lose his job or be sued.

Of course this is a joke.

What I would say is that it would be good practice to store who typed what, regardless of the approach chosen. Security also includes investigating malicious attacks.

Another principle is that, depending on the use of the system, for example if it is used internally by one in the limited number of people, it is not worth investing in something elaborate.