Using prepared statements in Qt

Question

I want to insert the data of a (custom) class "Table" into my database - the data is retrieved from internal files, but for the sake of security, I'd like to assume the sources are untrusted. Currently, I'm using a variation of the code below. This is obviously prone to SQL-injections as v.toString() might be exploited. table.getName() is checked against a table-whitelist, so there should be no need to defend against SQL-injections here.

QString rowQuery = "INSERT INTO " + table.getName() + " VALUES ";
for (Row* r : table) {
    rowQuery += "(";
    for (QVariant v : r.getValues()) {
        rowQuery += v.toString();
        rowQuery += ","
    }
    rowQuery.chop(1);
    rowQuery += "),";
}
rowQuery.chop(1);
QSqlQuery::exec(rowQuery)

An example query with two entries with three columns looks like:

INSERT INTO DebugTable VALUES (cell1x1, cell1x2, cell1x3), (cell2x1, cell2x2, cell2x3)

To make my application secure against SQL-injections, I started using prepared statements. Unfortunately, performance suffered greatly from this design choice, so I tried to use batch executions using QVariantLists, but even this approach could not deliver appropriate performance. My largest table has 15,000 entries; without prepared statements the insertion (q.exec() / q.execBatch()) took around four seconds, with prepared statements it took 90 seconds.

QList<QVariantList> columnVectors;
QString queryString;
queryString = "INSERT INTO " + table.getName() + " VALUES (";
for (auto i : table.getCols()) {
    columnVectors.append(QVariantList());
    queryString += "?,";
}
queryString.chop(1);
queryString += ")";
for (Row* row : table.getRows()) {
    for (int i = 0; i < row->getValues().length(); i++) {
        columnVectors[i].append(variant);
    }
}
QSqlQuery q;
q.prepare(queryString);
for (QVariantList columnVector : columnVectors) {
    q.addBindValue(columnVector);
}
q.execBatch();

An example query with x entries with three columns looks like:

INSERT INTO DebugTable VALUES (?, ?, ?)

I think there might be an error in my approach / implementation as I read that prepared statements should improve the performance. Any help would be appreciated, thank you

Answer 1

This is a hard question to answer, because database performance can be dependent on a lot of other things. So let me first answer your question directly, then suggest what I think you may wish to do instead (if you haven't tried it already), and finally explain my experiment and the results I got so others can repeat.

First, let me describe one way to set up a prepared statement with multiple rows inserted at the same time (remember I am going to explain my experiment so you can ignore the timer for now). I used two for-loops, and have something that looks like this:

void stringBuilderPreparedFunction()
{
  QElapsedTimer timer;
  timer.start();
  QList<Translation> list = getList();
  QString queryString("INSERT INTO test (val, english, spanish) VALUES ");
  for (int x = 0; x < list.size(); x++)
  {
    QString rowId = QString::number(x);
    queryString.append("(:val" + rowId + ", :english" + rowId + ", :spanish" + rowId + "),");
  }
  queryString.chop(1);
  QSqlQuery query;
  query.prepare(queryString);
  for (int y = 0; y < list.size(); y++)
  {
    QString rowId = QString::number(y);
    QString numberString(":val" + rowId);
    query.bindValue(numberString, QString::number(list.at(y).number));
    QString englishString(":english" + rowId);
    query.bindValue(englishString, list.at(y).english);
    QString spanishString(":spanish" + rowId);
    query.bindValue(spanishString, list.at(y).spanish);
  }
  query.exec();
  qDebug() << "The string builder perpared took" << timer.elapsed() << "milliseconds";
}

Now the next part is to mention that a lot of times wrapping all your inserts in a transaction will get roughly the same performance as writing a single insert statement. They are pretty similar actions. So you may consider the following code as well, before you settle on the complex string appending approach above:

void transactionFunction()
{
  QElapsedTimer timer;
  timer.start();
  QList<Translation> list = getList();
  QSqlDatabase::database().transaction();
  for (Translation row: list)
  {
    QSqlQuery query;
    query.prepare("INSERT INTO test (val, english, spanish) VALUES (:val, :english, :spanish)");
    query.bindValue(":val", row.number);
    query.bindValue(":english", row.english);
    query.bindValue(":spanish", row.spanish);
    query.exec();
  }
  QSqlDatabase::database().commit();
  qDebug() << "The transaction function took" << timer.elapsed() << "milliseconds";
}

I am not sure the details of what you are trying to do, and how sensitive you are to a performance hit. My results showed a slight edge to what you asked for, but the transaction may be enough and has less code. Also take the performance results with a grain of salt as this is not the most scientific way to test database performance.

For the experiment I used a single table in a MySQL database:

CREATE TABLE test (
    val INTEGER PRIMARY KEY,
    english VARCHAR(255),
    spanish VARCHAR(255)
);

I then inserted 500 rows that looked like:

returnList.append(Translation(500, "five hundred", "quinientos"));

This yielded the results of the first function as: "The string builder perpared took 116 milliseconds" AND "The transaction function took 138 milliseconds". Both of these were significant improvements over the single row insert with took around 20 seconds.

I hope that helps, and feel free to comment if I missed the mark in what you were asking, or if your environment yields different results.