Query control tutorial (MSSQL)

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.9.0-2157

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:

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:

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:

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:

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:

• finish this tutorial (below), or 

• continue and extend what you've done so far in part 2 of this tutorial