Using a read-only connection

The only completely secure way to do this is to create a second user in Oracle with read-only permissions for the tables and then execute those queries on a different connection from the other system queries.

The only work you will have is to slightly modify the structure of the program to allow this, perhaps set up a second data source on your application server and so on.

Creating a sublinguation from Oracle SQL

This alternative is much less secure and much more complicated.

It consists of creating a SQL interpreter (practically a compiler) that performs lexical, syntactic and semantic analysis and thus allows only the sub language of the SQL that you define. Anything that has more, outside of this sublinguation, would be accused as a code syntax error, even though it is a valid SQL.

Is this proposal feasible if you have proficiency in defining the grammar for a formal language (did you pay attention in the faculty compiler class?) to implement or generate a parser and if the user would be content in have access to a limited set of features, after all anything you do not include in your sublinguation can not be used, even if Oracle gives you access.

The good news is that it is not difficult to reuse something that others have already done. A well-known library in Java, called Antlr , is able to generate a parser based on a formal language and there are several available grammars for download, including PL / SQL .

Now, you simply remove from the grammar what you do not want. You will need to understand what you are doing, so you still need to remember a few of those compiler classes.

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 do not have to do anything with this tree, just check if the interpretation of SQL sublinguation has succeeded.

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 that can:

• Change the bank structure
• Change bank or session settings
• Delete data and tables, such as truncate
• Performing functions or procedures with destructive effects
• The list goes on ...

There are procedures in Oracle that are able to execute arbitrary SQL commands if the user has the permissions to do so.

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

I will not leave here because I know I could not compile something completely secure and I do not want to encourage this practice and indirectly cause security precedents out there.

However, I would say that the best way is to look at the Oracle manual, because there you have all the commands and syntax of each well explained. So just look at them one by one and add the ones you do not want to run in a list.

Once you have the list, just iteratively over it and check if any word is contained in SQL. In comparison, be sure to ignore case and also consider character encoding.

Implementing security using fear

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

Of course this is a joke.

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