The QSqlQuery returns exceptions for the words starting with some digits

[lmofallis]

I have this code:

foreach (QString TheKeyword, KeywordsList) {
    if (!TheKeyword.isEmpty())
    {
        TheKeyword.replace("'","''").replace("[", "[[]");

        QString sQuery = QString("UPDATE news SET deleted = '1' WHERE %1 like '%%2%' AND Id IN(%3) AND deleted = '0'")
                            .arg(TheColumn)
                            .arg(TheKeyword)
                            .arg(TheIDs);

        QSqlQuery TheQuery(TheDB);
        TheQuery.prepare(sQuery);
        TheQuery.exec();

        if (TheQuery.lastError().isValid())
        {
            qDebug() << TheQuery.lastError() << "   -   " << TheKeyword;
        }
    }
}

For example, the "KeywordsList" contains this word's list (The "KeywordsList" is a QStringList):

1Tap
World
A man from Mars
2 Battery
2Do
1CLICK
01 Net
2 Bananas
4 Bananas
system app remover

My code returns this errors:

QSqlError("", "Unable to fetch row", "No query")    -    "1Tap"
QSqlError("", "Unable to fetch row", "No query")    -    "2 Battery"
QSqlError("", "Unable to fetch row", "No query")    -    "2Do"
QSqlError("", "Unable to fetch row", "No query")    -    "1CLICK"
QSqlError("", "Unable to fetch row", "No query")    -    "01 Net"
QSqlError("", "Unable to fetch row", "No query")    -    "2 Bananas"

So, I see that problem is only with the keywords starting with '0', '1' or '2'

I tried to use bindValue, but without success.

Thank you.

[SGaist]

Hi,

You should print the query run with QSqlQuery::lastQuery. That might give you additional clues.

[lmofallis]

"UPDATE news SET deleted = '1' WHERE title like '146,293,294,295,297,193,219,231,291Tap%' AND Id IN(%3) AND deleted = '0'"
"UPDATE news SET deleted = '1' WHERE title like '146,293,294,295,297,193,219,231,291 Battery%' AND Id IN(%3) AND deleted = '0'"
"UPDATE news SET deleted = '1' WHERE title like '146,293,294,295,297,193,219,231,291Do%' AND Id IN(%3) AND deleted = '0'"
"UPDATE news SET deleted = '1' WHERE title like '17,208,224,228,60,230,146,293,294,295,297,193,219,231,291CLICK%' AND Id IN(%3) AND deleted = '0'"
"UPDATE news SET deleted = '1' WHERE title like '168,59,67,229,180,204,205,206 Net%' AND Id IN(%3) AND deleted = '0'"    -    "01 Net"
"UPDATE news SET deleted = '1' WHERE title like '9,10,11,12,13,14,15,16,198,199,200,168,195,196,197 Bananas%' AND Id IN(%3) AND deleted = '0'"

[lmofallis]

For example I think that the thing is dealt as:

%%2%
becomes
%2 Battery
then
146,293,294,295,297,193,219,231,291 Battery%

[jsulm]

@lmofallis You should use bindValue (see http://doc.qt.io/qt-5/qsqlquery.html) instead of using % and arg from QString.

[VRonin]

I don't think you can use bindValue in these cases (a column name, a like block and an in block)

On the other hand, a few things going wrong:

• TheKeyword.replace("'","''").replace("[", "[[]"); do not escape manually, use TheDB->driver()->escapeIdentifier(TheKeyword,QSqlDriver::FieldName);
• #include <QStringBuilder> (for efficiency) and build the the query (no need to use arg() here as this is not a user visible string and does not need internationalisation):

const QString sQuery = "UPDATE news SET deleted = '1' WHERE " 
% TheDB->driver()->escapeIdentifier(TheColumn,QSqlDriver::FieldName) 
% " like '%" % TheDB->driver()->escapeIdentifier(TheKeyword,QSqlDriver::FieldName) 
% "%' AND Id IN(" % TheDB->driver()->escapeIdentifier(TheIDs,QSqlDriver::FieldName) % ") AND deleted = '0'";

• add an if around exec to make sure the query was executed

[lmofallis]

@VRonin
Thank you.
Your escaping way works very well. I'll check my old projects to update them.