如何在JDBC预准备语句中转义文字问号（'？'）

I'd like to create a JDBC PreparedStatement like:

我想创建一个JDBC PreparedStatement,如:

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 ??

哪一号?是文字和第二?是一个参数。我可以使用CHAR(63)代替'?'但我认为额外的函数调用会减慢SQL执行速度。有没有办法逃脱第一次?

Edit:

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

下面的代码测试dkatzel的断言是什么?字符串中的字符不被视为标记:

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.

似乎dkatzel是正确的。我搜索了JDBC规范并且找不到任何提及的那个?如果它在引号内,则会忽略参数标记,但是我发现的PreparedStatement解析器的几个实现(MySql,c-JDBC,H2)似乎都将单引号中的文本排除在考虑范围之外作为参数标记。

5 个解决方案

#1

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

这个意思?在SQL规范中指定,JDBC规范遵循SQL规范。

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

驱动程序不会(也不应该)将文字中的问号解释为参数占位符,因为字符串文字中的问号只是字符串文字中的字符。有关更多信息,请参阅SQL:2011 Foundation(ISO-9075-2:2011)的第5章。

So escaping is not necessary (nor possible).

所以逃避不是必要的(也不可能)。

#2

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

根据您使用的JDBC驱动程序,您可以通过添加另一个问号来逃避,例如如果你正在使用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.

在JDBC中,问号(?)是PreparedStatement的位置参数的占位符。但是,有许多PostgreSQL运算符包含问号。要使SQL语句中的问号不被解释为位置参数,请使用两个问号(??)作为转义序列。您也可以在Statement中使用此转义序列,但这不是必需的。特别是在Statement中,单个(?)可以用作运算符。

#3

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

如果它不能与您的JDBC驱动程序一起使用,您可以将它绑定为String ?,

ps.setString(1, "?");

#4

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

你试过吗?我认为引用的问号是可以的。在准备好的声明中只应替换“裸”问号

#5

I have used CHR(63) in my query and that helped resolving my issue.

我在查询中使用了CHR(63),这有助于解决我的问题。

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

以下是我所做的例子:选择q'[

]'来自双重;

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.

然后我在insert语句中使用了这个查询,并运行了PreparedStatement。工作得非常好。

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.

CHR函数是一个内置函数,可以像其他oracle函数一样使用。如果您知道查询将不会重复多次,您可以使用此方法。

#1