Skip to content

Table.indexes / xindexes fail with OperationalError when identifiers contain double quotes (breaks transform()) #824

Closed

Description

@nyxst4ck

Table.indexes and Table.xindexes build their PRAGMA statements with naive f-string quoting instead of quote_identifier(), so any identifier containing a double quote produces malformed SQL:

import sqlite_utils

db = sqlite_utils.Database(memory=True)
db['Go"sh'].insert({"id": 1, 'c"1': 2}, pk="id")
db['Go"sh'].create_index(['c"1'])
db['Go"sh'].indexes
# sqlite3.OperationalError: near "sh": syntax error

The four sites are in sqlite_utils/db.py:

  • indexes / xindexes: sql = f'PRAGMA index_list("{self.name}")' — embedded " in the table name is never doubled
  • both properties then wrap the index name with an ad-hoc heuristic (f'"{index_name}"' if not index_name.startswith('"') else index_name) that has the same flaw

This looks like a missed spot in the #678 migration (fb93452, "Use double quotes not braces for tables and columns"): quote_identifier() is used 103 times in the same file, including the neighbouring PRAGMA table_info and PRAGMA foreign_key_list, but these four sites kept the old quoting.

It cascades beyond introspection because self.indexes is consumed by transform(), drop_index() and Database.create, so transform() raises OperationalError for any table whose name contains a " — or any table carrying an index on a column whose name contains one. Column names like that routinely arrive from CSV/JSON headers.

Related (same family, different method, can file separately if useful): detect_fts() interpolates content="{self.name}" into its like2 pattern without doubling embedded quotes, so search() reports "Full-text search is not configured" immediately after a successful enable_fts() on such a table.

I have a fix ready (replace the four sites with quote_identifier(), net −6 lines, plus two regression tests) and will open a PR referencing this issue.

Activity

  1. ikatyal2110 commented on Aug 11, 2026

    @ikatyal2110
    Contributor

    I've verified this against the current main and put together the fix. quote_identifier() is already defined at line 79 of db.py and used 103 times in the same file — the four PRAGMA sites in indexes / xindexes just missed it.

    The diff is minimal:

    # indexes property (around line 2286)
    - sql = f'PRAGMA index_list("{self.name}")'
    + sql = f"PRAGMA index_list({quote_identifier(self.name)})"
      ...
    - column_sql = f'PRAGMA index_info("{index_name}")'
    + column_sql = f"PRAGMA index_info({quote_identifier(index_name)})"
    
    # xindexes property (around line 2305)
    - sql = f'PRAGMA index_list("{self.name}")'
    + sql = f"PRAGMA index_list({quote_identifier(self.name)})"
      ...
    - column_sql = f'PRAGMA index_xinfo("{index_name}")'
    + column_sql = f"PRAGMA index_xinfo({quote_identifier(index_name)})"

    Repro that fails before and passes after:

    import sqlite_utils
    db = sqlite_utils.Database(memory=True)
    db['Go"sh'].insert({"id": 1, 'c"1': 2}, pk="id")
    db['Go"sh'].create_index(['c"1'])
    print(db['Go"sh'].indexes)   # OperationalError before fix, Index(...) after

    Happy to open a PR if that would help.


    Generated by Claude Code

  2. added a commit that references this issue on Aug 12, 2026
    e6be626
  3. added 2 commits that reference this issue on Aug 12, 2026
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions