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 <m...@egenix.com> 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 <m...@egenix.com
> > <mailto:m...@egenix.com>> wrote:
> >
> > On 07.05.2021 22:39, Ram Rachum wrote:
> > > Hi Marc.
> > >
> > > On Fri, May 7, 2021 at 11:32 PM M.-A. Lemburg <m...@egenix.com
> > <mailto:m...@egenix.com>
> > > <mailto:m...@egenix.com <mailto:m...@egenix.com>>> 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 -- python-ideas@python.org
> > To unsubscribe send an email to python-ideas-le...@python.org
> > https://mail.python.org/mailman3/lists/python-ideas.python.org/
> > Message archived at
> https://mail.python.org/archives/list/python-ideas@python.org/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 -- python-ideas@python.org
To unsubscribe send an email to python-ideas-le...@python.org
https://mail.python.org/mailman3/lists/python-ideas.python.org/
Message archived at
https://mail.python.org/archives/list/python-ideas@python.org/message/MYKV5BUW4IBSWRDNIUARKAHXM4R5HZO3/
Code of Conduct: http://python.org/psf/codeofconduct/
