Avoiding a Character Question Mark ('?') In a Prepared JDBC Statement

Question

I would like to create a JDBC PreparedStatement, for example:

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

Where is the 1st ? is a literal, and the second ? is a parameter. I could use CHAR(63) instead of '?' , but I think an extra function call will slow down SQL execution. Is there any way to avoid this 1st ? ?

Edit:

The following code checks dkatzel's statement that the character ? in a line 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(); } } 

Exit:

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

It seems that dkatzel is true. I searched for JDBC Spec and could not find mention of what parameter marker ? it is ignored if it is enclosed in quotation marks, but several implementations of the PreparedStatement parsers that I found ( MySql , c-JDBC , H2 ) all seem to exclude the text in single quotes from consideration as parameter markers.

Answer 1

Value ? specified in the SQL specification, and the JDBC specification refers to the SQL specification for this.

The driver does not (and should not) interpret the question mark in the literal as a placeholder, since the question mark in the string literal is just a character in the string literal. For more information, see Chapter 5 of the SQL: 2011 database (ISO-9075-2: 2011).

So shielding is not necessary (and not possible).

Answer 2

If this does not work with your JDBC driver, can you link it as a String ? ,

 ps.setString(1, "?"); 

Answer 3

Depending on the JDBC driver you are using, you may escape by adding another question mark, for example. if you are using PostgreSQL

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

In JDBC, the question mark ( ? ) Is a placeholder for the PreparedStatement positional parameters. However, there are a number of PostgreSQL statements that contain a question mark. To interpret such question marks in an SQL expression as positional parameters, use two question marks ( ?? ) as an escape sequence. You can also use this escape sequence in Statement, but this is not required. In particular, only in the instructions can one ( ? ) Be used as an operator.

Answer 4

Have you tried I think the quoted question marks are ok. only bare question marks should be replaced in the prepared statement

Answer 5

I used CHR (63) in my request and helped solve my problem.

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

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

Then I used this query inside the insert statement and went through PreparedStatement. It worked perfectly.

The CHR function is a built-in function and can be used similarly to other oracle functions. You can use this if you know that the request will not be repeated many times.