Query control tutorial
(MSSQL) - part 2 

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:

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:

⇨ 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 

• finish this tutorial (below), or 

• go on to part 3 of this tutorial for more advanced features