在 Qt 中使用预准备语句

Question

我想将(自定义)类"表"的数据插入我的数据库中 - 数据是从内部文件中检索的，但为了安全起见，我想假设来源不受信任。目前，我正在使用以下代码的变体。这显然容易被SQL注入，因为v.toString()可能会被利用。table.getName() 是针对表白名单进行检查的，因此这里应该不需要防御 SQL 注入。

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)

包含两个条目和三列的示例查询如下所示：

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

为了使我的应用程序免受 SQL 注入的影响，我开始使用预准备语句。不幸的是，这种设计选择对性能的影响很大，因此我尝试使用 QVariantList 使用批处理执行，但即使这种方法也无法提供适当的性能。我最大的表有 15,000 个条目;如果没有预准备语句，插入(q.exec()/q.execBatch())大约需要四秒钟，而预准备语句则需要90秒。

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();

包含x个条目和三列的示例查询如下所示：

INSERT INTO DebugTable VALUES (?, ?, ?)

我认为我的方法/实现可能存在错误，因为我读到准备好的语句应该提高性能。任何帮助将不胜感激，谢谢

Answer 1

这是一个很难回答的问题，因为数据库性能可能取决于许多其他因素。因此，让我首先直接回答您的问题，然后建议我认为您可能希望做什么(如果您还没有尝试过)，最后解释我的实验和我得到的结果，以便其他人可以重复。

首先，让我描述一种设置同时插入多行的预准备语句的方法(请记住，我将解释我的实验，以便您现在可以忽略计时器)。我使用了两个 for 循环，并且看起来像这样：

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";
}

现在下一部分是提到，很多时候将所有插入包装在事务中将获得与编写单个插入语句大致相同的性能。它们是非常相似的动作。因此，在确定上述复杂字符串追加方法之前，您也可以考虑以下代码：

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";
}

我不确定你试图做什么的细节，以及你对性能影响的敏感程度。我的结果显示与您要求的内容略有优势，但事务可能足够并且代码更少。还要对性能结果持保留态度，因为这不是测试数据库性能的最科学方法。

对于实验，我在MySQL数据库中使用了单个表：

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

然后我插入了 500 行，如下所示：

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

这产生了第一个函数的结果，如下所示： "字符串生成器花了 116 毫秒" 和 "交易功能耗时 138 毫秒"。这两项操作都比单行插入进行了重大改进，大约需要 20 秒。

我希望这有所帮助，如果我错过了您所问的分数，或者您的环境产生了不同的结果，请随时发表评论。

Answer 2

可能是一个更简单的例子。 我正在使用 QT5 Fedora 35 希望我的表有哪些列是显而易见的。 我包括如何处理布尔值。

QString updateSTMT;
QSqlQuery query;
updateSTMT="UPDATE sometableName ";
updateSTMT.append("SET title= :title, date= :date, location= :location, repaired= :repaired, comments= :comments where id= :id");
if (!query.prepare(updateSTMT)) {QMessageBox::warning(this,"prepare error",query.lastError().text()); return;}
query.bindValue(":title",ui->Title->text());
query.bindValue(":date",ui->DateCreated->text());
query.bindValue(":location",ui->Location->text());
query.bindValue(":comments",ui->Comments->text());
query.bindValue(":id",ui->IDValue->text());
//
// check a boolean field and process it too.
//
if (ui->Repaired->checkState()) {
query.bindValue(":repaired","true");
} else {
query.bindValue(":repaired","false");
}
if (!query.exec()) {
QMessageBox::warning(this,"SQL error",query.lastError().text());
}
#ifdef myDebug
QMessageBox::information(this,"SQL query",updateSTMT);
#endif
ui->updatedCheckBox->setChecked(true);
}