Handling Terminated Comments in SQLite3_complete Function

Understanding sqlite3_complete Behavior with Comments and Incomplete Statements

The core challenge revolves around the behavior of the sqlite3_complete function in SQLite when processing inputs containing terminated comments (e.g., -- comment or /* comment */) without accompanying executable SQL statements. The function returns 0 (incomplete) for inputs consisting solely of terminated comments, even though such inputs are syntactically valid and self-contained. This forces downstream applications like the SQLite shell to implement workarounds, such as appending semicolons or implementing custom comment-parsing logic. The problem is exacerbated by the absence of a mechanism to distinguish between "valid terminated comments with no executable code" and "incomplete SQL fragments."

This issue impacts tools that process SQL interactively (e.g., command-line shells, database IDEs) or programmatically (e.g., ORMs generating dynamic SQL). When users input comments without trailing semicolons or executable code, these tools must decide whether to treat the input as complete. The current implementation of sqlite3_complete fails to account for comment-only inputs, requiring developers to layer additional parsing logic on top of SQLite’s native API.

Root Causes of sqlite3_complete’s Inadequate Comment Handling

1. Definition of "Complete" in sqlite3_complete

The sqlite3_complete function is designed to validate whether a string contains one or more executable SQL statements terminated by semicolons. Its primary purpose is to determine if a string can be passed to sqlite3_prepare_v2 without syntax errors. Comments, by definition, are not executable statements. Consequently, the function treats comment-only inputs as incomplete because they lack executable content, even if the comments are properly terminated. This reflects a narrow interpretation of "completeness" tied to executable semantics rather than syntactic validity.

2. Ambiguity in Interactive Input Processing

Interactive SQL tools often accept multi-line input, requiring a heuristic to decide when the user has finished typing. For example, the SQLite shell uses sqlite3_complete to determine whether to execute the accumulated input. However, users may write comments without semicolons, leading the shell to wait indefinitely for more input. The current API provides no way to differentiate between "incomplete statement" and "complete comment," forcing tools to implement kludges like appending a semicolon or manually stripping comments before invoking sqlite3_complete.

3. Limitations in SQLite’s Public API

SQLite’s parser internally differentiates between comments, whitespace, and executable code during tokenization. However, this capability is not exposed through public APIs like sqlite3_complete. Developers cannot leverage SQLite’s native comment-parsing logic without reverse-engineering internal functions or duplicating logic, as seen in the SQLite shell’s source code. This violates the principle of abstraction—a public API should encapsulate implementation details while providing sufficient flexibility for common use cases.

Strategies for Resolving Comment Handling in sqlite3_complete

1. Modifying sqlite3_complete to Recognize Comment-Only Inputs

The ideal long-term solution is to enhance sqlite3_complete to return a distinct value (e.g., 2) when the input consists solely of whitespace and terminated comments. This preserves backward compatibility while allowing tools to handle comment-only inputs appropriately. Internally, this would require extending SQLite’s parser to track whether any executable tokens (e.g., SELECT, INSERT) were encountered during parsing. If the parser reaches the end of the input without finding executable tokens but detects valid terminated comments, it would return the new status code.

Implementation Steps:

• Extend the parser’s state machine to track the presence of executable tokens and terminated comments.
• Modify sqlite3_complete to return 2 for comment-only/whitespace inputs, 1 for valid SQL statements, and 0 otherwise.
• Update documentation to clarify the semantics of the new return value.

Example Code Snippet:

int sqlite3_complete(const char *zSql) {
  int rc;
  sqlite3_stmt *pStmt;
  int hasExecutableTokens = 0;
  const char *zEnd = &zSql[strlen(zSql)];

  /* Parse tokens, updating hasExecutableTokens when SQL keywords are found */
  while( zSql < zEnd ){
    int tokenType;
    zSql += sqlite3GetToken((unsigned char*)zSql, &tokenType);
    if( tokenType>=TK_SEMI && tokenType<TK_SPACE ) {
      hasExecutableTokens = 1;
    }
  }

  if( !hasExecutableTokens && sqlite3IsOnlyWhitespaceAndComments(zSql) ) {
    return 2;  /* Comment-only input */
  }
  /* Existing logic to check for complete statements */
}

