59

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
  • 30,962
  • 25
  • 85
  • 135
user2493164
  • 1,321
  • 3
  • 11
  • 15
  • 2
    Please define "does not work" - PHP error? MySQL error? No error but 0 results? – Mołot Aug 30 '13 at 07:50
  • I want to loop through all results. How can i do this? When i say doesn't work. It returns nothing... – user2493164 Aug 30 '13 at 07:56
  • 1
    there is no return operator in this code. **How it is supposed to return anything?** – Your Common Sense Aug 30 '13 at 08:00
  • Implementation of a query with zero or more LIKE comparisons (dynamically determined): [Build SELECT query with dynamic number of LIKE conditions as a mysqli prepared statement](https://stackoverflow.com/q/51010509/2943403) – mickmackusa Aug 25 '23 at 06:45

2 Answers2

89

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();

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

or, if you prefer the old fetch and bind_result syntax, 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();

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

I got the answer directly from the manual here and here.

Dharman
  • 30,962
  • 25
  • 85
  • 135
roninblade
  • 1,882
  • 15
  • 15
52

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();

PDO named placeholder version:

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

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

Martin
  • 22,212
  • 11
  • 70
  • 132
  • 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
  • 4
    @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('/(?<!\\\)([%_])/g', '\\\$1',$param);` cannot have the `/g` in there. `preg_replace` does not need the global modifier. It's there implicitly (unless you set the 'limit' argument). – 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
  • `$param = addcslashes($param, '%_');` – Your Common Sense Feb 03 '23 at 11:08