On 18/09/2019 10:13, Peter Bex wrote:

> 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.

Eargh!

> 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

Eurgh!

> 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).

Oh yes. I've had maintain an SQL parser. :-|

Here's an ugly but safe option to consider - pass in the query as a list
of strings and symbols, where the symbols are to be replaced by
appropriate DB-specific magic and the strings stitched together around them:

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

However, the later suggestion of {myval1} also looks good!

But I always prefer the SSQL approach to this of just shoving scheme
values in using quasiquoting:

(sql-execute `(insert (into foo)
                      (values #(,myval1 ,myval2)))

>
> Cheers,
> Peter
>

--
Alaric Snell-Pym   (M7KIT)
http://www.snell-pym.org.uk/alaric/