Query control tutorial
(MSSQL) - part 2

This tutorial is a continuation of the query control tutorial part 1.

If you have not gone through it, we strongly suggest that you do so, as this tutorial will not make any sense otherwise.

Level 2: normalizing requests

In this tutorial so far, we've recorded requests exactly as they come. That may be fine in many cases, but it's common to have dynamic SQL requests that contain values, for instance:

select * from sales.customers where id=345

select * from sales.customers where id=99

select * from sales.customers where id=1049

With our current setup, each of these requests will be recorded separately, which is clearly not scalable. What if we have millions of customers?

You might object that the application should use a prepared statement instead, so that the request would be:

select * from sales.customers where id=?

and we couldn't agree more: that would be more efficient and more elegant. But this kind of thing is going to happen, and changing the application is often not an option.

Thankfully, it turns out that it's fairly easy to solve this problem.

Normalizing the requests

We need to convert the requests from their raw form, which may include literal values (numbers and strings) into a canonical form.

So we need to convert this type of query:

select * from sales.customers where id=1049

into:

select * from sales.customers where id=<NUM>

That way, we can collapse all such queries into one.

While we're at it, we can also put the whole request in uppercase to make it case-insensitive, and collapse all spaces, tabs, carriage returns and so on.

The code to do this turns out to be fairly simple:

let sql = context.packet.sql.toUpperCase();


// Collapse multiple spaces, tabs, etc...

sql = sql.replaceAll(/[\s]+/g, ' ');


// Replace numbers with <NUM> if they're not part of a name

sql = sql.replaceAll(/(?<!\w)\d+(?!\w)/g, '<NUM>');


// Replace strings with <STR>

sql = sql.replaceAll(/'(([^'])|(''))*'/g, "<STR>");


// Check if it's already in the database, and insert it if not

let qctrl = context.connectionContext.qctrl;

qctrl.selectStmt.setString(1, sql);

let rs = qctrl.selectStmt.executeQuery();

if ( ! rs.next()) {

qctrl.insertStmt.setString(1, sql);

qctrl.insertStmt.executeUpdate();

}

rs.close();

Given this, a SQL request like:

select * from [MyDB].[MyTable] where col1 = 123 or col2 like 'foo%bar'

will be recorded as:

SELECT * FROM [MYDB].[MYTABLE] WHERE COL1 = <NUM> OR COL2 LIKE <STR>


Deploy this code

Change the code in "Recording filter" to the JavaScript code above

Activate "Recording filter"

De-activate "Enforcing filter"

Publish to Gallium Data


Try it out

In mssql-cli, run an arbitrary SQL query, e.g.:

select id, first_name from gallium_demo.customers where first_name like 'A%' or id < 5

⇨ Verify that it was recorded with:

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

You'll see that your last query has been recorded, but this time in a normalized form:

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

| query |

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

| select top 5 * from gallium_demo.customers |

| select * from qctrl.query |

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

| SELECT ID, FIRST_NAME FROM GALLIUM_DEMO.CUSTOMERS WHERE FIRST_NAME LI |

| SELECT * FROM QCTRL.QUERY WHERE QUERY LIKE <STR> OR QUERY LIKE <STR> |

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

Enforcement

The enforcement filter also need to be updated.

Change the code in "Enforcement filter" to:

let sql = context.packet.sql.toUpperCase();

sql = sql.replaceAll(/[\s]+/g, ' ');

sql = sql.replaceAll(/(?<!\w)\d+/g, '<NUM>');

sql = sql.replaceAll(/'(([^'])|(''))*'/g, "<STR>");


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

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

if ( ! rs.next()) {

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

}

rs.close();

De-activate "Recording filter"

Activate "Enforcement filter"

Publish to Gallium Data

Try it out again

In mssql-cli, run a known query:

select id, first_name from gallium_demo.customers where first_name like 'B%' or id < 6

This query should execute normally, since we've already executed a similar query, and it got recorded in the query table.

Now let's try a query we have never run before.

⇨ In mssql-cli, run:

select count(*) from gallium_demo.customers

That query gets rejected because it wasn't found in the query table.

What have we seen?

We now have a reasonably functional query restriction system. As you can see, it's pretty easy to put in place, and you get an enormous amount of flexibility. Putting such a query control system in place will not only enhance the security of your applications, but it will also give you insights into the behavior of your database applications.

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.3.1-1248

This will remove everything installed by this tutorial.

We hope you've enjoyed 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