2

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

1
  • Interesting. but can you make it simpler? Like provide a fixed query string for both examples. (And measure the performance of that) Mar 6, 2018 at 9:51

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.