Using SQLite's IN condition in an QSqlQuery
-
Hi all,
is there an official way to bind a list of values to be used in a
SELECT ... FROM ... WHERE ... IN (...)query?The only functioning way I found was to assemble a query with something like
auto query = QStringLiteral("SELECT something FROM somewhere WHERE value IN (?"); for (int i = 1; i < list.count(); i++) { query.append(QStringLiteral(",?")); } query.append(QStringLiteral(")"));and then bind all the values with something like
sqlQuery.prepare(query); for (const auto &value : list) { query.bindValue(value); }Is there a way to do this without having to assemble the query first with the correct number of
?s`?Thanks for all help :-)
-
Hi all,
is there an official way to bind a list of values to be used in a
SELECT ... FROM ... WHERE ... IN (...)query?The only functioning way I found was to assemble a query with something like
auto query = QStringLiteral("SELECT something FROM somewhere WHERE value IN (?"); for (int i = 1; i < list.count(); i++) { query.append(QStringLiteral(",?")); } query.append(QStringLiteral(")"));and then bind all the values with something like
sqlQuery.prepare(query); for (const auto &value : list) { query.bindValue(value); }Is there a way to do this without having to assemble the query first with the correct number of
?s`?Thanks for all help :-)
@l3u_
For SQLINyou cannot use "variable binding", whether via?or named parameters for the whole list. You can do it your way for each individual parameter I guess, though I haven't seen others do it. Normally you have to construct the whole of the text to go inside the(...)yourself, as a (single C++) string. I suppose your way gives you a little more protection for quoting each parameter if you want to do it that way. -
Hi all,
is there an official way to bind a list of values to be used in a
SELECT ... FROM ... WHERE ... IN (...)query?The only functioning way I found was to assemble a query with something like
auto query = QStringLiteral("SELECT something FROM somewhere WHERE value IN (?"); for (int i = 1; i < list.count(); i++) { query.append(QStringLiteral(",?")); } query.append(QStringLiteral(")"));and then bind all the values with something like
sqlQuery.prepare(query); for (const auto &value : list) { query.bindValue(value); }Is there a way to do this without having to assemble the query first with the correct number of
?s`?Thanks for all help :-)
-
I didn't want to assemble the
INcondition string into a QString or such, because in this case, it's about querying for names (QStrings). Thus I would have to take care about proper escaping, and there's no "escape" function. However, I found how it's done internally here: https://forum.qt.io/post/387435So maybe, another option would be to assemble the "in" string name by name by escaping it, maybe storing the result in a QStringList and joining it by a comma. Question is what would be the better/faster approach …
-
What's wrong with your current solution? Everything else will not work/vulnerable to sql injection.
-
There's nothing wrong with it, and the very fact that I don't want to be vulnerable to an SQL injection made me think about doing it this way in the first place.
However, using the escaping from said post is after all exactly what happens when binding a value. So I just wanted to point out that escaping the values manually and joining them to a string which then can be used as-is in the
WHERE ... INclause would be another option.I don't think it would have much benefit though (would it?!).
-
There's nothing wrong with it, and the very fact that I don't want to be vulnerable to an SQL injection made me think about doing it this way in the first place.
However, using the escaping from said post is after all exactly what happens when binding a value. So I just wanted to point out that escaping the values manually and joining them to a string which then can be used as-is in the
WHERE ... INclause would be another option.I don't think it would have much benefit though (would it?!).
@l3u_ said in Using SQLite's IN condition in an QSqlQuery:
However, using the escaping from said post is after all exactly what happens when binding a value
This is wrong.
Qt Online Installer direct download: https://download.qt.io/official_releases/online_installers/
Visit the Qt Academy at https://academy.qt.io/catalog -
@l3u_ said in Using SQLite's IN condition in an QSqlQuery:
However, using the escaping from said post is after all exactly what happens when binding a value
This is wrong.
@Christian-Ehrlicher said in Using SQLite's IN condition in an QSqlQuery:
This is wrong.
@VRonin posted Qt code at https://forum.qt.io/post/387420 which shows what happens "inside" when a named binding placeholder is replaced with the actual value. And it's exactly what has been posted as an "escape" solution. Could you explain what's wrong exactly?
-
@Christian-Ehrlicher said in Using SQLite's IN condition in an QSqlQuery:
This is wrong.
@VRonin posted Qt code at https://forum.qt.io/post/387420 which shows what happens "inside" when a named binding placeholder is replaced with the actual value. And it's exactly what has been posted as an "escape" solution. Could you explain what's wrong exactly?
@l3u_ said in Using SQLite's IN condition in an QSqlQuery:
inside" when a named binding placeholder is replaced with the actual value
There is no named placeholder replaced with the actual value but with a positional placeholder.This is a fallback for a driver which does not support binding - so basically not used by any of the Qt drivers.
-
@Christian-Ehrlicher said in Using SQLite's IN condition in an QSqlQuery:
This is wrong.
@VRonin posted Qt code at https://forum.qt.io/post/387420 which shows what happens "inside" when a named binding placeholder is replaced with the actual value. And it's exactly what has been posted as an "escape" solution. Could you explain what's wrong exactly?
@l3u_
You gain nothing for your case by moving to named parameters. Since you prefer not to build the string yourself with SQL escaping you already have a working solution, per your first post. Assuming you have tested it works OK. That is fine to use and gives you the Qt call to the driver's parameter passing/value formatting, so what is the problem/you can use that? -
Yeah, of course, I'll use that! My initial and only question was if this is actually really the only way to achieve what I want beacause it seemed a bit clumsy to me to first assemble the IN clause with placeholders and then replace them with the real thing parameter by parameter. Everything is fine if it's correct to do it this way ;-)
-
Yeah, of course, I'll use that! My initial and only question was if this is actually really the only way to achieve what I want beacause it seemed a bit clumsy to me to first assemble the IN clause with placeholders and then replace them with the real thing parameter by parameter. Everything is fine if it's correct to do it this way ;-)
@l3u_
All the binders I have used do not have support for the "list of values" required by anINand some other SQL statements. One would like to be able to pass a list (strings, integers) and have the driver be able pass it to the backend or deal with it for you. But they don't so you either build it yourself or use your method to make a generated-list of?s.
