Re: escape string for pgsql (using jdbc/java)?

[Dave Cramer <pg(at)fastcrypt(dot)com>]

Tobias,
On 25-Jan-07, at 6:34 PM, Tobias Thierer wrote:

> Hi,
>
> I'm writing a servlet that gets a few strings and puts them into a  
> pgsql database. In assembling an insert statement such as
>
> INSERT INTO table column1='value1' column2='value2'
>
> etc., of course I have to make sure an attacker can't put things  
> into value1 that will breaky my system (such as something that  
> contains a ' which will then be interpreted as terminating the  
> string). In other words, I have to escape value* so that it's safe  
> to use in an sql statement (more specifically inside a string).
>
> I was previously using MySQL and escaped strings following the  
> document at:
>
>    http://dev.mysql.com/doc/refman/5.0/en/string-syntax.html
>
> But I couldn't find a corresponding specification for pgsql. The  
> only way of doing this through JDBC that I'm aware of is to prepare  
> a statement first, which just seems wrong because my insert  
> statement is generated dynamically and executed exactly once (the  
> subset of the columns for which a value is actually set change  
> every time the code is run).
>
> So,
>
>   1.) Is there a built-in method somewhere in the jdbc driver that  
> escapes
>       strings and makes them safe to use in an SQL statement (inside a
>       string)?
Yup, preparedstatement.setString

Dave
>   2.) Which characters do I need to escape for pgsql? Is ' the only  
> one,
>       and I need to escape it as '' ? Do I need to escape \ ? Will  
> I need to
>       escape all the characters that I escaped for MySQL? Where can  
> I find
>       out more?
>
> Cheers,
>
>   Tobias
>
> ---------------------------(end of  
> broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo(at)postgresql(dot)org so that  
> your
>       message can get through to the mailing list cleanly