CODEWITHSUNDEEP
phpmysqlmysqli
braddders
braddders

Reputation: 41

PHP MySQLi Parameterized Query not functioning

I am updating my current unprotected queries to parameterized ones to protect from SQL Injection.

I have spent a few hours trying to sort this however cant find the issue, any help much appreciated.

BEFORE (echo $row['storeID'];) works before 

$storeName = mysqli_real_escape_string($conn,$_GET['store']); 
$query = "SELECT * FROM stores WHERE storeName = '$storeName'";
$results = mysqli_query($conn, $query);
$row = mysqli_fetch_assoc($results);

AFTER

$storeName = $_GET['store'];
$stmt = mysqli_prepare($conn, "SELECT * FROM stores WHERE storeName = ?");
mysqli_stmt_bind_param($stmt, "s", $storeName);
mysqli_stmt_execute($stmt);
$row = mysqli_stmt_fetch($stmt);

This echo should work but using statements it does not

 echo $row['storeID'];

Upvotes: 3

Views: 41

Answers (2)

Royar
Royar

Reputation: 621

You were missing a call to mysqli_stmt_get_result before fetching the row:

$storeName = $_GET['store'];
$stmt = mysqli_prepare($conn, "SELECT * FROM stores WHERE storeName = ?");
mysqli_stmt_bind_param($stmt, "s", $storeName);
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);
$row = mysqli_fetch_assoc($result);

echo $row['id'];

Upvotes: 1

miken32
miken32

Reputation: 42677

If you look at the documentation for mysqli_stmt_fetch you'll see this description:

Fetch results from a prepared statement into the bound variables

So if you want to go this route, you'll need to ue mysqli_stmt_bind_result as well:

$storeName = $_GET['store'];
$stmt = mysqli_prepare($conn, "SELECT * FROM stores WHERE storeName = ?");
mysqli_stmt_bind_param($stmt, "s", $storeName);
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $col1, $col2, $col3,...);
while (mysqli_stmt_fetch($stmt)) {
    // do stuff with $col1, $col2, etc.
}

Now, with each iteration of the loop, the bound result variables are given the value from the result set.

However, I'd strongly suggest moving to PDO, which is far less verbose:

$storeName = $_GET['store'];
$stmt = $db->prepare("SELECT * FROM stores WHERE storeName = ?");
$stmt->execute([$storeName]);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

// now you have a simple array with all your results
foreach ($rows as $row) {
    // do stuff with $row
}

Upvotes: 2

Related Questions