QSqlTableModel setFilter => Unable to execute statement

msauer751

Hi,
I use QSqlTableModel to access my sqlite database. If I want to select entrys with setFilter I will get the following error:

>> Database > searchData ( 724 ) INFO lastError= QSqlError("1", "Unable to execute statement", "near \"group\": syntax error")

My function to find data:

    QString CfgDb::cfgFindKey(const QString &groupArg, const QString &keyArg)
    {
        QJsonObject ret;
        errorId = 0;
        errorMsg = "";

        ret = searchData(QString::fromStdString(TBLNAME), "(group='" + groupArg + "') AND (key=" + keyArg + ")");
        qDebug() << ">> CfgDb > cfgChgKey (" << __LINE__ << ") INFO ret=" << ret;
        QString sret {};
        if (ret["anz"].toInt() == 0)
        {
            sret = "<not found>";
        }
        /*        else
                {
                    QJsonArray array = ret[QString::fromStdString(TBLNAME)].toArray();
                    for (uint16_t i = 0; i < array.size(); ++i)
                    {
                        if (array[i])
                    }
                }
        */
        return sret;
    }

searchData:

    QJsonObject Database::searchData(const QString &tableNameArg, const QString &whereArg)
    {
        QJsonObject ret {};
        errorId = 0;
        errorMsg = "";

        QSqlTableModel *model {nullptr};
        QJsonArray array {};
        model = new QSqlTableModel(this, db);
        qDebug() << ">> Database > searchData (" << __LINE__ << ") INFO " << model->database();
        model->setTable(tableNameArg);
        model->setEditStrategy(QSqlTableModel::OnManualSubmit);
        model->setFilter(whereArg);
        qDebug() << ">> Database > searchData (" << __LINE__ << ") INFO tableNameArg=" << tableNameArg;
        qDebug() << ">> Database > searchData (" << __LINE__ << ") INFO whereArg=" << model->filter();
        model->select();
        qDebug() << ">> Database > searchData (" << __LINE__ << ") INFO select=" << model->select();
        qDebug() << ">> Database > searchData (" << __LINE__ << ") INFO lastError=" << model->lastError();
        uint16_t anz = model->rowCount();
        if (anz == 0)
        {
            ret.insert("anz", anz);
        }
        else
        {
            for (uint16_t i = 0; i < anz; ++i)
            {
                QJsonObject temp {};
                QSqlRecord tempRec {model->record(i)};
                for (uint16_t ii = 0; ii < tempRec.count(); ++ii)
                {
                    temp.insert(tempRec.fieldName(ii), tempRec.value(ii).toString());
                }
                array.insert(array.size(), temp);
            }
            ret.insert(tableNameArg, array);
        }
        qDebug() << ">> Database > searchData (" << __LINE__ << ") INFO " << ret;

        return ret;
    }

The output:

>> Database > searchData ( 716 ) INFO  QSqlDatabase(driver="QSQLITE", database="cfg.db", host="", port=-1, user="", open=true)
>> Database > searchData ( 720 ) INFO tableNameArg= "config"
>> Database > searchData ( 721 ) INFO whereArg= "(group='global') AND (key=config_set)"
>> Database > searchData ( 723 ) INFO select= false
>> Database > searchData ( 724 ) INFO lastError= QSqlError("1", "Unable to execute statement", "near \"group\": syntax error")

Can you help me where the problem is?
Thank you for your help.
BR
martin

IgKh

group is a reserved word in SQL, being part of the keyword GROUP BY. If you have a column named group, it needs to be escaped according to the rules of the database you are using. You can use QSqlDriver::escapeIdentifier for that.

Christian Ehrlicher

Why do you use a QSqlTableModel to retrieve rows from the db instead a simple QSqlQuery?

IgKh

group is a reserved word in SQL, being part of the keyword GROUP BY. If you have a column named group, it needs to be escaped according to the rules of the database you are using. You can use QSqlDriver::escapeIdentifier for that.

Pl45m4

@IgKh said in QSqlTableModel setFilter => Unable to execute statement:

group is a reserved word in SQL, being part of the keyword GROUP BY

Fun story:
Because of something similar the whole server infrastructure "exploded" where I was working around 10 years ago :)
Wasn't my fault, I swear :))
DHCP server had the list/table of clients (MAC, client IP, subnet IP, name, email etc) stored in an SQL table, where the subnet column was named "range" (to configure a range of IPs)... WAS ok, for early SQL versions as the system was set up by former apprentices....
until our head made an upgrade on all servers, which also introduced new SQL releases to Linux, where range became a reserved name/keyword...
Around 500 clients with no IP after their leases ran out... and nobody knew why :)
Took two full days until we figured out what happened :'-)

JonB

@Pl45m4
If you want to be safe all column names in SQL statements should be "escaped". I have seen various SQLs use [], "" or ``for this.

Pl45m4

@JonB

With this hint you are about 12 years late or so :D
Btw: Actually in cases like these there is no one to blame... do we know what might become a reserved name for something in five years from now?! :-)
So you can never make anything "future-safe" ;-)

JonB

@Pl45m4 said in QSqlTableModel setFilter => Unable to execute statement:

With this hint you are about 12 years late or so :D

?

do we know what might become a reserved name for something in five years from now?! :-)

So you can never make anything "future-safe" ;-)

That is why I wrote what I did. You "escape" all column (and table) name references wherever they are and whatever name is being used. You don't just do it for those words which are reserved at present. That is what good auto-generated code does like I used for SQL Server and what I did in my method for inserting any column names into SQL statements I generated. If you don't, fair enough, but you are taking a chance against future changes. Just saying.

Pl45m4

@JonB said in QSqlTableModel setFilter => Unable to execute statement:

?

Escaping the column names and doing everything more thoughtfully would have prevented the crash... so "range" instead of range etc...
I think not only any select statements were influenced but the whole table itself. That's why the DHCP service didn't start up anymore ;)