Wildcards in Java PreparedStatements

Question

Here's my current SQL statement:

SEARCH_ALBUMS_SQL = "SELECT * FROM albums WHERE title LIKE ? OR artist LIKE ?;";

It's returning exact matches to the album or artist names, but not anything else. I can't use a '%' in the statement or I get errors.

How do I add wildcards to a prepared statement?

(I'm using Java5 and MySQL)

Thanks!

Here's a [better version of the same question](http://stackoverflow.com/q/8247970/154527). — Alain O'Dea, Dec 23 '15 at 22:28

score 19 · Accepted Answer · edited Dec 23 '15 at 22:22

19

You put the % in the bound variable. So you do

   stmt.setString(1, "%" + likeSanitize(title) + "%");
   stmt.setString(2, "%" + likeSanitize(artist) + "%");

You should add ESCAPE '!' to allow you to escape special characters that matter to LIKE in you inputs.

Before using title or artist you should sanitize them (as shown above) by escaping special characters (!, %, _, and [) with a method like this:

public static String likeSanitize(String input) {
    return input
       .replace("!", "!!")
       .replace("%", "!%")
       .replace("_", "!_")
       .replace("[", "![");
}

edited Dec 23 '15 at 22:22

Alain O'Dea

21,033
1
58
84

answered Nov 29 '08 at 17:55

Paul Tomblin

179,021
58
319
408

1

So, what if the bound variable actually had a '%' character in it? – Eric Noob Nov 29 '08 at 18:41
@Eric - Then you don't need to add it. There's nothing magical about doing it in the setString rather than anywhere else. – Paul Tomblin Nov 29 '08 at 19:10
4

If your bound variable has a literal '%' you should escape it as '\%'. See Java method java.lang.String.replace(). – Bill Karwin Nov 30 '08 at 17:42
@BillKarwin backslash isn't documented as an escape character for this purpose. If you include **ESCAPE '!'** and replace **%** with **!%**, **_** with `!_`, and **[** with **![** it should mostly mitigate the SQL injection risk here. – Alain O'Dea Dec 23 '15 at 22:14
@BillKarwin my mistake. OP said MySQL which defaults ESCAPE to backslash so you are completely right. Still good to be explicit for portability: Postgres and MySQL agree here, but Transact SQL docs are silent on its default. – Alain O'Dea Dec 23 '15 at 22:26
@AlainO'Dea I disagree with your edit. You don't need to sanitize the input in a bound variable. – Paul Tomblin Dec 24 '15 at 18:26
@PaulTomblin the bound variable is itself interpreted as a mix of data and control instructions. Try this with a user input that contains a **%** and see if it works as expected. I would want my program to match content with **%** in it rather than allowing the user supplied **%** to be treated as a wild card. Does that make sense? – Alain O'Dea Dec 24 '15 at 18:35
1

What about the other replacements? Are `_` treated as something other than a literal `_`? – Paul Tomblin Dec 24 '15 at 18:57
@PaulTomblin once **ESCAPE '!'** is specified then **!** becomes a special character. I added **[** in case it is run on a Transact SQL engine (MSSQL) which uses that to start a character class. In retrospect that's out of scope since the OP is targeting MySQL explicitly. – Alain O'Dea Dec 24 '15 at 21:55
@PaulTomblin **_** is a single character wildcard. – Alain O'Dea Dec 24 '15 at 21:56

Wildcards in Java PreparedStatements

1 Answers1

Linked