On Thu, Sep 12, 2019 at 07:29:17AM -0500, xxxxxx@ancell-ent.com wrote:
> Hiding the concept of prepared statements, which I'm not sure are so
> important nowadays, but I've got to research this, its been decades
> since I had to deal with this issue, and the world has fundamentally
> changed.  "Cache is the new RAM, RAM is the new disk, disk is the new
> tape", or at least this is true of hard disks, and of course flash
> has also fundamentally changed the game.

[...]

> Preventing statement injection in data; while this violates the
> plumbing focus, I believe it should be enforced at the lowest level
> so every use of this set of SRFIs and libraries benefits from it.

Prepared statements are often touted as the "only way" to prevent
injection attacks because data is sent separate from the query.

This is true for many database APIs but there are some APIs which
allow sending the data separately from the query without going
through a prepared statement.

I found this "common wisdom" so annoying I made mention of it in a
section of a ranty blog post of mine[1] regarding injection bugs.
And I do believe a good API will make it as convenient as possible
to avoid injection bugs.

In the postgresql egg:

(row-values (query conn "SELECT $1, $2" "hi" "there")) => ("hi" "there")

This is sent in one roundtrip to the database without the use of an
intermediate prepared statement.

The freetds egg[2] which I co-wrote also does this, but it implements
it via a hidden prepared statement.  That egg does not provide a way
to create prepared statements directly.  That's possibly an oversight
because creating prepared statements and executing them more often can
be a performance benefit (it avoids parsing the SQL over and over).

I don't know how often this is needed or how big of a benefit it really
is, though.  In Postgres, prepared statements are actually something that
exists at the SQL level and they must be named.  These can then be
executed with a separate C-level API.  In the egg I decided to abstract
this a little bit by having the user manually create them with the name,
and then you can use (query conn 'prepname "foo" "bar") to execute the
prepared statement "prepname" with arguments "foo" and "bar".  This
reduces the number of procedures needed.  Perhaps later I will supply
a with-prepared-statement procedure which automatically drops the
statement when the procedure is done.

[1] https://www.more-magic.net/posts/structurally-fixing-injection-bugs.html#pervasive-insecurity-through-bad-design
[2] https://wiki.call-cc.org/eggref/4/freetds

Cheers,
Peter