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?
