Introduction

This is a step-by-step tutorial showing how to apply the principles outlined in the article Query control made easy

If you have not read that article, we highly recommend you do so before continuing -- it will provide all the background you need.

Also, if you have never used Gallium Data before, we recommend you go through the basic tutorial to become familiar with basic operations.

This tutorial typically takes 10-15 minutes to complete for part 1 (this page). There are additional (optional) parts 2 and 3 for more advanced features.

Installation

⇨ Make sure Docker is up and running

Run the following from a command line:

docker network create gallium-qctrl


docker run -d --rm --name gallium-mssql --network gallium-qctrl galliumdata/gallium-data-demo-mssql:4


docker run -d --rm --name gallium-qctrl --network gallium-qctrl -p 8091:8080 -e repository_location=/galliumdata/repo_mssql galliumdata/gallium-data-engine:1.8.3-1851

Wait 5-10 seconds to let SQL Server start up, then

Run the following from a command line:

docker run -it --rm --name gallium-cli --network gallium-qctrl galliumdata/gallium-mssql-cli:1 -S gallium-qctrl -U sa -P Password1 -d GalliumDemoDB

Hint: try the same command again if it fails the first time -- it usually means that SQL Server was not yet up.

This will start three Docker containers:

If everything went well, you should see a command prompt, e.g.:

GalliumDemoDB>

Everything is now in place, let's get started.

Setup

To record database requests as they come in, we'll need a table in the database. 

⇨ In mssql-cli, execute the following statements:

create schema qctrl;

create table qctrl.query (query nvarchar(max));

Install the SQL Server client library

Next, we need our logic in Gallium Data to connect to the database to read and write to that new table, so we need to add the SQL Server driver to the repository.

⇨ Open Gallium Data at http://localhost:8091/web/index.html

⇨ Go to the Libraries page (left nav)

Using the Find tab, search for:

Click Add to this repository for the version marked for jre11

The exact version is not important, but it must be a version for jre11.

Publish (this may take a few seconds as the JDBC driver gets downloaded and installed)

Filters

Opening a connection to the database

In our logic in Gallium Data, we'll need a database connection to read and write to our qres.query table. We don't want to open and close that database connection every time we need it, so we'll open it when a connection is opened, and close it when the connection is closed. This can be done with a connection filter.

In Gallium Data:

Go back to the Repository page (left nav bar)

Open the project Simple demo - SQL Server

In Connection filters, create a connection filter of type JavaScript connection filter - name it "Connect to DB"

Set the code in that new connection filter to:

let qctrl = context.utils.createObject();

// Connect to query database

let SQLServerDriver = Java.type("com.microsoft.sqlserver.jdbc.SQLServerDriver");

let driver = new SQLServerDriver();

qctrl.conn = driver.connect("jdbc:sqlserver://gallium-mssql;" + 

    "databaseName=GalliumDemoDB;user=sa;password=Password1;trustServerCertificate=true", null);

// Prepare two SQL statements

qctrl.selectStmt = qctrl.conn.prepareStatement("select * from qctrl.query where query = ?");

qctrl.insertStmt = qctrl.conn.prepareStatement("insert into qctrl.query (query) values (?);");

// Store them in the connection context so they will be available in other filters

context.connectionContext.qctrl = qctrl;

With this filter in place, any time a database client connects to Gallium Data, the filter will open a side channel to the database so that we can read from and write to our qres.query table. We also prepare two SQL statements that we'll be using later: one to see if a given query is already present in the table, and one to insert a new query.

Eagle-eyed readers may notice that the code contains a password, which is of course bad practice -- see the article Using secrets in Gallium Data for details on how to avoid that in real-world code.

Closing the connection to the database

If we open connections to the database, we should also close them.

⇨ Go back to the project view (top nav bar, PROJECT SIMPLE DEMO - SQL SERVER)

Create a connection filter of type ConnectionCloseFilter -- name it "Close DB connection"

Set its code to:

context.connectionContext.qctrl.conn.close();

That way, whenever a database client closes its connection to Gallium Data, we'll also close our side channel to the database.

All the plumbing is in place, we can now get down to business.

