How can I with mysqli make a query with LIKE and get all results?

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?

edited Dec 7 '13 at 1:04

MPelletier

11.5k1171119

asked Aug 30 '13 at 7:49

user2493164

5061614

2

Please define "does not work" - PHP error? MySQL error? No error but 0 results?

– Mołot
Aug 30 '13 at 7: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 7:56

there is no return operator in this code. How it is supposed to return anything?

– Your Common Sense
Aug 30 '13 at 8:00

add a comment |

How can I with mysqli make a query with LIKE and get all results?

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?

edited Dec 7 '13 at 1:04

MPelletier

11.5k1171119

asked Aug 30 '13 at 7:49

user2493164

5061614

2

Please define "does not work" - PHP error? MySQL error? No error but 0 results?

– Mołot
Aug 30 '13 at 7: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 7:56

there is no return operator in this code. How it is supposed to return anything?

– Your Common Sense
Aug 30 '13 at 8:00

add a comment |

How can I with mysqli make a query with LIKE and get all results?

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?

edited Dec 7 '13 at 1:04

MPelletier

11.5k1171119

asked Aug 30 '13 at 7:49

user2493164

5061614

How can I with mysqli make a query with LIKE and get all results?

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?

php sql mysqli sql-like

edited Dec 7 '13 at 1:04

MPelletier

11.5k1171119

asked Aug 30 '13 at 7:49

user2493164

5061614

edited Dec 7 '13 at 1:04

MPelletier

11.5k1171119

asked Aug 30 '13 at 7:49

user2493164

5061614

edited Dec 7 '13 at 1:04

MPelletier

11.5k1171119

edited Dec 7 '13 at 1:04

MPelletier

11.5k1171119

edited Dec 7 '13 at 1:04

MPelletier

11.5k1171119

asked Aug 30 '13 at 7:49

user2493164

5061614

asked Aug 30 '13 at 7:49

user2493164

5061614

asked Aug 30 '13 at 7:49

user2493164

5061614

2

Please define "does not work" - PHP error? MySQL error? No error but 0 results?

– Mołot
Aug 30 '13 at 7: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 7:56

there is no return operator in this code. How it is supposed to return anything?

– Your Common Sense
Aug 30 '13 at 8:00

add a comment |

2

Please define "does not work" - PHP error? MySQL error? No error but 0 results?

– Mołot
Aug 30 '13 at 7: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 7:56

there is no return operator in this code. How it is supposed to return anything?

– Your Common Sense
Aug 30 '13 at 8:00

Please define "does not work" - PHP error? MySQL error? No error but 0 results?

– Mołot
Aug 30 '13 at 7: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 7:56

there is no return operator in this code. How it is supposed to return anything?

– Your Common Sense
Aug 30 '13 at 8:00

add a comment |

2 Answers
2

active

oldest

votes

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_array(MYSQLI_NUM)) {

  foreach ($row as $r) {

    print "$r ";

  }

  print "n";

}

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

edited Apr 21 '17 at 2:34

Jeff Puckett

14.9k656107

answered Aug 30 '13 at 8:00

roninblade

1,4701114

2

In defense of the OP, neither manual page makes any mention of the LIKE comparison "function"

– Brad Kent
Aug 22 '17 at 0:12

add a comment |

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.

edited Sep 30 '18 at 11:09

answered Apr 13 '16 at 8:42

Martin

13k53680

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 9:52

ok Thanks @Martin

– mike vorisis
Feb 16 '17 at 9:53

2

@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

|
show 6 more comments

protected by bummi Dec 10 '15 at 12:42

Thank you for your interest in this question.
Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

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_array(MYSQLI_NUM)) {

  foreach ($row as $r) {

    print "$r ";

  }

  print "n";

}

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

edited Apr 21 '17 at 2:34

Jeff Puckett

14.9k656107

answered Aug 30 '13 at 8:00

roninblade

1,4701114

2

In defense of the OP, neither manual page makes any mention of the LIKE comparison "function"

– Brad Kent
Aug 22 '17 at 0:12

add a comment |

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_array(MYSQLI_NUM)) {

  foreach ($row as $r) {

    print "$r ";

  }

  print "n";

}

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

edited Apr 21 '17 at 2:34

Jeff Puckett

