How to transparently secure database exchanges by filtering out bad or insecure SQL statements issued by an application?

This simple web application fulfills that use case and demonstrates how JDBC trace event listener can be used to analyze and may be intercepted SQL statements that are sent by an application to oracle database server.

=================================================================================================

=================================================================================================

In this application we leverage the event listener (aka interceptor) delivered by project https://github.com/oracle-samples/oracle-db-examples/tree/7aaa7ae05d36a7127cd5bd4bb84e66301f45908c/java/jdbc/statement-interceptor/interceptor That is a simple standalone SpringBoot application that performs search on an employee table. This simple table contains 5 employees like

CREATE TABLE employees ( id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, full_name VARCHAR2(60), visible NUMBER(1) DEFAULT 0 ) 

Employees with 'visible' attributes set to 0 must not be seen. This flag will be used to demonstrate how SQL injection can lead to unexpected response. See section Testing the interceptor

The SQL statement sent to the server is intercepted and analyzed according to the "security" rules.

This application uses a customized UCP connection pool.

see com.oracle.jdbc.samples.statementinterceptordemo.TracedDataSourceConfig

The pool configuration is taken from

src/main/resources/oracle-pooled-ds.properties

Please change it accordingly.

Each connection delivered by this pool will have the Statement interceptor trace event listener attached.

The interceptor rules are defined in

src/main/resources/statementRules.json

Be sure that you use gradle 8.5 or above.

We require the following dependency to be available

dependencies { implementation 'com.oracle.database.jdbc:JDBCInterceptor:0.1-SNAPSHOT' }

Information for the remote datasource must be correctly set. By default, the following environment variables are used

DATABASE_USER DATABASE_PASSWORD DATABASE_URL

You can change this behavior by setting correct values in oracle-pooled-ds.properties file

You can then start the application by running

gradle bootRun

Once the application is started, open a web browser and go to http://localhost:8080/

Note: To change the default port number, change server.port value in the application.properties file.

This application hosts two datasources, one with an interceptor in place. There is a checkbox that allows you to switch from one datasource to another

You can issue some search that will be intercepted by the listener

An example is a search like

' or 'a'='a

When the interceptor is not enabled, you will see that this search return all employees including the ones that are not supposed to be visible

When the interceptor is enabled, you will see the security error thrown by the interceptor and that the request does not reach the server.

By issuing "BabEmployee" as search criteria, you will see how security log record can be intercepted.

Run > Run statement-interceptor-demo [bootRun]

#gradle bootRun