This thread kind of took a turn to bikeshed, and thats probably my fault, I apologize.
I would like to get back to the original question which is, can we revisit PEP 501? What can I do to get this to happen? What is the process for revisiting existing deferred PEPs? Nick On Sat, May 8, 2021 at 4:02 AM M.-A. Lemburg <[email protected]> wrote: > On 07.05.2021 23:56, Nick Humrich wrote: > > Marc, > > > > You might have misunderstood me. I am not recommending sending the > database raw > > strings without parameters, but rather that i-strings turn things into > > parameters and its impossible to mess up. Let me explain a little. > > > > In sqlalchemy, you can use a format such as "update items set a=:value > where > > id=:item_id" then you tell it the value of the parameters. SQLAlchemy > then takes > > the :something part of the string and turns it into a parameter ($1, $2, > etc). > > The problem being however, there is nothing stopping me from doing an f > string > > on accident: f"update items set a={something} where id=:value". Because > > f-strings are eager, sqlalchemy cant protect you, you are now vulnerable > to > > injection. > > But with i-strings, because they are not eager, it would actually know > that you > > passed in the value as a variable, and turn it into a parameter. It > knows the > > difference between the static part of the query and the dynamic part of > the > > query, so it can actually protect you from yourself, or protect early > engineers > > who don't even know what injection is. > > Thanks for explaining again, Nick, but I still don't follow you. > > The templating language used for binding parameters to the > SQL strings is not defined by Python, it's defined by the various > database backends you are using, so i-strings won't help if you > already do the right thing, which is to keep the SQL strings and > the parameters separate :-) > > Now, you could suggest that database interfaces should only accept > i-strings as statement input, preventing the eager formatting > that takes place with f-strings, but that would just use i-strings > as a container for "don't format this string content before > sending it to the database". > > This would only mildly help, though, since the {}-syntax used > by i-strings (and f-strings) is not common with database engines > (I don't know of any engine which accepts this syntax). > > The point I wanted to make is that i-strings do have advantages > based on the late binding, but SQL injection protection is not > necessarily the most important one. > > Aside: Note that even with proper use of binding parameters in > SQL strings, you often still need to use Python templating on > these, since not all parts of the SQL strings can be templated > using binding parameters. E.g. table names are usually not > allowed to the templated in SQL strings by the databases, the > reason being that the query plans rely on these names. > > > Nick > > > > > > On Fri, May 7, 2021, 2:48 PM M.-A. Lemburg <[email protected] > > <mailto:[email protected]>> wrote: > > > > On 07.05.2021 22:39, Ram Rachum wrote: > > > Hi Marc. > > > > > > On Fri, May 7, 2021 at 11:32 PM M.-A. Lemburg <[email protected] > > <mailto:[email protected]> > > > <mailto:[email protected] <mailto:[email protected]>>> wrote: > > > > > > On 07.05.2021 21:40, Nick Humrich wrote: > > > > PEP 501 was deferred because more learning and time was > wanted after > > > introducing > > > > f-strings. Now that it has been 5 years, I wonder what the > > possibilities of > > > > revisiting PEP 501 are. > > > > > > > > I recently had the experience of using javascript "tagged > template > > > literals" and > > > > was able to build a SQL string parser that is impossible to > have SQL > > injection > > > > with. This is done by having the database connection object > only > > accept a > > > > certain type of object, and all sql tagged template literals > become that > > > object. > > > > Because variables are lazy evaluated, the template function > can turn all > > > dynamic > > > > inputs into parameters in a SQL query. It is impossible for > a dev to > > > > accidentally add a user imputed string as a literal. > > > > PEP 501 already mentions how templates (i-strings?) can > solve injection. > > > This is > > > > a very incredible goal. Injection has been the #1 > vulnerability on > > OWASP for > > > > over 10 years, and has been in the top 5 the entire time > OWASP has > > existed > > > > (almost 20 years now). > > > > We have an opportunity to completely remove injection > attacks. > > > > > > I think you ought to not use SQL injection as the primary > argument > > > for i-strings. > > > > > > The DB API highly recommends passing any arguments > > > to a SQL to the database via binding parameters and let the > database > > > do the binding of the SQL template on the server side. > > > > > > Sending those SQL templates and the parameters separately to > the > > > database is not only safer, but also a lot more efficient and > allows > > > for the database to much better manage query plan caching and > reuse. > > > > > > > > > Interesting. When you do that in Python, does that mean something > like %s > > in the > > > SQL query, and then after the query a list of arguments in the > same order > > as the > > > %s tokens? Because if that's the case, maybe it'll be better to > use an > > i-string > > > there, and NOT have the Python layer format the string, but use > that > > i-string to > > > send the parameters separately to the database. It might be easier > to read > > that way. > > > > The %s tokens in %-formatted SQL strings for e.g. PostgreSQL > > are sent to the database as-is. The binding of the parameters, > > which are passed separately as a tuple, is done by the database > > and not in Python, even though the format looks a lot like the > > %-formatting used in Python. > > > > There are other formats as well, e.g. the ? token format > > used in ODBC or the :1 tokens used for e.g. Oracle. > > > > See https://www.python.org/dev/peps/pep-0249/#paramstyle for > > details. > > > > > Even with i-strings we should *not* recommend doing the binding > > > of SQL strings in the Python application. > > > > > > There are other use cases where lazy binding can be useful, > though, > > > e.g. when you don't know whether the interpolation will > actually > > > get used (logging) or where you may want to render the template > > > in a different or extended namespace. > > -- > > Marc-Andre Lemburg > > eGenix.com > > > > Professional Python Services directly from the Experts (#1, May 07 > 2021) > > >>> Python Projects, Coaching and Support ... > https://www.egenix.com/ > > >>> Python Product Development ... > https://consulting.egenix.com/ > > > ________________________________________________________________________ > > > > ::: We implement business ideas - efficiently in both time and costs > ::: > > > > eGenix.com Software, Skills and Services GmbH Pastor-Loeh-Str.48 > > D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg > > Registered at Amtsgericht Duesseldorf: HRB 46611 > > https://www.egenix.com/company/contact/ > > https://www.malemburg.com/ > > > > > > _______________________________________________ > > Python-ideas mailing list -- [email protected] > > To unsubscribe send an email to [email protected] > > https://mail.python.org/mailman3/lists/python-ideas.python.org/ > > Message archived at > https://mail.python.org/archives/list/[email protected]/message/FXSHIJ5TV6ZRN2D74FEFEGSHTB4LKGQJ/ > > Code of Conduct: http://python.org/psf/codeofconduct/ > > > > -- > Marc-Andre Lemburg > eGenix.com > > Professional Python Services directly from the Experts (#1, May 08 2021) > >>> Python Projects, Coaching and Support ... https://www.egenix.com/ > >>> Python Product Development ... https://consulting.egenix.com/ > ________________________________________________________________________ > > ::: We implement business ideas - efficiently in both time and costs ::: > > eGenix.com Software, Skills and Services GmbH Pastor-Loeh-Str.48 > D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg > Registered at Amtsgericht Duesseldorf: HRB 46611 > https://www.egenix.com/company/contact/ > https://www.malemburg.com/ > >
_______________________________________________ Python-ideas mailing list -- [email protected] To unsubscribe send an email to [email protected] https://mail.python.org/mailman3/lists/python-ideas.python.org/ Message archived at https://mail.python.org/archives/list/[email protected]/message/MYKV5BUW4IBSWRDNIUARKAHXM4R5HZO3/ Code of Conduct: http://python.org/psf/codeofconduct/
