From: Shane Synan Date: Wed, 6 Feb 2019 23:37:01 +0000 (-0500) Subject: core: Workaround Qt 4 SQL bindValue() duplicates X-Git-Tag: 0.13.1~4 X-Git-Url: https://git.quassel-irc.org/?p=quassel.git;a=commitdiff_plain;h=85087a839d89dc53ed71b41e12244e57f21f67b1;hp=3c910a417a72dfa529ad514d9a616139b7d57bab core: Workaround Qt 4 SQL bindValue() duplicates Workaround Qt 4 QSqlQuery::bindValue() not handling duplicate parameter names by giving every duplicated parameter a unique name. Qt 5 handles this as one expects, so this doesn't need forward-ported to 0.14/master where Qt 4 support has been dropped. Applies to SQLite and PostgreSQL. Note: PostgreSQL prepared statements properly handle repeated "$" parameters, so there's no need to modify those. Test case for prepared statements: > PREPARE qcore_test_var AS SELECT * FROM buffer WHERE bufferid = $1 AND userid = $1 AND networkid = $1; > EXECUTE qcore_test_var(1); Gives a result if bufferid = userid = networkid = 1 exists. Credit to @justJanne for finding the root cause of the issue, and suggesting the fix, and to 'galfwender' and @darkstar for reporting. Fixes #1506 See https://doc.qt.io/archives/qt-4.8/qsqlquery.html#bindValue And https://doc.qt.io/qt-5/qsqlquery.html#bindValue --- diff --git a/src/core/SQL/PostgreSQL/select_messagesAllNew_filtered.sql b/src/core/SQL/PostgreSQL/select_messagesAllNew_filtered.sql index 780263fc..2bf44e2c 100644 --- a/src/core/SQL/PostgreSQL/select_messagesAllNew_filtered.sql +++ b/src/core/SQL/PostgreSQL/select_messagesAllNew_filtered.sql @@ -4,6 +4,6 @@ JOIN sender ON backlog.senderid = sender.senderid WHERE backlog.bufferid IN (SELECT bufferid FROM buffer WHERE userid = :userid) AND backlog.messageid >= :firstmsg AND backlog.type & :type != 0 - AND (:flags = 0 OR backlog.flags & :flags != 0) + AND (:flags = 0 OR backlog.flags & :flagsDup1 != 0) ORDER BY messageid DESC -- Unlike SQLite, no LIMIT clause, mimicking the unfiltered version - investigate later..? diff --git a/src/core/SQL/PostgreSQL/select_messagesAll_filtered.sql b/src/core/SQL/PostgreSQL/select_messagesAll_filtered.sql index 3e1ce655..1eb6278c 100644 --- a/src/core/SQL/PostgreSQL/select_messagesAll_filtered.sql +++ b/src/core/SQL/PostgreSQL/select_messagesAll_filtered.sql @@ -5,6 +5,6 @@ WHERE backlog.bufferid IN (SELECT bufferid FROM buffer WHERE userid = :userid) AND backlog.messageid >= :firstmsg AND backlog.messageid < :lastmsg AND backlog.type & :type != 0 - AND (:flags = 0 OR backlog.flags & :flags != 0) + AND (:flags = 0 OR backlog.flags & :flagsDup1 != 0) ORDER BY messageid DESC -- Unlike SQLite, no LIMIT clause, mimicking the unfiltered version - investigate later..? diff --git a/src/core/SQL/PostgreSQL/select_messagesNewerThan_filtered.sql b/src/core/SQL/PostgreSQL/select_messagesNewerThan_filtered.sql index 46b4d47b..5adf5fa0 100644 --- a/src/core/SQL/PostgreSQL/select_messagesNewerThan_filtered.sql +++ b/src/core/SQL/PostgreSQL/select_messagesNewerThan_filtered.sql @@ -2,9 +2,9 @@ SELECT messageid, time, type, flags, sender, senderprefixes, realname, avatarur FROM backlog JOIN sender ON backlog.senderid = sender.senderid WHERE backlog.messageid >= :first - AND backlog.messageid <= (SELECT buffer.lastmsgid FROM buffer WHERE buffer.bufferid = :buffer) + AND backlog.messageid <= (SELECT buffer.lastmsgid FROM buffer WHERE buffer.bufferid = :bufferDup1) AND bufferid = :buffer AND backlog.type & :type != 0 - AND (:flags = 0 OR backlog.flags & :flags != 0) + AND (:flags = 0 OR backlog.flags & :flagsDup1 != 0) ORDER BY messageid DESC LIMIT :limit diff --git a/src/core/SQL/PostgreSQL/select_messagesNewestK_filtered.sql b/src/core/SQL/PostgreSQL/select_messagesNewestK_filtered.sql index cdb960c7..af3e2f66 100644 --- a/src/core/SQL/PostgreSQL/select_messagesNewestK_filtered.sql +++ b/src/core/SQL/PostgreSQL/select_messagesNewestK_filtered.sql @@ -2,8 +2,8 @@ SELECT messageid, time, type, flags, sender, senderprefixes, realname, avatarur FROM backlog JOIN sender ON backlog.senderid = sender.senderid WHERE bufferid = :buffer - AND backlog.messageid <= (SELECT buffer.lastmsgid FROM buffer WHERE buffer.bufferid = :buffer) + AND backlog.messageid <= (SELECT buffer.lastmsgid FROM buffer WHERE buffer.bufferid = :bufferDup1) AND backlog.type & :type != 0 - AND (:flags = 0 OR backlog.flags & :flags != 0) + AND (:flags = 0 OR backlog.flags & :flagsDup1 != 0) ORDER BY messageid DESC LIMIT :limit diff --git a/src/core/SQL/PostgreSQL/select_messagesRange_filtered.sql b/src/core/SQL/PostgreSQL/select_messagesRange_filtered.sql index 6030e532..ef1c1d2e 100644 --- a/src/core/SQL/PostgreSQL/select_messagesRange_filtered.sql +++ b/src/core/SQL/PostgreSQL/select_messagesRange_filtered.sql @@ -5,6 +5,6 @@ WHERE backlog.messageid >= :first AND backlog.messageid < :last AND bufferid = :buffer AND backlog.type & :type != 0 - AND (:flags = 0 OR backlog.flags & :flags != 0) + AND (:flags = 0 OR backlog.flags & :flagsDup1 != 0) ORDER BY messageid DESC LIMIT :limit diff --git a/src/core/SQL/SQLite/select_messagesAllNew_filtered.sql b/src/core/SQL/SQLite/select_messagesAllNew_filtered.sql index 1a742b8a..7749c4cc 100644 --- a/src/core/SQL/SQLite/select_messagesAllNew_filtered.sql +++ b/src/core/SQL/SQLite/select_messagesAllNew_filtered.sql @@ -4,6 +4,6 @@ JOIN sender ON backlog.senderid = sender.senderid WHERE backlog.bufferid IN (SELECT bufferid FROM buffer WHERE userid = :userid) AND backlog.messageid >= :firstmsg AND backlog.type & :type != 0 - AND (:flags = 0 OR backlog.flags & :flags != 0) + AND (:flags = 0 OR backlog.flags & :flagsDup1 != 0) ORDER BY messageid DESC LIMIT :limit diff --git a/src/core/SQL/SQLite/select_messagesAll_filtered.sql b/src/core/SQL/SQLite/select_messagesAll_filtered.sql index c09be6b3..b4f91f2b 100644 --- a/src/core/SQL/SQLite/select_messagesAll_filtered.sql +++ b/src/core/SQL/SQLite/select_messagesAll_filtered.sql @@ -5,6 +5,6 @@ WHERE backlog.bufferid IN (SELECT bufferid FROM buffer WHERE userid = :userid) AND backlog.messageid >= :firstmsg AND backlog.messageid < :lastmsg AND backlog.type & :type != 0 - AND (:flags = 0 OR backlog.flags & :flags != 0) + AND (:flags = 0 OR backlog.flags & :flagsDup1 != 0) ORDER BY messageid DESC LIMIT :limit diff --git a/src/core/SQL/SQLite/select_messagesNewerThan.sql b/src/core/SQL/SQLite/select_messagesNewerThan.sql index 5040b9e2..6ccf81da 100644 --- a/src/core/SQL/SQLite/select_messagesNewerThan.sql +++ b/src/core/SQL/SQLite/select_messagesNewerThan.sql @@ -2,7 +2,7 @@ SELECT messageid, time, type, flags, sender, senderprefixes, realname, avatarur FROM backlog JOIN sender ON backlog.senderid = sender.senderid WHERE backlog.messageid >= :firstmsg - AND backlog.messageid <= (SELECT buffer.lastmsgid FROM buffer WHERE buffer.bufferid = :bufferid) + AND backlog.messageid <= (SELECT buffer.lastmsgid FROM buffer WHERE buffer.bufferid = :bufferidDup1) AND bufferid = :bufferid ORDER BY messageid DESC LIMIT :limit diff --git a/src/core/SQL/SQLite/select_messagesNewerThan_filtered.sql b/src/core/SQL/SQLite/select_messagesNewerThan_filtered.sql index 198df7c2..df6b75eb 100644 --- a/src/core/SQL/SQLite/select_messagesNewerThan_filtered.sql +++ b/src/core/SQL/SQLite/select_messagesNewerThan_filtered.sql @@ -2,9 +2,9 @@ SELECT messageid, time, type, flags, sender, senderprefixes, realname, avatarur FROM backlog JOIN sender ON backlog.senderid = sender.senderid WHERE backlog.messageid >= :firstmsg - AND backlog.messageid <= (SELECT buffer.lastmsgid FROM buffer WHERE buffer.bufferid = :bufferid) + AND backlog.messageid <= (SELECT buffer.lastmsgid FROM buffer WHERE buffer.bufferid = :bufferidDup1) AND bufferid = :bufferid AND backlog.type & :type != 0 - AND (:flags = 0 OR backlog.flags & :flags != 0) + AND (:flags = 0 OR backlog.flags & :flagsDup1 != 0) ORDER BY messageid DESC LIMIT :limit diff --git a/src/core/SQL/SQLite/select_messagesNewestK.sql b/src/core/SQL/SQLite/select_messagesNewestK.sql index eb4af1f2..29c0eac2 100644 --- a/src/core/SQL/SQLite/select_messagesNewestK.sql +++ b/src/core/SQL/SQLite/select_messagesNewestK.sql @@ -2,6 +2,6 @@ SELECT messageid, time, type, flags, sender, senderprefixes, realname, avatarur FROM backlog JOIN sender ON backlog.senderid = sender.senderid WHERE bufferid = :bufferid -AND backlog.messageid <= (SELECT buffer.lastmsgid FROM buffer WHERE buffer.bufferid = :bufferid) +AND backlog.messageid <= (SELECT buffer.lastmsgid FROM buffer WHERE buffer.bufferid = :bufferidDup1) ORDER BY messageid DESC LIMIT :limit diff --git a/src/core/SQL/SQLite/select_messagesNewestK_filtered.sql b/src/core/SQL/SQLite/select_messagesNewestK_filtered.sql index b4411af5..49515b4d 100644 --- a/src/core/SQL/SQLite/select_messagesNewestK_filtered.sql +++ b/src/core/SQL/SQLite/select_messagesNewestK_filtered.sql @@ -2,8 +2,8 @@ SELECT messageid, time, type, flags, sender, senderprefixes, realname, avatarur FROM backlog JOIN sender ON backlog.senderid = sender.senderid WHERE bufferid = :bufferid -AND backlog.messageid <= (SELECT buffer.lastmsgid FROM buffer WHERE buffer.bufferid = :bufferid) +AND backlog.messageid <= (SELECT buffer.lastmsgid FROM buffer WHERE buffer.bufferid = :bufferidDup1) AND backlog.type & :type != 0 -AND (:flags = 0 OR backlog.flags & :flags != 0) +AND (:flags = 0 OR backlog.flags & :flagsDup1 != 0) ORDER BY messageid DESC LIMIT :limit diff --git a/src/core/SQL/SQLite/select_messagesRange_filtered.sql b/src/core/SQL/SQLite/select_messagesRange_filtered.sql index d1e4c0f1..d53d5aef 100644 --- a/src/core/SQL/SQLite/select_messagesRange_filtered.sql +++ b/src/core/SQL/SQLite/select_messagesRange_filtered.sql @@ -5,6 +5,6 @@ WHERE bufferid = :bufferid AND backlog.messageid >= :firstmsg AND backlog.messageid < :lastmsg AND backlog.type & :type != 0 - AND (:flags = 0 OR backlog.flags & :flags != 0) + AND (:flags = 0 OR backlog.flags & :flagsDup1 != 0) ORDER BY messageid DESC LIMIT :limit diff --git a/src/core/postgresqlstorage.cpp b/src/core/postgresqlstorage.cpp index 50871b56..9ed72e36 100644 --- a/src/core/postgresqlstorage.cpp +++ b/src/core/postgresqlstorage.cpp @@ -1909,13 +1909,18 @@ QList PostgreSqlStorage::requestMsgsFiltered(UserId user, BufferId buff QSqlQuery query(db); if (last == -1 && first == -1) { query.prepare(queryString("select_messagesNewestK_filtered")); + // Workaround for Qt 4 QSqlQuery::bindValue() not supporting repeated placeholder names + query.bindValue(":bufferDup1", bufferId.toInt()); } else if (last == -1) { query.prepare(queryString("select_messagesNewerThan_filtered")); query.bindValue(":first", first.toQint64()); + // Workaround for Qt 4 QSqlQuery::bindValue() not supporting repeated placeholder names + query.bindValue(":bufferDup1", bufferId.toInt()); } else { query.prepare(queryString("select_messagesRange_filtered")); query.bindValue(":last", last.toQint64()); query.bindValue(":first", first.toQint64()); + // Workaround for Qt 4 QSqlQuery::bindValue() not needed, only has one ":buffer" } query.bindValue(":buffer", bufferId.toInt()); query.bindValue(":limit", limit); @@ -1923,6 +1928,8 @@ QList PostgreSqlStorage::requestMsgsFiltered(UserId user, BufferId buff query.bindValue(":type", typeRaw); int flagsRaw = flags; query.bindValue(":flags", flagsRaw); + // Workaround for Qt 4 QSqlQuery::bindValue() not supporting repeated placeholder names + query.bindValue(":flagsDup1", flagsRaw); safeExec(query); if (!watchQuery(query)) { @@ -2045,6 +2052,8 @@ QList PostgreSqlStorage::requestAllMsgsFiltered(UserId user, MsgId firs int flagsRaw = flags; query.bindValue(":flags", flagsRaw); + // Workaround for Qt 4 QSqlQuery::bindValue() not supporting repeated placeholder names + query.bindValue(":flagsDup1", flagsRaw); safeExec(query); if (!watchQuery(query)) { diff --git a/src/core/sqlitestorage.cpp b/src/core/sqlitestorage.cpp index 8a0b2bc0..8c2df4b0 100644 --- a/src/core/sqlitestorage.cpp +++ b/src/core/sqlitestorage.cpp @@ -1979,15 +1979,20 @@ QList SqliteStorage::requestMsgs(UserId user, BufferId bufferId, MsgId QSqlQuery query(db); if (last == -1 && first == -1) { query.prepare(queryString("select_messagesNewestK")); + // Workaround for Qt 4 QSqlQuery::bindValue() not supporting repeated placeholder names + query.bindValue(":bufferidDup1", bufferId.toInt()); } else if (last == -1) { query.prepare(queryString("select_messagesNewerThan")); query.bindValue(":firstmsg", first.toQint64()); + // Workaround for Qt 4 QSqlQuery::bindValue() not supporting repeated placeholder names + query.bindValue(":bufferidDup1", bufferId.toInt()); } else { query.prepare(queryString("select_messagesRange")); query.bindValue(":lastmsg", last.toQint64()); query.bindValue(":firstmsg", first.toQint64()); + // Workaround for Qt 4 QSqlQuery::bindValue() not needed, only has one ":bufferid" } query.bindValue(":bufferid", bufferId.toInt()); query.bindValue(":limit", limit); @@ -2054,15 +2059,20 @@ QList SqliteStorage::requestMsgsFiltered(UserId user, BufferId bufferId QSqlQuery query(db); if (last == -1 && first == -1) { query.prepare(queryString("select_messagesNewestK_filtered")); + // Workaround for Qt 4 QSqlQuery::bindValue() not supporting repeated placeholder names + query.bindValue(":bufferidDup1", bufferId.toInt()); } else if (last == -1) { query.prepare(queryString("select_messagesNewerThan_filtered")); query.bindValue(":firstmsg", first.toQint64()); + // Workaround for Qt 4 QSqlQuery::bindValue() not supporting repeated placeholder names + query.bindValue(":bufferidDup1", bufferId.toInt()); } else { query.prepare(queryString("select_messagesRange_filtered")); query.bindValue(":lastmsg", last.toQint64()); query.bindValue(":firstmsg", first.toQint64()); + // Workaround for Qt 4 QSqlQuery::bindValue() not needed, only has one ":bufferid" } query.bindValue(":bufferid", bufferId.toInt()); query.bindValue(":limit", limit); @@ -2070,6 +2080,8 @@ QList SqliteStorage::requestMsgsFiltered(UserId user, BufferId bufferId query.bindValue(":type", typeRaw); int flagsRaw = flags; query.bindValue(":flags", flagsRaw); + // Workaround for Qt 4 QSqlQuery::bindValue() not supporting repeated placeholder names + query.bindValue(":flagsDup1", flagsRaw); safeExec(query); watchQuery(query); @@ -2193,6 +2205,8 @@ QList SqliteStorage::requestAllMsgsFiltered(UserId user, MsgId first, M query.bindValue(":type", typeRaw); int flagsRaw = flags; query.bindValue(":flags", flagsRaw); + // Workaround for Qt 4 QSqlQuery::bindValue() not supporting repeated placeholder names + query.bindValue(":flagsDup1", flagsRaw); safeExec(query); watchQuery(query);