Ability to use parameterized queries, including with textareas

[simonw]

This would have been useful for inserting a big blob of JSON data as seen in this TIL: https://til.simonwillison.net/sqlite/geopoly

I had to manually replace ' with '' in order to get the import to work.

It would have been better if I could have done this:

insert into raw_data (geojson) values (:geojson_textarea)

And had it detect the parameter and use it to add a textarea to the page.

Might have to detect it with JavaScript here since unlike GET read-only queries it's not safe to submit the query first.

Thought I could detect incoming POST that was missing those params and redirect to a ?sql= GET page that prepopulates the form and adds the form fields.

Need protection against clickjacking attacks if I do that though: prevent framing and add a short countdown before the submit button can be pressed.

[simonw]

Related:

• datasette-template-sql causes <function ...> in textarea #6

[simonw]

Might have to detect it with JavaScript here since unlike GET read-only queries it's not safe to submit the query first.

Actually I can use the explain trick here for that: https://github.com/simonw/datasette/blob/dfd4ad558b74defbe23b01196260b087f9a56813/datasette/utils/__init__.py#L1130-L1140

That's in a (currently undocumented) utility function async def derive_named_parameters(db, sql):

It was added in 0.59 simonw/datasette@fc48468 so it's safe to use. I should document it in Datasette though to make sure it's part of the documented stable API.

[simonw]

Demo:

I decided to have the green message fade out after 2s because otherwise submitting the form multiple times looked like nothing had happened.

[simonw]

This syntax does not work in Python 3.8:

        response2 = await ds.client.post(
            "/-/write",
>           data={
                "sql": sql,
                "csrftoken": csrftoken,
                "database": database,
            }
            | params,
            cookies=cookies,
        )
E       TypeError: unsupported operand type(s) for |: 'dict' and 'dict'