Re: [Cppcms-users] CppDB: SQL Parameter for IN clause

[Artyom B. <art...@gm...>]

First of all sorry for delayed reply - was offline for a while.

>
> I do not simply add this question as a feature request to CppDB project
> site because I am not sure whether or not I am asking too much or there
> actually are workarounds of this issue.
>
> Say I have this SQL:
>
> SELECT 1 FROM t WHERE c IN (?)
>
> Users might enter the following values for the "?" inside the
> parentheses:
>
> 'a','b','c'
> 2,3,4
>
> and expect they faithfully replace the question mark as they are.

That is wrong assumption.

The ? is translated to a placeholder of the specific library in Sqlite
and MySQL it
would be "?" and in PosgreSQL it would be AFAIR $1, $2 etc.

Than you bind a value to a single element so if you bind a string like
'a','b','c' it
would effectively be IN('''a''.''b''.''c''') i.e. a single string.

>
> However, if I am correct, working with PostgreSQL libpq, the current
> implementation of CppDB and another database connection product not
> belonging to CppCMS products tend to incorrectly send the final SQL to
> PostgreSQL server like so:
>
> SELECT 1 FROM t WHERE c IN ('<v>')
>
> , where <v> is a character string which can be one of the following two
> values
>
> 'a','b','c'
> 2,3,4
>
> with all single quotes properly escaped, if any. Although the ultimate
> SQL is valid, it yields wrong query result.
>

Note the parameters aren't "injected" to query directly but rather binder
by a library to a statement.

> How do you folks handle such corner case? Is this issue supposed to be
> handled on application level or database connection component level?

What I did in such a case I build manually a query with several placeholders
according to number of iterms in IN clause and binded prameters later i.


sql << "SELECT 1 FROM t WHERE c IN (?)" << 1 ;
sql << "SELECT 1 FROM t WHERE c IN (?,?)" << 1 << 2;

and so on.

Currently there is no special placeholder for a variable number of
parameters in CppDB

Artyom