AppletTalk.com Java discussions newsgroups   Archives   FAQ   Search   Memberlist   Usergroups   Register   Profile   Log in to check your private messages   Log in  limitations of PreparedStatement with PostgreSQL          AppletTalk.com Forum Index -> Java and Databases View previous topic :: View next topic   Author Message Bernd Fuhrmann Guest Posted: Thu Apr 26, 2007 3:26 pm    Post subject: limitations of PreparedStatement with PostgreSQL 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 Back to top Robert Klemme Guest Posted: Thu Apr 26, 2007 4:09 pm    Post subject: Re: limitations of PreparedStatement with PostgreSQL On 26.04.2007 12:26, Bernd Fuhrmann wrote: Quote: 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? I am no Postgres expert but I would guess that the JDBC driver is right. Quote: 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 Back to top Lew Guest Posted: Thu Apr 26, 2007 5:01 pm    Post subject: Re: limitations of PreparedStatement with PostgreSQL Robert Klemme wrote: Quote: On 26.04.2007 12:26, Bernd Fuhrmann wrote: 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 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. Quote: 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; Quote: 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. -- Lew Back to top Bernd Fuhrmann Guest Posted: Thu Apr 26, 2007 5:59 pm    Post subject: Re: limitations of PreparedStatement with PostgreSQL Lew wrote: Quote: 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; 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 Back to top Philipp Taprogge Guest Posted: Thu Apr 26, 2007 11:55 pm    Post subject: Re: limitations of PreparedStatement with PostgreSQL Hi! Thus spake Bernd Fuhrmann on 04/26/2007 02:59 PM: Quote: 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 Back to top Bernd Fuhrmann Guest Posted: Fri Apr 27, 2007 12:10 pm    Post subject: Re: limitations of PreparedStatement with PostgreSQL Philipp Taprogge wrote: Quote: 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. 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 Back to top Florian Weimer Guest Posted: Fri Apr 27, 2007 4:29 pm    Post subject: Re: limitations of PreparedStatement with PostgreSQL * Bernd Fuhrmann: Quote: 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. Back to top Lew Guest Posted: Fri Apr 27, 2007 4:56 pm    Post subject: Re: limitations of PreparedStatement with PostgreSQL Bernd Fuhrmann: Quote: 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? 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;"; Florian Weimer wrote: Quote: 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. -- Lew Back to top Lee Fesperman Guest Posted: Sat Apr 28, 2007 12:53 am    Post subject: Re: limitations of PreparedStatement with PostgreSQL Bernd Fuhrmann wrote: Quote: Lew wrote: 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; 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? 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) Back to top Bernd Fuhrmann Guest Posted: Sat Apr 28, 2007 7:11 am    Post subject: Re: limitations of PreparedStatement with PostgreSQL Lee Fesperman wrote: Quote: 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. That explains a lot for me. Thanks for all your fast and helpful answers. Best, Bernd Fuhrmann Back to top Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First         AppletTalk.com Forum Index -> Java and Databases All times are GMT Page 1 of 1   Jump to: Select a forum Java Newsgroups----------------Java DiscussionsWeb-based java games comp.lang.java 3D Graphics API's for Java Java: Support and criticism Java System Announcements comp.lang.java.api JavaBeans Java and CORBA Java and Databases comp.lang.java.developer Java GUI Toolkits Java Help comp.lang.java.javascript JVM, native methods and hardware comp.lang.java.misc Java Language Programming Security and Java comp.lang.java.setup Java Software Tools comp.lang.java.tech ibm.software.java.linux  Non-English Java Newsgroups----------------Java Language (German)Language Java (Français)Java (Italian)  You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum Powered by phpBB © 2001, 2006 phpBB Group SEO toolkit © 2004-2006 webmedic.