QSqlQuery prepared statment using QString as a bind value

sairun

I'm trying to prepare and exec the following SQL statement to check the existence of some IDs in a SQLite3 database

QString idList = "(289455),(241326),(212694),(212676),...";

QSqlQuery q( db );
        q.prepare( "WITH cte( id ) AS (VALUES :val) "
                            " SELECT id FROM cte LEFT JOIN records t" 
                            " ON t.rid = cte.id WHERE t.rid IS NULL");
        q.bindValue( ":val", idList  );

The idList may have 1 up to 500 ids, each within parenthesis, to be used in a Common Table Expression (CTE). Apparently this construct doesn't work. idList is passed as a QVariant and somewhow it doesn't get replaced as a regular string in the query. The result is always a "Parameter count mismatch" error!

The solution is to build the query as a QString by concatenating the parts and executing it. But I wonder why the prepared query doesn't work in this case. I've used this type of construct to bind string values elsewhere, such as passwords or user names. Is there a size limit for the values passed to a prepared query?

JonB

@sairun
For bound variables you can only use what the driver actually accepts. This does not mean you can use them "anywhere", e.g. as though it was a "textual substitution". I don't know what SQLite actually accepts for its VALUES syntax, but I very much doubt it allows any single variable in (VALUES :val). It is likely to want each item/element passed in separately. You might try with idList being a QStringList rather than a single QString, but again I doubt it will accept that either.

As a similar example, if you wanted to generate a clause like

WHERE something IN ( val1, val2, val3, ...)

I know that you cannot do that via IN ( :vals ) for the same reason.

Either generate the whole thing with as many separate variables as items and as many separate binds or generate a complete literal string as should be passed to SQLite for the statement without variables/bindings.

sairun

Thanks, I suspect that trying to pass the idList as a literal string it gets quoted autommatically which does not work for the CTE. I think I'm not even going to try the QStringList variant. I'll just build a query string by concatenation and use it.

JonB

@sairun
Indeed. When building a string of a complete SQL statement without variable bindings it is your responsibility to verify any user input involved for potential SQL injection. If your ids are all stored as integers you should be OK.

Christian Ehrlicher

@sairun said in QSqlQuery prepared statment using QString as a bind value:

somewhow it doesn't get replaced as a regular string in the query.

This would completely contradict the prepared query idiom... https://en.wikipedia.org/wiki/Prepared_statement