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.
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.
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.
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.
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.
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.
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.
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?
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:
> 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!
Also:
Is dangerously close to: 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.