Using SQLite

Name

sqlite3_snprintf() — Format a string

Definition

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.
Additional parameters: Message formatting parameters.
Returns: A pointer to the formatted string buffer.

Description

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.

Table of Contents for
Using SQLite

Name

Definition

Description

See Also

Table of Contents for Using SQLite

Name

Definition

Description

See Also

Table of Contents for
Using SQLite