Thread: escape string for pgsql (using jdbc/java)?
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
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 >
On Fri, 26 Jan 2007, Tobias Thierer wrote: > 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. http://www.postgresql.org/docs/8.2/static/sql-syntax-lexical.html See 4.1.2.1 String constants. > 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)? There is org.postgresql.core.Utils#appendEscapedString, but it's not something we support or advertise. It's really for internal use only. > 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? You need to escape ' and \ if you standard_conforming_strings is on. Monitoring this setting can be tough, so the safest thing to do is probably to always use the E'string' escape syntax and escape both characters. Kris Jurka
Kris Jurka wrote:
>> 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)?
>
> There is org.postgresql.core.Utils#appendEscapedString, but it's not
> something we support or advertise. It's really for internal use only.
I dislike that this method expects me to tell it whether i have
standard_conforming_strings set - this kinda defeats the "write once, run
everywhere" principle.
If I replace \ with \\ and DO have standard_conforming_strings set, then
this will actually create two \ characters in my string - right? So there is
no way I can do this "safely".
>> 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?
>
> You need to escape ' and \ if you standard_conforming_strings is on.
> Monitoring this setting can be tough, so the safest thing to do is
> probably to always use the E'string' escape syntax and escape both
> characters.
I haven't found anything in the documentation about how this syntax works
exactly. The documentation refers to "the E'...' syntax", but doesn't tell
me what this syntax actually is (am I supposed to already know how this
syntax works, so just need to be told to use it!?). Do I have to put the E
in front of the beginning ', i.e.
'foo'
becomes E'foo' ? (that can't be right, there must be some way I escape '
inside the string). So does 'foo' become
'E'f'E'o'E'o'' ?
or what? How do I represent the literal string
foo'bar\baz
?
Thanks in advance,
Tobias
On Sun, 28 Jan 2007, Tobias Thierer wrote: > Kris Jurka wrote: > >>> 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)? >> >> There is org.postgresql.core.Utils#appendEscapedString, but it's not >> something we support or advertise. It's really for internal use only. > > I dislike that this method expects me to tell it whether i have > standard_conforming_strings set - this kinda defeats the "write once, run > everywhere" principle. > > If I replace \ with \\ and DO have standard_conforming_strings set, then this > will actually create two \ characters in my string - right? So there is no > way I can do this "safely". Right, again this is really something just for the driver (which does know the setting of standard_conforming_strings. >>> 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? >> >> You need to escape ' and \ if you standard_conforming_strings is on. >> Monitoring this setting can be tough, so the safest thing to do is probably >> to always use the E'string' escape syntax and escape both characters. > > I haven't found anything in the documentation about how this syntax works > exactly. The documentation refers to "the E'...' syntax", but doesn't tell me > what this syntax actually is (am I supposed to already know how this syntax > works, so just need to be told to use it!?). Do I have to put the E in front > of the beginning ', i.e. > > 'foo' > > becomes E'foo' ? (that can't be right, there must be some way I escape ' > inside the string). So does 'foo' become > > 'E'f'E'o'E'o'' ? > > or what? How do I represent the literal string > > foo'bar\baz I think the documentation I pointed you to earlier describers this (4.1.2.1 here http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html). You write WHERE x = E'foo' or x = E'foo''bar\\baz'. The preceding E simply states that you want backslash to mean something special regardless of the setting of standard conforming strings. Kris Jurka
Tobias Thierer wrote: > Kris Jurka wrote: > >>> 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)? >> >> >> There is org.postgresql.core.Utils#appendEscapedString, but it's not >> something we support or advertise. It's really for internal use only. > > > I dislike that this method expects me to tell it whether i have > standard_conforming_strings set - this kinda defeats the "write once, > run everywhere" principle. If you want something portable just use PreparedStatement.setString() and parameter placeholders. The "preparation" overhead you are worrying about is not really an issue as the PreparedStatement implementation is designed to handle one-shot queries as well as reused queries efficiently .. since PreparedStatement is often used just for parameter value interpolation to avoid exactly the issues you are encountering. -O
Oliver Jowett wrote:
> If you want something portable just use PreparedStatement.setString()
> and parameter placeholders. The "preparation" overhead you are worrying
> about is not really an issue as the PreparedStatement implementation is
> designed to handle one-shot queries as well as reused queries
> efficiently .. since PreparedStatement is often used just for parameter
> value interpolation to avoid exactly the issues you are encountering.
I'm not so worried about the performance. But if I have a column of type
SERIAL in my table, then I can retrieve the generated value using:
statement.executeUpdate(sqlString, Statement.RETURN_GENERATED_KEYS);
ResultSet resultSet = statement.getGeneratedKeys();
Integer result;
if (resultSet.next()) {
result = resultSet.getInt(1);
} else {
result = null;
}
resultSet.close();
whereas it is not clear to me how this works with a prepared statement.
Strangely, PreparedStatement extends Statement, so PreparedStatement still
has the executeUpdate(String,int) method - but it is not clear to me whether
this method will throw the previously prepared statement away or what.
Is there any easy way to retrieve the generated value for the SERIAL column
when using a prepared statement?
Tobias
On 29-Jan-07, at 6:27 PM, Tobias Thierer wrote:
> Oliver Jowett wrote:
>
>> If you want something portable just use PreparedStatement.setString
>> () and parameter placeholders. The "preparation" overhead you are
>> worrying about is not really an issue as the PreparedStatement
>> implementation is designed to handle one-shot queries as well as
>> reused queries efficiently .. since PreparedStatement is often
>> used just for parameter value interpolation to avoid exactly the
>> issues you are encountering.
>
> I'm not so worried about the performance. But if I have a column of
> type SERIAL in my table, then I can retrieve the generated value
> using:
>
> statement.executeUpdate(sqlString, Statement.RETURN_GENERATED_KEYS);
> ResultSet resultSet = statement.getGeneratedKeys();
> Integer result;
> if (resultSet.next()) {
> result = resultSet.getInt(1);
> } else {
> result = null;
> }
> resultSet.close();
>
You could if that worked in postgresql but getGeneratedKeys does not
currently work
> whereas it is not clear to me how this works with a prepared
> statement. Strangely, PreparedStatement extends Statement, so
> PreparedStatement still has the executeUpdate(String,int) method -
> but it is not clear to me whether this method will throw the
> previously prepared statement away or what.
>
> Is there any easy way to retrieve the generated value for the
> SERIAL column when using a prepared statement?
>
Yes, if it worked it would work with prepared statements.
Dave
> Tobias
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
Hi,
Dave Cramer wrote:
>> I'm not so worried about the performance. But if I have a column of
>> type SERIAL in my table, then I can retrieve the generated value using:
[...]
> You could if that worked in postgresql but getGeneratedKeys does not
> currently work
Yes, I was actually just about to post a follow-up to my previous message
because I just found out that pgsql doesn't support this.
>> whereas it is not clear to me how this works with a prepared
>> statement. Strangely, PreparedStatement extends Statement, so
>> PreparedStatement still has the executeUpdate(String,int) method - but
>> it is not clear to me whether this method will throw the previously
>> prepared statement away or what.
>>
>> Is there any easy way to retrieve the generated value for the SERIAL
>> column when using a prepared statement?
>>
> Yes, if it worked it would work with prepared statements.
Yes, I just found that out too.
But given that pgsql doesn't allow me to immediately retrieve the value of
the autogenerated key, how can I (or can't I!?)? If I made a
SELECT MAX(id)
statement right afterwards then I couldn't be sure that that is the id just
generated, because due to concurrency someone else may have just submitted
an entry too, right?
How are these things done in pgsql?
Tobias
On 29-Jan-07, at 7:15 PM, Tobias Thierer wrote:
> Hi,
>
> Dave Cramer wrote:
>
>>> I'm not so worried about the performance. But if I have a column
>>> of type SERIAL in my table, then I can retrieve the generated
>>> value using:
> [...]
>
>> You could if that worked in postgresql but getGeneratedKeys does
>> not currently work
>
> Yes, I was actually just about to post a follow-up to my previous
> message because I just found out that pgsql doesn't support this.
>
>>> whereas it is not clear to me how this works with a prepared
>>> statement. Strangely, PreparedStatement extends Statement, so
>>> PreparedStatement still has the executeUpdate(String,int) method
>>> - but it is not clear to me whether this method will throw the
>>> previously prepared statement away or what.
>>>
>>> Is there any easy way to retrieve the generated value for the
>>> SERIAL column when using a prepared statement?
>>>
>> Yes, if it worked it would work with prepared statements.
>
> Yes, I just found that out too.
>
> But given that pgsql doesn't allow me to immediately retrieve the
> value of the autogenerated key, how can I (or can't I!?)? If I made a
>
> SELECT MAX(id)
>
> statement right afterwards then I couldn't be sure that that is the
> id just generated, because due to concurrency someone else may have
> just submitted an entry too, right?
select currval('sequence name') is how it's done as long as you use
the exact same connection.
alternatively you can increment the sequence before hand select
nextval('sequence name') and insert the value
and if you are using 8.2 you can do 'insert into foo (cols) values
(vals) returning id
Dave
>
> How are these things done in pgsql?
>
> Tobias
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>