Mysql Php Always get same return

Question

I have a PHP script that is for direct calling that checks to see if a key is in the blacklist database. However, no matter what I try, including mysql_num_rows, it always returns that the value does not exist (returns 'OK')...

What am I doing wrong?

<?php

$pin = $_GET['keycode'];

$cn=mysqli_connect('localhost', 'root', 'password', 'serials') or die($cn->connect_error);
$sql = "SELECT keycode FROM blacklist";
$rs = $cn->query($sql) or die(mysqli_error($cn));
while($row = mysqli_fetch_array($rs)){
if ($row[keycode] == $pin) {
    echo "BL";
    return;
}
}
echo "OK";
$cn->close(); 
?>

There's no need to loop all results in PHP, you can just use WHERE in SQL like: $sql = "SELECT COUNT(*) FROM blacklist WHERE keycode=$pin"; — Dan Ionescu, Mar 19 '17 at 16:27
`mysql_*` won't work with `mysqli`. Did you use `mysql_num_rows` or the `mysqli` variation? — chris85, Mar 19 '17 at 16:31
*"no matter what I try, including mysql_num_rows, it always returns that the value does not exist"* - that's because you can't mix different mysql apis — Funk Forty Niner, Mar 19 '17 at 16:32

score 3 · Accepted Answer · edited May 19 '20 at 22:10

3

Change your code to this :

<?php  
  $cn = mysqli_connect('localhost', 'root', 'password', 'serials');

  $pin =  $_GET['keycode'];

  $sql = $cn->prepare("SELECT * FROM blacklist WHERE keycode = ?;");

  $sql->bind_param('s', $pin);// set $pin to type string and bind it to the query

  $sql->execute();// execute query

  $result = $sql->get_result();// get the result

  if(mysqli_num_rows($result)>0){
     echo "Keycode Blacklisted";// inform that keycode found in blacklist
  }

  $cn->close(); 
?>

The problem with your code is :

You have not added single quotes to your $row[keycode] so change it to $row['keycode']
Your query was not selecting all columns but still you were treating the result as if it had several rows when you were using $row['acolumn']. Plus you were not using your $pin to check for equality first. By using SELECT * FROM blacklist WHERE keycode = ?; you will check for equality without needing the if condition. So that's the explanation for my code change.

edited May 19 '20 at 22:10

Dharman

30,962
25
85
135

answered Mar 19 '17 at 16:27

programmingandroid

340
1
5
14

@downvoter - care to explain why? – programmingandroid Mar 19 '17 at 16:29
1

I didn't vote but answers should have an explanation. – chris85 Mar 19 '17 at 16:30
3

I didn't downvote and the answer looks correct'ish. However, I would never upvote because the value should be passed in as a parameter, not by munging the string. – Gordon Linoff Mar 19 '17 at 16:30
1

Answer looks correct, Who gives the minus? – Scaffold Mar 19 '17 at 16:33
@chris85 - Check the explanation, should be easy to understand now. – programmingandroid Mar 19 '17 at 16:37
3

It is better. I'd parameterize, and remove the `while`, http://php.net/manual/en/mysqli-result.num-rows.php. – chris85 Mar 19 '17 at 16:43
This has a whacking great SQL injection in the code, and readers should not use it! I acknowledge the effort and it is kind of you to help, but I think downvoting is appropriate. I will gladly reverse my DV if you can make it safe. – halfer Mar 19 '17 at 18:30
Also, you're `return`ing without closing in the case of a blacklist match. – halfer Mar 19 '17 at 18:32
@halfer - prevented SQL injection, check the new answer – programmingandroid Mar 19 '17 at 19:27
@chris85 - removed the while loop - check it now – programmingandroid Mar 19 '17 at 19:27
@GordonLinoff - made it parameterised. – programmingandroid Mar 19 '17 at 19:28
1

Parameterizing removes the need for `mysqli_real_escape_string`. Looks good now. – chris85 Mar 19 '17 at 19:30
Great, thanks for the positive response. – halfer Mar 19 '17 at 19:38
Thanks everyone, I learned quite a bit, espicially that you cant do a * with mysqli. – Chris Mar 20 '17 at 10:06

Mysql Php Always get same return

1 Answers1