Unable to Prepare a Postgres Statement in Java (java.sql.PreparedStatement)

Question

Consider two statements that I may want to send to my server from Java.

Simple SQL: This is a typically insert statement.

insert into table_things (thing_1_value, thing_2_value) values(?, ?);

PlPgSQL: I want to avoid round-trips to the database by doing some login in the database. We are not allowed to use stored procedures or functions in the database (the reasons are seem valid).

do $$
declare
    my_thing1 varchar(100) = ?;
    my_thing2 varchar(100) = ?;
begin
    insert into table_things
    (
          thing_1_value
        , thing_2_value
    )
    values
    (
          my_thing1
        , my_thing2
    )
    ;
end
$$;

The code that executes these statements is represented below in Java8 test cases:

package com.somecompany.someservice.test.database;

import org.apache.commons.dbcp2.BasicDataSource;
import org.junit.Assert;
import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Types;

public class PreparedStatementDatabaseTest {
    private static final String CONNECTION_URI = "jdbc:postgresql://localhost:5432/somedb?user=someuser&password=somepass";

    private static final String PLPGSQL_STATEMENT = "" +
            "do $$\n" +
            "declare\n" +
            "    my_thing1 varchar(100) = ?;\n" +
            "    my_thing2 varchar(100) = ?;\n" +
            "begin\n" +
            "    insert into table_things\n" +
            "    (\n" +
            "          thing_1_value\n" +
            "        , thing_2_value\n" +
            "    )\n" +
            "    values\n" +
            "    (\n" +
            "          my_thing1\n" +
            "        , my_thing2\n" +
            "    )\n" +
            "    ;\n" +
            "end\n" +
            "$$;";

    private static final String EASY_SQL_STATEMENT = "insert into table_things (thing_1_value, thing_2_value) values(?, ?);";

    @Test
    public void testPlpgsqlStatement() throws Exception {
        Class.forName("org.postgresql.Driver");
        BasicDataSource basicDataSource = new BasicDataSource();
        basicDataSource.setUrl(CONNECTION_URI);
        Connection conn = basicDataSource.getConnection();
        PreparedStatement statement = conn.prepareStatement(PLPGSQL_STATEMENT, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
        statement.setObject(1, "hello", Types.VARCHAR);
        statement.setObject(2, "world", Types.VARCHAR);
        boolean isResultSet = statement.execute();
        conn.close();
        Assert.assertFalse(isResultSet);
    }

    @Test
    public void testEasySqlStatement() throws Exception {
        Class.forName("org.postgresql.Driver");
        BasicDataSource basicDataSource = new BasicDataSource();
        basicDataSource.setUrl(CONNECTION_URI);
        Connection conn = basicDataSource.getConnection();
        PreparedStatement statement = conn.prepareStatement(EASY_SQL_STATEMENT, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
        statement.setObject(1, "hello", Types.VARCHAR);
        statement.setObject(2, "world", Types.VARCHAR);
        boolean isResultSet = statement.execute();
        conn.close();
        Assert.assertFalse(isResultSet);
    }
}

testEasySqlStatement works, but testPlpgsqlStatement throws an exception:

org.postgresql.util.PSQLException: The column index is out of range: 1, number of columns: 0.

    at org.postgresql.core.v3.SimpleParameterList.bind(SimpleParameterList.java:65)
    at org.postgresql.core.v3.SimpleParameterList.setStringParameter(SimpleParameterList.java:128)
    at org.postgresql.jdbc.PgPreparedStatement.bindString(PgPreparedStatement.java:996)
    at org.postgresql.jdbc.PgPreparedStatement.setString(PgPreparedStatement.java:326)
    at org.postgresql.jdbc.PgPreparedStatement.setObject(PgPreparedStatement.java:528)
    at org.postgresql.jdbc.PgPreparedStatement.setObject(PgPreparedStatement.java:881)
    at org.apache.commons.dbcp2.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:185)
    at org.apache.commons.dbcp2.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:185)
    at com.somecompany.someservicetest.database.PreparedStatementDatabaseTest.testPlpgsqlStatement(PreparedStatementDatabaseTest.java:44)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
    at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
    at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
    at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47)
    at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242)
    at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)

Question: how can I send code like PLPGSQL_STATEMENT to the Postgres database?

