0

I am trying to get a search that searches through SQL and brings back results and display no match found if there were no matches. I have tried using an if statement but it does not seem to work. When I type something I know that is not in the database, it does not print out not found and does nothing. I was thinking maybe a for statement but I don't know how using PDO.

Also, can I get help for modifying the code to prevent SQL injection. Thanks.

if(isset($_POST['find'])) {
$sq = $_POST['find'];
$q = $db->prepare("SELECT * FROM table WHERE name LIKE '%$sq%'");
$q->execute();

  if (!$q->rowCount() == 1 ) {
    while ($r = $q->fetch(PDO::FETCH_ASSOC)) {
    print $r['datadb'] . "<br />\n";
    }
  } else {
    print 'Not found';
  }
}
thenoob
  • 67
  • 9
  • Your code is vulnerable to SQL injection attacks. You should use [mysqli](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) or [PDO](http://php.net/manual/en/pdo.prepared-statements.php) prepared statements as described in [this post](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). – Alex Howansky Apr 05 '17 at 15:20
  • Thank you. I also need help fixing my search query because the if statement does not seem to work. – thenoob Apr 05 '17 at 15:24
  • @Alex Howansky how does that work on a `like` parameter. Because `like '%:name%'` will not work. So the OP must (even he uses statements), cleanup the lookup by himself. OR? – JustOnUnderMillions Apr 05 '17 at 15:24
  • @JustOnUnderMillions Do it on the bind, not in the query: `$st->bind_param(':name', '%' . $str . '%');` – Alex Howansky Apr 05 '17 at 15:26
  • @Alex Howansky OK, you are right, didnt thing of that (adding % before binding 8-]). And how would you make a like query for finding a single or double backshlash then `%\%` `%\\%`? Dont take that seriously ;-) But it is/canbe tricky :-) – JustOnUnderMillions Apr 05 '17 at 15:30
  • @AlexHowansky Can you give me a full example on how I would change this so it prevents SQL injection attack? Thanks – thenoob Apr 05 '17 at 15:36
  • I don't like the "wrap what the user types in `%`" notion here. It just means that searching for `%` will basically return everything which while not technically being an injection is just a bad user experience (e.g. a user looks for something with a percent in it and finds gibberish). – apokryfos Apr 05 '17 at 15:37
  • @apokryfos WIll using the bindparam as Alex has mentioned to use, prevent this? – thenoob Apr 05 '17 at 15:40
  • @thenoob no, because as I said, it's not an injection and it sometimes is desirable. You'll probably need to manually escape permitted `LIKE` wildcards. – apokryfos Apr 05 '17 at 15:47
  • @apokryfos Thank you I will look that up. Do you know how I can get the search to work in terms of displaying no results found? The if statement does not seem to work. – thenoob Apr 05 '17 at 15:50
  • @thenoob Why does it say `if (!$q->rowCount() == 1 )`? Shouldn't it be `if ($q->rowCount() != 0 )` ? – apokryfos Apr 05 '17 at 16:07
  • @apokryfos I have tried the way you have said and it still does the same thing. If I search for something that I know isn't in the database, it just refreshes the page. – thenoob Apr 05 '17 at 16:48

0 Answers0