Mukund Mukund - 3 months ago 12
MySQL Question

cannot set bind parameters in php mysql

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

Answer

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.