I could do this, but it is bad practice due to SQL Injection risk:

    @Test
    public void testSqlInjectionRisk() throws Exception {
        String hello = "hello-testSqlInjectionRisk";
        String world = "world-testSqlInjectionRisk";

        String PLPGSQL_STATEMENT = "" +
                "do $$\n" +
                "declare\n" +
                "    my_thing1 varchar(100) = '" + hello + "';\n" +
                "    my_thing2 varchar(100) = '" + world + "';\n" +
                "begin\n" +
                "    insert into table_things\n" +
                "    (\n" +
                "          thing_1_value\n" +
                "        , thing_2_value\n" +
                "    )\n" +
                "    values\n" +
                "    (\n" +
                "          my_thing1\n" +
                "        , my_thing2\n" +
                "    )\n" +
                "    ;\n" +
                "end\n" +
                "$$;";

        Class.forName("org.postgresql.Driver");
        BasicDataSource basicDataSource = new BasicDataSource();
        basicDataSource.setUrl(CONNECTION_URI);
        Connection conn = basicDataSource.getConnection();
        PreparedStatement statement = conn.prepareStatement(PLPGSQL_STATEMENT, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
        boolean isResultSet = statement.execute();
        conn.close();
        Assert.assertFalse(isResultSet);

Question Restated: Is there a problem with the way I am trying to prepare PLPGSQL_STATEMENT? Can PLPGSQL_STATEMENT be prepared?

Update: @Izruo pointed out that I should be using prepareCall, and this seems to be part of the answer. But unfortunately, the following code fails with the same exception:

    @Test
    public void testEasySqlStatement2() throws Exception {
        final String SQL_STATEMENT = "" +
                "do $$\n" +
                "declare\n" +
                "    x varchar(100) = ?;\n" +
                "    y varchar(100) = ?;\n" +
                "begin\n" +
                "    insert into table_things\n" +
                "    (\n" +
                "          my_thing1\n" +
                "        , my_thing2\n" +
                "    )\n" +
                "    values\n" +
                "    (\n" +
                "          x\n" +
                "        , y\n" +
                "    )\n" +
                "    ;\n" +
                "end\n" +
                "$$;";

        Class.forName("org.postgresql.Driver");
        BasicDataSource basicDataSource = new BasicDataSource();
        basicDataSource.setUrl(CONNECTION_URI);
        System.out.println(SQL_STATEMENT);
        Connection conn = basicDataSource.getConnection();
        CallableStatement statement = conn.prepareCall(SQL_STATEMENT);
        statement.setObject(1, "hello", Types.VARCHAR);
        statement.setObject(2, "world", Types.VARCHAR);
        boolean isResultSet = statement.execute();
        conn.close();
        Assert.assertFalse(isResultSet);

If I copy the sql statement printed by System.out.println(SQL_STATEMENT); into DataGrip (A database IDE by JetBrains) and run it, then DataGrip asks for me to fill in two parameter values (for the two question marks) and successfully runs the sql statement. In other words, the plpgsql code is syntactically valid (once the params are replaced).

It seems there are three possibilities here, and I cannot tell which is true:

This functionality (creating a CallableStatement/PreparedStatement with plpgsql variables in it) is unsupported.
This functionality is supported but I am doing it wrong.
The functionality is supported, I am using it correctly, but there is a bug.

According to this question and the postresql documentation, you must use Connection#prepareCall instead of Connection#prepareStatement. — Izruo
– Izruo, Commented Jun 7, 2019 at 16:30

Mạnh Quyết Nguyễn · Accepted Answer · 2019-06-11 02:36:55Z

You cannot call a dynamic procedure directly.

You must first create the procedure (manually or dynamically via a Statement call) and then call the procedure by name.

Statement stmt = conn.createStatement();
stmt.execute("CREATE OR REPLACE FUNCTION myfunc(x text, y text) RETURNS refcursor AS '"
        + "do $$\n"
        + "begin\n"
        + "    insert into table_things\n"
        + "    (\n"
        + "          my_thing1\n"
        + "        , my_thing2\n"
        + "    )\n"
        + "    values\n"
        + "    (\n"
        + "          x\n"
        + "        , y\n"
        + "    )\n"
        + "    ;\n"
        + "end\n"
        + "$$;"
        + "' language plpgsql");
stmt.close();

// We must be inside a transaction for cursors to work.
conn.setAutoCommit(false);

// Procedure call.
CallableStatement proc = conn.prepareCall("{ call myfunc(?,?)}");
CallableStatement statement = conn.prepareCall(SQL_STATEMENT);
statement.setObject(1, "hello", Types.VARCHAR);
statement.setObject(2, "world", Types.VARCHAR);

Karen Tserunyan · Accepted Answer · 2019-06-17 12:22:11Z

3

    try (Connection con = DriverManager.getConnection(dbConnectionString, user, password);
         Statement st = con.createStatement();
         ResultSet rs = st.executeQuery("SELECT  * FROM public.\"Airplanes\" ")) {
        while (rs.next()) {

            //use result
        }

    } catch (SQLException ex) {

        //handle exception
    }

    return results;
}

answered Jun 17, 2019 at 12:22

Karen Tserunyan

311 bronze badge

1 Comment

chengpohi Over a year ago

For helping other people find solution, Could you add explanation for your answer?

Matthew James Briggs · Accepted Answer · 2019-06-20 03:57:56Z

Although Mạnh Quyết Nguyễn's answer is on the right track, it is devoid of an explanation and the suggested workaround is not viable in my case (most cases, I would think).

I received an authoritative answer from postgresql.org.

you attempted to add question marks to a location where they are not interpreted as parameters.

Basically you wrote:

SELECT 'let me say ? ? to you';

Which is a perfectly valid query that has zero input parameters and will return:

"let me say ? ? to you"

It has no input parameters because the question marks you wrote are inside a string literal.

The $$...$$ in your DO statement also denote a string literal.

This is unfortunate, as far as I can tell it means the entire PL/pgSQL language is off-limits if you need to pass parameters into that PL/pgSQL code. (Unless, of course, you compile PL/pgSQL procedures or functions either on the fly or as part of schema development). Looks like I cannot send a PL/pgSQL 'script' to the database along with parameters.

Collectives™ on Stack Overflow

Unable to Prepare a Postgres Statement in Java (java.sql.PreparedStatement)

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related