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
– chris85, Commented Apr 7, 2015 at 1:18
@chris85 ^ Yes, that would also work with mysqli_. Have a look stackoverflow.com/a/24207056 — Funk Forty Niner
– Funk Forty Niner, Commented Apr 7, 2015 at 1:34
it throws an error: Warning, execute() expects exactly 0 parameters and 1 is given — user3634933
– user3634933, Commented Apr 7, 2015 at 1:50
Here, try this LIKE CONCAT ('%', ?, '%') that should work. — Funk Forty Niner
– Funk Forty Niner, Commented Apr 7, 2015 at 2:27
that works!!, @Fred-ii- make it an answer so i can upvote you — user3634933
– user3634933, Commented Apr 7, 2015 at 2:34

Funk Forty Niner · Accepted Answer · 2015-04-07 02:36:02Z

11

You can use CONCAT(), like this:

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

answered Apr 7, 2015 at 2:36

Funk Forty Niner

74.2k14 gold badges71 silver badges146 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

danielson317 Over a year ago

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.

Community · Accepted Answer · 2017-05-23 12:07:46Z

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, 2017 at 12:07

CommunityBot

11 silver badge

answered Apr 7, 2015 at 1:34

Norbert

6,1043 gold badges19 silver badges40 bronze badges

1 Comment

user3634933 Over a year ago

doesn't work that just crates a string '%$key%' and then it searches the database for column_name with " '%$key%' " in it.

Collectives™ on Stack Overflow

SQL parameterized query with LIKE '% ? %' PHP

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related