Converting a Json to include prepared statements for security. Just getting [] or undefined-CodePudding

Someone mentioned on a previous post that I need to use prepared statements in my php to protect from attacks. I'm trying to convert my documents but it seems to be breaking everything.

Here is my original code:

<?php

if(!empty($_POST['number'])){
    $data = array();
    
    $dbHost     = '******';
    $dbUsername = '******';
    $dbPassword = '******';
    $dbName     = '******';
    
    $db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName);
    if($db->connect_error){
        die("Unable to connect database: " . $db->connect_error);
    }
    
    $query = $db->query("SELECT * FROM db WHERE id = {$_POST['number']}");
    
    if($query->num_rows > 0){
        $userData = $query->fetch_assoc();
        $data['status'] = 'ok';
        $data['result'] = $userData;
    }else{
        $data['status'] = 'err';
        $data['result'] = '';
    }
    
    echo json_encode($data);

}
?>

and this is the code that has at least connected to the server, but seems to be returning nothing.

 <?php

if(!empty($_POST['number'])){
    $data = array();
    
        $dbHost     = '******';
        $dbUsername = '******';
        $dbPassword = '******';
        $dbName     = '******';
    
    $db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName);
    if($db->connect_error){
        die("Unable to connect database: " . $db->connect_error);
    }
    

if ($stmt = $db->prepare("SELECT * FROM db WHERE id=? ")) {
    $stmt->bind_param("i", $_POST['number']);
    $stmt->execute();
    $result = $stmt->get_result();
    while ($row = $result->fetch_assoc()) {
        $data[] = $row;

    }
    $stmt->close();
}
$db->close();

echo json_encode($data);

}
?>

console.log(data) = undefined. console log(data.id) = []

CodePudding user response：

you are binding i but you never declare it

$idnum = $_POST['number'] // declare this below session_start it's much cleaner to see 

if ($stmt = $db->prepare("SELECT * FROM db WHERE id= :id ")) {
    $stmt->execute([':id' => $idnum]);
    $result = $stmt->get_result();

also you don't have error handler so you don't know what going wrong

    setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}catch(PDOException $exception) {
echo nl2br("PDO Exception \r\n");
echo nl2br('Error Code: ' . $exception->getCode() . "\r\n");
echo nl2br('Error Message: ' . $exception->getMessage() . "\r\n");
}

anyway this site teach you how to create PDO Prepared Statements and Connection from scratch https://websitebeaver.com/php-pdo-prepared-statements-to-prevent-sql-injection