SQL/PSQL: Handle jsonb operators in prepared queries

Add an option to disable handling of positional binding so jsonb
operators are not screwed up

[ChangeLog][QtSql][QSqlQuery] Add setEnablePositionalBinding() to be
able to disable positional binding.

Fixes: QTBUG-96636
Change-Id: I428a9d3b10274b97292ab86a74d9b3971d6f10e9
Reviewed-by: Andy Shaw <andy.shaw@qt.io>

1 files changed, 82 insertions, 0 deletions

diff --git a/tests/auto/sql/kernel/qsqlquery/tst_qsqlquery.cpp b/tests/auto/sql/kernel/qsqlquery/tst_qsqlquery.cpp
index 6f471cb55e..70b71d6654 100644
--- a/tests/auto/sql/kernel/qsqlquery/tst_qsqlquery.cpp
+++ b/tests/auto/sql/kernel/qsqlquery/tst_qsqlquery.cpp
@@ -77,6 +77,9 @@ private slots:
     void psql_forwardOnlyQueryResultsLost_data() { generic_data("QPSQL"); }
     void psql_forwardOnlyQueryResultsLost();
 
+    void positionalBindingEnabled_data() { generic_data(); }
+    void positionalBindingEnabled();
+
     // Bug-specific tests:
     void oci_nullBlob_data() { generic_data("QOCI"); }
     void oci_nullBlob();
@@ -254,6 +257,9 @@ private slots:
     void ibaseTimeStampTzArray_data() { generic_data("QIBASE"); }
     void ibaseTimeStampTzArray();
 
+    void psqlJsonOperator_data() { generic_data("QPSQL"); }
+    void psqlJsonOperator();
+
     // Double addDatabase() with same name leaves system in a state that breaks
     // invalidQuery() if run later; so put this one last !
     void prematureExec_data() { generic_data(); }
@@ -5048,5 +5054,81 @@ void tst_QSqlQuery::ibase_executeBlock()
     QCOMPARE(qry.value(0).toInt(), 4);
 }
 
+void tst_QSqlQuery::positionalBindingEnabled()
+{
+    QFETCH(QString, dbName);
+    QSqlDatabase db = QSqlDatabase::database(dbName);
+    QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db);
+    CHECK_DATABASE(db);
+    TableScope ts(db, "positionalBinding", __FILE__);
+    const QString &tableName = ts.tableName();
+
+    QSqlQuery qry(db);
+    QVERIFY_SQL(qry, exec("CREATE TABLE " + tableName + " (integer_col integer)"));
+    QVERIFY_SQL(qry, exec("INSERT INTO " + tableName + "(integer_col) VALUES(42)"));
+
+    qry.enablePositionalBinding(true);
+    QVERIFY_SQL(qry, prepare("SELECT integer_col FROM " + tableName + " WHERE integer_col = :integer_val"));
+    qry.bindValue(":integer_val", 42);
+    QVERIFY_SQL(qry, exec());
+    QVERIFY_SQL(qry, next());
+    QCOMPARE(qry.value(0).toInt(), 42);
+    QVERIFY_SQL(qry, prepare("SELECT integer_col FROM " + tableName + " WHERE integer_col = ?"));
+    qry.bindValue(0, 42);
+    QVERIFY_SQL(qry, exec());
+    QVERIFY_SQL(qry, next());
+    QCOMPARE(qry.value(0).toInt(), 42);
+
+    qry.enablePositionalBinding(false);
+    QVERIFY_SQL(qry, prepare("SELECT integer_col FROM " + tableName + " WHERE integer_col = :integer_val"));
+    qry.bindValue(":integer_val", 42);
+    QVERIFY_SQL(qry, exec());
+    QVERIFY_SQL(qry, next());
+    QCOMPARE(qry.value(0).toInt(), 42);
+    // the next query will only work when the underlying database support question mark notation natively
+    if (dbType == QSqlDriver::PostgreSQL) {
+        QVERIFY(!qry.prepare("SELECT integer_col FROM " + tableName + " WHERE integer_col = ?"));
+        qry.bindValue(0, 42);
+        QVERIFY(!qry.exec());
+        QVERIFY(!qry.next());
+    } else {
+        QVERIFY_SQL(qry, prepare("SELECT integer_col FROM " + tableName + " WHERE integer_col = ?"));
+        qry.bindValue(0, 42);
+        QVERIFY_SQL(qry, exec());
+        QVERIFY_SQL(qry, next());
+        QCOMPARE(qry.value(0).toInt(), 42);
+    }
+}
+
+void tst_QSqlQuery::psqlJsonOperator()
+{
+    QFETCH(QString, dbName);
+    QSqlDatabase db = QSqlDatabase::database(dbName);
+    CHECK_DATABASE(db);
+    TableScope ts(db, "qTableName", __FILE__);
+    const QString &tableName = ts.tableName();
+
+    QSqlQuery qry(db);
+    qry.enablePositionalBinding(false); // don't allow / handle '?' as placeholder
+    QVERIFY_SQL(qry, exec("CREATE TABLE " + tableName + " (integer_col integer, json_col jsonb)"));
+    QVERIFY_SQL(qry, exec("INSERT INTO " + tableName + "(integer_col, json_col) VALUES(42, '{\"a\": [1, 2]}')"));
+    QVERIFY_SQL(qry, exec("INSERT INTO " + tableName + "(integer_col, json_col) VALUES(43, '{\"b\": [3, 4]}')"));
+
+    QVERIFY_SQL(qry, prepare("SELECT integer_col, json_col FROM " + tableName + " WHERE json_col @? '$.a[*] ? (@ == 1)' and integer_col = :int"));
+    qry.bindValue(":int", 42);
+    QVERIFY_SQL(qry, exec());
+    QVERIFY_SQL(qry, next());
+    QCOMPARE(qry.value(0).toInt(), 42);
+    QCOMPARE(qry.value(1).toByteArray(), "{\"a\": [1, 2]}");
+
+    QVERIFY_SQL(qry, prepare("SELECT integer_col, json_col FROM " + tableName + " WHERE json_col ? 'b' and integer_col = :int"));
+    qry.bindValue(":int", 43);
+    QVERIFY_SQL(qry, exec());
+    QVERIFY_SQL(qry, next());
+    QCOMPARE(qry.value(0).toInt(), 43);
+    QCOMPARE(qry.value(1).toByteArray(), "{\"b\": [3, 4]}");
+}
+
+
 QTEST_MAIN(tst_QSqlQuery)
 #include "tst_qsqlquery.moc"