Fix problem in php line of code $sql = SELECT item FROM table WHERE user = $user

Question

I have a php script that includes a $sql = SELECT FROM WHERE = $variable query. The query should produce a JSON list of itemids linked to a userid in a usersitems table. When I hard code the userID the query works but when I use the $variable the query $Result is No Records.

I have rewritten the script code and tried various punctuation variations numerous times. No joy.

//A missing prior code block establishes connection with database.

    $userID = $_POST["userID"];

    $conn = new mysqli($servername, $username, $password, $dbname);

    if ($conn->connect_error) 
    {
        die("Connection failed: " . $conn->connect_error);
    } 
    echo "Connected successfully". "<br>";
      //below testing that $userID holds correct value
    echo $userID. "<br>";

    //Below is commented line to test hard coded userID value.
    //$sql = "SELECT itemID FROM usersitems WHERE userID = '1' ";

    //Here's the SELECT line that's driving me batty.
    $sql = "SELECT itemID FROM usersitems WHERE userID = '" . $userID ."'";

    $result = $conn->query($sql);

    if ($result->num_rows > 0) 
    {
        // output data of each row
        $rows = array();
        while($row = $result->fetch_assoc()) 
        {
            $rows[] = $row;
        }

        echo json_encode($rows);
    }
    else
    {
        echo "No records.";
    }
    $conn->close();

If I use a hard coded userID value (in place of the $userID) then the script works and I get the json output expected. When I use the $userID I get a No Records result.

Answer 1

Firstly, your code is vulnerable to SQL injection. You need to use prepared statements to ensure that malformed SQL cannot be passed via the $userID variable.

Please see: How can I prevent SQL injection in PHP?

I've updated your code to use a prepared statement, which should work correctly.

<?php

$userID = trim($_POST["userID"]);

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error)
{
    die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully". "<br>";
//below testing that $userID holds correct value
echo $userID. "<br>";

//Below is commented line to test hard coded userID value.
//$sql = "SELECT itemID FROM usersitems WHERE userID = '1' ";

//Here's the SELECT line that's driving me batty.
$stmt = $conn->prepare("SELECT itemID FROM usersitems WHERE userID = ?");
$stmt->bind_param("i", $userID);
$stmt->execute();

$result = $stmt->get_result();

if ($result->num_rows > 0)
{
    // output data of each row
    $rows = array();
    while($row = $result->fetch_assoc())
    {
        $rows[] = $row;
    }

    echo json_encode($rows);
}
else
{
    echo "No records.";
}
$conn->close();