Safe Dynamic Sql Generator

TL; DR

There is always a desire to dynamically limit the results of large tables. The need to limit, and order columns dynamically. And then, filter on these modified columns. You either have to do some machinations to allow the shape of the data to appear to change for the user, or you result to dynamic SQL.

The problem with dynamic SQL is that it opens your server up to attack. So, what if you could do dynamic SQL without the risk to attack? That was the goal to making Safe Sql Builder.

What is Safe Dynamic Sql Generator

Safe Dynamic SQL Generator is a tool that takes a predetermined list of valid column names and uses that to generate valid parameterized sql based off of a supplied query specification.

In other words, it takes an untrusted request that defines the desired query, and generates SQL from a preconfigured setup. This query is parameterized, and built using the predetermined configuration not the request.

Why

My team had a need to develop a highly customizable website that showed data from a very wide and deep table. The customer needed to be able to slice the data into smaller chunks dynamically. Not wanting to simply concatenate strings together, we engineered a way to break the configuration into 2 parts. One is provided by the developer, the other by the client. We trust the configuration given by the developer, but not that given by the client.

This allowed us to simplify our domain and make an application that could be used in endless ways.

How to Use

The Orchestrator is setup with the following fields:

var validColumns = new ValidColumns(new []
{
    "ProductId",
    "Name",
    "ProductNumber",
    "MakeFlag",
    "FinishedGoodsFlag",
    "Color",
    "SafetyStockLevel",
    "ReorderPoint",
    "StandardCost",
    "ProductLine",
});

Then it receives a query object that looks like this:

var query = new Query
{
    Fields = new[]
    {
        "ProductId",
        "Color",
        "Name",
        "ProductNumber",
        "ProductLine",
        "StandardCost",
    },
    Distinct = true,
    Filters = new[]
    {
        new Filter {Property = "ProductLine", Values = new object[] {"Canceled"}},
        new Filter {Property = "Name", Values = new object[] {"Joe's Car"}},
    },
    Sort = "ProductNumber",
    Direction = "asc"
};

var builder = new QueryOrchestrator();
var (sql, _) = builder.BuildQuery(query, validColumns, "[AdventureWorks].[Product]");

It will produce a SQL statement that looks like:

SELECT [ProductId]
               ,[Color]
               ,[Name]
               ,[ProductNumber]
               ,[ProductLine]
               ,[StandardCost]
FROM [AdventureWorks].[Product]
WHERE ([ProductLine] = @ProductLine_EQUAL)
               AND ([Name] = @Name_EQUAL)
ORDER BY CASE
                WHEN [ProductNumber] IS NULL
                                THEN 1
                ELSE 0
                END
               ,[ProductNumber]