From 284d15bbec07fee6e695ea16bebd8f05d1601e19 Mon Sep 17 00:00:00 2001 From: Andy Shaw Date: Fri, 16 Apr 2021 16:11:07 +0200 Subject: SQLite: Handle tables and fields with a dot in the name correctly Fixes: QTBUG-91885 Change-Id: Iba76bb50266dd4fb5f50e4ea1549d1d2bb6e3431 Reviewed-by: Edward Welbourne (cherry picked from commit 66acee69a1563488e5950645c171d6be73dd5f70) Reviewed-by: Qt Cherry-pick Bot --- src/plugins/sqldrivers/sqlite/qsql_sqlite.cpp | 33 +++++++++++---- .../models/qsqltablemodel/tst_qsqltablemodel.cpp | 49 ++++++++++++++++++++++ 2 files changed, 73 insertions(+), 9 deletions(-) diff --git a/src/plugins/sqldrivers/sqlite/qsql_sqlite.cpp b/src/plugins/sqldrivers/sqlite/qsql_sqlite.cpp index 4f70728d73..3527d2cd32 100644 --- a/src/plugins/sqldrivers/sqlite/qsql_sqlite.cpp +++ b/src/plugins/sqldrivers/sqlite/qsql_sqlite.cpp @@ -74,13 +74,18 @@ Q_DECLARE_METATYPE(sqlite3_stmt*) QT_BEGIN_NAMESPACE -static QString _q_escapeIdentifier(const QString &identifier) +static QString _q_escapeIdentifier(const QString &identifier, QSqlDriver::IdentifierType type) { QString res = identifier; + // If it contains [ and ] then we assume it to be escaped properly already as this indicates + // the syntax is exactly how it should be + if (identifier.contains(QLatin1Char('[')) && identifier.contains(QLatin1Char(']'))) + return res; if (!identifier.isEmpty() && !identifier.startsWith(QLatin1Char('"')) && !identifier.endsWith(QLatin1Char('"'))) { res.replace(QLatin1Char('"'), QLatin1String("\"\"")); res.prepend(QLatin1Char('"')).append(QLatin1Char('"')); - res.replace(QLatin1Char('.'), QLatin1String("\".\"")); + if (type == QSqlDriver::TableName) + res.replace(QLatin1Char('.'), QLatin1String("\".\"")); } return res; } @@ -905,13 +910,24 @@ static QSqlIndex qGetTableInfo(QSqlQuery &q, const QString &tableName, bool only { QString schema; QString table(tableName); - int indexOfSeparator = tableName.indexOf(QLatin1Char('.')); + const int indexOfSeparator = tableName.indexOf(QLatin1Char('.')); if (indexOfSeparator > -1) { - schema = tableName.left(indexOfSeparator).append(QLatin1Char('.')); - table = tableName.mid(indexOfSeparator + 1); + const int indexOfCloseBracket = tableName.indexOf(QLatin1Char(']')); + if (indexOfCloseBracket != tableName.size() - 1) { + // Handles a case like databaseName.tableName + schema = tableName.left(indexOfSeparator + 1); + table = tableName.mid(indexOfSeparator + 1); + } else { + const int indexOfOpenBracket = tableName.lastIndexOf(QLatin1Char('['), indexOfCloseBracket); + if (indexOfOpenBracket > 0) { + // Handles a case like databaseName.[tableName] + schema = tableName.left(indexOfOpenBracket); + table = tableName.mid(indexOfOpenBracket); + } + } } - q.exec(QLatin1String("PRAGMA ") + schema + QLatin1String("table_info (") + _q_escapeIdentifier(table) + QLatin1Char(')')); - + q.exec(QLatin1String("PRAGMA ") + schema + QLatin1String("table_info (") + + _q_escapeIdentifier(table, QSqlDriver::TableName) + QLatin1Char(')')); QSqlIndex ind; while (q.next()) { bool isPk = q.value(5).toInt(); @@ -973,8 +989,7 @@ QVariant QSQLiteDriver::handle() const QString QSQLiteDriver::escapeIdentifier(const QString &identifier, IdentifierType type) const { - Q_UNUSED(type); - return _q_escapeIdentifier(identifier); + return _q_escapeIdentifier(identifier, type); } static void handle_sqlite_callback(void *qobj,int aoperation, char const *adbname, char const *atablename, diff --git a/tests/auto/sql/models/qsqltablemodel/tst_qsqltablemodel.cpp b/tests/auto/sql/models/qsqltablemodel/tst_qsqltablemodel.cpp index 2ceba66bf8..1229b4d64d 100644 --- a/tests/auto/sql/models/qsqltablemodel/tst_qsqltablemodel.cpp +++ b/tests/auto/sql/models/qsqltablemodel/tst_qsqltablemodel.cpp @@ -153,6 +153,9 @@ private slots: void invalidFilterAndHeaderData_data() { generic_data(); } void invalidFilterAndHeaderData(); //QTBUG-23879 + + void sqlite_selectFromIdentifierWithDot_data() { generic_data("QSQLITE"); } + void sqlite_selectFromIdentifierWithDot(); private: void generic_data(const QString& engine=QString()); void generic_data_with_strategies(const QString& engine=QString()); @@ -160,6 +163,7 @@ private: tst_QSqlTableModel::tst_QSqlTableModel() { + QVERIFY(dbs.open()); } tst_QSqlTableModel::~tst_QSqlTableModel() @@ -2159,5 +2163,50 @@ void tst_QSqlTableModel::modelInAnotherThread() QVERIFY(t.isFinished()); } +void tst_QSqlTableModel::sqlite_selectFromIdentifierWithDot() +{ + QFETCH(QString, dbName); + QSqlDatabase db = QSqlDatabase::database(dbName); + CHECK_DATABASE(db); + { + const auto fieldDot = qTableName("fieldDot", __FILE__, db); + tst_Databases::safeDropTable(db, fieldDot); + QSqlQuery qry(db); + QVERIFY_SQL(qry, exec("create table " + fieldDot + " (id int primary key, " + "\"person.firstname\" varchar(20))")); + QVERIFY_SQL(qry, exec("insert into " + fieldDot + " values(1, 'Andy')")); + QSqlTableModel model(0, db); + model.setTable(fieldDot); + QVERIFY_SQL(model, select()); + QCOMPARE(model.data(model.index(0, 0)).toInt(), 1); + QCOMPARE(model.data(model.index(0, 1)).toString(), QString("Andy")); + } + const auto tableDot = QLatin1Char('[') + qTableName("table.dot", __FILE__, db) + QLatin1Char(']'); + { + tst_Databases::safeDropTable(db, tableDot); + QSqlQuery qry(db); + QVERIFY_SQL(qry, exec("create table " + tableDot + " (id int primary key, " + "\"person.firstname\" varchar(20))")); + QVERIFY_SQL(qry, exec("insert into " + tableDot + " values(1, 'Andy')")); + QSqlTableModel model(0, db); + model.setTable(tableDot); + QVERIFY_SQL(model, select()); + QCOMPARE(model.data(model.index(0, 0)).toInt(), 1); + QCOMPARE(model.data(model.index(0, 1)).toString(), QString("Andy")); + } + { + QSqlDatabase attachedDb = QSqlDatabase::addDatabase("QSQLITE", "attachedDb"); + attachedDb.setDatabaseName(db.databaseName().replace("foo.db", "attached.db")); + QVERIFY(attachedDb.open()); + QSqlQuery qry(attachedDb); + QVERIFY_SQL(qry, exec(QString("attach '%1' AS 'attached'").arg(db.databaseName()))); + QSqlTableModel model(0, attachedDb); + model.setTable(QString("attached.%1").arg(tableDot)); + QVERIFY_SQL(model, select()); + QCOMPARE(model.data(model.index(0, 0)).toInt(), 1); + QCOMPARE(model.data(model.index(0, 1)).toString(), QString("Andy")); + } +} + QTEST_MAIN(tst_QSqlTableModel) #include "tst_qsqltablemodel.moc" -- cgit v1.2.3