2. Implementing a Wrapper Function for Comment-Aware Completion

For developers unable to modify SQLite’s source code, a workaround is to create a wrapper function that combines sqlite3_complete with custom comment/whitespace detection. This function would first check if the input is entirely whitespace/comments using a regex or a simple parser, then fall back to sqlite3_complete.

Steps:

• Strip comments and whitespace from the input string.
• If the stripped string is empty, treat the input as "complete" (for comment-only cases).
• Otherwise, delegate to sqlite3_complete.

Example (Python):

def is_complete(sql):
    # Remove C-style /* */ comments
    sql = re.sub(r'/\*.*?\*/', '', sql, flags=re.DOTALL)
    # Remove -- line comments
    sql = re.sub(r'--.*', '', sql)
    # Remove whitespace
    sql = sql.strip()
    if not sql:
        return True  # Input is entirely comments/whitespace
    # Append semicolon and check with sqlite3_complete
    return sqlite3.complete_statement(sql + ';')

3. Adopting the SQLite Shell’s Workaround with Semicolon Injection

The SQLite shell’s approach of temporarily appending a semicolon to the input string can be generalized. By inserting a semicolon before invoking sqlite3_complete, the function is forced to evaluate the input as a potential statement. If the original input was a comment, the appended semicolon creates a valid (albeit empty) statement.

C Code Example:

int custom_complete(const char *zSql) {
  char *modified_sql = sqlite3_mprintf("%s;", zSql);
  int rc = sqlite3_complete(modified_sql);
  sqlite3_free(modified_sql);
  return rc;
}

Caveats:

• This fails if the original input already contains a trailing semicolon.
• It may incorrectly mark inputs like SELECT 1; -- comment as incomplete if the semicolon is not the last character.

4. Proposing a New API Function to the SQLite Team

Developers can advocate for a new public API function, such as sqlite3_input_complete, which returns a tri-state value:

• 0: Incomplete input.
• 1: Complete executable statement.
• 2: Complete non-executable input (comments/whitespace).

This approach aligns with the SQLite team’s philosophy of keeping the core lean while allowing extensions for edge cases. A formal proposal to the SQLite mailing list or issue tracker would be necessary, accompanied by a patch implementing the new function.

5. Leveraging SQLite’s Internal Comment Parsing Logic

The SQLite shell implements a comment parser in shell.c (function is_complete_comment). Developers can adapt this code into their projects, though this carries risks if SQLite’s internal implementation changes. The parser checks for unclosed /* */ comments and trailing -- comments.

Adapted Code:

int is_complete_comment(const char *zSql) {
  int state = 0;  // 0: normal, 1: in -- comment, 2: in /* comment
  while(*zSql) {
    switch(state) {
      case 0:
        if( zSql[0]=='-' && zSql[1]=='-' ) state = 1;
        else if( zSql[0]=='/' && zSql[1]=='*' ) { state = 2; zSql++; }
        else if( !isspace(*zSql) ) return 0;  // Non-comment text
        break;
      case 1:
        if( *zSql == '\n' ) state = 0;
        break;
      case 2:
        if( zSql[0]=='*' && zSql[1]=='/' ) { state = 0; zSql++; }
        break;
    }
    zSql++;
  }
  return (state == 0);  // 1 if all comments are terminated
}

Final Recommendations

For most applications, Solution 2 (wrapper function with comment stripping) provides a balance between reliability and portability. Those contributing to SQLite itself should pursue Solution 1 or 4 to address the issue at the API level. The semicolon injection hack (Solution 3) remains a last resort due to its brittleness. By understanding the parser’s behavior and leveraging SQLite’s existing infrastructure, developers can mitigate the limitations of sqlite3_complete while awaiting upstream improvements.