3 ms·

I tend to agree. The main trouble with SQL is that the query parameters are mixed in with the query, which results in string escape problems. That dates from w

I tend to agree. The main trouble with SQL is that the query parameters are mixed in with the query, which results in string escape problems. That dates from when people were expected to query a database by hand from SQL, which is sometimes useful. For machine generated use, SQL queries should consist of a well defined fill-in-the-blanks system, with a text SQL query and a machine data structure. Something like this: Query in SQL text as a constant string: "SELECT foo FROM BAR WHERE name=NAME1" Parameters: key/value pairs in a safe native format for the language in use. {"NAME1" : "Smith"} That gets you out of the curse of character escaping and SQL injection, the only real problem. It avoids trying to rewrite SQL in yet another ORM. Microsoft has something which works this way, but it's unnecessarily clunky.[1] [1] https://learn.microsoft.com/en-us/sql/relational-databases/native-client-odbc-queries/using-statement-parameters-binding-parameters?view=sql-server-ver15 https://learn.microsoft.com/en-us/sql/relational-databases/n...

Animats 2y ago
This suggests a simple API using a Rust macro: fill_in_sql!(query_string, query_params) Where query_params is a structure containing the parameters: struct some query { name: &str, id: u32, } The macro would parse the query string, find the variables to be filled in, match them to fields in the "params" structure, and generate the code to safely escape and fill in the params. Any matching failures are found at compile time. This is similar to what the usual "derive" macros do - examine a data structure at compile time and generate appropriate code.
- kelnos 2y ago
  I've used systems like this in Scala. It's actually pretty magical to write something like: val foos = sql"SELECT * from foos WHERE status = 'frobbed'".as[List[Foo]] ... and have the macro system parse the query at compile time and verify that the query will indeed return a `List[Foo]`. I think this is a nice middle ground, but constructing dynamic queries often can't be checked at compile-time. (I'm probably getting the syntax somewhat wrong; I haven't done Scala in quite a few years at this point.)
kelnos 2y ago
I don't really think SQL injection bugs are the main driver. Certainly everyone should be using a query builder that protects them from this (and yes, I know, there are still people who screw this up), but I think the issues with SQL go far beyond that. Personally I would much rather deal with a database table as a collection of objects with various fields in them. In a language with a reasonable collections library, I want to be operating on that data using functional combinators like filter, map, group_by, sort/order_by, etc. That feels much more natural to me than writing out stringly-typed SQL by hand. This is sorta an ORM, but not quite: it's somewhere in between writing raw queries, and using a full-fledged (usually inefficient) ORM. The downside, of course, is that the performance of my queries is at the mercy of whatever the query generator underneath it is doing. But the nice thing is that if I need to optimize a query, I can always bypass the generator and write it by hand. But I wouldn't do that until I've measured things; no reason to prematurely optimize.
avianlyric 2y ago
> For machine generated use, SQL queries should consist of a well defined fill-in-the-blanks system, with a text SQL query and a machine data structure. Something like this: Erm, that’s exactly how modern DB and DB clients work. The client passes the SQL query with placeholders for parameters, and then sends the parameters separately. It’s also a core part of how prepared SQL queries work. Every SQL client in any worthwhile language implements this pattern of passing a SQL query and its parameters as separate data structures, and they have done for decades. SQL injection only happens when people don’t use their SQL client properly, and attempt to compose SQL queries using naïve string templating. Effectively doing an end run around all the protections DB and clients provide by default.

Animats 2y ago

This suggests a simple API using a Rust macro: fill_in_sql!(query_string, query_params) Where query_params is a structure containing the parameters: struct some query { name: &str, id: u32, } The macro would parse the query string, find the variables to be filled in, match them to fields in the "params" structure, and generate the code to safely escape and fill in the params. Any matching failures are found at compile time. This is similar to what the usual "derive" macros do - examine a data structure at compile time and generate appropriate code.

kelnos 2y ago
I've used systems like this in Scala. It's actually pretty magical to write something like: val foos = sql"SELECT * from foos WHERE status = 'frobbed'".as[List[Foo]] ... and have the macro system parse the query at compile time and verify that the query will indeed return a `List[Foo]`. I think this is a nice middle ground, but constructing dynamic queries often can't be checked at compile-time. (I'm probably getting the syntax somewhat wrong; I haven't done Scala in quite a few years at this point.)

kelnos 2y ago

I don't really think SQL injection bugs are the main driver. Certainly everyone should be using a query builder that protects them from this (and yes, I know, there are still people who screw this up), but I think the issues with SQL go far beyond that. Personally I would much rather deal with a database table as a collection of objects with various fields in them. In a language with a reasonable collections library, I want to be operating on that data using functional combinators like filter, map, group_by, sort/order_by, etc. That feels much more natural to me than writing out stringly-typed SQL by hand. This is sorta an ORM, but not quite: it's somewhere in between writing raw queries, and using a full-fledged (usually inefficient) ORM. The downside, of course, is that the performance of my queries is at the mercy of whatever the query generator underneath it is doing. But the nice thing is that if I need to optimize a query, I can always bypass the generator and write it by hand. But I wouldn't do that until I've measured things; no reason to prematurely optimize.

avianlyric 2y ago

> For machine generated use, SQL queries should consist of a well defined fill-in-the-blanks system, with a text SQL query and a machine data structure. Something like this: Erm, that’s exactly how modern DB and DB clients work. The client passes the SQL query with placeholders for parameters, and then sends the parameters separately. It’s also a core part of how prepared SQL queries work. Every SQL client in any worthwhile language implements this pattern of passing a SQL query and its parameters as separate data structures, and they have done for decades. SQL injection only happens when people don’t use their SQL client properly, and attempt to compose SQL queries using naïve string templating. Effectively doing an end run around all the protections DB and clients provide by default.