Using python 3.14 template strings as an easier way to provide parameter bindings

[char101]

I really enjoyed writing sql with parameter bindings using javascript tagged template, and now we can also use the same syntax in python 3.14.

from string.templatelib import Template

import apsw


class Connection(apsw.Connection):
    def execute(self, sql, params=None, *args, **kwargs):
        if isinstance(sql, Template):
            params = sql.values
            sql = ' ? '.join(sql.strings)
        return super().execute(sql, params, *args, **kwargs)


conn = Connection(':memory:')
conn.execute('create table a (x int, y int)')
x = 1
conn.execute(t'insert into a (x, y) values ({x + 2}, 4)')
rows = conn.execute(t'select y from a where x = {x + 2}').fetchall()
print(rows)
# [(4,)]

[rogerbinns]

String templates are a really bad match for SQL, and even worse for SQLite. A completely safe Pythonic equivalent is:

conn.execute("insert in a (x,y) values($x+2,  4)", locals())
conn.execute("select y from a where x = $x + 2", locals()).fetchall()

Traditional networked databases usually had the client driver send a SQL string, so even if the developer used bindings, the driver made a SQL string. SQLite doesn't have a layer like that and has to parse out the stringized values back to their originals anyway, so the templates are extra work to compose and decompose the SQL string. And it defeats the statement cache so will be less efficient again.

The above example also doesn't handle strings or blobs correctly, and it means you can't use APSW's JSONB and convert binding support. And just for completeness, it won't handle strings with embedded nulls which SQLite and APSW handle just fine.

You can only use bindings for SQL values, and cannot use them for identifiers like table and column names. That is a situation where you do have to use string interpolation or templating, but usually at that point you are doing something similar to an ORM and so can handle it correctly.

[char101]

String templates are a really bad match for SQL

Depends on the usage. I think string template is perfect for writing parameter binding.

And it defeats the statement cache so will be less efficient again.

I don't understand how it defeats the statement cache since it is one layer above apsw.

The above example also doesn't handle strings or blobs correctly, and it means you can't use APSW's JSONB and convert binding support. And just for completeness, it won't handle strings with embedded nulls which SQLite and APSW handle just fine.

It does actually.

conn = Connection(':memory:')
r = conn.execute(t'select null is {None}').fetchone()
print(r)
r = conn.execute(t"select 'a' = {'a'}").fetchone()
print(r)
r = conn.execute(t'select null is not {None}').fetchone()
print(r)
r = conn.execute(t"select 'a' <> {'a'}").fetchone()
print(r)

conn.convert_binding = lambda c, n, v: apsw.jsonb_encode(v)
conn.convert_jsonb = lambda c, l, v: apsw.jsonb_decode(v)
conn.execute('create table t (j jsonb)')
conn.execute(t'insert into t values ({[1, 2, 3]})')
r = conn.execute('select * from t').fetchone()
print(r, type(r[0]))

(1,)
(1,)
(0,)
(0,)
([1, 2, 3],) <class 'list'>

I feel that you might be mixing string template with python string format.

[char101]

Also for blob

conn.execute('create table t2 (b blob)')
conn.execute(t'insert into t2 values ({b"\0\1\2"})')
r = conn.execute('select * from t2').fetchone()
print(r)

(b'\x00\x01\x02',)

[rogerbinns]

I don't understand how it defeats the statement cache since it is one layer above apsw.

Because the SQL strings after templates have been expanded is what the statement cache sees. So these would be two different entries:

select y from a where x = 3
select y from a where x = 4

Whereas this would not be:

select y from a where x = $x + 2

As for strings and blobs, show the results of:

s = "Hello "world", how's y'all doing?"
sz = "with \0 null"
b = b"\x00\x11\x22\x33"

[char101]

String template does not expand the parameter.

>>> a = 1
>>> t'{a} + 2'
Template(strings=('', ' + 2'), interpolations=(Interpolation(1, 'a', None, ''),))

>>> a = 1
>>> b = None
>>> c = [4, 5, 6]
>>> t = t'a = {a}, b = {b}, c = {c}'
>>> t.strings
('a = ', ', b = ', ', c = ', '')
>>> t.values
(1, None, [4, 5, 6])

[char101]

Let me explain a bit what python 3.14 string template is. It transforms

t'prefix {variable} suffix'

into a list of strings and values from locals()

strings: ['prefix ', ' suffix'']
values: [variable]

The values keep their original type, so this is why string template is made perfectly for writing sql with parameters, we can simply concat the strings with ? and then pass the values as bindings.

[rogerbinns]

My mistake - I rewrote the code above to clarify what it really happening:

def query(sql):
    params = sql.values
    sql = ' ? '.join(sql.strings)
    print(sql, params)

x = b"\x00\x11"
query(t'insert into a (x, y) values ({x}, 4)')

Result

insert into a (x, y) values ( ? , 4) (b'\x00\x11',)

And it will generate invalid SQL if used for identifiers so can't be unsafe.

table = "foo"
query(t"select * from {table}")

[char101]

query(t"select * from {table}")

Well, my post title is using template strings for parameter bindings, and table name is not a binding.

[rogerbinns]

The significance is to ensure there is no foot gun! ie if someone does try to use them for identifiers then it will fail, which is desirable behaviour.

[char101]

It is the same case and result as someone trying to write conn.execute('select * from ?', ('table', ))

[char101]

I might also note that there is string.Template and string.templatelib.Template.

The first one is string template while the later is called string template literal. I might have wrongly used the wrong term, missing the literal suffix.

[char101]

For things like table name, we can use the format spec to tag the value as a sql fragment

class Connection(apsw.Connection):
    def execute(self, sql, params=None, *args, **kwargs):
        if isinstance(sql, Template):
            _sql = ''
            params = []
            for v in sql:
                if isinstance(v, str):
                    _sql += v
                else:
                    if v.format_spec == 'sql':
                        _sql += v.value
                    else:
                        _sql += '?'
                        params.append(v.value)
            sql = _sql
        return super().execute(sql, params, *args, **kwargs)

conn.execute('create table t (x int)')
table = 't'
conn.execute(t'select * from {table:sql}')

[rogerbinns]

Single quotes are not ok. The SQL rules are that strings (values) are quoted using single quotes and identifiers (table & column names etc) are quoted using double quotes. SQLite accepts them the wrong way round for historical reasons. But if you use APSW bestpractice then that won't be accepted and turn into errors.

[char101]

Oh you meant quoting the table name when concating the sql. But the contents of the interpolation can also be a sql fragment, so it can't simply be double quoted. It is up to the user to double quote it if the table name is a reverved word.

[rogerbinns]

It is up to the user to ...

Which is how every SQL injection attack happened :) It isn't only reserved words that matter. Anything with spaces, starts with a number, contains a dot, contains a double quote, and no doubt more I have forgotten. Many approaches always double quote all identifiers whether they need it or not, since that is simpler.

