Re: Parameters are not being setted in a copy statement

From: pgsql-jdbc-owner(at)postgresql(dot)org [mailto:pgsql-jdbc-owner(at)postgresql(dot)org] On Behalf Of Martin
Sent: Wednesday, October 17, 2012 11:52 AM
To: pgsql-jdbc(at)postgresql(dot)org
Subject: [JDBC] Parameters are not being setted in a copy statement

Hi all

We need to copy from an arbitray query to a csv. We are using Postgresql 9.1 an we would use the copy statement

We need to trigger this query from a java application. We are using Hibernate but you could find the problem to jdbc level

It's work with query with no parameters but if you try to set parameters you get a there is no parameter $1 Error

Do you know if this is a bug and if there is a workarround to lead with this?

I wirite a test to show this

SQL :

create schema test;

create table test.test_copy (

id serial primary key

,data1 varchar( 100)

,data2 varchar( 100)

);

insert into test.test_copy(data1,data2) values ('test1','test2');

insert into test.test_copy(data1,data2) values ('test3','test4');

insert into test.test_copy(data1,data2) values ('test5','test6');

insert into test.test_copy(data1,data2) values ('test7','test8');

Java test

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import org.testng.annotations.Test;

public class PostgressTest {

private String DOES_NOT_WORK = "COPY (SELECT * " +

"FROM test.test_copy t "+

"WHERE data1 like (?) OR data2 ilike (?) " +

") TO '/data/export/fail.csv' WITH CSV HEADER DELIMITER ';'";

private String WORK = "COPY (SELECT * " +

"FROM test.test_copy t "+

"WHERE data1 like ('test1') OR data2 ilike ('test4') " +

") TO '/data/export/work.csv' WITH CSV HEADER DELIMITER ';'";

private Connection connection;

public PostgressTest() throws Exception {

Class.forName("org.postgresql.Driver");

connection = DriverManager.getConnection("jdbc:postgresql://127.0.0.1:5432/test", "my_user",

"my_pass");

}

@Test

public void testDoesNotWork() throws Exception {

PreparedStatement statement = connection

.prepareStatement(DOES_NOT_WORK);

statement.setString(1, "test1");

statement.setString(2, "test4");

statement.execute();

}

@Test

public void testWork() throws Exception {

PreparedStatement statement = connection

.prepareStatement(WORK);

statement.execute();

}

}

Thanks!!

Martin

I am pretty sure this is PostgreSQL’s limitation and not JDBC but my quick search for confirmation in the documentation (under COPY, PREPARE, or EXECUTE) did not turn up anything.

Much like “CREATE TABLE” and similar statements do not provide for “parameters” the COPY command does not at well. Thus in order to do what you want you need to build a non-parameterized (i.e., no “?”) statement and execute that as-is; making sure you deal with SQL-injection attack vectors.

My personal option would be to create a function to encapsulate the desired query:

“COPY (SELECT * FROM some_function(“ & param_1_value & “ , ” & param_2_value & “ ) TO …”

Making sure the values of the parameters can be safely inserted as literals into the query (including adding single-quotes if necessary).

David J.