Table names containing single quotes break things

[simonw]

e.g. I found a table called Yesterday's ELRs by County

It threw an error inside the detect_fts() function attempting to run this SQL query:

        select name from sqlite_master
            where rootpage = 0
            and (
                sql like '%VIRTUAL TABLE%USING FTS%content="Yesterday's ELRs by County"%'
                or sql like '%VIRTUAL TABLE%USING FTS%content=[Yesterday's ELRs by County]%'
                or (
                    tbl_name = "Yesterday's ELRs by County"
                    and sql like '%VIRTUAL TABLE%USING FTS%'
                )
            )

Here's the code at fault:

datasette/datasette/utils/__init__.py

Lines 534 to 548 in 640ac70

	def detect_fts_sql(table):
	return r"""
	select name from sqlite_master
	where rootpage = 0
	and (
	sql like '%VIRTUAL TABLE%USING FTS%content="{table}"%'
	or sql like '%VIRTUAL TABLE%USING FTS%content=[{table}]%'
	or (
	tbl_name = "{table}"
	and sql like '%VIRTUAL TABLE%USING FTS%'
	)
	)
	""".format(
	table=table
	)

[simonw]

Created a test database like this:

% sqlite-utils create-table quote-in-name.db "this'hasquoteinname" id integer name text --pk id
% datasette quote-in-name.db -p 8025 --pdb
INFO:     Started server process [86046]
INFO:     Waiting for application startup.
INFO:     Application startup complete.
INFO:     Uvicorn running on http://127.0.0.1:8025 (Press CTRL+C to quit)
> /Users/simon/Dropbox/Development/datasette/datasette/utils/__init__.py(530)detect_fts()
-> rows = conn.execute(detect_fts_sql(table)).fetchall()
(Pdb) c
Traceback (most recent call last):
  File "/Users/simon/Dropbox/Development/datasette/datasette/app.py", line 1124, in route_path
    response = await view(request, send)
  File "/Users/simon/Dropbox/Development/datasette/datasette/views/base.py", line 147, in view
    return await self.dispatch_request(
  File "/Users/simon/Dropbox/Development/datasette/datasette/views/base.py", line 122, in dispatch_request
    return await handler(request, *args, **kwargs)
  File "/Users/simon/Dropbox/Development/datasette/datasette/views/index.py", line 72, in get
    "fts_table": await db.fts_table(table),
  File "/Users/simon/Dropbox/Development/datasette/datasette/database.py", line 279, in fts_table
    return await self.execute_fn(lambda conn: detect_fts(conn, table))
  File "/Users/simon/Dropbox/Development/datasette/datasette/database.py", line 155, in execute_fn
    return await asyncio.get_event_loop().run_in_executor(
  File "/Users/simon/.pyenv/versions/3.8.2/lib/python3.8/concurrent/futures/thread.py", line 57, in run
    result = self.fn(*self.args, **self.kwargs)
  File "/Users/simon/Dropbox/Development/datasette/datasette/database.py", line 153, in in_thread
    return fn(conn)
  File "/Users/simon/Dropbox/Development/datasette/datasette/database.py", line 279, in <lambda>
    return await self.execute_fn(lambda conn: detect_fts(conn, table))
  File "/Users/simon/Dropbox/Development/datasette/datasette/utils/__init__.py", line 530, in detect_fts
    rows = conn.execute(detect_fts_sql(table)).fetchall()
sqlite3.OperationalError: near "hasquoteinname": syntax error

[simonw]

I tried and failed to get this fix working for tables with double quotes in their name - I couldn't figure out what the double-quote-in-a-table-name version of this code would look like:

datasette/datasette/utils/__init__.py

Lines 538 to 548 in 807de37

	return r"""
	select name from sqlite_master
	where rootpage = 0
	and (
	sql like '%VIRTUAL TABLE%USING FTS%content="{table}"%'
	or sql like '%VIRTUAL TABLE%USING FTS%content=[{table}]%'
	or (
	tbl_name = "{table}"
	and sql like '%VIRTUAL TABLE%USING FTS%'
	)
	)