fetch records with apostrophe in mysql using LIKE query

Question

I have MYSQL query like this

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

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

i have fetching result from mysql database .

How can i achieve this.

You can use `"` instead of `'`, you can escape them (like this `\'`), or you can make your query much safer by making it a parameterized one, and passing the string with `%` appended on the end as the parameter. — Uueerdo, Jun 13 '16 at 19:14
http://stackoverflow.com/a/1812920/4421474 Try to use prepared statements as a best practice — Alex, Jun 13 '16 at 19:15
@Uueerdo i tried to change and its showing same error will u change above query like that — teach me, Jun 13 '16 at 19:29
I'm not strongly familiar with Java, the escape sequence I provided may be wrong (as Himalay's answer below would suggest). — Uueerdo, Jun 13 '16 at 19:37
You should really use a prepared statement and set the parameter through the prepared statement, then you don't need to escape yourself. — Mark Rotteveel, Jun 15 '16 at 07:49

score 0 · Answer 1 · answered Jun 13 '16 at 19:22

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.

answered Jun 13 '16 at 19:22

Himalay Majumdar

3,883
14
65
94

i didnt hard code the name and value in the column contains like that and i cant change every name into like that and in 1000 values are there – teach me Jun 13 '16 at 19:31
There should be some replace function available to you in Java that will allow you to replace `'` with `''` in your `search` variable. Something _similar to_ `search = replace(search, "'", "''") + "%";` should give you a usable search variable. – Uueerdo Jun 13 '16 at 19:40
@teachme Actually, disregard the above, the time to replace `'` with `''` would have been needed before this point (otherwise `'` would not be in the search variable). – Uueerdo Jun 13 '16 at 20:14
@Uueerdo thankx man . i have changed into prepared statement and its working fine . – teach me Jun 13 '16 at 20:42
@Uueerdo That is bad advice, the OP should be using a PreparedStatement instead of mangling values themselves. – Mark Rotteveel Jun 15 '16 at 07:49
@MarkRotteveel that was my first advice in my initial comment to the question; but due to various circumstances using them may not always be feasible, so... – Uueerdo Jun 15 '16 at 16:06

Mark Rotteveel · Answer 2 · 2016-06-16T17:43:05.123

0

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

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)

edited Jun 16 '16 at 17:43

answered Jun 15 '16 at 07:54

Mark Rotteveel

100,966
191
140
197

yes i have done like this only later and thank you for your valuable answer . – teach me Jun 15 '16 at 13:35
Does the `||` in `sctName Like ? || '%'` do something special in Java? Because I don't think that works, equivalent to the original query, in MySQL. – Uueerdo Jun 15 '16 at 16:08
That is the SQL standard string concatenation, maybe MySQL uses `+` instead. – Mark Rotteveel Jun 15 '16 at 16:18
I pretty much always use MySQL's `CONCAT(item1, item2, ...)` function; it always messes me up in MSSQL since SSMS doesn't warn me until I've made 10 such statements and have to go change them back to `+` expressions. _(Edit: just confirmed both `+` and `||` used on two strings in MySQL yield `0`... because it converts them both to numbers. For example, `'a' + '12b'` would give `12`.)_ ...MSSQL doesn't seem to like `||` either. – Uueerdo Jun 15 '16 at 16:52
Thanks, I will edit to reflect that once I am at a real computer. – Mark Rotteveel Jun 16 '16 at 05:39

score -1 · Answer 3 · answered Jun 13 '16 at 20:17

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

answered Jun 13 '16 at 20:17

Uueerdo

15,723
1
16
21

That is bad advice, the OP should be using a PreparedStatement instead of mangling values themselves. – Mark Rotteveel Jun 15 '16 at 07:50

fetch records with apostrophe in mysql using LIKE query

3 Answers3