Skip to content

Reconsider policy on blocking queries containing the string "pragma" #1618

Open

Description

@strada

First of all, thanks for creating this cool project, and also supporting publishing to various hosting services out of the box.

While testing out, I noticed legitimate queries such as

select * from books where title like 'Pragmatic%'

or

select * from books where title = 'The Pragmatic Programmer'

are blocked, due to the regular expression check here:
https://github.com/simonw/datasette/blob/main/datasette/utils/__init__.py#L185

Example as seen from a Datasette instance:
https://fivethirtyeight.datasettes.com/polls?sql=select+*+from+books+where+title+like+%27Pragmatic%25%27%0D%0A

I'd propose a regular expression like

re.compile(f"pragma_(?!({'|'.join(allowed_pragmas)}))"),

instead of

re.compile(f"pragma(?!_({'|'.join(allowed_pragmas)}))"),

I can create a pull request with this change, unless the maintainers think it would allow unwanted queries to be executed.

Activity

  1. simonw commented on Feb 2, 2022

    @simonw
    Owner

    There's a workaround for this at the moment, which is to use parameterized SQL queries. For example, this:

    https://fivethirtyeight.datasettes.com/polls?sql=select+*+from+books+where+title+%3D+%3Atitle&title=The+Pragmatic+Programmer

    So the SQL query is select * from books where title = :title and then &title=... is added to the URL.

    The reason behind the quite aggressive pragma filtering is that SQLite allows you to execute pragmas using function calls, like this one:

    SELECT * FROM pragma_index_info('idx52');

    These can be nested arbitrarily deeply in sub-queries, so it's difficult to write a regular expression that will definitely catch them.

    I'm open to relaxing the regex a bit, but I need to be very confident that it's safe to do so.

  2. simonw commented on Feb 2, 2022

    @simonw
    Owner

    But... I just noticed something I had missed in the docs for https://www.sqlite.org/pragma.html#pragfunc

    Table-valued functions exist only for PRAGMAs that return results and that have no side-effects.

    So it's possible I'm being overly paranoid here after all: what I want to block here is people running things like PRAGMA case_sensitive_like = 1 which could affect the global state for that connection and cause unexpected behaviour later on.

    So maybe I should allow all pragma functions. I previously allowed an allow-list of them in:

  3. changed the title [-]Checks for allowed PRAGMAs prevent legitimate queries[/-] [+]Reconsider policy on blocking queries containing the string "pragma"[/+] on Feb 2, 2022
  4. simonw commented on Feb 2, 2022

    @simonw
    Owner

    I also just realized that pragma pragma_list can be used to generate a list of all known pragmas for the connection:

    sqlite-utils fixtures.db 'pragma pragma_list' --fmt github
    
    name
    analysis_limit
    application_id
    auto_vacuum
    automatic_index
    busy_timeout
    cache_size
    cache_spill
    case_sensitive_like
    cell_size_check
    checkpoint_fullfsync
    collation_list
    compile_options
    count_changes
    data_version
    database_list
    default_cache_size
    defer_foreign_keys
    empty_result_callbacks
    encoding
    foreign_key_check
    foreign_key_list
    foreign_keys
    freelist_count
    full_column_names
    fullfsync
    function_list
    hard_heap_limit
    ignore_check_constraints
    incremental_vacuum
    index_info
    index_list
    index_xinfo
    integrity_check
    journal_mode
    journal_size_limit
    legacy_alter_table
    lock_proxy_file
    locking_mode
    max_page_count
    mmap_size
    module_list
    optimize
    page_count
    page_size
    pragma_list
    query_only
    quick_check
    read_uncommitted
    recursive_triggers
    reverse_unordered_selects
    schema_version
    secure_delete
    short_column_names
    shrink_memory
    soft_heap_limit
    synchronous
    table_info
    table_list
    table_xinfo
    temp_store
    temp_store_directory
    threads
    trusted_schema
    user_version
    wal_autocheckpoint
    wal_checkpoint
    writable_schema

    So I could use that list to create a much more specific regular expression, which would then allow the word "pragma" to be used more freely while still protecting against any known pragma function being called.

  5. simonw commented on Feb 2, 2022

    @simonw
    Owner

    I also need to consider if supposedly harmless side-effect free pragma functions could be used to work around the Datasette permissions system. My hunch is that wouldn't be a problem, because if you're allowing arbitrary SQL queries you're already letting people ignore the permissions system.

    One example:

    sqlite-utils fixtures.db 'pragma database_list' -t
      seq  name    file
    -----  ------  ------------------------------------------------------
        0  main    /Users/simon/Dropbox/Development/datasette/fixtures.db
    

    Though it looks like I already allow-listed that one in #761: https://latest.datasette.io/_memory?sql=select+*+from+pragma_database_list%28%29

  6. simonw commented on Feb 2, 2022

    @simonw
    Owner

    I was hoping that explain select ... might be able to easily spot when people are calling PRAGMA functions, but this output doesn't look very helpful:

    % sqlite-utils fixtures.db 'explain select * from pragma_database_list()' -t
      addr  opcode         p1    p2    p3  p4                   p5  comment
    ------  -----------  ----  ----  ----  -----------------  ----  ---------
         0  Init            0    11     0                        0
         1  VOpen           0     0     0  vtab:7F9C90AC3070     0
         2  Integer         0     1     0                        0
         3  Integer         0     2     0                        0
         4  VFilter         0    10     1                        0
         5  VColumn         0     0     3                        0
         6  VColumn         0     1     4                        0
         7  VColumn         0     2     5                        0
         8  ResultRow       3     3     0                        0
         9  VNext           0     5     0                        0
        10  Halt            0     0     0                        0
        11  Transaction     0     0    35  0                     1
        12  Goto            0     1     0                        0
    
  7. strada commented on Feb 2, 2022

    @strada
    Author

    Thanks for looking into this. It might have been nice if explain surfaced these function calls. Looks like explain query plan does, but only for basic queries.

    sqlite-utils fixtures.db 'explain query plan select * from pragma_function_list(), pragma_database_list(), pragma_module_list()' -t
      id    parent    notused  detail
    ----  --------  ---------  ------------------------------------------------
       4         0          0  SCAN pragma_function_list VIRTUAL TABLE INDEX 0:
       8         0          0  SCAN pragma_database_list VIRTUAL TABLE INDEX 0:
      12         0          0  SCAN pragma_module_list VIRTUAL TABLE INDEX 0:
    
    sqlite-utils fixtures.db 'explain query plan select * from pragma_function_list() as fl, pragma_database_list() as dl, pragma_module_list() as ml' -t
      id    parent    notused  detail
    ----  --------  ---------  ------------------------------
       4         0          0  SCAN fl VIRTUAL TABLE INDEX 0:
       8         0          0  SCAN dl VIRTUAL TABLE INDEX 0:
      12         0          0  SCAN ml VIRTUAL TABLE INDEX 0:
    
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

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions