On storing datetime in the database, and textual representation of fields

[nick name]

I've just opened issue #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, 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?

[Massimo Di Pierro]

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 <i.like.privacy....@gmail.com> 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>,

[Massimo Di Pierro]

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 <massimo.dipie...@gmail.com>
wrote:

[Massimo Di Pierro]

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 <massimo.dipie...@gmail.com>

[Carlos]

Hi Massimo,

I'm using postgresql 9.0 on win7 with the latest web2py trunk, up to changeset [ (5ac67457363f) fixed pgsql problem, getquoted ].

I executed the 3 lines of code in shell (using 'xx' for table name instead of the invalid 'x'), and so far no errors, with " '\' " inserted into the y field.

Should I do any more testing?.

Take care,

   Carlos

[Massimo Di Pierro]

No that is ok. Thanks!

[Kurt Grutzmacher]

Hi Massimo,

I updated to the the latest trunk and my imports no longer have the problem in Issue #403. I swear nick and I didn't collude to report similar issues! :)

On another system running web2py 1.98.2 (2011-08-31 20:40:46) and postgresql 8.4 I do not receive the same error. With the same web2py code and postgreslql 9.0.1 I do. Your assumption about 9.0.1 ignoring standard_conforming_strings is most likely correct (and answers why some of my colleagues haven't seen the same error!)

grutz

[Massimo Di Pierro]

Thanks for the confirmation. I still do not understand fully how
psycopg2 knows whether one has set standard_conforming_string to on or
off. I asked the psycog2 list but I have not got a reply yet.

Massimo

[nick name]

psycopg uses a Postgres provided routine to escape the strings, which is aware of what version of postgres is at the other end of the connection (apparently since library shipped with 8.1.4, if I can read the source correctly): Link to relevant psycopg file is here.

Massimo - thanks again for web2py, and for running the project so awesomely!

[Massimo Di Pierro]

My problem was this?

value = " '\' "
connection = psycopg2.Connect(....)
cursor = connection.cursor()
curser.execute('set normal_conformal_string=on;")
cursor.execute("INSERT INTO TABLE(FIELD) VALUES(%s);",value) #1
curser.execute('set normal_conformal_string=off;")
cursor.execute("INSERT INTO TABLE(FIELD) VALUES(%s);",value) #2

How does psycopg2 knows it needs to escape #1 and #2 differently? Same
problems applies to the adapt function.

I found the answer here: http://www.postgresql.org/docs/8.3/static/libpq-status.html

<quote>
Certain parameter values are reported by the server automatically at
connection startup or whenever their values change.
...
Parameters reported as of the current release include server_version,
server_encoding, client_encoding, is_superuser, session_authorization,
DateStyle, TimeZone, integer_datetimes, and
standard_conforming_strings. (server_encoding, TimeZone, and
integer_datetimes were not reported by releases before 8.0;
standard_conforming_strings was not reported by releases before 8.1.)
</quote>

Thanks again for your help. It helped us look into this in more detail
than we did before and web2py is better/safer as a result.

Massimo

On Sep 2, 9:41 pm, nick name <i.like.privacy....@gmail.com> wrote:
> psycopg uses a Postgres provided routine to escape the strings, which is
> aware of what version of postgres is at the other end of the connection
> (apparently since library shipped with 8.1.4, if I can read the source

> correctly): Link to relevant psycopg file is here<https://dndg.it/cgi-bin/gitweb.cgi?p=public/psycopg2.git;a=blob;f=psy...>
> .