sqlite3_stmt_readonly Behavior with EXPLAIN QUERY PLAN and Data-Modifying Statements

sqlite3_stmt_readonly Returns False for EXPLAIN QUERY PLAN on UPDATE: Core Mechanics

The sqlite3_stmt_readonly API function is designed to indicate whether a prepared SQLite statement (represented as a sqlite3_stmt object) will modify the database when executed. Developers rely on this function to enforce read-only constraints, optimize transaction handling, or implement security policies. However, a nuanced behavior arises when using EXPLAIN QUERY PLAN with data-modifying statements such as UPDATE, INSERT, or DELETE.

When preparing a statement like EXPLAIN QUERY PLAN UPDATE test SET a = 1, sqlite3_stmt_readonly returns false (0), even though executing the EXPLAIN QUERY PLAN command itself does not alter the database. This contradicts the intuition that EXPLAIN operations are inherently read-only. The confusion stems from how SQLite’s parser and virtual machine interpret the prepared statement’s structure.

The root of this behavior lies in SQLite’s internal handling of EXPLAIN commands. While EXPLAIN QUERY PLAN does not execute the underlying data-modifying operation, it prepares the statement as if it were going to be executed. This preparation phase includes analyzing the query’s bytecode and determining whether the statement contains operations that could modify the database. Consequently, sqlite3_stmt_readonly reflects the nature of the original query (in this case, an UPDATE), not the EXPLAIN wrapper.

This distinction is critical for developers who assume that sqlite3_stmt_readonly exclusively reports whether a statement as executed will modify data. In reality, the function inspects the structure of the prepared statement, including nested or wrapped operations, even if those operations are never executed.

Why EXPLAIN QUERY PLAN on Data-Modifying Statements Triggers Readonly False

1. SQLite’s Bytecode Preparation and the Role of EXPLAIN

When a statement like EXPLAIN QUERY PLAN UPDATE test SET a = 1 is prepared, SQLite’s parser first processes the EXPLAIN keyword. The EXPLAIN command instructs SQLite to generate and return the query plan for the subsequent statement. To generate this plan, SQLite must parse, analyze, and prepare the UPDATE statement as if it were going to execute it. This involves:

• Generating bytecode for the UPDATE operation.
• Resolving table and column references.
• Calculating optimization strategies.

The sqlite3_stmt_readonly function examines the generated bytecode to determine if any operations could modify the database. Since the UPDATE bytecode is present in the prepared statement—even though it is not executed—the function correctly identifies the statement as non-read-only.

2. The Scope of sqlite3_stmt_readonly

The documentation for sqlite3_stmt_readonly explicitly states that it returns false if the statement "makes direct changes to the database file." However, the function does not account for context (e.g., whether the statement is wrapped in EXPLAIN). It operates at the bytecode level, checking for the presence of data-modifying opcodes such as OP_Update, OP_Insert, or OP_Delete.

In the case of EXPLAIN QUERY PLAN, the UPDATE bytecode is embedded within the prepared statement. Even though the UPDATE is never executed, its bytecode is still present, causing sqlite3_stmt_readonly to return false.

3. Design Philosophy: Safety and Accuracy

SQLite prioritizes accuracy in reporting the potential for modification. If a prepared statement includes bytecode that could modify the database under different circumstances (e.g., without the EXPLAIN wrapper), sqlite3_stmt_readonly errs on the side of caution. This design prevents false negatives where a statement might appear read-only but contains hidden write operations.

Resolving Misleading Readonly Reports for EXPLAIN QUERY PLAN Statements

1. Clarifying the Documentation

The SQLite documentation was updated to explicitly address this behavior:

"The sqlite3_stmt_readonly() interface returns true (non-zero) if and only if the prepared statement makes no direct changes to the database file. Note that EXPLAIN and EXPLAIN QUERY PLAN statements do not actually execute the statements they describe, and thus sqlite3_stmt_readonly() returns true for those statements, even if the statements being explained would themselves have changed the database."

This revision emphasizes that sqlite3_stmt_readonly reflects the execution impact of the prepared statement, not the impact of the explained query. Developers must distinguish between:

• The prepared statement’s behavior: EXPLAIN QUERY PLAN itself does not modify the database.
• The nature of the embedded query: The UPDATE within the EXPLAIN is analyzed as a standalone operation.

2. Workarounds for Readonly Checks

If your application requires determining whether a statement will actually modify the database (including EXPLAIN wrappers), implement additional checks:

a. Parsing the SQL Text

Before preparing a statement, inspect the SQL text for the presence of EXPLAIN or EXPLAIN QUERY PLAN. If either keyword is present, treat the statement as read-only regardless of sqlite3_stmt_readonly’s output.

Example code snippet:

int is_readonly(const char *sql) {  
    return (strstr(sql, "EXPLAIN") != NULL);  
}  

b. Using sqlite3_stmt_isexplain

SQLite provides the sqlite3_stmt_isexplain API to detect EXPLAIN statements:

int stmt_type = sqlite3_stmt_isexplain(stmt);  
if (stmt_type == SQLITE_EFFECTIVE_EXPLAIN) {  
    // Handle as read-only  
}  

3. Best Practices for Security and Optimization

• Security: Never grant write permissions based solely on sqlite3_stmt_readonly. Use SQL text analysis or higher-level policies to enforce access control.
• Optimization: Use EXPLAIN QUERY PLAN judiciously. While it does not execute data-modifying operations, preparing such statements still incurs overhead due to bytecode generation.

4. Testing and Validation

To avoid surprises, validate sqlite3_stmt_readonly behavior with test cases:

void test_readonly(sqlite3 *db, const char *sql, int expected) {  
    sqlite3_stmt *stmt;  
    sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);  
    int actual = sqlite3_stmt_readonly(stmt);  
    assert(actual == expected);  
    sqlite3_finalize(stmt);  
}  

int main() {  
    sqlite3 *db;  
    sqlite3_open(":memory:", &db);  
    test_readonly(db, "EXPLAIN QUERY PLAN UPDATE test SET a=1", 1); // Fails!  
    test_readonly(db, "EXPLAIN QUERY PLAN SELECT 1", 1); // Passes  
}  

The first test fails because sqlite3_stmt_readonly returns 0 (false) for the EXPLAIN QUERY PLAN UPDATE statement. Adjust assertions based on the documented behavior.

By understanding SQLite’s bytecode preparation model and leveraging supplemental checks, developers can accurately determine the read-only status of EXPLAIN statements. The behavior of sqlite3_stmt_readonly is not a bug but a deliberate design choice to reflect the presence of data-modifying bytecode, ensuring transparency and safety in database operations.