issue_comments
6 rows where issue = 1121121305
This data as json, CSV (advanced)
Suggested facets: user, author_association, created_at (date), updated_at (date)
| id ▼ | html_url | issue_url | node_id | user | created_at | updated_at | author_association | body | reactions | issue | performed_via_github_app |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1027653005 | https://github.com/simonw/datasette/issues/1618#issuecomment-1027653005 | https://api.github.com/repos/simonw/datasette/issues/1618 | IC_kwDOBm6k_c49QL2N | simonw 9599 | 2022-02-02T07:22:13Z | 2022-02-02T07:22:13Z | 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: ```sql 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. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Reconsider policy on blocking queries containing the string "pragma" 1121121305 | |
| 1027654979 | https://github.com/simonw/datasette/issues/1618#issuecomment-1027654979 | https://api.github.com/repos/simonw/datasette/issues/1618 | IC_kwDOBm6k_c49QMVD | simonw 9599 | 2022-02-02T07:25:22Z | 2022-02-02T07:25:22Z | 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: - #761 | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Reconsider policy on blocking queries containing the string "pragma" 1121121305 | |
| 1027656000 | https://github.com/simonw/datasette/issues/1618#issuecomment-1027656000 | https://api.github.com/repos/simonw/datasette/issues/1618 | IC_kwDOBm6k_c49QMlA | simonw 9599 | 2022-02-02T07:27:14Z | 2022-02-02T07:27:14Z | 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 | … | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Reconsider policy on blocking queries containing the string "pragma" 1121121305 | |
| 1027656518 | https://github.com/simonw/datasette/issues/1618#issuecomment-1027656518 | https://api.github.com/repos/simonw/datasette/issues/1618 | IC_kwDOBm6k_c49QMtG | simonw 9599 | 2022-02-02T07:28:14Z | 2022-02-02T07:31:30Z | 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 | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Reconsider policy on blocking queries containing the string "pragma" 1121121305 | |
| 1027659018 | https://github.com/simonw/datasette/issues/1618#issuecomment-1027659018 | https://api.github.com/repos/simonw/datasette/issues/1618 | IC_kwDOBm6k_c49QNUK | simonw 9599 | 2022-02-02T07:32:47Z | 2022-02-02T07:32:47Z | 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 ``` | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Reconsider policy on blocking queries containing the string "pragma" 1121121305 | |
| 1028294089 | https://github.com/simonw/datasette/issues/1618#issuecomment-1028294089 | https://api.github.com/repos/simonw/datasette/issues/1618 | IC_kwDOBm6k_c49SoXJ | strada 770231 | 2022-02-02T19:42:03Z | 2022-02-02T19:42:03Z | NONE | 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: ``` | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Reconsider policy on blocking queries containing the string "pragma" 1121121305 |
Advanced export
JSON shape: default, array, newline-delimited, object
CREATE TABLE [issue_comments] (
[html_url] TEXT,
[issue_url] TEXT,
[id] INTEGER PRIMARY KEY,
[node_id] TEXT,
[user] INTEGER REFERENCES [users]([id]),
[created_at] TEXT,
[updated_at] TEXT,
[author_association] TEXT,
[body] TEXT,
[reactions] TEXT,
[issue] INTEGER REFERENCES [issues]([id])
, [performed_via_github_app] TEXT);
CREATE INDEX [idx_issue_comments_issue]
ON [issue_comments] ([issue]);
CREATE INDEX [idx_issue_comments_user]
ON [issue_comments] ([user]);