[bug#30386] [PATCH v2 cuirass] database: Prevent SQL injection.

[ludo@gnu.org (Ludovic Courtès)]

Danny Milosavljevic <dannym@scratchpost.org> skribis:

>> optimization looks good, provided the extra conditions don’t make sqlite
>> slower.  
>
> Compared to parsing the SQL text again and again (which is dead slow), I think
> an extra NULL check *on the same field* is not going to matter at all.
>
> Even compared to using lots of main memory and thus not being able to use
> the processor's cache (if we had lots of prepared statements), I think an
> extra NULL check is still better :)
>
> Of course once we have a lot of data in the tables, the actual lookup costs
> will dwarf any setup costs.  Then still, it's checking the same field that's
> used anyway, so the extra cost should be neglible.

Sounds good, let’s do that then.

>> It might allow us to use ‘sqlite-exec’ directly, and thus
>> benefit from the binding support in there, as in:
>> 
>>   (sqlite-exec db "… WHERE " id " is NULL or …")
>
> I added sqlite-bind-arguments with keyword arguments specifically so sqlite-exec
> doesn't suck.
>
> So it would be like (sqlite-exec db "SELECT … :a … :b … :a"
>                                     #:a 42
>                                     #:b 2)
>
> Before, it was:
>
> (sqlite-exec db "SELECT … ? … ? … ?"
>                 42
>                 2
>                 42)

Right, but now it’s as I wrote above: you can include arguments in the
middle of the SQL strings, and ‘sqlite-exec’ takes care of turning
that into question marks and so on:

  https://git.savannah.gnu.org/cgit/guix/guix-cuirass.git/commit/?id=b0c39b31f61cfc494e0dfbe823b3fe4275efbc7a

Anyway, we can support both the keyword style you show above, and the
other thing I mention, and use whichever is most convenient for the code
at hand.

I find the ‘sqlite-exec’ convenient for simple cases where the query is
a literal, but the keyword style might be more convenient for complex
queries like ‘db-get-builds’.

Thanks,
Ludo’.