Interpolated strings and SQL

[Timon Gehr]

On 1/12/24 07:13, Walter Bright wrote:
> I'd like to see an example of how DIP1027 does not prevent an injection 
> attack.

```d
// mock SQL
import std.format, std.variant;
class Sqlite{
     this(string){}
     Sqlite query(string command,scope Variant[int] args=null){
         writeln("EXECUTING");
         writeln(command);
         if(args.length){
             writeln("ARGS:");
             foreach(k,v;args){
                 if(v!=Variant.init)
                     writefln(i"?$k = ($v)");
             }
         }
         writeln("DONE");
         return this;
     }
     struct Row{ int opIndex(int i){ return 0; } }
     int opApply(scope int delegate(Row) dg){
         writeln("ITERATING OVER ROWS");
         return 0;
     }
}
struct Statement{
     Sqlite db;
     string query;
     Variant[int] args;
     void bind(T)(int i,T arg){
         args[i]=Variant(arg);
     }
     void execute(){
         db.query(query,args);
     }
}

auto execi(Args...)(Sqlite db, Args args) {
     // sqlite lets you do ?1, ?2, etc

     string query = () { // note: parsing done at runtime
         string sql;
         int number;
         import std.conv;
         auto fmt = args[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();
}

import std.stdio;

void main() {
     auto db = new Sqlite(":memory:");
     db.query("CREATE TABLE Students (id INTEGER, name TEXT)");

     // you might think this is sql injection... and you'd be right! the lib
     // cannot use rich metadata because it is not provided by the istring
     // therefore, it cannot verify that the user didn't construct the
     // query themselves in an unsafe way
     int id = 1;
     string name = "Robert'); DROP TABLE Students;--";
     db.execi(i"INSERT INTO sample VALUES ($(id), '$(name)')".format);

     foreach(row; db.query("SELECT * from sample"))
         writeln(row[0], ": ", row[1]);
}
```

Prints:
EXECUTING
CREATE TABLE Students (id INTEGER, name TEXT)
DONE
EXECUTING
INSERT INTO sample VALUES (1, 'Robert'); DROP TABLE Students;--')
DONE
EXECUTING
SELECT * from sample
DONE
ITERATING OVER ROWS


https://xkcd.com/327/