QtSQL双重插入db(QtSQL double inserting into db)

编程入门行业动态更新时间:2024-10-27 02:27:29

问题是，当我执行sql insert查询（我正在使用Qt Creator和sqlite）时，两个新行被添加到数据库而不是一个。

这是代码块

void MainWindow::on_insertButton_clicked(){ db.open(); QString name = ui->nameLineEdit->text(); QString queryString = "INSERT INTO highscores (name, score) VALUES '%1',%2"; QSqlQuery query(queryString.arg(name).arg(score)); query.exec(); }

此代码应仅将值name和score插入数据库一次，但在查询exec之后插入两个相同的行。

任何人都知道问题是什么？

Problem is that when I exec sql insert query (I'm using Qt Creator and sqlite), two new rows are added into database instead of one.

Here's code block

This code should insert values name and score into database just once but after query exec two same rows are inserted.

Anyone has an idea what's the problem?

最满意答案

不是

QString queryString = "INSERT INTO highscores (name, score) VALUES '%1',%2";
 
 你应该写  
QString queryString = "INSERT INTO highscores (name, score) VALUES ('%1',%2)";
 
 SQL的INSERT命令需要VALUES之后用逗号分隔的数据集列表 ，每个数据集都在括号中，并带有列值。  
 以下命令将一个元组插入table ：  
INSERT INTO table (col1, col2) VALUES (val1, val2);
 
| col1 | col2 |
+------+------+
| val1 | val2 |
 
 因为您没有将这两个值放在括号中，所以您要求SQLite插入两个数据集 ，每个数据集都有一个值 ：  
INSERT INTO table (col1, col2) VALUES val1, val2;
 
| col1 | col2 |
+------+------+
| val1 |      |
| val2 |      |
 
 （有趣的是我用我的SQLite和MySQL安装测试了它。当我省略括号时它们都抛出了一个错误。但这可能取决于你使用的版本和一些选项。）  
 请永远不要使用QString::arg来填充SQL查询的参数！  
 （或者任何其他字符串构建方法，如连接。） 这可能导致可能的SQL注入 。  
 为了使用变量参数构建语句，您应该使用QSqlQuery绑定值提供的SQL预处理语句 ：  
QString queryString = "INSERT INTO highscores (name, score) VALUES (?,?)";
QSqlQuery query(queryString);
query.addBindValue(name);
query.addBindValue(score);
query.exec();
 
 请注意，我没有在预准备语句中引用字符串参数。 
Isntead of 
QString queryString = "INSERT INTO highscores (name, score) VALUES '%1',%2";
 
you should write 
QString queryString = "INSERT INTO highscores (name, score) VALUES ('%1',%2)";
 
SQL's INSERT command expects a list of datasets after VALUES separated by commas, each of them in parentheses with their column values. 
The following command inserts one tuple into the table table: 
INSERT INTO table (col1, col2) VALUES (val1, val2);
 
| col1 | col2 |
+------+------+
| val1 | val2 |
 
Because you didn't put the two values in parentheses you asked SQLite to insert two datasets with one value each: 
INSERT INTO table (col1, col2) VALUES val1, val2;
 
| col1 | col2 |
+------+------+
| val1 |      |
| val2 |      |
 
(The funny thing is that I tested this with both my SQLite and MySQL installations. They both threw an error when I omitted the parentheses at all. But this may depend on the version and some options you are using.) 
Please never use QString::arg to fill in arguments of a SQL query! 
(Or any other method of string building, like concatenation.) This can lead to a possible SQL injection. 
In order to build statements with variable arguments you should use SQL prepared statements as provided by QSqlQuery bound values: 
QString queryString = "INSERT INTO highscores (name, score) VALUES (?,?)";
QSqlQuery query(queryString);
query.addBindValue(name);
query.addBindValue(score);
query.exec();
 
Note that I did not quote the string argument in the prepared statement.