QSqlQuery::exec() returns false all the time

[ayanda83]

hi there, the function exec() in the code below returns false all the time and I've tried everything to fix it but without any success. Could I be missing something here. please help.

void MainWindow::query(QStringList qry)
{
QString qryStr = QString("INSERT INTO Modules VALUES(\"%1, %2 %3 %4\")").arg(qry.at(0)).arg(qry.at(1)).arg(qry.at(2)).arg (qry.at(3));
QSqlQuery query;
query.prepare(qryStr);

if(query.exec())
QMessageBox::critical(0, "DB Status","DATABASE UPDATED", QMessageBox::Ok);
else
QMessageBox::critical(0, "DB Status","DATABASE NOT UPDATED", QMessageBox::Ok);
}

[alizadeh91]

Have u add database connection to sqlQuery?

[ayanda83]

I'm not sure if i know what you mean by "adding a database connection to SqlQuery" but i am going to tell you what I have done. I have pointed my program to the database and I have confirmed that the program is able to open the database and make changes to it, but this particular code I posted above is not doing what it should.

[alizadeh91]

please show output of QSqlQuery::lastError()->text()

[ayanda83]

Never mind, I have fixed the problem. For those who might encounter the same problem is future, the problem was that i added double quotation marks inside the brackets instead of single quotation marks on this line of code QString qryStr = QString("INSERT INTO Modules VALUES(\"%1, %2 %3 %4\")").arg(qry.at(0)).arg(qry.at(1)).arg(qry.at(2)).arg (qry.at(3)); the correct line is QString qryStr = QString("INSERT INTO Modules VALUES('%1', '%2', '%3', '%4')").arg(qry.at(0)).arg(qry.at(1)).arg(qry.at(2)).arg (qry.at(3));

[Scorp2us]

DO NOT DO WHAT YOU ARE DOING. You have multiple bad practices in play.
1. Always provide the fields for insert. What happens when the table gets altered and the column order changes?
2. It is VERY bad form to construct SQL as you are going. See this as for why (https://xkcd.com/327/). This is why/how people get online databases hacked.

ALWAYS ALWAYS ALWAYS use the bindValue interface.




void MainWindow::query(QStringList qry)
{
QSqlQuery query;
query.prepare("INSERT INTO Modules (A,B,C,D) VALUES(:a, :b, :c, d)");

query.bindValue(":a", qry[0]);
query.bindValue(":b", qry[1]);
query.bindValue(":c", qry[2]);
query.bindValue(":d", qry[3]);

if(query.exec())
QMessageBox::critical(0, "DB Status","DATABASE UPDATED", QMessageBox::Ok);
else
QMessageBox::critical(0, "DB Status","DATABASE NOT UPDATED", QMessageBox::Ok);
}


The bind interface:
1. prevents exploits, by performing the proper escaping in a database-engine specific manner.
2. is more potable between databases
3. does not use that ugly string of embedded quotes

The only time is it ok to not use it, is when you are sure the content is not ever user supplied.

If "Robert'); drop table students" is used in your query, what happens? your query fails, and if you have a students table, it sets dropped. Using bind parameters, you will get a field value of "Robert'); drop table students". And YES, I have seen this on systems with software I've written. (it wasn't Qt, but PHP at the time)

[ayanda83]

thank you Scorp2us. good advise.