SQLite insert into table -> Parameter count mismatch

[KeineAhnung]

Hi,

I was following a tutorial to learn something about Qt and C++ but at one point my code does not work. I try to enter some data in a fresh created SQLite3 database. The database was create with the FireFox add-on and is empty at the moment. I double checked that all names are correct and watched the tutorial three time to figure out what I did wrong.
Here is the code for inserting. I am quite sure that I am connected to the data base be cause I can read from a different table.


Settings::Settings(QWidget *parent) :
QDialog(parent),
ui(new Ui::Settings)
{
ui->setupUi(this);
MainWindow connect;
if(connect.conOpen()){
qDebug()<<("Connected...");
}else{
qDebug()<<("Not connected!");
}
}

void Settings::on_pushButton_ok_clicked(){
// Save data to db
MainWindow connect; // here are the functions conOpen() and conClose() defined
QString str1, str2;
QDateTime date(QDateTime::currentDateTime());

str1=ui->editName->text();
str2=ui->editPassword->text();

QSqlQuery qry;
qry.prepare("insert into table (name,password,date) values ('"+str1+"', "+str2+", "+date.toString()+")");
if(qry.exec()){
qDebug()<<("Data was saved");
// Sent result to log on MainWindow
connect.conClose();
this->hide();
}else{
qDebug()<<(qry.lastError().text());
}
}

Before I try to write to the data base I closed the old connection and opened a new one. Here is the output:


Connected to database...
Connected...
QSqlDatabasePrivate::addDatabase: duplicate connection name 'qt_sql_default_connection', old connection removed.
Connected to database...
" Parameter count mismatch"

I have read that there are issues if you create a SQLite DB with Qt but I did not. Does anyone see what I did wrong?

[jefftee]

qry.prepare("insert into table (name,password,date) values ('"+str1+"', "+str2+", "+date.toString()+")");


First of all, the Sqlite documentation shows that table is a reserved keyword, so you should avoid using a table name of "table" as you show in your insert statement. Not sure if that's allowed (I didn't actually try it), but you should avoid using reserved keywords as object names or column names, etc.

Secondly, it doesn't look to me like you have formatted the query values correctly. The name value is properly quoted in single quotes, but you're missing single quotes around the password and the date values which also need to be quoted unless they are numeric values.

As a best practice, you should not build the SQL query like that, you should either use use parameterized queries as such:



qry.prepare("insert into table (name,password,date) values (?,?,?)";
qry.bindValue(0,str1);
qry.bindValue(1,str2);
qry.bindValue(2,date.toString());
qry.exec();

or even better, use named query parameters:



qry.prepare("insert into table (name,password,date) values (:name,:password,:date)");
qry.bindValue(":name", str1);
qry.bindValue(":password", str2);
qry.bindValue(":date", date.toString());
qry.exec();

Lastly, when you created the QSqlQuery qry on the stack, you used the default constructor, which uses an empty query string and a default QSqlDatabase() instance. You should have specified the DB instance in the constructor. This associates your query with the correct database instance (namely, the instance you already opened). Since you have not done that, this may also be the source of your "Parameter count mismatch" error as well. Here's how you should have specified the QSqlQuery declaration:



QSqlQuery qry(db); // you don't show what your QSqlDatabase variable is named, so I used db as an example




I have read that there are issues if you create a SQLite DB with Qt but I did not. Does anyone see what I did wrong?

There are no issues creating or using Sqlite databases with Qt. There is no need to close/reopen the database before you write to it.

Good luck.

Jeff

[KeineAhnung]

Hi Jeff,

thanks for the hints! It were the single quotes and the missing constructor for the QSqlQuery.
I should have seen the missing quotes. This is something I ran into a couple of times writing php code for MySQL. Actually this is why I wrote the sql statement like that. Parameterization looks odd to me. Is there an advantage doing this or is this just a personal style thing?

[Lesiok]

But if you had used such a structure is probably this thread would have never been.

[ChrisW67]

Parameterization looks odd to me. Is there an advantage doing this or is this just a personal style thing?
It has the advantage that you do not have to worry about quoting values, the query is easier to read and validate, and it is clear what values are inserted into the query. The first point has substantial security advantages in the face of user-input. Consider this example:


QString qry = "UPDATE passwordTable SET password ='" + newPassword + "' WHERE userName = '" + userInput +"' ";

when the malicious user has provided this as userInput:


dummy' OR 'x' = 'x

the query becomes:


UPDATE passwordTable SET password ='opensesame' WHERE userName = 'dummy' OR 'x' = 'x'

which is obviously a bad thing. Using parameters avoids this possibility.

[jefftee]

Parameterization looks odd to me. Is there an advantage doing this or is this just a personal style thing?
There are two advantages to using parameterized queries:

1. As written, your query string is susceptible to an SQL injection issue that could be exploited by someone.

2. Improved performance. You won't notice a difference in your example, but if you were looping and inserting lots of rows with the same SQL statement using different data values, you should prepare the query outside of the loop (one time), then bind values and exec inside the loop. This allows the db engine to optimize the query when it is prepared and reduces overhead when executing the prepared query over and over again.

As you have written your example, there's really no benefit to you doing a prepare/exec since you are building the query string dynamically and only executing the prepared query once. You could just have easily passed the query string to exec and skipped the prepare. I would recommend, however, that you get used to using parameterized queries, which are more secure and offer better performance.

Good luck.

Jeff

[Lesiok]

There are two advantages to using parameterized queries:

2. Improved performance. You won't notice a difference in your example, but if you were looping and inserting lots of rows with the same SQL statement using different data values, you should prepare the query outside of the loop (one time), then bind values and exec inside the loop. This allows the db engine to optimize the query when it is prepared and reduces overhead when executing the prepared query over and over again.


If performance is important use preparing with ? char not names. This method is about 20-40% faster. This is my observation when the program copies the tens on millions of records from one database to another.