cannot set bind parameters in php mysql

Question

This my php-mysql select query

$servername = "localhost";
$username = "root";
$password = "mukund";
$dbname = "dbdata";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
else
{

        $uname="admin";
        $pass = "admin";
        $pass = md5($pass);

$sql = "SELECT ID,NAME,ROLE FROM USERDETAIL WHERE USERNAME = ? AND PASSWORD = ? AND STATUS = 'VERIFIED'";  

     if(!$stmt = $conn->prepare($sql))
     {
         echo 'stmt failed'.$mysqli->errno."<br>" ;
     }

if(!$stmt->bind_param('s', $uname))
{
        echo "failed bind  : ".$stmt->errno.": > ".$stmt->error."<br>";
}
if(!$stmt->bind_param("s", $pass))
{
        echo "failed bind2 : ".$stmt->errno.": > ".$stmt->error."<br>";
}




if (!$stmt->execute()) {
    echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
}

$uid="";
 $result = $stmt->get_result();
 echo $count = mysqli_num_rows($result);

    if($count>0)
    {

          echo 'result obtained';  
        }
        }

here is the output obtained

failed bind : 0: >

failed bind2 : 0: >

Execute failed: (2031) No data supplied for parameters in prepared statement

But the same code i rewrote in a different format worked!!! Below is the working code

$servername = "localhost";
$username = "root";
$password = "mukund";
$dbname = "dbdata";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
$uname ="admin";
$pass = "admin";
$pass = md5($pass);
$sql = "SELECT ID, NAME FROM USERDETAIL WHERE USERNAME = '$uname' AND PASSWORD = '$pass' AND STATUS = 'VERIFIED'";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo "id: " . $row["ID"]. " - Name: " . $row["NAME"]. " ". "<br>";
    }
} else {
    echo "0 results";
}
$conn->close();

OUTPUT

id: 1 - Name: Administrator

I dont know that i am doing wrong in the first one. I heard that the mysql queries in php is more secure. Please help me

score 2 · Accepted Answer · edited May 23 '17 at 11:44

2

Bind all params with single function call:

$stmt->bind_param('ss', $uname, $pass)

Also, never use the second approach. Binding parameters is the only way to avoid SQL injection vulnerabilities, as explained here.

edited May 23 '17 at 11:44

Community

1
1

answered Aug 10 '16 at 11:32

Bartosz Zasada

3,762
2
19
25

cannot set bind parameters in php mysql

1 Answers1