sqlite3_mprintf - should I always use %Q as format specifier instead of %s?
sqlite
Solution
Everything is about to create SQL-statements in a secure way to minimize the possibility of SQL-injections.
You should prefer `%q` over `%s` all the time.
The `%q` option works like `%s` in that it substitutes a nul-terminated string from the argument list. But `%q` also doubles every `'\''` character. `%q` is designed for use inside a string literal. By doubling each `'\''` character it escapes that character and allows it to be inserted into the string.
The `%Q` has some futher advantages over `%q`.
The %Q option works like %q except it also adds single quotes around the outside of the total string. Additionally, if the parameter in the argument list is a NULL pointer, %Q substitutes the text "NULL" (without single quotes).
This means it will add single quotes and it will render nul pointers as the string literal `"NULL"` inside the string.
As you can see it depends on what you want to do. If you are ok with adding single quotes and that `"NULL"` generating behavior you can simply use `%Q` (I think it makes highly sense for paramter values in SQL-statements). Maybe sometimes you dont want this behavior then you can fall back to `%q`.
Problem
Excerpt from SQLite reference on sqlite3_mprintf() API http://www.sqlite.org/c3ref/mprintf.html "The %Q option works like %q except it also adds single quotes around the outside of the total string. Additionally, if the parameter in the argument list is a NULL pointer, %Q substitutes the text "NULL" (without single quotes)." When using %q, we have to be careful to always use single quotes around it e.g. ``` char *zText = "It's a happy day!"; char *zSQL = sqlite3_mprintf("INSERT INTO table VALUES('%q')", zText); ``` It seems more convenient to always use %Q instead of %q as follows: ``` char *zSQL = sqlite3_mprintf("INSERT INTO table VALUES(%Q)", zText); ``` My question - is there a valid use case where '%q' is more suitable or more efficient? Or can I safely use %Q as a replacement for %s in all statements?