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
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.