Also did you know SQLite supports zero length database, table, and column names, and types? It wasn't intentional but does work.

ATTACH ':memory:' as "";
CREATE TABLE "".""("" "");
INSERT INTO "".""("") VALUES(3);
SELECT "" FROM ""."" WHERE "" > 2;

[char101]

I don't think it is the responsibility of a library to prevent sql injection. Any foreign data should always be used as binding.

Also did you know SQLite supports zero length database, table, and column names, and types?

I didn't know. Looks like anything that can be quoted is an identifier. Might also be a feature, e.g. for a dynamically created table.

[rogerbinns]

APSW ensures that data is done via bindings by making it as easy as possible, and by having a section near the top of the tour and doc. If I could have it reject SQL where someone used strings instead of bindings, I'd add that. (It isn't practical yet ...)

The problem is that identifiers can only be done by some form of string templating. We've been through this already with HTML where the various solutions required opt-in to correct quoting, and that resulted in vulnerabilities. Every library that does HTML now requires you to opt out of automatic correct quoting. eg see markupsafe with regular jinja, and set_auto_escape_callback in minijinja.

Zero length identifiers were just an accident. They never added code to ensure an identifier is at least one byte in length, and by the time it was discovered it was too late to change. (Never break existing code.) I use it in my test suite to make sure I handle things correctly.

[rogerbinns]

A bigger picture question is how should raw SQL be handled in Python applications. In my view the majority of devs avoid it completely and use an ORM like SQLAlchemy. FastAPI and Pydantic have been doing well in the Python ecosystem recently and have a corresponding SQLModel. In both cases they map Python objects to SQL tables, and have a LINQ like query scheme, and then do the corresponding SQL behind the scenes.

What I do like is the approach from yesql which has you put the queries in a separate text file with a comment to name each one. aiosql provides an implementation for Python.

[rogerbinns]

Yep, same approach. For APSW (and sqlite3) there is no need for minijinja since you can just use $var in the SQL and locals() at execute time. The only time you do need a solution is for identifiers which need to be handled with care. (aiosql doesn't handle them as far as I can tell.) aiosql also distinguishes between queries and statements (insert etc) which presumably makes a difference for other databases, but doesn't matter for APSW. Nor does its operators etc.

Getting values into SQL is easy as discussed above. What I haven't been able to solve is getting structure out ergonomic:

for name, description, price in db.execute("SELECT name, description, price FROM ...."):
     ...

The ORMs address it by getting rid of the SQL interface. There are build time solutions that run the queries against a live database, and then generate wrapper code with the correct signatures. And some resort to having a named tuple / dict / dataclass dynamically generated by the query response (also in APSW).

I don't like any of those, and keep searching for something better.

[char101]

What don't you like with the namedtuple/dataclass approach?

I don't see how it can be improved further. At least for performance aspect, I use the recordclass library instead of python's dataclass.

[rogerbinns]

I don't like the duplication and disconnect. You are having to use names at the Python level (variables or dict keys or attributes) and then you have to use names inside the SQL. Note that SQLite doesn't guarantee any name stability unless you use AS to explicitly provide a name for each one. In practise the above is fine, but to be technically correct it would have to be:

SELECT name AS name, description AS description, price AS price FROM ....

So now we have the names three times - twice in SQL and once in Python. It is possible to make mistakes which I don't like, and I especially don't like having to keep them coordinated.

The fundamental problem is that SQL is really an opaque data type from Python. It is impractical to inspect, although APSW can do something with valid queries. Modifying it is even harder. That is why ORMs just generate SQL from scratch.

There is SQLGlot (just inside the top 1,000 pypi monthly downloads) which is nice, but I only care about SQLite. A solution would be something like that, but far more focused. (Also #566 which would ensure matching SQLite grammar and syntax exactly)

[char101]

I suppose python 3.14 template literals can also be used to make a simple ORM

class Rec(ColumnDefs):
    id: int
    name: str
    count: Expr['count(1)']

# when interpolation is a ColumnDefs type, generate column names for sql statement, adding AS is simple
for rec in conn.execute(t'select {Rec} from table group by id, name'):
    # rec is an instance of Rec

# when interpolation is a ColumnsDefs type, generate insert bindings from properties
conn.execute('insert into table values {rec}')

[rogerbinns]

That is probably how all the ORMs started. But then you quickly need to deal with computed columns, joins, expressions etc. At which point you either shoehorn them into your Rec equivalent structure, or create something resembling Linq.

I used MongoDB ages ago. It was nice because each record was a JSON (like) object, and the queries were also structured as JSON objects. It was trivial to bounce between records, queries, and Python code. SQL is just not amenable to that.