Using "like" wildcard in prepared statement

Question

I am using prepared statements to execute mysql database queries. And I want to implement a search functionality based on a keyword of sorts.

For that I need to use LIKE keyword, that much I know. And I have also used prepared statements before, but I do not know how to use it with LIKE because from the following code where would I add the 'keyword%'?

Can I directly use it in the pstmt.setString(1, notes) as (1, notes+"%") or something like that. I see a lot of posts on this on the web but no good answer anywhere.

PreparedStatement pstmt = con.prepareStatement(
      "SELECT * FROM analysis WHERE notes like ?");
pstmt.setString(1, notes);
ResultSet rs = pstmt.executeQuery();

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

324

You need to set it in the value itself, not in the prepared statement SQL string.

So, this should do for a prefix-match:

notes = notes
    .replace("!", "!!")
    .replace("%", "!%")
    .replace("_", "!_")
    .replace("[", "![");
PreparedStatement pstmt = con.prepareStatement(
        "SELECT * FROM analysis WHERE notes LIKE ? ESCAPE '!'");
pstmt.setString(1, notes + "%");

or a suffix-match:

pstmt.setString(1, "%" + notes);

or a global match:

pstmt.setString(1, "%" + notes + "%");

edited Dec 23 '15 at 22:23

Alain O'Dea

21,033
1
58
84

answered Nov 23 '11 at 19:35

BalusC

1,082,665
372
3,610
3,555

22

+1 The OP could "set" it in the SQL — as by `... LIKE '%' || ? || '%'` or similar — but that's much less flexible. – pilcrow Nov 23 '11 at 19:42
how do i do it with NON-CASE SENSITIVE mode? :) – Alpha Gabriel V. Timbol Aug 26 '15 at 13:52
2

Non-case-sensitive can still use `WHERE UPPER(?) LIKE UPPER(?)` when using `pstmt.setString(2, "%" + notes + "%")` – Zig Nov 20 '15 at 17:30
What if the String **notes** contains a `%` character? – Alain O'Dea Dec 23 '15 at 21:40
This is vulnerable to trivial SQL injection attacks. They may only broaden the scope of returned values but could easily DoS the database by stuffing piles of `%this%that%theother%` into the query making it very heavy to service. Adding `ESCAPE '!'` to the query and running `notes = notes.replace("!", "!!").replace("_", "!_").replace("[", "![")` prior to supplying **notes** as a parameter improves things. Using a full text query mechanism which handles these intricacies directly for you is best. – Alain O'Dea Dec 23 '15 at 21:59
Edited answer to include SQL injection avoidance. – Alain O'Dea Dec 23 '15 at 22:06
1

@Alain: Thank you. Just wondering, does this apply to all RDBMS the world is aware of? Perhaps `'%' || ? || '%'` as mentioned in 1st comment was better, after all? I don't have the opportunity to experiment right now. – BalusC Dec 23 '15 at 22:47
2

