使用LIKE查询在mysql中使用撇号获取记录

I have MYSQL query like this

我有像这样的MYSQL查询

String query="select DISTINCT sctName from snomedicd10map where sctName Like '" + search + "%" + "' LIMIT 5 ";

this query is for auto complete and if i search word like code's , girl's etc during typing of Apostrophe(') null point exception is occurred

此查询用于自动完成,如果我在键入Apostrophe(')null点异常时搜索类似代码,女孩等的单词

how can i overcome from this . when user enters name with Apostrophe(') in text field it should fetch the result .

我怎么能克服这一点。当用户在文本字段中输入带有Apostrophe(')的名称时,它应该获取结果。

i have fetching result from mysql database .

我从mysql数据库中获取结果。

How can i achieve this.

我怎样才能做到这一点。

3 个解决方案

#1

Usually when you have the name hard coded you can repeat and escape

通常当您使用硬编码的名称时,您可以重复并逃脱

Select * from Students where name like 'John O''Neal'

If you are taking them sanitize the string or use paramaterized queries. Look into prepared statements.

如果您正在使用它们清理字符串或使用参数化查询。查看准备好的陈述。

#2

You should be using a PreparedStatement, so it will handle the escaping for you. For example:

您应该使用PreparedStatement,因此它将为您处理转义。例如:

try (PreparedStatement pstmt = Connection.prepareStatement("select DISTINCT sctName from snomedicd10map where sctName Like concat(?, '%') LIMIT 5") {
    pstmt.setString(1, search);
    try (ResultSet rs = pstmt.executeQuery()) {
        // process result
    }
}

Alternatively (handle wildcard in code):

或者(在代码中处理通配符):

try (PreparedStatement pstmt = Connection.prepareStatement("select DISTINCT sctName from snomedicd10map where sctName Like ? LIMIT 5") {
    pstmt.setString(1, search + "%");
    try (ResultSet rs = pstmt.executeQuery()) {
        // process result
    }
}

The JDBC driver will automatically handle escaping (or send the parameters in a way that does not require escaping)

JDBC驱动程序将自动处理转义(或以不需要转义的方式发送参数)

#3

-1

This is what I was suggesting in my original comment, is it what you tried?

这是我在原始评论中提出的建议,是你尝试过的吗?

String query="select DISTINCT sctName from snomedicd10map where sctName Like '" 
            + search.replaceAll("'", "\'") + "%' LIMIT 5 ";

#1