>> We just have to ban
>> braces in the placeholder name itself, so '|{foo}| and '|{foo| and '|foo}|
>> are not valid placeholder symbols.
>
> Possible, but not required, I'd say.

Putting {} in the symbol names does look like deliberately shooting
oneself in the foot :)

>>> I could see some problems with this if the user would incorrectly quote
>>> their expressions.  In the case of
>>>
>>>        (sql-execute
>>>         "INSERT INTO foo VALUES ('{myval1}', '{myval2}'::text[])"
>>>         '((myval1 . "hi there")))
>>>
>>> it would get translated into the following, because we're doing "dumb"
>>> replacement:
>>>
>>>      "INSERT INTO foo VALUES ('$1', '{myval2}'::text[])"
>>>
>>> or in MySQL:
>>>
>>>      "INSERT INTO foo VALUES ('?', '{myval2}'::text[])"
>>>
>>> With a positional parameter of 'hi there', which will hopefully give
>>> an error message.
>>
>> Good point. So in this case, the DB engine would not parse any parameter
>> names from the SQL query, but we would send it one parameter anyway. I.e.
>> the same thing as calling a procedure with too many arguments in a
>> programming language.
>
> Yeah.  There might be engines that don't care about this, for instance
> it is possible in Postgres to do
>
>      (query "SELECT $2, $3" 1 2 3)
>
> It won't give an error about $1 not being used, but it does give an error
> about not being able to figure out the type of parameter 1.  I don't know
> if it will _always_ do this, though.

For the love of god :D With this design mentality across the board, it's
a wonder most websites aren't pwned by now.

>> One more thing that bothers me is that in (sql-execute sql params), the sql
>> string and params list can come from anywhere. [...]
>>
>> In Scheme, this might be a good place for a macro so we can guarantee that
>> the query is a literal string, we can parse the placeholders from the string
>> template at macroexpansion time, can parse the parameter names (which must
>> be symbols known at macroexpansion time), and can check that the set of
>> placeholders exactly matches the set of symbols.
>
> I would definitely vote against this idea.  Consider the basic case of
> wanting to dynamically build a query (for example, WHERE filters passed
> in from somewhere).  In my personal experience, it is VERY rare to have
> completely static strings where only the parameters are dynamic.  Maybe
> only in extremely simple statements, but that's a case I don't think is
> worth optimising for.
>
>> There could be a low-level (sql-execute-raw ...) that is a procedure instead
>> of a macro.
>
> Sounds ugly to me.

It's true that the necessity of concatenating SQL snippets comes up
sooner than we'd like. IMHO that kind of practice is bonkers in a
high-level language. It's unsafe, and even where it's safe the code
quickly turns unreadable.

I would solve this by having a set of SQL combinators that can be used
to safely compose whole statements from reusable parts. Something like
Roda in Ruby is a good example. Any way of doing it with string
concatenation just turns into misery. So it's best to hide the string
concatenation behind a DSL.

IIRC you mentioned earlier that there are problems with such DSLs not
covering advanced features of particular DB engines. This may have to be
handled by having some kind of (verbatim ...) escape hatch in the DSL to
inject (no pun intended) arbitrary SQL.

In light of such a DSL, maybe you're right that the templating macro is
too fancy. But I'd still prefer any "raw strings with magic templating"
API to have some kind of name that hints "please only use this if you
know what you are doing".