Unsolved 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.
-
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% -
@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, useTheDB->driver()->escapeIdentifier(TheKeyword,QSqlDriver::FieldName);
#include <QStringBuilder>
(for efficiency) and build the the query (no need to usearg()
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.