Hacker News Reader

Comment by benwilber0 | Hacker News Reader

Aren't there other benefits to server-side parameter binding besides just SQL-injection safety? For instance, using PG's extended protocol (binary) instead of just raw SQL strings. Caching parameterized prepared statements, etc.

Also:

    db.execute(t"QUERY WHERE name = {name}")

Is dangerously close to:

    db.execute(f"QUERY WHERE name = {name}")

A single character difference and now you've just made yourself trivially injectible.

I don't think this new format specifier is in any way applicable to SQL queries.

View on HN

Templates are a very different duck type from strings and intentionally don't support __str__(). The SQL tool can provide a `safe_execute(Template)` that throws if passed a string and not a Template. You can imagine future libraries that only support Template and drop all functions that accept strings as truly safe query libraries.

> Caching parameterized prepared statements, etc.

Templates give you all the data you need to also build things like cacheable parameterized prepared statements. For DB engines that support named parameters you can even get the interpolation expression to auto-name parameters (get the string "name" from your example as the name of the variable filling the slot) for additional debugging/sometimes caching benefits.

But t"..." and f"..." have different types; we can make db.execute reject character strings and take only template objects.

Yeah that would be a backward compatible way to do stuff.

You solve that with an execute(stmt) function that requires you to pass in a template.

In Javascript, sql`where id = ${id}` is dangerously close to normal string interpolation `where id = ${id}`, and db libs that offer a sql tag have query(stmt) fns that reject strings.

> A single character difference and now you've just made yourself trivially injectible.

No; a single character difference and now you get a `TypeError`, which hopefully the library has made more informative by predicting this common misuse pattern.

> Aren't there other benefits to server-side parameter binding besides just SQL-injection safety? For instance, using PG's extended protocol (binary) instead of just raw SQL strings. Caching parameterized prepared statements, etc.

All of which can be implemented on top of template strings.

> A single character difference and now you've just made yourself trivially injectible.

It's not just a one character difference, it's a different type. So `db.execute` can reject strings both statically and dynamically.

> I don't think

Definitely true.

> this new format specifier is in any way applicable to SQL queries.

It's literally one of PEP 750's motivations.

> It's literally one of PEP 750's motivations.

Python is notorious for misguided motivations. We're not "appealing to authority" here. We're free to point out when things are goofy.

    from string.templatelib import Template

    def execute(query: Template)

Should allow for static analysis to prevent this issue if you run mypy as part of your pr process.

That would be in addition to doing any runtime checks.

The first mistake we're going to see a library developer make is:

    def execute(query: Union[str, Template]):

Maybe because they want their execute function to be backwards compatible, or just because they really do want to allow either raw strings are a template string.

> they really do want to allow either raw strings are a template string.

I’d consider that an invalid use case:

1. You can create a template string without placeholders.

2. Even if the caller does need to pass in a string (because they’re executing from a file, or t-strings don’t support e.g. facetting) then they can just… wrap the string in a template explicitly.

nitpicking:

> It's not just a one character difference, it's a different type. So `db.execute` can reject strings both statically and dynamically.

in this case, that's not actually helpful because SQL statements don't need to have parameters, so db.execute will always need to accept a string.

You can just pass it a template with no substitutions.

> db.execute will always need to accept a string.

No. A t-string with no placeholders is perfectly fine. You can use that even if you have no parameters.

> > I don't think

> Definitely true.

The rest of your comment is valuable, but this is just mean-spirited and unnecessary.

>> I don't think >Definitely true.

I thought we left middle-school playground tactics behind.

> Caching parameterized prepared statements, etc.

I didn’t explicitly mention this in my post but, yes, the Template type is designed with caching in mind. In particular, the .strings tuple is likely to be useful as a cache key in many cases.

> I don't think this new format specifier is in any way applicable to SQL queries.

Agree. And the mere presence of such a feature will trigger endless foot-gunning across the Python database ecosystem.

Quite easy to detect with a proper linter.

t vs f going to be hard to spot.

