SQLite insert into table -> Parameter count mismatch

[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 +"' ";

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

To copy to clipboard, switch view to plain text mode 

when the malicious user has provided this as userInput:

dummy' OR 'x' = 'x

dummy' OR 'x' = 'x

To copy to clipboard, switch view to plain text mode 

the query becomes:

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

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

To copy to clipboard, switch view to plain text mode 

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.