4 ms·

The execute function can recognize it as a t-string and prevent SQL injection if the name is coming from user input. f-strings immediately evaluate to a string,

by ds_ 1y ago

The execute function can recognize it as a t-string and prevent SQL injection if the name is coming from user input. f-strings immediately evaluate to a string, whereas t-strings evaluate to a template object which requires further processing to turn it into a string.

Tenoke 1y ago
Then the useful part is the extra execute function you have to write (it's not just a substitute like in the comment) and an extra function can confirm the safety of a value going into a f-string just as well. I get the general case, but even then it seems like an implicit anti-pattern over doing db.execute(f"QUERY WHERE name = {safe(name)}")
- ubercore 1y ago
  Problem with that example is where do you get `safe`? Passing a template into `db.execute` lets the `db` instance handle safety specifically for the backend it's connected to. Otherwise, you'd need to create a `safe` function with a db connection to properly sanitize a string. And further, if `safe` just returns a string, you still lose out on the ability for `db.execute` to pass the parameter a different way -- you've lost the information that a variable is being interpolated into the string.
  - Tenoke 1y ago
    db.safe same as the new db.execute with safety checks in it you create for the t-string but yes I can see some benefits (though I'm still not a fan for my own codebases so far) with using the values further or more complex cases than this.
    - ubercore 1y ago
      Yeah but it would have to be something like `db.safe("SELECT * FROM table WHERE id = {}", row_id)` instead of `db.execute(t"SELECT * FROM table WHERE id = {row_id}")`. I'd prefer the second, myself.
      - Tenoke 1y ago
        No, just `db.execute(f"QUERY WHERE name = {db.safe(name)}")` And you add the safety inside db.safe explicitly instead of implicitly in db.execute. If you want to be fancy you can also assign name to db.foos inside db.safe to use it later (even in execute).
        ZiiS 1y ago
        But if someone omits the `safe` it may still work but allow injection.
        thunky 1y ago
        Same is true if someone forgets to use t" and uses f" instead. At least db.safe says what it does, unlike t".
        ewidar 1y ago
        Not really, since f"" is a string and t"" is a template, you could make `db.execute` only accept templates, maybe have `db.execute(Template)` and `db.unsafeExecute(str)`
        thunky 1y ago
        agreed. but then you're breaking the existing `db.execute(str)`. if you don't do that, and instead add `db.safe_execute(tpl: Template)`, then you're back to the risk that a user can forget to call the safe function. also, you're trusting that the library implementer raises a runtime exception if a string a passed where a template is expected. it's not enough to rely on type-checks/linting. and there is probably going to be a temptation to accept `db.execute(sql: Union[str, Template])` because this is non-breaking, and sql without params doesn't need to be templated - so it's breaking some stuff that doesn't need to be broken. i'm not saying templates aren't a good step forward, just that they're also susceptible to the same problems we have now if not used correctly.
- NewEntryHN 1y ago
  Some SQL engines support accepting parameters separately so that values get bound to the query once the abstract syntax tree is already built, which is way safer than string escapes shenanigans.
  - ljm 1y ago
    I’d always prefer to use a prepared statement if I can, but sadly that’s also less feasible in the fancy new serverless execution environments where the DB adapter often can’t support them. For me it just makes it easier to identify as safe, because it might not be obvious at a glance that an interpolated template string is properly sanitised.
- Mawr 1y ago
  But you have to remember to call the right safe() function every time: db.execute(f"QUERY WHERE name = {name}") db.execute(f"QUERY WHERE name = {safe_html(name)}") Oops, you're screwed and there is nothing that can detect that. No such issue with a t-string, it cannot be misused.
- dragonwriter 1y ago
  > and an extra function can confirm the safety of a value going into a f-string just as well. Yes, you could require consumers to explicitly sanitize each parameter before it goes into the f-string, or, because it has the structure of what is fixed and what is parameters, it can do all of that for all parameters when it gets a t-string. The latter is far more reliable, and you can't do it with an f-string because an f-string after creation is just a static string with no information about construction.
- zahlman 1y ago
  > Then the useful part is the extra execute function you have to write Well, no, the library author writes it. And the library author also gets to detect whether you pass a Template instance as expected, or (erroneously) a string created by whatever formatting method you choose. Having to use `safe(name)` within the f-string loses type information, and risks a greater variety of errors.

by ds_ 1y ago

Tenoke 1y ago

Then the useful part is the extra execute function you have to write (it's not just a substitute like in the comment) and an extra function can confirm the safety of a value going into a f-string just as well. I get the general case, but even then it seems like an implicit anti-pattern over doing db.execute(f"QUERY WHERE name = {safe(name)}")

ubercore 1y ago
Problem with that example is where do you get `safe`? Passing a template into `db.execute` lets the `db` instance handle safety specifically for the backend it's connected to. Otherwise, you'd need to create a `safe` function with a db connection to properly sanitize a string. And further, if `safe` just returns a string, you still lose out on the ability for `db.execute` to pass the parameter a different way -- you've lost the information that a variable is being interpolated into the string.
- Tenoke 1y ago
  db.safe same as the new db.execute with safety checks in it you create for the t-string but yes I can see some benefits (though I'm still not a fan for my own codebases so far) with using the values further or more complex cases than this.
  - ubercore 1y ago
    Yeah but it would have to be something like `db.safe("SELECT * FROM table WHERE id = {}", row_id)` instead of `db.execute(t"SELECT * FROM table WHERE id = {row_id}")`. I'd prefer the second, myself.
    - Tenoke 1y ago
      No, just `db.execute(f"QUERY WHERE name = {db.safe(name)}")` And you add the safety inside db.safe explicitly instead of implicitly in db.execute. If you want to be fancy you can also assign name to db.foos inside db.safe to use it later (even in execute).
      - ZiiS 1y ago
        But if someone omits the `safe` it may still work but allow injection.
        thunky 1y ago
        Same is true if someone forgets to use t" and uses f" instead. At least db.safe says what it does, unlike t".
        ewidar 1y ago
        Not really, since f"" is a string and t"" is a template, you could make `db.execute` only accept templates, maybe have `db.execute(Template)` and `db.unsafeExecute(str)`
        thunky 1y ago
        agreed. but then you're breaking the existing `db.execute(str)`. if you don't do that, and instead add `db.safe_execute(tpl: Template)`, then you're back to the risk that a user can forget to call the safe function. also, you're trusting that the library implementer raises a runtime exception if a string a passed where a template is expected. it's not enough to rely on type-checks/linting. and there is probably going to be a temptation to accept `db.execute(sql: Union[str, Template])` because this is non-breaking, and sql without params doesn't need to be templated - so it's breaking some stuff that doesn't need to be broken. i'm not saying templates aren't a good step forward, just that they're also susceptible to the same problems we have now if not used correctly.
NewEntryHN 1y ago
Some SQL engines support accepting parameters separately so that values get bound to the query once the abstract syntax tree is already built, which is way safer than string escapes shenanigans.
- ljm 1y ago
  I’d always prefer to use a prepared statement if I can, but sadly that’s also less feasible in the fancy new serverless execution environments where the DB adapter often can’t support them. For me it just makes it easier to identify as safe, because it might not be obvious at a glance that an interpolated template string is properly sanitised.
Mawr 1y ago
But you have to remember to call the right safe() function every time: db.execute(f"QUERY WHERE name = {name}") db.execute(f"QUERY WHERE name = {safe_html(name)}") Oops, you're screwed and there is nothing that can detect that. No such issue with a t-string, it cannot be misused.
dragonwriter 1y ago
> and an extra function can confirm the safety of a value going into a f-string just as well. Yes, you could require consumers to explicitly sanitize each parameter before it goes into the f-string, or, because it has the structure of what is fixed and what is parameters, it can do all of that for all parameters when it gets a t-string. The latter is far more reliable, and you can't do it with an f-string because an f-string after creation is just a static string with no information about construction.
zahlman 1y ago
> Then the useful part is the extra execute function you have to write Well, no, the library author writes it. And the library author also gets to detect whether you pass a Template instance as expected, or (erroneously) a string created by whatever formatting method you choose. Having to use `safe(name)` within the f-string loses type information, and risks a greater variety of errors.