5 ms·

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

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.

deleted 2y ago
[deleted]
VWWHFSfQ 2y ago
> 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.
masklinn 2y ago
> 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.
- VWWHFSfQ 2y ago
  > 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.
- willcipriano 2y ago
  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.
  - benwilber0 2y ago
    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.
    - masklinn 2y ago
      > 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.
- woodrowbarlow 2y ago
  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.
  - anamexis 2y ago
    You can just pass it a template with no substitutions.
  - masklinn 2y ago
    > 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.
- tczMUFlmoNk 2y ago
  > > I don't think > Definitely true. The rest of your comment is valuable, but this is just mean-spirited and unnecessary.
- rangerelf 2y ago
  >> I don't think >Definitely true. I thought we left middle-school playground tactics behind.
- davepeck 2y ago
  > 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.
- deleted 2y ago
  [deleted]
MR4D 2y ago
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.
- pphysch 2y ago
  How? tstrings and fstrings are literals for completely different types. CS has survived for decades with 1 and 1.0 being completely different types.
  - Izkata 2y ago
    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.
    - vlovich123 2y ago
      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.
      - Izkata 2y ago
        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://docs.python.org/3/library/sqlite3.html https://www.psycopg.org/docs/cursor.html https://www.psycopg.org/docs/cursor.html https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-execute.html https://dev.mysql.com/doc/connector-python/en/connector-pyth...
        2y ago
deleted 2y ago
[deleted]
WorldMaker 2y ago
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.
deleted 2y ago
[deleted]
rastignack 2y ago
Quite easy to detect with a proper linter.
hombre_fatal 2y ago
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.
InstaPage 2y ago
t vs f going to be hard to spot.
- acdha 2y ago
  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.
  - PennRobotics 2y ago
    and syntax highlighting
kazinator 2y ago
But t"..." and f"..." have different types; we can make db.execute reject character strings and take only template objects.
- HackerThemAll 2y ago
  Yeah that would be a backward compatible way to do stuff.
zahlman 2y ago
> 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.

deleted 2y ago

[deleted]

VWWHFSfQ 2y ago

> 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.

masklinn 2y ago

> 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.

VWWHFSfQ 2y ago
> 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.
willcipriano 2y ago
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.
- benwilber0 2y ago
  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.
  - masklinn 2y ago
    > 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.
woodrowbarlow 2y ago
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.
- anamexis 2y ago
  You can just pass it a template with no substitutions.
- masklinn 2y ago
  > 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.
tczMUFlmoNk 2y ago
> > I don't think > Definitely true. The rest of your comment is valuable, but this is just mean-spirited and unnecessary.
rangerelf 2y ago
>> I don't think >Definitely true. I thought we left middle-school playground tactics behind.
davepeck 2y ago
> 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.
deleted 2y ago
[deleted]

MR4D 2y ago

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.

pphysch 2y ago
How? tstrings and fstrings are literals for completely different types. CS has survived for decades with 1 and 1.0 being completely different types.
- Izkata 2y ago
  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.
  - vlovich123 2y ago
    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.
    - Izkata 2y ago
      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://docs.python.org/3/library/sqlite3.html https://www.psycopg.org/docs/cursor.html https://www.psycopg.org/docs/cursor.html https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-execute.html https://dev.mysql.com/doc/connector-python/en/connector-pyth...
      - 2y ago

deleted 2y ago

[deleted]

WorldMaker 2y ago

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.

deleted 2y ago

[deleted]

rastignack 2y ago

Quite easy to detect with a proper linter.

hombre_fatal 2y ago

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.

InstaPage 2y ago

t vs f going to be hard to spot.

acdha 2y ago
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.
- PennRobotics 2y ago
  and syntax highlighting

kazinator 2y ago

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

HackerThemAll 2y ago
Yeah that would be a backward compatible way to do stuff.

zahlman 2y ago

> 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.