SQL parameterized query with LIKE '% ? %' PHP

Question

I have a Search function in php and have created it using a parameterized query to make it secure.

$words = $_POST['words']//words is the form that has the words submitted by the user 
$array = explode(',', $words);
$con = mysqli_connect("localhost","user","pass","database");

$stmt = $con->prepare(" SELECT column_name FROM table WHERE column_name LIKE ?")
foreach($array as $key) { //searches each word and displays results   
  $stmt->bind_param('s', $key)
  $stmt->execute();
  $result = $stmt->get-result();

  while($row = $result->fetch_assoc(){
    echo $row["column_name"]
  }
}

however I want $stmt statement to be

  $stmt = $con->prepare(" SELECT column_name FROM table WHERE column_name LIKE '%?%' ")

otherwise people have to type in the entire value of column_name to find it.

I do it in the execute, `$stmt->execute(array('%' . $key . '%');`... that's in PDO not sure if mysqli supports that. — chris85, Apr 07 '15 at 01:18
@chris85 ^ Yes, that would also work with `mysqli_`. Have a look http://stackoverflow.com/a/24207056/ — Funk Forty Niner, Apr 07 '15 at 01:34
it throws an error: Warning, execute() expects exactly 0 parameters and 1 is given — user3634933, Apr 07 '15 at 01:50
Here, try this `LIKE CONCAT ('%', ?, '%')` that should work. — Funk Forty Niner, Apr 07 '15 at 02:27
that works!!, @Fred-ii- make it an answer so i can upvote you — user3634933, Apr 07 '15 at 02:34

score 11 · Accepted Answer · answered Apr 07 '15 at 02:36

11

You can use CONCAT(), like this:

LIKE CONCAT ('%', ?, '%')

answered Apr 07 '15 at 02:36

Funk Forty Niner

74,450
15
68
141

This isn't very flexible. What if you want the wildcard in the middle of the string for example. Their must be a better way. – danielson317 Jul 04 '18 at 20:08

score 1 · Answer 2 · edited May 23 '17 at 12:07

1

You can do this as follows:

$key="%$key%"

Then bind $key.

Also see PHP Binding a Wildcard for pretty much the same question....

edited May 23 '17 at 12:07

Community

1
1

answered Apr 07 '15 at 01:34

Norbert

6,026
3
17
40

doesn't work that just crates a string '%$key%' and then it searches the database for column_name with " '%$key%' " in it. – user3634933 Apr 07 '15 at 02:02

SQL parameterized query with LIKE '% ? %' PHP

2 Answers2

Linked