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?
