Escape hatch for dynamic SQL

[rspeele]

I think the vast majority of queries in most applications should be fully static, but occasionally there is a real need to build SQL at runtime.

In a perfect world we would have a LINQ-style query builder able to benefit from the compile-time model, but that will take time to create and needs a lot more thought put into its design.

So, to avoid blocking people from getting things done, I'd like to allow including pieces of dynamic SQL in a mostly-static query. Currently I think this could work like so:

type MyQuery = SQL<"""
    select * from MyTable where inject_raw_sql(@x)
""">

open Rezoom.SQL.Raw // sql and arg functions

let exampleCommand() =
    let rawSql =
        [   sql "SomeColumn = "
            arg DateTime.UtcNow
            sql " and SomeOtherColumn <> "
            arg 3
        ]
    MyQuery.Command(x = rawSql)

The raw SQL would have to be written for the backend dialect. It would not go through RZSQL parsing/checking/translation at any time.

In this design, raw SQL would just be a special type, much like there is currently a special "list" type for expr IN @param. inject_raw_sql would be an erased function, whose only job is to inform the typechecker that the parameter's type is raw SQL.

Perhaps there could also be an option in rzsql.json to ban use of this function, for people who want to make sure their codebase definitely has no SQL injection.

A weird thing about this design is that because it uses a plain old bind parameter, it would only be possible to add raw SQL in places that you could use an expression. But since the raw SQL is totally unchecked, it could actually include extra clauses -- in the above example, there would be nothing stopping inject_raw_sql(@x) from adding an order by clause onto the query. This is a bit ugly.

Thoughts on this idea?

[rkosafo]

Just like how #8 provides the By type, will it be ok to either add a new type , e.g. Columns or rename and extend By.

The idea is to be able to use this to validate the column names (like OfString) and also determine the parser from the column type (DbType.Int64 or Int64) to use to convert the data. This could be useful especially when the data is a obj or a date (which is likely to be received as a string from JS)

Also, can this be used together with orderBy like MyQuery.orderBy(...).thenBy(...).Command(x = rawSql?

[rspeele]

I like the idea of having typed columns. I am rethinking #8 and believe it should be possible to do filtering on the output columns of a query in addition to sorting/paging. Will come back to that one soon and make it better.

For this issue check out https://github.com/rspeele/Rezoom.SQL/blob/escape-hatch-dynamic-sql/doc/Language/DynamicSQL.md . That branch has the code as well. Will merge to master soon.

[rspeele]

Released in 0.6.2.