The QSqlQuery returns exceptions for the words starting with some digits



  • 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.


  • Lifetime Qt Champion

    Hi,

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



  • "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'"
    


  • 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%


  • Moderators

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



  • 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


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


Log in to reply