Skip site navigation (1) Skip section navigation (2)

Re: Sql injection attacks

From: Harald Fuchs <hf0722x(at)protecting(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Sql injection attacks
Date: 2004-07-28 15:30:11
Message-ID: pu4qnsgnek.fsf@srv.protecting.net (view raw or flat)
Thread:
Lists: pgsql-general
In article <6(dot)1(dot)2(dot)0(dot)1(dot)20040728170935(dot)01f2ef38(at)localhost>,
Lincoln Yeoh <lyeoh(at)pop(dot)jaring(dot)my> writes:

> Just curious on what are the correct/official ways for dealing with
> the following scenarios:

> 1) Input string contains % and _ and would be used in a LIKE query
> that should not have user controllable wildcards.

Perhaps you mean something like the following:

  my $sth = $dbh->prepare (q{
    SELECT whatever
    FROM mytable
    WHERE somecol LIKE ? || '%'
  });
  $sth->execute ($input);

Even if $input contains '%' or '_', those characters get properly escaped.

> 2) Input string are numbers which can hold negative and positive
> values and could potentially be used in the following query:

> update tablea set a=10-$inputstring where key=1;

> When I tested at least one version of DBD::Pg doesn't appear to escape
> the inputstring when it's a number.
> e.g.
> $SQL="update tablea set a=10-? where key=1";
> And $SQL is used in a prepared statement with $inputstring as a parameter.

> I found that when $inputstring contained a negative number, all rows
> in tablea are set to the same value.

What exactly did you do?  I tried

  $sth = $dbh->prepare (q{
    UPDATE t1
    SET val = 10-?
    WHERE id = ?
  });
  $sth->execute (-1, 1);

and it surely set val to 11.

> 3) Postgresql queries with select .... from ...  where ... LIMIT $inputstring

The same thing:

  $sth = $dbh->prepare ("SELECT id, val FROM t1 ORDER BY id LIMIT ?");
  $sth->execute ($inputstring);


In response to

Responses

pgsql-general by date

Next:From: =?iso-8859-1?q?Dag-Erling_Sm=F8rgrav?=Date: 2004-07-28 15:34:25
Subject: Re: tablename type?
Previous:From: Tom LaneDate: 2004-07-28 15:25:06
Subject: Re: tablename type?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group