Reputation: 3947

How do I escape a literal question mark ('?') in a JDBC prepared statement

I'd like to create a JDBC PreparedStatement like:

SELECT URL,LOCATE ( '?', URL ) pos FROM Links WHERE pageId=? ORDER BY pos ASC

Where the 1st ? is a literal and the 2nd ? is a parameter. I could use CHAR(63) in place of the '?' but I think the extra function call would slow down the SQL execution. Is there some way to escape that 1st ??

Edit:

The following code tests dkatzel's assertion that the ? character in a string is not considered a marker:

public class Test {
    public static void main(String[] args) throws SQLException {
        Connection conn = DriverManager.getConnection("jdbc:h2:mem:test");
        Statement stmt = conn.createStatement();
        stmt.executeUpdate("CREATE TABLE Links(URL VARCHAR(255) PRIMARY KEY,pageId BIGINT)");
        stmt.executeUpdate("INSERT INTO Links(URL,pageId) VALUES('http://foo.bar?baz',1)");
        stmt.executeUpdate("INSERT INTO Links(URL,pageId) VALUES('http://foo.bar/baz',1)");
        stmt.close();
        PreparedStatement ps = conn
            .prepareStatement("SELECT URL,LOCATE ( '?', URL ) pos FROM Links WHERE pageId=? ORDER BY pos ASC");
         ps.setLong(1, 1);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            System.out.println(rs.getString(1) + ":" + rs.getInt(2));
        }
        rs.close();
        ps.close();
        conn.close();
    }
}

The output:

http://foo.bar/baz:0
http://foo.bar?baz:15

It appears that dkatzel is correct. I searched the the JDBC Spec and could not find any mention that the ? parameter marker would be ignored if it's within quotes, but the few implementations of PreparedStatement parsers that I found (MySql,c-JDBC,H2) all appear to exclude text within single quotes from consideration as parameter markers.

Upvotes: 17

Answers (5)

Mark Rotteveel

Reputation: 109257

The meaning of the ? is specified in the SQL specification, and the JDBC specification defers to the SQL specification for this.

A driver doesn't (and shouldn't) interpret a question mark in a literal as a parameter placeholder, as a question mark within a string literal is simply a character within the string literal. For more information look at chapter 5 of SQL:2011 Foundation (ISO-9075-2:2011).

So escaping is not necessary (nor possible).

However, that is the theory, and, unfortunately, in practice it doesn't work like that in the PostgreSQL JDBC driver. To address this, recent versions of the PostgreSQL driver provide an option to escape a question mark which is not a parameter placeholder by using ?? instead.

Upvotes: 6

bobmarksie

Reputation: 3646

Depending on the JDBC driver you are using you may be able to escape by adding another question mark e.g. if you're using PostgreSQL

From Using the Statement or PreparedStatement Interface:

In JDBC, the question mark (?) is the placeholder for the positional parameters of a PreparedStatement. There are, however, a number of PostgreSQL® operators that contain a question mark. To keep such question marks in an SQL statement from being interpreted as positional parameters, use two question marks (??) as escape sequence. You can also use this escape sequence in a Statement, but that is not required. Specifically only in a Statement a single (?) can be used as an operator.

Upvotes: 31

Yogesh A Sakurikar

Reputation: 1881

I have used CHR(63) in my query and that helped resolving my issue. (Decimal 63 is the question mark in ASCII.)

Here is what I did for example: select q'[<div id=['|"]TRD_%%GEN%%['|"].*]' || chr(63) || q'[</div>]' from dual;

This helped getting the string as : <div id=['|"]TRD_%%GEN%%['|"].*?</div>

I have then used this query inside the insert statement, and ran through PreparedStatement. Worked perfectly fine.

The CHR function is an in built function and can be used similar to other oracle functions. You can use this if you know the query will not going to be repeated lots of times.

Upvotes: 2