4 ms·
Will this allow neat SQL syntax like the following? city = 'London' min_age = 21 # Find all users in London who are 21 or older: users = db.get
by TekMol 1y ago
Will this allow neat SQL syntax like the following?
city = 'London'
min_age = 21
# Find all users in London who are 21 or older:
users = db.get(t'
SELECT * FROM users
WHERE city={city} AND age>{min_age}
')
If the db.get() function accepts a template, it should, right?
This would be the nicest way to use SQL I have seen yet.
- zelphirkalt 1y agoIsn't the actually proper way to use prepared statements anyway? If we are doing that properly, then what does this t string business buy us for SQL usage from Python?
- scott_w 1y agoBecause, as the article states, people aren’t using prepared statements. Instead, they pass f-strings because they’re more convenient.
- hedgehog 1y agof strings are syntax rather than a type, the resulting templates look like a reasonable way to specify a prepared statement.
- vultour 1y agoExcept to maintain backwards compatibility we're probably going to get new methods that only accept templates, completely circumventing any effort to stop people passing in strings. Prepared statements were the recommended way to run SQL queries when I was starting with PHP 15 years ago, anyone writing code vulnerable to SQL injection at this point should not be writing code.
- scott_w 1y agoWell yes but the alternative is to never make language improvements because legacy code exists.
- jbaiter 1y agoThanks, I hate it. While it's nice syntactic sugar, the difference between an SQL injection vulnerability and a properly parametrized query is now a single letter that's easily missed
- baggiponte 1y agoType checkers to the rescue ahaha I think db.get could also raise if the type does not match?
- TekMol 1y agoI guess that is a misunderstanding on your side, about how templates work. Less hate and more love might help to avoid this type of hotheaded misconception ;-) Why do you think changing a letter would cause a vulnerability? Which letter do you mean?
- hyperbovine 1y agoOP is referring to swapping t with f.
- TekMol 1y agoThat would result in a string passed to get() and raise an error as get() operates on a template, not on a string.
- baegi 1y agoexcept if get() can also accept a raw string, which is likely
- orphea 1y agoWhy would it?
- mcintyre1994 1y agoNo sane library is going to do that. If they do let you pass a raw string it should be a different function with the risks clearly documented. The thing this replaces is every library having their own bespoke API to create a prepared statement on their default/safe path. Now they can just take a template.
- fweimer 1y agoThe SQLite extension for Tcl offers something similar: db1 eval {INSERT INTO t1 VALUES(5,$bigstring)} https://sqlite.org/tclsqlite.html#the_eval_method https://sqlite.org/tclsqlite.html#the_eval_method
- wizzwizz4 1y agoAs I understand, that's less powerful, because you can do: t"INSERT INTO mytable VALUES ({s}, {s[::-1]})" but you can't do: mydb eval {INSERT INTO mytable VALUES ($s, [string reverse $s])} Instead, you have to write: set t [string reverse $s] mydb eval {INSERT INTO mytable VALUES ($s, $t)} There's no reason you couldn't have such power in Tcl, though: it's just that the authors of SQLite didn't.
- meander_water 1y agoThis definitely seems like one of the motivations for implementing this feature in the first place - https://peps.python.org/pep-0750/#motivation https://peps.python.org/pep-0750/#motivation. Having more control over the interpolation of string values is a win IMO.
- bombela 1y agoYes, it's quite delightful.
- neonsunset 1y ago> This would be the nicest way to use SQL I have seen yet. EF/EF Core has existed for years :) https://learn.microsoft.com/en-us/ef/core/querying/sql-queries?tabs=sqlserver#composing-with-linq https://learn.microsoft.com/en-us/ef/core/querying/sql-queri...
- nurettin 1y agoI've used it for years. In order to generate the models you had to use the visual designer which was slow and buggy. Generally annoying experience if you have to clock in and out every day to watch that UI break your database relations whenever you click save.
- neonsunset 1y agoNo one uses it today, or in the last 5 years or so I presume. You use https://learn.microsoft.com/en-us/ef/core/modeling/#use-data-annotations-to-configure-a-model https://learn.microsoft.com/en-us/ef/core/modeling/#use-data... This was a completely separate, legacy extension of VS, not EF let alone EF Core.
- nurettin 1y agoCompletely separate is pushing it since it was recommended by Microsoft, but yes, I am old and times have changed.
- mcintyre1994 1y agoThat’s the sort of thing people have built with the equivalent feature in JavaScript, so it should do. Eg https://github.com/andywer/squid https://github.com/andywer/squid is a nice example.
- zahlman 1y agoYou would need triple-quotes to span multiple lines, but yes, that is exactly how it's intended to work. `db.get` will receive a Template instance, which stores string parts something like `('\n SELECT * FROM users\n WHERE city=', ' AND age>', '\n')` and interpolated parts like `(Interpolation('London'), Interpolation(21))`. It's then responsible for assembling and executing the query from that.