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

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@postgresql.org so that
> your
>       message can get through to the mailing list cleanly
>