(Avoiding) SQL Injection In LIKE Clause

A lot of web developers have gotten the message about the SQL injection and are using parametrized statements. Still, there are a few cases where using parametrized statements is not quite straightforward, such as in LIKE clause.

Suppose you want to do something like this:

SELECT * FROM people WHERE name LIKE 'joe%'

and the string "joe" is supplied by the user. How do you do that? This won't work:

SELECT * FROM people WHERE name LIKE '?%'

Neither will this:

SELECT * FROM people WHERE name LIKE ?%

What will work, is building the string containing the wildcard first and submitting it as a parameter:

String search = userinput + "%";
PreparedStatement st = con.prepareStatement("SELECT * FROM people WHERE name LIKE ?");
st.setString(1,search);

This will do the job and prevent SQL injection in "userinput" parameter.