New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

org.postgresql.core.Parser fails on two queries separated by semicolon #1289

Closed
spbfox opened this Issue Aug 15, 2018 · 2 comments

Comments

3 participants
@spbfox

spbfox commented Aug 15, 2018

I'm submitting a ...

  • [x ] bug report
  • feature request

Describe the issue

My Java WEB application creates a prepared statement using the following call:

connection.prepareStatement(query);

where query looks like this:

String query = "INSERT INTO user(login, lastname, firstname, userpassword) VALUES (?, ?, ?, ?);SELECT currval('userp_id_seq')";

The first query has four parameters to bind. The second query is supposed to retrieve an ID of the inserted record generated by the known sequence. The second query does not have any bound parameters.

Java Version
Java(TM) SE Runtime Environment 18.3 (build 10.0.2+13)
OS Version
Windows 7
PostgreSQL Version
PostgreSQL 9.6.8
To Reproduce
Create a database connection and try to create a prepared statement using connection.prepareStatement(query) call. The query string should include two queries separated by a semicolon. The first query should have parameters to bind (question mark parameter placeholders). The second query should NOT have the bound parameters (see example in the context description). Execute the code.

Expected behaviour
The call completes without exceptions and a prepared statement is created.

What actually happens
The following exception is thrown:

java.lang.IndexOutOfBoundsException: Index -1 out-of-bounds for length 0] with root cause
java.lang.IndexOutOfBoundsException: Index -1 out-of-bounds for length 0
at java.base/jdk.internal.util.Preconditions.outOfBounds(Unknown Source)
at java.base/jdk.internal.util.Preconditions.outOfBoundsCheckIndex(Unknown Source)
at java.base/jdk.internal.util.Preconditions.checkIndex(Unknown Source)
at java.base/java.util.Objects.checkIndex(Unknown Source)
at java.base/java.util.ArrayList.get(Unknown Source)
at org.postgresql.core.Parser.parseJdbcSql(Parser.java:257)

I ran some investigation, and it looks like the problem is caused by calling bindPositions.get(bindPositions.size() - 1) with bindPositions collection empty, which makes the index for get() call equal to -1.

The source of the problem is really in the parsing of a semicolon (line 175):

    if (splitStatements) {
        // Prepare for next query
        if (bindPositions != null) {
             bindPositions.clear();
        }
        nativeSql.setLength(0);
        valuesBraceOpenPosition = -1;
        valuesBraceClosePosition = -1;
        valuesBraceCloseFound = false;
    }  

The code should reset bindPositions to nulll, not just empty the collection. According to the code logic, the fact that bindPositions is null means that no question mark placeholders were found, and if it is not null, then there must be at least one placeholder in the current query. Semicolon terminates the first query and starts new one, therefore you cannot now at this point if there are any bindings in it, and the collection should be null.

As I understand it, the first query with parameter binding creates the bindPositions collection:

    if (bindPositions == null) {
        bindPositions = new ArrayList<Integer>();
    }

and then populates it with the question mark positions.
Then, the semicolon between two queries comes, and the code above clears the bindPositions collection keeping it not null.
Then the second query comes. It does not have any bound parameters, but the collection is not null and bindPositions.get(bindPositions.size() - 1) still gets executed throwing "OutOfBounds" exception.

I think, the bindPositions should either be reset to null in the semicolon parsing code, or even better the code should check if the bindPositions is empty before trying to get anything from it.

@vlsi vlsi closed this in c2885dd Aug 15, 2018

@vlsi vlsi added this to To do in 42.3.0 Release via automation Aug 15, 2018

@vlsi vlsi added this to the 42.3.0 milestone Aug 15, 2018

@bokken

This comment has been minimized.

Member

bokken commented Aug 15, 2018

While we certainly should resolve the issue, it seems a better approach to accomplish getting the primary key would be to use the jdbc functionality to define columns to be returned.

final String key[] = {"user_id"}; //put the name of the primary key column

PreparedStatement ps = con.prepareStatement("INSERT INTO user(login, lastname, firstname, userpassword) VALUES (?, ?, ?, ?)", key);
ps.executeUpdate();

ResultSet rs = ps.getGeneratedKeys();
if (rs.next()) {
    generatedKey = rs.getLong(1);
}
@spbfox

This comment has been minimized.

spbfox commented Aug 15, 2018

Thank you for the hint. Will definitely use it.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment