limitations of PreparedStatement with PostgreSQL

Thread view:

Bernd Fuhrmann - 26 Apr 2007 11:26 GMT

Hi!

I'm trying to write some simple mechanism, that creates a database named
after a given string.

In order to do that, I need to have some mechanism to assemble a correct
SQL statement with that string inserted into it. I guess that
PrepareStatement should be used for that.

The problem is however, that PostgreSQL doesn't seem to support this.
This is documented here: http://jdbc.postgresql.org/documentation/faq.html

They explain, that it is only possible to use "?" on certain places.
However, that seems to forbid to have statements like this:

PreparedStatement statement = connection.prepareStatement(
"CREATE DATABASE ? ;");
statement.setString(1,"my crazy database name");

So I'd like to ask:
Is this behaviour of PostgreSQL correct? Is it wrong to use code like
the one I mentioned with other databases?

Is there a reasonable portable way to avoid this problem? Is there e.g.
a Java-way to do string escaping and just concatenate all the bits?

I have a very strong feeling that I missed some important FAQ. If you
can give me a hint, that would be great.

Thanks in advance,
Bernd Fuhrmann

Reply to this Message

Robert Klemme - 26 Apr 2007 12:09 GMT

> I'm trying to write some simple mechanism, that creates a database named
> after a given string.
[quoted text clipped - 16 lines]
> Is this behaviour of PostgreSQL correct? Is it wrong to use code like
> the one I mentioned with other databases?

I am no Postgres expert but I would guess that the JDBC driver is right.

> Is there a reasonable portable way to avoid this problem? Is there e.g.
> a Java-way to do string escaping and just concatenate all the bits?

Not afaik. But since DDL is database dependent you need to code DB
specific anyway. And in this case a PS probably won't help much. Just
assemble the string and execute it via a Statement.

Kind regards

robert

Reply to this Message

Lew - 26 Apr 2007 13:01 GMT

>> I'm trying to write some simple mechanism, that creates a database
>> named after a given string.
[quoted text clipped - 20 lines]
>
> I am no Postgres expert but I would guess that the JDBC driver is right.

No JDBC or ODBC prepared statement allows this, for any RDBMS. That's because
things like the database name, column names, etc., in SQL are not typed, that
is, they aren't strings, dates, numbers or anything else parametrizable. You
couldn't, say, setString( 1, dbName ) because the database name is not a
string (CHAR or VARCHAS or any other variant).

Since the database name is not a "string" in the SQL sense, there isn't a way
to plug in "string concatenation" in the SQL world for that part of the statement.

>> Is there a reasonable portable way to avoid this problem? Is there
>> e.g. a Java-way to do string escaping and just concatenate all the bits?

The "Java-way" is to build the string, e.g.,
String dbName = buildDbName();

then plug it into the statement:

String stmt = "CREATE DATABASE "+ dbName;

> Not afaik. But since DDL is database dependent you need to code DB
> specific anyway. And in this case a PS probably won't help much. Just
> assemble the string and execute it via a Statement.

This is not a portability issue at all. The code in question is reasonably
portable, even to not being able to parametrize those that are not parameters.

Signature

Lew

Reply to this Message

Bernd Fuhrmann - 26 Apr 2007 13:59 GMT

>>> Is there a reasonable portable way to avoid this problem? Is there
>>> e.g. a Java-way to do string escaping and just concatenate all the bits?
[quoted text clipped - 5 lines]
>
> String stmt = "CREATE DATABASE "+ dbName;

Thanks for your help. So it's a typing issue. However, I still need to
know, how to actually get that string for e.g. dbName. Which method can
do the job of "buildDbName"? I couldn't find any way to do that so far,
except doing it manually with search/replace. It is hard to believe,
that there is not function to do that right away. So, how can I get my
database name with escaping?

If I have to do it manually, is there some sort of standard, that tells
me which characters are allowed and which are forbidden?

Thanks in advance,
Bernd Fuhrmann

Reply to this Message

Philipp Taprogge - 26 Apr 2007 19:55 GMT

Hi!

