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
0
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
0
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
0
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
0
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 ";