Looks like Nick is on 9.0.1 and pgsql is ignoring
SET standard_conforming_strings=on;
Has anybody seen this behavior before? If you are on postgresql and
want to help with this security issue please try from web2py shell
python web2py.py -S welcome -N
>>> db=DAL('postgresql://....')
>>> db.define_table('x',Field('y'))
>>> db.x.insert(y=" '\\' ")
do you get an error? what postgresql version?
I also made some changes in trunk about this (that may fix the issue
even if standard_conforming_strings=off):
if you upgrade to trunk, do you get an error?
Massimo
On Sep 1, 10:52 pm, Massimo Di Pierro <[email protected]>
wrote:
> P.S.
>
> Nick, I want to clarify that you bringing this up is very important
> for us and I really appreciate. Security is a priority so if this
> turns out to be a different issue than the one I suspect we will fix
> it promptly.
>
> Even if this is not a new issue and it is the problem I suspect, we
> should still have a discussion about the pros and cons of rewriting
> the dal using the alternative syntax that delegates the security issue
> to the driver.
>
> massimo
>
> On Sep 1, 10:47 pm, Massimo Di Pierro <[email protected]>
> wrote:
>
>
>
>
>
>
>
> > To follow up the discussion on the issue...
>
> > At some point a choice was made in web2py whether the role of escaping
> > of strings relies in web2py or in the database driver. We decided this
> > was a job for web2py because we would have more control over it. This
> > allows the code to be simpler and allows to support more databases as
> > we do not need to know the details of their APIs.
>
> > The web2py implementation assume the database is conform to SQL
> > escaping convention. Postgresql before 9.1 does not. Postgresql 8.4
> > can be set to be conform (as we do it in web2py). Postgresql 8.2 is
> > never conform. Postgrsql 8.3 I am not sure.
>
> > Version of postgresql which are non-conform and cannot be made conform
> > to the SQL standrad MUST not be used with web2py because together they
> > cause a SQL-Injection vulnerability.
>
> > This is a known issue and I believe this is the issue that is
> > affecting you.
>
> > Your patch only cures the vulnerability for insert-type queries. The
> > vulnerability would still be there for updates and selects. Using your
> > approach for selects would result in a much more complex dal. I am not
> > convinced we should we worried about this as postgresql 8.3 is now
> > very old.
>
> > There is also the possibility that I am wrong. That you are using a
> > more recent version of postgresql and something else it putting in a
> > non-conformal mode. If that is the case we need to investigate more...
>
> > Massimo
>
> > On Sep 1, 7:52 pm, nick name <[email protected]> wrote:
>
> > > I've just opened issue
> > > #404<http://code.google.com/p/web2py/issues/detail?id=404>: datetime gets
> > > downgraded to 1-sec resolution when inserting to database -
> > > example and full details there. And just 90 minutes before that, someone
> > > else submitted issue
> > > #403<http://code.google.com/p/web2py/issues/detail?id=403>,
> > > which deals with textual vs. parametric SQL queries.
>
> > > Both issues stem from (a) web2py expanding every query to be textual, and
> > > (b) not properly representing/escaping field values. My proposed solution
> > > to
> > > #404 is to add some control to the textual datetime representation. The
> > > patch included in #403 switches postgresql to work with parametric queries
> > > (thus bypassing the escaping issue), but still uses the same textual
> > > representation.
>
> > > I think it should be possible to combine them robustly, by letting
> > > represent
> > > either return a string (in which case it is textually inserted), or some
> > > kind of wrapped value object, in which case the query gets a '%s' or '?'
> > > in
> > > the text, and the underlying value is passed to dbapi as a parameter.
>
> > > Massimo / Jonathan / other web2py gurus - does this make sense?
