Re: SQL injection - Mailing list pgsql-general

Jim C. Nasby wrote:
> Does PHP support prepared queries with bound parameters for PostgreSQL?
> Not only is that foolproof (unless you're calling a function that uses
> an argument to build a query string...), you'll get a performance boost
> as well since PostgreSQL won't have to reparse and plan every query.
>
> On Mon, Oct 31, 2005 at 07:54:58PM +0200, Yonatan Ben-Nes wrote:
>
>>Hi all,
>>
>>I'm currently trying to build a defence against SQL INJECTION, after
>>reading some material on it I arrived to few possible solutions and I
>>would like to know if anyone can comment anything about them or maybe
>>add a solution of its own:
>>
>>1. PachyRand: SQL Randomization for the PostgreSQL JDBC Driver - seems
>>to be the best solution (easiest and most protective) though I didnt
>>understood entirely if the solution is available for production
>>enviorments or not, information can be attained at:
>>http://nsl.cs.columbia.edu/projects/pachyrand/ &
>>http://mice.cs.columbia.edu/getTechreport.php?techreportID=355&format=pdf&
>>
>>2. Running for each data which will be used at the db checks with
>>regular expressions to find out if its valid, this method is quite
>>complicated to me (dont know regular expressions too much) and it
>>demands diffrent checks to each data field (with quite big problems at
>>open text data), at the end im afraid that holes will exist..
>>
>>3. Running PHP functions like settype($data, 'integer') to be sure that
>>the data which arrive is at the correct format and to the text run
>>pg_escape_string($data), I suspect that this method wont block even
>>close to 100% of the attacks, just like the former option.
>>
>>Another factor is the overhead to the system, I think that the previous
>>methods don't create much overhead but if anyone have another idea of
>>course it will also need to be efficent.
>>
>>Any new ideas or comments will be received gladly.
>>
>>Thanks in advance!
>>  Yonatan Ben-Nes
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 5: don't forget to increase your free space map settings
>>
>
>

Won't that create a performance penalty to extremly dynamic sites cause
the plan will be planned only once and the data may vary alot?
Beside that I still won't have a solution to places where I create a
query which can vary alot (JOIN diffrent tables, diffrent WHERE etc...),
it doesn't seem logical to me to start and create all of the diffrent
possibilites of queries when I create such an option at a site.

Thanks alot everyone and sorry for posting something and then not
returning for so long (though everything seem like rolling alone nicely :)).
   Yonatan Ben-Nes