Re: text column constraint, newbie question

On Mon, 23 Mar 2009 01:07:18 -0600
Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:

> On Mon, Mar 23, 2009 at 12:59 AM, Stephen Cook <sclists(at)gmail(dot)com>
> wrote:
> > You should use pg_query_params() rather than build a SQL
> > statement in your code, to prevent SQL injection attacks. Also,
> > if you are going to read this data back out and show it on a web
> > page you probably should make sure there is no rogue HTML or
> > JavaScript or anything in there with htmlentities() or somesuch.
>
> Are you saying pg_quer_params is MORE effective than
> pg_escape_string at deflecting SQL injection attacks?

I didn't follow the thread from the beginning but I'd say yes.
It should avoid queueing multiple statements and it is a more
"general" method that let you pass parameters in one shot in spite
of building the string a bit at a time for every parameter you
insert (string, float, integer...).

Of course if you correctly escape/cast/whatever everything injecting
2 statements shouldn't be possible... but if you don't you give more
freedom to the attacker.

$sql='select * from '.$table.' where a=$1 and $b=$2'; //oops I made
a mistake.
$result=db_query_params($sql,array(1,'bonzo'));

If $table is external input and an attacker pass
existingtable; delete from othertable; --

The attack may just result in a DOS if existingtable is there but
your othertable shouldn't be wiped.

untested... but I recall pg_query and pg_query_params use different C
calls PGexec vs. PGexecParams and the later "Unlike PQexec,
PQexecParams allows at most one SQL command in the given string."

http://www.postgresql.org/docs/8.3/interactive/libpq-exec.html

I think pg_query_params should make a difference between floats and
integers and signal an error if you pass float where integers are
expected... but I'm not sure.
Not really a security concern, but an early warning for some mistake.

--
Ivan Sergio Borgonovo
http://www.webthatworks.it