Re: text column constraint, newbie question

On Mon, Mar 23, 2009 at 2:33 AM, Ivan Sergio Borgonovo
<mail(at)webthatworks(dot)it> wrote:
> 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.

So, what are the performance implications? Do both methods get
planned / perform the same on the db side?