Running parameterized queries in QSqlTableModel

Question

I have a QTableView and I am using a derived class SqlTableModel of QSqlTableModel to fetch data from a MySQL database. I want to prevent injection. I ran a union injection and it was easier than taking candy from a baby. The SQL query utilizes the LIKE keyword.

Attempt 1 (injectable):

QString query = QString("select * from table where col like '%%1%'").arg(edit->text());
QSqlQuery q(query);
SqlTableModel *model = new SqlTableModel();
model->setQuery(q);
model->select();
tableView->setModel(model);

Attempt 2 (no data is returned, no errors):

QString query = "select * from table";
QSqlQuery q(query);
SqlTableModel *model = new SqlTableModel();
model->setQuery(q);
model->setFilter(QString("col like '%%1%'").arg(edit->text()));
model->select();
tableView->setModel(model);

Attempt 3 (no data is returned, no errors):

QString query = "select * from table where col like :param";
QSqlQuery q(query);
q.prepare(query);
q.bindValue(":param", QString("%%1%").arg(edit->text()));
SqlTableModel *model = new SqlTableModel();
model->setQuery(q);
model->select();
tableView->setModel(model);

Alexey · Accepted Answer · 2022-06-20T07:59:57.267

It looks like you are using QSqlTableModel in wrong way. The common usage is to setTable with subsequent select call, according to docs

model->setTable("table");
model->select()

Filter can be set using setFilter

model->setFilter(QString("col like '%%1%'").arg(edit->text()));

Meanwhile you use a QSqlTableModel like it's a QSqlQueryModel with setQuery call. Even if it should work, then according to docs query should be active, i.e. QSqlQuery::exec should be called before setQuery call.

So, finally, you should use QSqlTableModel in the way as it assumed, like

SqlTableModel *model = new SqlTableModel();
model->setTable("table");
model->setFilter(QString("col like '%%1%'").arg(edit->text()));
model->select();   
tableView->setModel(model);

If you want to build a query yourself, then you should use a QSqlQueryModel based class, like

QString query = "select * from table where col like '%%1%'";
QSqlQuery q(query);
q.prepare();
q.addBindValue(edit->text());
q.exec();
QSqlQueryModel *model = new QSqlQueryModel();
model->setQuery(std::move(q));
tableView->setModel(model)

Running parameterized queries in QSqlTableModel

Attempt 1 (injectable):

Attempt 2 (no data is returned, no errors):

Attempt 3 (no data is returned, no errors):

1 Answers1