Postgres Pro
Downloads
Home   >   mailing lists

Thread: Cannot use prepared statements with create user command

Cannot use prepared statements with create user command

From
"Justin Waddell"
Date:
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

Re: Cannot use prepared statements with create user command

From
Oliver Jowett
Date:
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