sqlite3_mprintf - should I always use %Q as format specifier instead of %s?

Question

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?

Answer 1

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.