char* sqlite3_snprintf( int bytes, char* buf, const char* format, ... );
bytes
The number of bytes available in the output buffer.
buf
A pre-allocated buffer to receive the formatted string.
format
The format string used to build the output
string. This is similar to the standard printf() style formatted
string, but it supports a few extra formatting
flags.
Message formatting parameters.
A pointer to the formatted string buffer.
This function formats and builds a UTF-8 string in the provided
buffer. It is designed to mimic the standard snprintf() function. Assuming
the provided buffer is one byte or larger, the string will
always be null-terminated.
Note that the first two parameters of sqlite3_snprintf() are reversed from the
standard snprintf(). Also,
snprintf() returns the
number of characters in the output string, while sqlite3_snprintf() returns a
pointer to the buffer passed in by the application.
In addition to the standard %s, %c,
%d, %i, %o, %u,
%x, %X, %f, %e,
%E, %g, %G, and %%
formatting flags, all SQLite printf() style functions also support the
%q, %Q, %z, %w, and
%p flags.
The %q flag is similar to
%s, only it will
sanitize the string for use as an SQL string literal. Mostly,
this consists of doubling all the single quote characters
(') to form a proper
SQL escape (''). Thus,
%q will take the input
string O'Reilly and output
O''Reilly. The
formatted string should contain enclosing single quotes (e.g.,
"... '%q' ...").
The %Q flag is similar to
%q, only it will wrap
the input string in single quotes as well. The %Q flag will take the input
string O'Reilly and output
'O''Reilly' (including
the enclosing single quotes). The %Q flag will also output the constant NULL (without single quotes) if
the string value is a NULL pointer. This allows the %Q flag to accept a more diverse
set of character pointers without additional application logic.
Because the %Q includes its
own, the formatted string should contain enclosing single quotes
(e.g., "... %Q ...").
The %w flag is similar to the
%q flag, only it is
designed to work on SQL identifiers, rather than SQL string
constants. Identifiers include database names, table names, and
column names. The %w flag
will sanitize input values by doubling all the double quote
characters (") to form a
proper SQL escape ("").
Similar to %q, the formatted
string that uses %w should
include enclosing quotes. In the case of identifiers, they
should be double quotes:
"... \"%w\" ..."Finally, the %p flag is
designed to format pointers. This will produce a hexadecimal
value, and should work correctly on both 32- and 64-bit
systems.
Generally, building SQL queries using string manipulations is
somewhat risky. For literal values, it is better to use the
prepare/bind/step interface, even if the statement will only be
used once. If you must build a query string, always make sure to
properly sanitize your input values using %q, %Q, or %w,
and always be sure values are properly quoted. That includes
putting double quotes around all identifiers and names.