CODEWITHSUNDEEP
javaoracle-databasejdbcprepared-statementojdbc
Jeff Brower
Jeff Brower

Reputation: 602

PreparedStatement: Missing expression on single quote (ORA-00936)

I am querying an Oracle database like this:

try (Connection c = dataSource.getConnection();
    PreparedStatement p = c.prepareStatement(
        "SELECT * FROM table WHERE column_string = ?")) {
    p.setString(1, input);

    try (ResultSet r = p.executeQuery()) {
        // handle ResultSet
    }
}

And the input string is received from the user. This call is working for normal input strings, like:

hello world
special: characters 0_2 (ftlo) -

But failing for an input string like this:

7-8 l/o y-S '

(note the dashes, forward slash, and single quote at the end)

I was assuming it had to do with the single quote, but I was under the impression that PreparedStatement takes care of all of the escaping for you. Has anyone seen issues like this, or knows more about character escaping in the oracle JDBC drivers?

I am using:

Stacktrace:

SQLSyntaxErrorException: ORA-00936: missing expression
oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)
oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)
oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:884)
oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1167)
oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1289)
oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3584)
oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3628)
oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1493)
org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:462)
... <my classes>

Upvotes: 0

Views: 434

Answers (3)

Jeff Brower
Jeff Brower

Reputation: 602

It actually turned out that I was getting an error for a different piece of code than I thought. My ResultSetHandler is doing some additional cleanup logic after the main query is finished, and that was throwing the SQLException (missing expression). Due to the implementation of the apache DButils and JDBC libraries, the error was swallowed and not printed in a helpful way. Sorry for the confusion.

Upvotes: 1

Andreas
Andreas

Reputation: 159165

Unable to reproduce. Added file ojdbc6-11.2.0.3.jar from here.

Ran following MCVE code against 12.1.0.2 server (values to getConnection has of course been masked).

public static void main(String[] args) throws Exception {
    test("hello world");
    test("special: characters 0_2 (ftlo) -");
    test("7-8 l/o y-S '");
    test("X");
}
private static void test(String input) throws SQLException {
    System.out.println(input);
    try (Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@XXX:1521:XXX", "XXX", "XXX")) {
        String sql = "select DUMMY from DUAL where DUMMY = ?";
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, input);
            try (ResultSet rs = stmt.executeQuery()) {
                int count = 0;
                while (rs.next()) {
                    System.out.println("  " + rs.getString("DUMMY"));
                    count++;
                }
                System.out.println("  " + count + " rows");
            }
        }
    }
}

Output

hello world
  0 rows
special: characters 0_2 (ftlo) -
  0 rows
7-8 l/o y-S '
  0 rows
X
  X
  1 rows

Upvotes: 0

Romantic Ali
Romantic Ali

Reputation: 1

Possibly the problem due single quote, because in Oracle the last must be repeated twice to be enterpreted as single quote or whole string must be included in q'{...}' construct where ... is a given string.

Upvotes: 0

Related Questions