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

Question

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.

possible duplicate of [Does the JDBC spec prevent '?' from being used as an operator (outside of quotes)?](http://stackoverflow.com/questions/14779896/does-the-jdbc-spec-prevent-from-being-used-as-an-operator-outside-of-quotes) — Mohammad Najar, Oct 22 '14 at 20:13
The suggested duplicate, http://stackoverflow.com/questions/14779896/does-the-jdbc-spec-prevent-from-being-used-as-an-operator-outside-of-quotes specifically does not address the question of the `?` parameter marker within quotes (asked here). — Mike Godin, Oct 23 '14 at 01:49

score 30 · Answer 1 · answered Feb 12 '16 at 09:49

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

https://jdbc.postgresql.org/documentation/head/statement.html

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 a 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.

Elliott Frisch · Answer 2 · 2014-10-23T02:15:48.453

9

If it doesn't work with your JDBC driver you could bind it as a String ?,

ps.setString(1, "?");

edited Oct 23 '14 at 02:15

answered Oct 22 '14 at 20:12

Elliott Frisch

198,278
20
158
249

score 5 · Accepted Answer · answered Oct 24 '14 at 20:17

5

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).

answered Oct 24 '14 at 20:17

Mark Rotteveel

100,966
191
140
197

11

Postgres uses the question mark as an operator on jsonb types. See: http://www.postgresql.org/docs/9.4/static/functions-json.html – hasen Oct 08 '15 at 04:26
@hasen Although that is true, this would need to be remapped by the driver (which I believe recent PostgreSQL JDBC drivers do), as in JDBC the question mark is supposed to only be a parameter placeholder. See http://stackoverflow.com/questions/14779896/does-the-jdbc-spec-prevent-from-being-used-as-an-operator-outside-of-quotes and http://stackoverflow.com/questions/27573778/postgresql-jsonb-and-jdbc – Mark Rotteveel Oct 08 '15 at 08:51
5

@hasen specifically in the latest PostgreSQL driver you need to use `??` for the hstore ? operator. – Mark Rotteveel Oct 08 '15 at 08:53

score 3 · Answer 4 · answered Oct 22 '14 at 20:12

3

Did you try it? I think quoted question marks are OK. only "bare" question marks should get replaced in the prepared statement

answered Oct 22 '14 at 20:12

dkatzel

31,188
3
63
67

I edited the op to include a test... Which confirms that your suggestion is correct. – Mike Godin Oct 23 '14 at 01:47

score 2 · Answer 5 · edited Mar 09 '23 at 16:56

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.

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

5 Answers5

Linked