@BalusC this applies to MSSQL, Postgres, and MySQL in my testing. The String being made into a parameter is itself interpreted as a mix of data and control instructions. SQL concatenation occurs before it is interpreted and preserves the vulnerability. The IEEE Center for Secure Design says to [Strictly Separate Data and Control Instructions, and Never Process Control Instructions Received from Untrusted Sources](http://cybersecurity.ieee.org/2015/11/13/strictly-separatedata-and-control-instructions-and-never-process-control-instructions-received-from-untrusted-sources/). – Alain O'Dea Dec 24 '15 at 02:18
I've used the `'%' || ? || '%'` in PostgreSQL consistently without problems. Against Sybase ASE 15.7 I was getting very weird, non reproducible erratic behavior (from JDBC), sometimes working, sometimes not. After I switched to setting `?` to be `"%"+str"%"` I had no more issues. – Marcus Junius Brutus Sep 05 '17 at 19:30
@MarcusJuniusBrutus The discussion here is not about whether or not this works - this will work with most SQL backends - but whether or not it is secure (it will _not_ be secure with most SQL backends, unless you have one that recognizes this construct and does the escaping for you, which would be highly sketchy). – Cubic May 02 '18 at 13:06
1

Why do you escape the "[" char, please? – dpelisek Feb 26 '20 at 07:55
@dpelisek: because it's a special character for `LIKE`. If your DB doesn't consider it a special character, then feel free to remove it (or replace the DB). – BalusC Feb 26 '20 at 10:41
@BalusC Could you please help me? Why are you escaping characters when you're using prepared statement? As far as I know, prepared statements decrease the probability of SQL-injection to none – JustLearn Jul 21 '22 at 11:23
1

@JustLearn: I'm not escaping the prepared statement. I'm escaping the LIKE value. It's to avoid a mismatch caused by characters considered special to the LIKE clause such as `%`, `_` and `[`. Otherwise when you search for e.g. `foo_bar` it would incorrectly match e.g. `fooxbar`. – BalusC Jul 21 '22 at 12:09
Yeah, this doesn't seem secure at all. The user might end their string with a % sign and get access to all results in the database that *contain* stuff, not just those that are starting with that particular string. – Ambrus Tóth Mar 08 '23 at 19:19
Uh, this is exactly why the characters are escaped. – BalusC Mar 22 '23 at 12:33

score 46 · Answer 2 · edited Oct 07 '20 at 00:37

46

We can use the CONCAT SQL function.

PreparedStatement pstmt = con.prepareStatement(
      "SELECT * FROM analysis WHERE notes like CONCAT( '%',?,'%')";
pstmt.setString(1, notes);
ResultSet rs = pstmt.executeQuery();

This works perfectly for my case.

edited Oct 07 '20 at 00:37

patstuart

1,931
1
19
29

answered Nov 21 '19 at 12:58

Basharat Ali

1,099
9
11

2

`.. notes like '%' || ? || '%'` also works – shooma Jan 13 '22 at 16:21
Sadly, not working in H2 2.2.220. A `%` in the argument given to a prepared statement as shown is interpreted as another wildcard, not as a character that shall be escaped. For the syntax of the argument to `LIKE`, see [Like Predicate Right Hand Side](http://h2database.com/html/grammar.html?highlight=escape&search=ESCAPE#like_predicate_right_hand_side). – David Tonhofer Aug 28 '23 at 12:10

score 37 · Answer 3 · edited Sep 05 '17 at 12:36

37

Code it like this:

PreparedStatement pstmt = con.prepareStatement(
    "SELECT * FROM analysis WHERE notes like ?");
pstmt.setString(1, notes + "%");`

Make sure that you DO NOT include the quotes ' ' like below as they will cause an exception.

pstmt.setString(1,"'%"+ notes + "%'");

edited Sep 05 '17 at 12:36

Vlad Schnakovszki

8,434
6
80
114

answered Mar 14 '15 at 13:24

The Wedding Wolf

387
3
2

2

Though it sounds like someone won't run into this assumption, it's actually very valid especially when working with Oracle. Thanks for pointing out! – asgs Jun 14 '15 at 20:27

score 7 · Answer 4 · answered Mar 10 '16 at 18:01

7

PreparedStatement ps = cn.prepareStatement("Select * from Users where User_FirstName LIKE ?");
ps.setString(1, name + '%');

Try this out.

answered Mar 10 '16 at 18:01

Faiz

377
3
7

score 0 · Answer 5 · edited Jun 13 '18 at 16:15

0

String fname = "Sam\u0025";

PreparedStatement ps= conn.prepareStatement("SELECT * FROM Users WHERE User_FirstName LIKE ? ");

ps.setString(1, fname);

edited Jun 13 '18 at 16:15

Young Emil

2,220
2
26
37

answered Nov 15 '17 at 13:27

Ram Kumar

9
1

3

Could you elaborate the answer rather than just giving the answer? See: https://stackoverflow.com/help/how-to-answer – Edwin Nov 15 '17 at 13:32

score -21 · Answer 6 · edited Oct 05 '18 at 03:08

-21

String query="select * from test1 where "+selected+" like '%"+SelectedStr+"%';";


PreparedStatement preparedStatement=con.prepareStatement(query);


// where seleced and SelectedStr are String Variables in my program

edited Oct 05 '18 at 03:08

Jayendran

9,638
8
60
103

answered Oct 05 '18 at 00:33

mahesh dhote

1

1

it is unsafe, kindly use parametrized preparedstatement. – Durgesh Kumar Sep 23 '19 at 10:51
https://jsparrow.github.io/rules/use-parameterized-query.html#description – Rafs Aug 19 '22 at 11:32

Using "like" wildcard in prepared statement

6 Answers6

Linked

Related