Interpolated strings and SQL

[Timon Gehr]

On 1/9/24 00:06, Walter Bright wrote:
> Here's how SQL support is done for DIP1036:
> 
> https://github.com/adamdruppe/interpolation-examples/blob/master/lib/sql.d
> 
> ```
> auto execi(Args...)(Sqlite db, InterpolationHeader header, Args args, 
> InterpolationFooter footer) {
>      import arsd.sqlite;
> 
>      // sqlite lets you do ?1, ?2, etc
> 
>      enum string query = () {
>          string sql;
>          int number;
>          import std.conv;
>          foreach(idx, arg; Args)
>              static if(is(arg == InterpolatedLiteral!str, string str))
>                  sql ~= str;
>              else static if(is(arg == InterpolationHeader) || is(arg == 
> InterpolationFooter))
>                  throw new Exception("Nested interpolation not supported");
>              else static if(is(arg == InterpolatedExpression!code, 
> string code))
>                  {   } // just skip it
>              else
>                  sql ~= "?" ~ to!string(++number);
>          return sql;
>      }();
> 
>      auto statement = Statement(db, query);
>      int number;
>      foreach(arg; args) {
>          static if(!isInterpolatedMetadata!(typeof(arg)))
>              statement.bind(++number, arg);
>      }
> 
>      return statement.execute();
> }
> ```
> This:
> 
> 1. The istring, after converted to a tuple of arguments, is passed to 
> the `execi` template.
> 2. It loops over the arguments, essentially turing it (ironically!) back 
> into a format

This is not ironic at all. The point is it _can_ do that, while DIP1027 
_cannot_ do _either this or the opposite direction_. It is yourself who 
called the istring the building block instead of the end product, but 
now you are indeed failing to turn the sausage back into the cow.

> string. The formats, instead of %s, are ?1, ?2, ?3, etc.
> 3. It skips all the Interpolation arguments inserted by DIP1036.
> 4. The remaining argument are each bound to the indices 1, 2, 3, ...
> 5. Then it executes the sql statement.
> 
> Note that nested istrings are not supported.
> ...

But you get a useful error message that exactly pinpoints what the 
problem is. Also, they could be supported, which is the point.

> Let's see how this can work with DIP1027:
> 
> ```
> auto execi(Args...)(Sqlite db, Args args) {
>      import arsd.sqlite;
> 
>      // sqlite lets you do ?1, ?2, etc
> 
>      enum string query = () {
>          string sql;
>          int number;
>          import std.conv;
>          auto fmt = arg[0];
>          for (size_t i = 0; i < fmt.length, ++i)
>          {
>              char c = fmt[i];
>              if (c == '%' && i + 1 < fmt.length && fmt[i + 1] == 's')
>              {
>                  sql ~= "?" ~ to!string(++number);
>                  ++i;
>              }
>              else if (c == '%' && i + 1 < fmt.length && fmt[i + 1] == '%')
>                  ++i;  // skip escaped %
>              else
>                  sql ~= c;
>          }
>          return sql;
>      }();
> 
>      auto statement = Statement(db, query);
>      int number;
>      foreach(arg; args[1 .. args.length]) {
>          statement.bind(++number, arg);
>      }
> 
>      return statement.execute();
> }
> ```
> This:
> ...

This does not work.

> 1. The istring, after converted to a tuple of arguments, is passed to 
> the `execi` template.
> 2. The first tuple element is the format string.
> 3. A replacement format string is created by replacing all instances of 
> "%s" with
> "?n", where `n` is the index of the corresponding arg.
> 4. The replacement format string is bound to `statement`, and the 
> arguments are bound
> to their indices.
> 5. Then it executes the sql statement.
> 
> It is equivalent.

No. As Nickolay already explained, it is not equivalent.

- It does not even compile, even if we fix the typo arg -> args.

That is enough to dismiss DIP1027 for this example. However, let's for 
the sake of argument assume that, miraculously, `execi` can read the 
format string at compile time, then:

- With this signature, if you pass a manually-constructed string to it, 
it would just accept the SQL injection.
- It does not give a proper error message for nested istrings.
- It has to manually parse the format string. It iterates over each 
character of the original format string.
- It (ironically!) constructs a new format string, the original one was 
useless.
- If you pass a bad format string to it (for example, by specifying a 
manual format), it will just do nonsense, while DIP1036e avoids bad 
format strings by construction.