c++qtsqliteqt5qtsql

Single quotes prevent bindValue from replacing placeholder mark


When I prepare a statement with bindValue the placeholder mark is not replaced if it is surrounded with single quotes. This is problematic since in SQL strings are surrounded by single quotes to avoid keyword conflicts.

See my attachments with screenshots of the content of the database once inserted with and once without single quotes.

I already reported a bug, but meanwhile, I am not sure anymore if this is not just an encoding problem. Is it correct to use single quotes, i.e., should this work/ is this really a bug?

With quotes:

enter image description here

Without quotes:

enter image description here


Solution

  • It is not a bug. Just don't use the single quotes. The bindValue mechanism does not just replace your :path with a string in your statement. No risk of name conflicts. See it as some kind of different namespace.

    Wikipedia — Prepared statement — Software support:

    Prepared statements are normally executed through a non-SQL binary protocol, for efficiency and protection from SQL injection, but with some DBMSs such as MySQL are also available using a SQL syntax for debugging purposes.

    Wikipedia — SQL injection — Parameterized statements:

    With most development platforms, parameterized statements that work with parameters can be used (sometimes called placeholders or bind variables) instead of embedding user input in the statement. A placeholder can only store a value of the given type and not an arbitrary SQL fragment. Hence, the SQL injection would simply be treated as a strange (and probably invalid) parameter value.