Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Cannot use prepared statements with create user command

6 views
Skip to first unread message

Justin Waddell

unread,
Aug 23, 2006, 12:09:03 AM8/23/06
to
To avoid SQL injection attacks I am keen to use prepared statements
for all JDBC calls. However prepared statements do not work with the
CREATE USER command.

I am using the following command:

CREATE USER ? PASSWORD ? CREATEUSER

and then setting the parameters before executing it. This causes the
following exception:

org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1"
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1525)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1309)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:188)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:354)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:347)

If a prepared statement is not used, then this command is definitely
open to SQL injection attacks - I dropped a database table by using
the following username:

blah;drop table dummy;--

From my research into this problem it seems like prepared statements
have been deliberately left out of the create user command as they use
a different type of parameter; this would be fine if the security hole
wasn't there!

If this problem is not going to be fixed I'll have to escape the
username manually (are there any pre-existing escapers available?),
but it seems like a bug to me.

I'm using the latest server (8.1.4) and JDBC driver (8.1-407)

Justin

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majo...@postgresql.org so that your
message can get through to the mailing list cleanly

Oliver Jowett

unread,
Aug 23, 2006, 2:31:50 AM8/23/06
to
Justin Waddell wrote:

>> From my research into this problem it seems like prepared statements
> have been deliberately left out of the create user command as they use
> a different type of parameter; this would be fine if the security hole
> wasn't there!

The JDBC driver can only put parameters in where the server-side grammar
allows it (it does not interpolate parameter values into the actual
query, it puts $1,$2,... placeholders in place of the ? placeholders and
passes the actual parameter values out-of-line from the query itself).
If you want to change the grammar, you need to take this up with the
backend developers.. the JDBC driver can't do anything about it.

-O

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

0 new messages