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 780263f..2bf44e2 100644 (file)
--- 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 3e1ce65..1eb6278 100644 (file)
--- 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 46b4d47..5adf5fa 100644 (file)
--- 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 cdb960c..af3e2f6 100644 (file)
--- 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 6030e53..ef1c1d2 100644 (file)
--- 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 1a742b8..7749c4c 100644 (file)
--- 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 c09be6b..b4f91f2 100644 (file)
--- 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 5040b9e..6ccf81d 100644 (file)
--- 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 198df7c..df6b75e 100644 (file)
--- 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 eb4af1f..29c0eac 100644 (file)
--- 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 b4411af..49515b4 100644 (file)
--- 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 d1e4c0f..d53d5ae 100644 (file)
--- 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 50871b5..9ed72e3 100644 (file)
--- a/src/core/postgresqlstorage.cpp
+++ b/src/core/postgresqlstorage.cpp
@@ -1909,13 +1909,18 @@ QList<Message> 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<Message> 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<Message> 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 8a0b2bc..8c2df4b 100644 (file)
--- a/src/core/sqlitestorage.cpp
+++ b/src/core/sqlitestorage.cpp
@@ -1979,15 +1979,20 @@ QList<Message> 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<Message> 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<Message> 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<Message> 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);