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

escape string for pgsql (using jdbc/java)?

From: Tobias Thierer <t_thierer(at)yahoo(dot)de>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: escape string for pgsql (using jdbc/java)?
Date: 2007-01-25 23:34:31
Message-ID: epbeqa$1g90$1@news.hub.org (view raw or flat)
Thread:
Lists: pgsql-jdbc
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)?

   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

Responses

pgsql-jdbc by date

Next:From: Dave CramerDate: 2007-01-25 23:44:30
Subject: Re: escape string for pgsql (using jdbc/java)?
Previous:From: Mark LewisDate: 2007-01-24 15:45:33
Subject: Re: XML type in PostgreSQL 8.3

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