On Sat, May 8, 2021 at 4:02 AM M.-A. Lemburg <mal@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 <mal@egenix.com
> <mailto:mal@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 <mal@egenix.com
> <mailto:mal@egenix.com>
> > <mailto:mal@egenix.com <mailto:mal@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-leave@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/