Feature request: allow using template strings as query statements

[cgranade]

Python 3.14 adds template strings, which are written like f-strings, but allow capturing interpolations before they are resolved. This feature was originally designed to support usecases such as parameterized SQL statements, and could be adopted for APSW cursor.execute calls.

As a proof of concept, the following function converts t-strings to (str, Bindings) tuples appropriate for use with cursor.execute:

def template_as_bindings(query: str | Template) -> tuple[str, Bindings]:
    if isinstance(query, str):
        return (query, {})

    query_as_str = ""
    bindings: dict[str, SQLiteValue] = {}
    idx = 0
    for segment in query:
        if isinstance(segment, str):
            query_as_str += segment

        else:
            name = f"param{idx}"
            bindings[name] = segment.value
            query_as_str += f"${name}"
            idx += 1

    return query_as_str, bindings

For example:

>>> user_id = 12
>>> order_id = 42
>>> template_as_bindings(t"select * from foo where foo.user_id = {user_id} and foo.order_id = {order_id};") 
('select * from foo where foo.user_id = $param0 and foo.order_id = $param1;', {'param0': 12, 'param1': 42})

[rogerbinns]

See also #593 (where I completely misunderstood to start out ...)

One of the things I want to get right is non-values. The above and the discussion are fine for value bindings. But there is also the requirement for things that have be expanded inline. As an example SQLite doesn't allow bindings for database, table, column, function etc names.

In those cases folks have to use string interpolation to compose SQL and inevitably end up with SQL injection. So how should it look for:

table = 'foo'
cross = 'order'
id = 73
t"SELECT * FROM {table} WHERE {table}.{cross}_id = {id} AND ..."

I've been thinking along the lines of using :q to mark as SQL identifier quoting, so you'd write:

t"SELECT * FROM {table:q} WHERE {table:q}.{cross:q}_id = {id} AND ..."

That means every interpolation is treated as a binding and replaced by ? in the SQL providing there is no formatting, and the only formatting allowed is q which always double quotes the value back into the SQL. That would prevent partial identifiers like {cross:q}_id but it seems like a good thing.

For reference the equivalent in SQLite although they need all the formats whereas we only need the quote SQL bit that they use %w for.

Thoughts?

[cgranade]

Ah, sorry for missing the discussion thread, I searched issues but didn't think to check there as well!

Anyway, I really like the idea of using formatting flags to indicate quoting or double-quoting, as it's not clear from context what t"SELECT * FROM {table};" should do. That is, would the user expect a double-quoted string, so that table is safe to use as an identifier, or would they expect a very literal interpolation?

The other way to go could be to use a format flag to indicate that a binding should be generated, making the intent more explicit, something like t"SELECT * FROM foo WHERE foo.user = {user:?};", then having the case without flags act like normal interpolation (or possibly causing a warning or error, depending on apsw.bestpractice flags).

[rogerbinns]

Sketch of a solution, name perhaps changed later using template_string here.

• Add Connection.convert_template_string
• Add apsw.ext.convert_template_string (default for above?)
• It should return a tuple of (SQL, bindings) where bindings can be None
• When Connection.execute gets PyTemplate_Type/string.templatelib.Template call the convertor. The returned bindings override query bindings if they were None else error. This allows ids to be expanded but not bindings from both the template string and the query.
• When Connection.executemany gets the same, the returned bindings must be None

Later consider allowing bindings coming from both convert_template_string and the execute/executemany. Something like look in the execute/executemany bindings first, and fallback to convert_template_string bindings if not found. This could be complicated.

[rogerbinns]

@cgranade I'm thinking the default of no format flag means a binding, and something like id means identifier (ie double quote). This will always be safe from injection:

table = "x); DROP TABLE students;--"

convert_template_string(t"SELECT * FROM {table}")
-> ("SELECT * FROM ?", ("x); DROP TABLE students;--",))

convert_template_string(t"SELECT * FROM {table:id}")
-> ('SELECT * FROM "x); DROP TABLE students;--"', None)

convert_template_string(t"SELECT {a:hello}")
-> Exception("Format 'hello' not allowed')

column="order"
convert_template_string(t"SELECT {column:id}_id FROM {column:id}")
-> ('SELECT "order"_id FROM "order"', None)   # SQL error

[cgranade]

That makes a lot of sense, yeah! Would :id check whether the string contains double-quotes or otherwise might cause an injection when double-quoted? E.g., the case where table = "\"x); DROP TABLE students;--", and where table is used in convert_template_string(t"SELECT * FROM {table:id}"). Would that cause an exception immediately, or would the \" in table be escaped, possibly leading to a safe failure in sqlite later?

[rogerbinns]

id would always double quote itself and double up any double quotes inside. That would prevent usage like {cross:id}_id which I am okay with.

For folks using apsw.bestpractise no form of injection is possible since double quotes can only be used for identifiers and give an error when used for a value.

For everyone else you won't be able to cause different tokenization of the SQL (the usual method of injection)

I'll have to think a little more about the values too. Things like {name['foo'] + 'bar'} and {'one' if a else 'two':id} which then requires performing expression evaluation.

[cgranade]

The expression evaluation, at least, is handled by the string template class directly; Interpolation.value returns the evaluated expression, but before that value is inserted into the string.

[rogerbinns]

Two reasons:

• For the folks on Python 3.13 or lower it should be possible to make a wrapper in apsw.ext that uses the locals() of the caller. eg con.execute(apsw.ext.Template("SELECT {x} FROM {y:id}")) and have it magically work.
• I'm thinking ahead to Query storage in separate file #594 and need to address the same issue - the "template" string coming from that file and the values coming from locals() of the caller.

But eval is scary ...

[rogerbinns]

The eval problem turns out to have an easy solution. Just like id, also allow eval so it is opt in. It won't affect folks using 3.14 tstring directly but will help the two scenarios I gave above.