Record incoming requests

In Gallium Data, create a new request filter of type Query filter - MSSQL - name it "Recording filter"

Set its code to:

let qctrl = context.connectionContext.qctrl;

// Find if this query is already known

qctrl.selectStmt.setString(1, context.packet.sql);

let rs = qctrl.selectStmt.executeQuery();

if ( ! rs.next()) {

    // It's not known: insert it in the query table

    qctrl.insertStmt.setString(1, context.packet.sql);

    qctrl.insertStmt.executeUpdate();

}

rs.close();

This code will be invoked any time a database client sends a SQL query to SQL Server. The code looks in the query table to see if the SQL query has already been seen, and, if not, it inserts it into the table.

Try it out

Make sure that you have published to Gallium Data

Exit mssql-cli with Ctrl-D

Restart it with either up-arrow, or:

docker run -it --rm --name gallium-cli --network gallium-qctrl galliumdata/gallium-mssql-cli:1 -S gallium-qctrl -U sa -P Password1 -d GalliumDemoDB

This is necessary because we want a new database connection.

In mssql-cli, run an arbitrary SQL query:

select top 5 * from gallium_demo.customers

This will show you a few customer records. Now let's see if our filter has captured that request:

⇨ In mssql-cli, run:

select * from qctrl.query where query like '%gallium_demo%' or query like '%qctrl%'

This will show you the query you just ran, along with the current one -- they've been saved to the database by the recording filter:

+-------------------------------------------------------------------------+

| query                                                                   |

|-------------------------------------------------------------------------|

| select top 5 * from gallium_demo.customers                              |

| select * from qctrl.query where query like '%gallium_demo%' or query li |

+-------------------------------------------------------------------------+

Incidentally, the reason for this rather specific select is that mssql-cli runs all kinds of SQL commands behind your back, and those have been recorded too -- if you're curious, you can try:

select * from qctrl.query

Hint: hit space for page down, hit q to regain control

As long as the filter in Gallium Data is active, all requests to the database will be recorded.

Reject unknown requests

Now that we have recorded a few queries, let's reject any unknown queries.

In Gallium Data, create a new request filter of type Query filter - MSSQL and call it "Enforcement filter"

Set its code to:

context.connectionContext.qctrl.selectStmt.setString(1, context.packet.sql);

let rs = context.connectionContext.qctrl.selectStmt.executeQuery();

if ( ! rs.next()) {

    context.result.errorMessage = "Unknown query was rejected by enforcement filter";

}

rs.close();

This code simply looks up the incoming request in the query table. If it finds it, the request gets forwarded to SQL Server. If it is not found, though, the request gets rejected.

Deactivate the first filter (called "Recording filter") - hint: you can click the green arrow to activate/deactivate a filter

Publish to Gallium Data

⇨ In mssql-cli, execute a  new query, for instance:

select count(*) from gallium_demo.customers

and observe that the query fails with the error message from the enforcement filter:

Unknown query was rejected by enforcement filter

But if you run a query that has already been recorded, like:

select top 5 * from gallium_demo.customers

that query runs fine.

What have we seen so far?

In this tutorial, we saw how Gallium Data can be set to record all incoming database requests in an arbitrary database (it doesn't have to be SQL Server, obviously), and later, to reject requests that have not been recorded thusly. 

This is a good demonstration of the power of Gallium Data: something that would be quite expensive with a specialized product can be done easily and at no cost.

At this point, you can either:

Cleanup

Once you're done with this tutorial, and you want to remove everything that was installed,

Exit mssql-cli with Ctrl-D

⇨ Execute the following commands:

docker stop gallium-mssql
docker stop gallium-qctrl
docker network rm gallium-qctrl

This will stop all the Docker containers started during this tutorial.

If you also want to remove the Docker images:

docker rmi galliumdata/gallium-mssql-cli:1
docker rmi galliumdata/gallium-data-demo-mssql:4
docker rmi galliumdata/gallium-data-engine:1.4.0-1296

This will remove everything installed by this tutorial.

We'd love to hear from you -- good or bad! Please drop us an email and let us know if you have any questions or comments.

feedback at gallium data dot com