Convenience Functions

SQLite includes a number of convenience functions that can be used to prepare, step, and finalize an SQL statement in one call. Most of these functions exist for historical reasons and, as the name says, convenience.

While they’re not fully deprecated, there are a number of reasons why their use is not exactly encouraged. First off, understand that there is nothing special under the hood. Both of these functions eventually call the same sqlite3_prepare_xxx(), sqlite3_step(), and sqlite3_finalize() calls that are available in the public API. These functions are not faster, nor are they more efficient.

Second, since the API doesn’t support the use of bound parameters, you’re forced to use string manipulations to build your SQL commands. That means these functions are slower to process and much more vulnerable to SQL injection attacks. This is particularly dangerous because all the convenience functions are designed to automatically process multiple SQL statements from a single command string. If input strings are not properly sanitized, this situation effectively gives anyone providing input data full access to the database engine, including the ability to delete data or drop whole tables.

These functions also tend to be a bit slower. All results are returned in a string representation, without any kind of type information. This can make it difficult to determine the type of a return value, and can lead to a lot of extra type conversions.

For all their disadvantages, ...

Get Using SQLite now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.