58

This is my code but it dosn't work:

$param = "%{$_POST['user']}%";
$stmt = $db->prepare("SELECT id,Username FROM users WHERE Username LIKE ?");
$stmt->bind_param("s", $param);
$stmt->execute();
$stmt->bind_result($id,$username);
$stmt->fetch();

This code it doesn't seem to work. I have searched it a lot. Also it may return more than 1 row. So how can I get all the results even if it returns more than 1 row?

Dharman
  • 26,923
  • 21
  • 73
  • 125
user2493164
  • 1,311
  • 3
  • 11
  • 15

3 Answers3

85

Here's how you properly fetch the result

$param = "%{$_POST['user']}%";
$stmt = $db->prepare("SELECT id,username FROM users WHERE username LIKE ?");
$stmt->bind_param("s", $param);
$stmt->execute();
$stmt->bind_result($id,$username);

while ($stmt->fetch()) {
  echo "Id: {$id}, Username: {$username}";
}

or you can also do:

$param = "%{$_POST['user']}%";
$stmt = $db->prepare("SELECT id, username FROM users WHERE username LIKE ?");
$stmt->bind_param("s", $param);
$stmt->execute();

$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
    echo "Id: {$row['id']}, Username: {$row['username']}";
}

I hope you realise I got the answer directly from the manual here and here, which is where you should've gone first.

Your Common Sense
  • 154,967
  • 38
  • 205
  • 325
roninblade
  • 1,834
  • 15
  • 15
46

Updated

From comments it is found that LIKE wildcard characters (_and %) are not escaped by default on Paramaterised queries and so can cause unexpected results.

Therefore when using "LIKE" statements, use this 'negative lookahead' Regex to ensure these characters are escaped :

$param = preg_replace('/(?<!\\\)([%_])/', '\\\$1',$param);

As an alternative to the given answer above you can also use the MySQL CONCAT function thus:

$stmt = $db->prepare("SELECT id,Username FROM users WHERE Username LIKE CONCAT('%',?,'%') ");
$stmt->bind_param("s", $param);
$stmt->execute();

Which means you do not need to edit your $param value but does make for slightly longer queries.

Community
  • 1
  • 1
Martin
  • 20,858
  • 7
  • 60
  • 113
  • Hi is concat safe from sql injection? I'm asking because it has quotes. Thanks – mike vorisis Feb 15 '17 at 23:29
  • @mikevorisis Yes, it's safe because the query is still parameterised (*using `?`*) . the quotes are because MySQL is CONCATenating three strings. – Martin Feb 16 '17 at 09:52
  • I upvoted this because I'm pretty sure you can't do 'begins with' or 'ends with' using the accepted answer (as in, you need to use `CONCAT`). – adam rowe Jun 15 '18 at 13:01
  • 3
    @MikeVorisis, `CONCAT` in prepared statements *are safe* from injection, but you should be cautious while using `LIKE` clause in prepared statements. Consider this: `$stmt=$db->prepare("SELECT `customer` as `suggestion` WHERE `customername` LIKE CONCAT('cust_', ?, '%');");` and then `$key='J%';` (Or even `$key='';`), `$stmt->bind_param('s', $key);`. Now `$stmt->execute();` will return *ALL records*. So, when dealing with Prepared statements having 'LIKE' clause, you still need to handle `_` and `%` in proper way. Even if using prepared statements, escaping like `$key='J\%';` is needed. – Jay Dadhania Jul 13 '18 at 14:28
  • 1
    @mikevorisis Though I think you are probably aware of this and have already taken steps to handle such queries properly, I thought it'd be important to share as it may help others reading this too. – Jay Dadhania Jul 13 '18 at 14:30
  • `$param = preg_replace('/(? – starshine531 Sep 30 '18 at 07:01
  • @starshine531 thanks - such an old question/answer I'd missed that and simply copy/pasted from my raw Regex tool. `:-D` – Martin Sep 30 '18 at 11:10
  • php.net/addcslashes is a much simpler alternative to that bushy regexp – Your Common Sense Nov 03 '20 at 05:26
  • exactly without using Concat function I was facing a weird issue in query – Azhar Uddin Sheikh Oct 19 '21 at 05:22
  • it is better to avoid the `CONCAT` function let's suppose we do have a `query` in which we have used the `CONCAT` function which found out to grab `1000` data from the `database` it means that this function `CONCAT` recalled `1000` times – Azhar Uddin Sheikh May 06 '22 at 15:00
-1

The answer is obviously outdated, you don’t even need to use bind_param.

$querySql    = "SELECT * FROM table WHERE columnName LIKE :likeKeyWord";
$likeKeyWord = "%". $keyWord ."%";
$pdoStm      = $this -> db -> prepare($querySql);
$pdoStm -> execute([':likeKeyWord' => $likeKeyWord]);
return $pdoStm -> fetchAll();
Pork Jackson
  • 111
  • 10