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 315 · Accepted Answer · edited Dec 23 '15 at 22:23

315

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

19,721
1
50
76

answered Nov 23 '11 at 19:35

BalusC

1,040,783
362
3,548
3,513

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

score 35 · Answer 2 · edited Sep 05 '17 at 12:36

35

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

7,981
5
80
108

answered Mar 14 '15 at 13:24

The Wedding Wolf

367
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 28 · Answer 3 · edited Oct 07 '20 at 00:37

28

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,900
1
18
29

answered Nov 21 '19 at 12:58

Basharat Ali

719
7
11

Man, You just saved my day :D – Kavinda Jayakody Feb 03 '21 at 11:34
Saved my day as well. thank you. – kiwicomb123 Nov 01 '21 at 19:36
`.. notes like '%' || ? || '%'` also works – shooma Jan 13 '22 at 16:21

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

6

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

367
3
7

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

1

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,074
2
21
35

answered Nov 15 '17 at 13:27

Ram Kumar

11
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 -19 · Answer 6 · edited Oct 05 '18 at 03:08

-19

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

8,189
5
47
93

answered Oct 05 '18 at 00:33

mahesh dhote

1

it is unsafe, kindly use parametrized preparedstatement. – Durgesh Kumar Sep 23 '19 at 10:51

Using "like" wildcard in prepared statement

6 Answers6

Linked

Related