Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Wildcards in Java PreparedStatements

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!

like image 847
Eric Noob Avatar asked Nov 29 '08 17:11

Eric Noob


People also ask

What is parameterized query in Java?

A parameterized query is a query in which placeholders are used for parameters and the parameter values are supplied at execution time. The most important reason to use parameterized queries is to avoid SQL injection attacks. Let's take a look at what can happen if we don't use parameterized queries.

How do you pass parameters in PreparedStatement?

To execute a statement with Where clause using PreparedStatement. Prepare the query by replacing the value in the clause with place holder “?” and, pass this query as a parameter to the prepareStatement() method.


1 Answers

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("[", "![");
} 
like image 176
Paul Tomblin Avatar answered Sep 28 '22 08:09

Paul Tomblin