diff options
author | Christian Ehrlicher <ch.ehrlicher@gmx.de> | 2023-02-26 20:32:21 +0100 |
---|---|---|
committer | Christian Ehrlicher <ch.ehrlicher@gmx.de> | 2023-07-23 16:49:15 +0200 |
commit | e532933a2a9ff0219f0179880e05c95e0ec5e19d (patch) | |
tree | 7c2eab8afe3f86d67b90a8b03ea44a766f137984 | |
parent | 4a7023f5b4c45682f344e5185e82e518b653368a (diff) |
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>
-rw-r--r-- | src/plugins/sqldrivers/psql/qsql_psql.cpp | 4 | ||||
-rw-r--r-- | src/sql/kernel/qsqlquery.cpp | 28 | ||||
-rw-r--r-- | src/sql/kernel/qsqlquery.h | 3 | ||||
-rw-r--r-- | src/sql/kernel/qsqlresult.cpp | 29 | ||||
-rw-r--r-- | src/sql/kernel/qsqlresult.h | 2 | ||||
-rw-r--r-- | src/sql/kernel/qsqlresult_p.h | 1 | ||||
-rw-r--r-- | tests/auto/sql/kernel/qsqlquery/tst_qsqlquery.cpp | 82 |
7 files changed, 146 insertions, 3 deletions
diff --git a/src/plugins/sqldrivers/psql/qsql_psql.cpp b/src/plugins/sqldrivers/psql/qsql_psql.cpp index 8fcd9a47af..1607e1141f 100644 --- a/src/plugins/sqldrivers/psql/qsql_psql.cpp +++ b/src/plugins/sqldrivers/psql/qsql_psql.cpp @@ -247,14 +247,14 @@ void QPSQLDriverPrivate::checkPendingNotifications() const } } -class QPSQLResultPrivate : public QSqlResultPrivate +class QPSQLResultPrivate final : public QSqlResultPrivate { Q_DECLARE_PUBLIC(QPSQLResult) public: Q_DECLARE_SQLDRIVER_PRIVATE(QPSQLDriver) using QSqlResultPrivate::QSqlResultPrivate; - QString fieldSerial(qsizetype i) const override { return u'$' + QString::number(i + 1); } + QString fieldSerial(qsizetype i) const override { return QString("$%1"_L1).arg(i + 1); } void deallocatePreparedStmt(); std::queue<PGresult*> nextResultSets; diff --git a/src/sql/kernel/qsqlquery.cpp b/src/sql/kernel/qsqlquery.cpp index f93ca168d1..37f49caf74 100644 --- a/src/sql/kernel/qsqlquery.cpp +++ b/src/sql/kernel/qsqlquery.cpp @@ -1270,6 +1270,34 @@ QSql::NumericalPrecisionPolicy QSqlQuery::numericalPrecisionPolicy() const } /*! + Enables or disables the positional binding for this query. + This is useful when the query contains a '?' which must not be handled + as a positional binding parameter but, for example, as a JSON operator + for a Postgres database. + This function will have no effect when the database has native + support for positional bindings with question marks. + + \since 6.7 + \sa positionalBindingEnabled() +*/ +void QSqlQuery::enablePositionalBinding(bool enable) +{ + d->sqlResult->enablePositionalBinding(enable); +} + +/*! + Returns true when the positional binding is currently enabled. + + \since 6.7 + \sa enablePositionalBinding() +*/ +bool QSqlQuery::positionalBindingEnabled() const +{ + return d->sqlResult->positionalBindingEnabled(); +} + + +/*! \since 4.3.2 Instruct the database driver that no more data will be fetched from diff --git a/src/sql/kernel/qsqlquery.h b/src/sql/kernel/qsqlquery.h index 9d019d5cd2..e0071575d8 100644 --- a/src/sql/kernel/qsqlquery.h +++ b/src/sql/kernel/qsqlquery.h @@ -69,6 +69,9 @@ public: void setNumericalPrecisionPolicy(QSql::NumericalPrecisionPolicy precisionPolicy); QSql::NumericalPrecisionPolicy numericalPrecisionPolicy() const; + void enablePositionalBinding(bool enable); + bool positionalBindingEnabled() const; + bool seek(int i, bool relative = false); bool next(); bool previous(); diff --git a/src/sql/kernel/qsqlresult.cpp b/src/sql/kernel/qsqlresult.cpp index aa3608fb8c..97e2757f0b 100644 --- a/src/sql/kernel/qsqlresult.cpp +++ b/src/sql/kernel/qsqlresult.cpp @@ -37,6 +37,9 @@ static bool qIsAlnum(QChar ch) QString QSqlResultPrivate::positionalToNamedBinding(const QString &query) const { + if (!positionalBindingEnabled) + return query; + const qsizetype n = query.size(); QString result; @@ -92,6 +95,7 @@ QString QSqlResultPrivate::namedToPositionalBinding(const QString &query) int count = 0; qsizetype i = 0; bool ignoreBraces = (sqldriver->dbmsType() == QSqlDriver::PostgreSQL); + const bool qmarkNotationSupported = (sqldriver->dbmsType() != QSqlDriver::PostgreSQL); while (i < n) { QChar ch = query.at(i); @@ -115,10 +119,16 @@ QString QSqlResultPrivate::namedToPositionalBinding(const QString &query) int pos = i + 2; while (pos < n && qIsAlnum(query.at(pos))) ++pos; + // if question mark notation is not supported we have to use + // the native binding. fieldSerial() should be renamed + // to toNativeBinding() and used unconditionally here + if (qmarkNotationSupported) + result += u'?'; + else + result += fieldSerial(count); QString holder(query.mid(i, pos - i)); indexes[holder].append(count++); holders.append(QHolder(holder, i)); - result += u'?'; i = pos; } else { if (ch == u'\'' || ch == u'"' || ch == u'`') @@ -1005,6 +1015,23 @@ QSql::NumericalPrecisionPolicy QSqlResult::numericalPrecisionPolicy() const } /*! \internal + */ +void QSqlResult::enablePositionalBinding(bool enable) +{ + Q_D(QSqlResult); + d->positionalBindingEnabled = enable; +} + +/*! \internal + */ +bool QSqlResult::positionalBindingEnabled() const +{ + Q_D(const QSqlResult); + return d->positionalBindingEnabled; +} + + +/*! \internal */ bool QSqlResult::nextResult() { diff --git a/src/sql/kernel/qsqlresult.h b/src/sql/kernel/qsqlresult.h index 1135caf765..2307c12b6f 100644 --- a/src/sql/kernel/qsqlresult.h +++ b/src/sql/kernel/qsqlresult.h @@ -102,6 +102,8 @@ protected: virtual void detachFromResultSet(); virtual void setNumericalPrecisionPolicy(QSql::NumericalPrecisionPolicy policy); QSql::NumericalPrecisionPolicy numericalPrecisionPolicy() const; + void enablePositionalBinding(bool enable); + bool positionalBindingEnabled() const; virtual bool nextResult(); void resetBindCount(); // HACK diff --git a/src/sql/kernel/qsqlresult_p.h b/src/sql/kernel/qsqlresult_p.h index 1e8e8a3033..6eebdaaba4 100644 --- a/src/sql/kernel/qsqlresult_p.h +++ b/src/sql/kernel/qsqlresult_p.h @@ -98,6 +98,7 @@ public: bool active = false; bool isSel = false; bool forwardOnly = false; + bool positionalBindingEnabled = true; static bool isVariantNull(const QVariant &variant); }; 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" |