> Thanks for your help. So it's a typing issue. However, I still need to
> know, how to actually get that string for e.g. dbName. Which method can
> do the job of "buildDbName"?

You did not get the point...
You just have to build the statement yourself, inserting the db name
into the statement string like this:

String dbName = "THE_NEW_DATABASE";

Statement stmt = connection.createStatement();
stmt.executeUpdate("CREATE DATABASE " + dbName + ";");

Where you actually get your dbName from, is no concern of JDBC or
the database itself but obviously depends on your application
requirements.

Regards,

Phil

Reply to this Message

Bernd Fuhrmann - 27 Apr 2007 08:10 GMT

> You did not get the point...
> You just have to build the statement yourself, inserting the db name
[quoted text clipped - 8 lines]
> the database itself but obviously depends on your application
> requirements.

I have the name of the database, of course. Let's assume I have it from
a malicious user. Maybe it is this:

dbName = "don'tcare;\" DROP important_database;";

This is obviously an example of code injection (maybe not working) Which
method do I need use to do proper escaping on this. Let's assume I want
a database with some arbitrary name, even if it is totally crazy like
the example I wrote above. Isn't there some portable usual way of doing
this?

Thanks again for your time and help,
Bernd Fuhrmann

Reply to this Message

Florian Weimer - 27 Apr 2007 12:29 GMT

* Bernd Fuhrmann:

> This is obviously an example of code injection (maybe not working)
> Which method do I need use to do proper escaping on this. Let's assume
> I want a database with some arbitrary name, even if it is totally
> crazy like the example I wrote above. Isn't there some portable usual
> way of doing this?

No, there isn't. For instance, PostgreSQL doesn't support quoting in
identifiers, so you can't use identifiers containing a '"' character.

Reply to this Message

Lew - 27 Apr 2007 12:56 GMT

Bernd Fuhrmann:

>> This is obviously an example of code injection (maybe not working)
>> Which method do I need use to do proper escaping on this. Let's assume
[quoted text clipped - 4 lines]
>>
>> dbName = "don'tcare;\" DROP important_database;";

> No, there isn't. For instance, PostgreSQL doesn't support quoting in
> identifiers, so you can't use identifiers containing a '"' character.

Besides, the user shouldn't even know about the database, much less get to
specify its name. That's application logic, not user logic.

Signature

Lew

Reply to this Message

Lee Fesperman - 27 Apr 2007 20:53 GMT

> >>> Is there a reasonable portable way to avoid this problem? Is there
> >>> e.g. a Java-way to do string escaping and just concatenate all the bits?
[quoted text clipped - 15 lines]
> If I have to do it manually, is there some sort of standard, that tells
> me which characters are allowed and which are forbidden?

There is a standard -- ANSI SQL. However, compliance is not very high
for many RDBMSs. ANSI SQL specifies allowable characters in
identifiers and also allows quoting (escaping) with double-quotes for
using special characters and keywords as identifiers. To find out the
quoting character for a given RDBMS use the getIdentifierQuoteString()
method in java.sql.DatabaseMetaData. If it returns space " ", then the
RDBMS does not support quoting.

Also, the other posters did not explain the situation with ?
parameters in prepared statements very well... ? parameters can only
be used in SQL-Data statements (SELECT, INSERT, DELETE, UPDATE) and
only in places where a 'scalar' value is expected. ? parameters can't
be used for identifiers (tables names, column names and other entity
names) or for keywords.

--
Lee Fesperman, FFE Software, Inc. (http://www.firstsql.com)
==============================================================
* The Ultimate DBMS is here!
* FirstSQL/J Object/Relational DBMS (http://www.firstsql.com)

Reply to this Message

Bernd Fuhrmann - 28 Apr 2007 06:29 GMT

> There is a standard -- ANSI SQL. However, compliance is not very high
> for many RDBMSs. ANSI SQL specifies allowable characters in
[quoted text clipped - 10 lines]
> be used for identifiers (tables names, column names and other entity
> names) or for keywords.

That explains a lot for me. Thanks for all your fast and helpful answers.

Best,
Bernd Fuhrmann

Reply to this Message

limitations of PreparedStatement with PostgreSQL

Free Magazines