14.9k656107

answered Aug 30 '13 at 8:00

roninblade

1,4701114

2

In defense of the OP, neither manual page makes any mention of the LIKE comparison "function"

– Brad Kent
Aug 22 '17 at 0:12

add a comment |

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_array(MYSQLI_NUM)) {

  foreach ($row as $r) {

    print "$r ";

  }

  print "n";

}

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

edited Apr 21 '17 at 2:34

Jeff Puckett

14.9k656107

answered Aug 30 '13 at 8:00

roninblade

1,4701114

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_array(MYSQLI_NUM)) {

  foreach ($row as $r) {

    print "$r ";

  }

  print "n";

}

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

edited Apr 21 '17 at 2:34

Jeff Puckett

14.9k656107

answered Aug 30 '13 at 8:00

roninblade

1,4701114

edited Apr 21 '17 at 2:34

Jeff Puckett

14.9k656107

edited Apr 21 '17 at 2:34

Jeff Puckett

14.9k656107

edited Apr 21 '17 at 2:34

Jeff Puckett

14.9k656107

answered Aug 30 '13 at 8:00

roninblade

1,4701114

answered Aug 30 '13 at 8:00

roninblade

1,4701114

answered Aug 30 '13 at 8:00

roninblade

1,4701114

2

In defense of the OP, neither manual page makes any mention of the LIKE comparison "function"

– Brad Kent
Aug 22 '17 at 0:12

add a comment |

2

In defense of the OP, neither manual page makes any mention of the LIKE comparison "function"

– Brad Kent
Aug 22 '17 at 0:12

In defense of the OP, neither manual page makes any mention of the LIKE comparison "function"

– Brad Kent
Aug 22 '17 at 0:12

add a comment |

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.

edited Sep 30 '18 at 11:09

answered Apr 13 '16 at 8:42

Martin

13k53680

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 9:52

ok Thanks @Martin

– mike vorisis
Feb 16 '17 at 9:53

2

@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

|
show 6 more comments

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.

edited Sep 30 '18 at 11:09

answered Apr 13 '16 at 8:42

Martin

13k53680

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 9:52

ok Thanks @Martin

– mike vorisis
Feb 16 '17 at 9:53

2

@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

|
show 6 more comments

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.

edited Sep 30 '18 at 11:09

answered Apr 13 '16 at 8:42

Martin

13k53680

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.

edited Sep 30 '18 at 11:09

answered Apr 13 '16 at 8:42

Martin

13k53680

edited Sep 30 '18 at 11:09

answered Apr 13 '16 at 8:42

Martin

13k53680

answered Apr 13 '16 at 8:42

Martin

13k53680

answered Apr 13 '16 at 8:42

Martin

13k53680

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 9:52

ok Thanks @Martin

– mike vorisis
Feb 16 '17 at 9:53

2

@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

|
show 6 more comments

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 9:52

ok Thanks @Martin

– mike vorisis
Feb 16 '17 at 9:53

2

@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

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 9:52

ok Thanks @Martin

– mike vorisis
Feb 16 '17 at 9:53

@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

@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

|
show 6 more comments

protected by bummi Dec 10 '15 at 12:42

This page is only for reference, If you need detailed information, please check here

Search This Blog

Ufyukyu

php mysqli prepared statement LIKE

2 Answers
2

Updated

protected by bummi Dec 10 '15 at 12:42

2 Answers
2

2 Answers
2

Updated

Updated

Updated

Updated

protected by bummi Dec 10 '15 at 12:42

Popular posts from this blog

'app-layout' is not a known element: how to share Component with different Modules

android studio warns about leanback feature tag usage required on manifest while using Unity exported app?

WPF add header to Image with URL pettitions [duplicate]

Category

Random preview

php mysqli prepared statement LIKE

2 Answers 2

Updated

protected by bummi Dec 10 '15 at 12:42

2 Answers 2

2 Answers 2

Updated

Updated

Updated

Updated

protected by bummi Dec 10 '15 at 12:42

Popular posts from this blog

'app-layout' is not a known element: how to share Component with different Modules

android studio warns about leanback feature tag usage required on manifest while using Unity exported app?

WPF add header to Image with URL pettitions [duplicate]

2 Answers
2

2 Answers
2

2 Answers
2