On Wed, Sep 18, 2019 at 11:48:30AM +0300, Lassi Kortela wrote:
> Personally, I find it a bit questionable to pass implicitly numbered
> parameters to SQL statement instead of explicitly named parameters. It
> starts with things like this:
>
> (sql-execute "insert into foo values (?, ?, ?, ?)"
>              (list "Thing" -123 45.67 other-stuff))
>
> And rapidly gets worse, especially with big WHERE clauses in selects etc.
>
> Would it be possible to support named parameters only, or are there some DB
> engines or scenarios where those don't fly?

Named params are nice, but require a translation step so we need to
consider the syntax (how to escape and so on).  On the other hand,
anything "common" would require a translation step.  AFAIK most
databases use positional arguments with question marks like in your
example, but Postgres does not support that syntax, it only has $1, $2
and so forth.  The nice thing about that is that if a parameter repeats,
it needs to be sent only once.  But it's not quite a named parameter.

So I would say if we can, use named parameters.  These can be translated
under the hood to question marks (and repeated in the bindings, if
necessary) or numbered arguments.

The real issue with any common syntax we want to support is translation.
There must be a way to escape them.  And I'm not sure how that works
with quoted arguments like SELECT 'Que pasa?'; should not be converted
to SELECT 'Que pasa$1' in Postgres.  So in order to do this correctly,
you need some sort of rudimental SQL parser?!  I don't know how other
languages do this.

Apparently PHP's PDO totally botches this (no surprise there):
https://stackoverflow.com/questions/16311939/how-to-prevent-pdo-from-interpreting-a-question-mark-as-a-placeholder

We could use a really really stupid approach and simply replace any
placeholder with its exact string.

So for example

(sql-execute "INSERT INTO foo VALUES (:myval1, :myval2)"
             '((:myval1 . 1) (:myval2 . 2)))

If the query gets messed up the user should simply choose better
placeholders.

If we want to do it the way some language drivers do, you end up with
issues because the colon is used for casting in Postgres.  So:

(sql-execute "INSERT INTO foo VALUES (:myval1::int, :myval2::int)"
             '((myval1 . 1) (myval2 . 2)))

might result in an error because ":int" is not bound to a value.  A simple
solution would be to only replace the variables in the alist, but then
you can't give an error when some variables are forgotten to be bound.
Of course, the SQL engine will give an error in such a case...

I see PDO actually includes an SQL parser in order to be able to replace
their questionmark-based parameter handling:
https://github.com/php/php-src/blob/master/ext/pdo/pdo_sql_parser.re#L51

I don't really want to go down this route, it is too complicated for a
straightforward database abstraction.  And as the Stack Overflow post
showed, it's too brittle to get right (SQL is a pain to parse, especially
with all the extensions that various dialects throw onto it).

If you search the web for "postgresql question mark placeholders", you
see several posts on the Postgres mailing list of people struggling with
various language bindings which try to do this question mark
normalisation, which fails.  It's even so that Postgres manual includes
a note in the documentation that you must escape bare question marks in
SQL statements in the JDBC driver:
https://jdbc.postgresql.org/documentation/head/statement.html

Yay, another can of worms!

Cheers,
Peter