Search code examples
postgresqljdbcprepared-statement

Use JDBC placeholder into string literal

looking for a way to submit parameterized queries through dblink (PostgreSQL) using JDBC (PreparedStatement).

This is a sample query I want to use:

select * from dblink('host=localhost user=*** password=***', $$
    select 'abc'
$$) as tmp (n text)

select * from dblink(?, $$
    select ?
$$) as tmp (n text)

However, JDBC provides an option to pass only the first parameter (connstr). The second one is enclosed in a string literal and therefore not processed. At the moment I'm solving the problem by using concatenation, but I don't like this approach and that's why I'm looking for an alternative

Solution

  • You can use the PostgreSQL "format" function to inject the parameter on the server side, with proper escaping and quoting:

    select * from dblink(?, format('select %L',?)) as tmp (n text)

    JDBC probably provides helper functions which should also let you do this client side more safely than just naive concatenation, but I've not investigated that.