This is true of many other things, which is why we have type checkers and linters to be perfectly rigorous rather than expecting humans to never make mistakes.

and syntax highlighting

Dang! Thanks for pointing this out.

I had to look SEVERAL times at your comment before I noticed one is an F and the other is a T.

This won’t end well. Although I like it conceptually, this few pixel difference in a letter is going to cause major problems down the road.

How? tstrings and fstrings are literals for completely different types.

CS has survived for decades with 1 and 1.0 being completely different types.

Because they're both passed to "execute", which can't tell between the f-string and a non-interpolated query, so it just has to trust you did the right thing. Typoing the "t" as an "f" introduces SQL injection that's hard to spot.

Assuming `execute` takes both. You could have `execute(template)` and `execute_interpolated(str, ...args)` but yeah if it takes both you'll have challenges discouraging plain-text interpolation.

It would have to be the other way around or be a (possibly major) breaking change. Just execute() with strings is already standard python that all the frameworks build on top of, not to mention tutorials:

https://docs.python.org/3/library/sqlite3.html

https://www.psycopg.org/docs/cursor.html

https://dev.mysql.com/doc/connector-python/en/connector-pyth...

> It would have to be the other way around or be a (possibly major) breaking change.

If it is going to reject the currently-accepted unsafe usage, its going to be a major breaking change in any case, so I don't see the problem. I mean, if you are lamenting it can't reject the currently-accepted SQL-interpolated-via-f-string because it can't distinguish it by type from plain strings with no interpolation, you are already saying that you want a major breaking change but are upset because the particular implementation you want is not possible. So you can't turn around and dismiss an alternative solution because it would be a major breaking change, that's what was asked for!

`execute` can tell the difference, because `t"..."` does not create the same type of object that `f"..."` does.

I had an extended debugging session last week that centered on 1 and 1. confusion in a library I have to use...

Yeah, it's a real bummer when that happens. I wish JSON never tried to do types.

Reread my comment. It’s about noticing you have an “f” or a “t” and both are very similar characters.

Yes, but you will get an error since string and templates are different types and have different interfaces.

Click "parent" a few times and look at the code example that started this thread. It's using the same function in a way that can't distinguish whether the user intentionally used a string (including an f-string) and a t-string.

Yes, and the parent is misguided. As was pointed out in multiple replies, the library can distinguish whether an ordinary string or a t-string is passed because the t-string is not a string instance, but instead creates a separate library type. A user who mistakenly uses an f prefix instead of a t prefix will, with a properly designed library, encounter a `TypeError` at runtime (or a warning earlier, given type annotations and a checker), not SQL injection.

In this particular instance it can't, because there are 3 ways in question here, and it can't distinguish between correct intentional usage and accidental usage of an f-string instead of a t-string:

  db.execute("SELECT foo FROM bar;")
  db.execute(f"SELECT foo FROM bar WHERE id = {foo_id};")
  db.execute(t"SELECT foo FROM bar WHERE id = {foo_id};")

The first and second look identical to execute() because all it sees is a string. But the second one is wrong, a hard-to-see typo of the third.

If f-strings didn't exist there'd be no issue because it could distinguish by type as you say. But we have an incorrect SQL-injection-prone usage here that can't be distinguished by type from the correct plain string usage.

There is no reason to support the first or second usage. It's totally fine to always require a t-string:

    db.execute(t"SELECT foo FROM bar;")

See? No reason to accept strings, it's absolutely fine to always error if a string is passed.

My (and their) point is that's the already existing API. You're proposing a big breaking change, with how many frameworks and tutorials are built on top of that.

It's not like this is the first time APIs have been improved. There are many tools (e.g. deprecation warnings & hints in editors, linter rules) that can help bridge the gap - even if t-strings are only used for new or refactored code, it's still a big improvement!

There's also simply no hard requirement to overload an `execute` function. We have options beyond "no templates at all" and "execute takes templates and strings", for example by introducing a separate function. Why does perfect have to be the enemy of good here?

Reply on news.ycombinator.com