[CDBI] Re: Using scalar functions with AbstractSearch

Cees Hek ceeshek at gmail.com
Thu Feb 2 18:19:01 GMT 2006

On 2/2/06, Edward J. Sabol <sabol at alderaan.gsfc.nasa.gov> wrote:
> Rhesa wrote:
> > I suppose it's my lack of experience with postgresql, but I find it a
> > bit unsettling that placeholder values would be executed instead of
> > used as plain strings. I would not have expected
> >
> > $sth->execute( " where end > ? and start < ? ", {}, qw/ now() now() / );
> >
> > to give the same resultset as
> >
> > $sth->execute( " where end > now() and start < now() ", {}, qw// );
> >
> > Is it just me, or does that look like a potential sql injection hole?
> I'm with Rhesa on this one. None of the DBDs I've used allow this, and I
> would not have expected DBD::Pg to either. It does seem dangerous.

This is not related to the DBD, but how PostgreSQL actually works. 
now() is a function call, wheras 'now()' is a string that has a
special meaning when cast as a date/time field.

There are other special strings that have meaning when cast as a
date/time field: epoch, infinity, -infinity, now, today, tomorrow,
yesterday and allballs.

The extra () makes it look like a function call, but postgres ignores
most non alpha characters that appear before or after the 'special
input value'.  Try some of the following and they should work for you
as well (tested on PostgreSQL 7.4.7):

select 'now :*)'::timestamp, '<<<<yesterday>>>>'::date,
-[ RECORD 1 ]-------------------------
timestamp | 2006-02-02 13:10:56.572849
date      | 2006-02-01
timestamp | -infinity

so just to sum up:

select now();                       <<< calls a function
select 'now()';                      <<< is just a string
select 'now'::timestamp;      <<< is a special timestamp input value
select 'now()'::timestamp;    <<< same as above with some useless extra chars



More information about the ClassDBI mailing list