Thread: How to implement a dynamic string into a sql statement?

Hello Mailinglist! :-)

I have some spatial datasets which are availabe in a opensource metadata
catalog software GeoNetwork and in a PostGIS database. The problem is
that the student who created the database used different IDs for the
same datasets. So the IDs in the GeoNetwork differs from the IDs in the
postGIS database. They only have the same name

So, if someone uploads a new dataset into GeoNetwork, the same dataset
is uploaded into a postGis database too.

Now I want to add a ID column into the database to give the field the
same ID like the ID in geonetwork. I think I can only do this with using
the filename cause they are same in both cases.

Now I want to alter the table in eclipse with following code:

Connection con = null;
PreparedStatement ps = null;
final String sqlps = "ALTER TABLE ? ADD COLUMN ?";

String filen = filename.substring(0, filename.indexOf('.'));
                 try {
                     con =
DriverManager.getConnection("jdbc:postgresql_postGIS://localhost:5433/testdb?user=postgres&password=test");
                     try {
                         ps = con.prepareStatement(sqlps);
                         ps.setString(1, filen);
                         ps.setString(2, "GN_ID");

                         ps.execute();

But this does not work cause I am getting the following
Exception:org.postgresql.util.PSQLException: ERROR: syntax error at »$1«
Position: 13

So, it seems that PSQL has problems with the ? in the prepared
statement. Does anyone know a solution for this problem?

I also tried using normale statement, but this is giving me a findbug
error that I try to pass a nonconstant string to an execute method on an
sql statement. So I really dont know how to get rid of this problem

Thank you in advance for every help

Lussier, Denis wrote
> Don't use prepared statements for DDL.

Parameters only handle values, not identifiers.  DDL deals exclusively with
identifiers.  So while you can use prepared statements you cannot
parameterize them - so the prepared aspect becomes fairly useless except for
consistency if you have some sort of wrapper layer making use of them.

David J.





--
View this message in context:
http://postgresql.1045698.n5.nabble.com/How-to-implement-a-dynamic-string-into-a-sql-statement-tp5816355p5816359.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

howalt wrote
> So, it seems that PSQL has problems with the ? in the prepared
> statement. Does anyone know a solution for this problem?

The most secure way to handle this would be to create a custom pl/pgsql
function and build the dynamic sql string in there and execute it.  You can
then make use of the "quote_ident" function to help make sure that the sql
you are creating is valid and resistant to injection.


> I also tried using normale statement, but this is giving me a findbug
> error that I try to pass a nonconstant string to an execute method on an
> sql statement. So I really dont know how to get rid of this problem

I have no clue what a "findbug error" is.  I presume it is warning you about
sql injection risks.  In which case you will have to override the error
since the only way to do what you want, directly in Java, is to risk sql
injection.  Thus you have to be especially careful to validate the user
input.

Again, you can offload that responsibility to PostgreSQL itself by simply
passing the variables, as values, into a function on the server and let that
function convert them into identifiers.

David J.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/How-to-implement-a-dynamic-string-into-a-sql-statement-tp5816355p5816363.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

On 08/27/2014 02:26 AM, David G Johnston wrote:
> Lussier, Denis wrote
>> Don't use prepared statements for DDL.
>
> Parameters only handle values, not identifiers.  DDL deals exclusively with
> identifiers.

Actually, that isn't strictly true, there are plenty of places where DDL
takes literals.

The fundamental difference is that anything going through ProcessUtility
isn't subject to parameter expansion and cannot be prepared with
parameters. Only plannable statements can have server-side parameters.

PgJDBC can still, if it chooses, perform client-side parameter
substitution on DDL, though.

--
 Craig Ringer                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services