[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[bug#30386] [PATCH v2 cuirass] database: Prevent SQL injection.
From: |
Ludovic Courtès |
Subject: |
[bug#30386] [PATCH v2 cuirass] database: Prevent SQL injection. |
Date: |
Fri, 09 Feb 2018 17:05:34 +0100 |
User-agent: |
Gnus/5.13 (Gnus v5.13) Emacs/25.3 (gnu/linux) |
Danny Milosavljevic <address@hidden> 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’.