Search code examples
phpmysqlarrayspdocode-injection

using prepared statements and fetchAll() to display to a table

I'm trying to get my code SQL Injection safe, I am having trouble converting the pdo data to an array then comparing row data.

I have been reading up on how to prevent sql injection as well as fetchAll() documentation and how to handle SELECT statements with pdo.

Here is the relevant code. I believe it prepares the statement, then executes it, then the table is fetched and stored in $data where it is handed to $row, and then it looks up the player column and compares it with the logged in user to get the context based code to run. Where is the issue?


$stmt = $pdo->prepare("SELECT * FROM userdata");

$stmt->execute();

$data = $stmt->fetchAll();

echo "<table border='1'>
<tr>
<th>username</th>
<th>words</th>
</tr>";

while($row = $data)
{
echo $row['player'];
echo "<tr>";
echo "<td>" . $row['player'] . "</td>";
if($row['player'] == $logedInUsername)
{
    echo "<td>" . $row['words'] . "<a href='edityourword.php?edit=$row[words]'> edit</a></td>";
}
else
{
    echo "<td>" . $row['words'] . "</td>";
}
echo "</tr>";
}
echo "</table>";

My current error is reoccurring, here is the segment which the while loop keeps printing.

Notice: Undefined index: player on line 41

Notice: Undefined index: player on line 43

Notice: Undefined index: player on line 44

Notice: Undefined index: words on line 50

Notice: Undefined index: player on line 41

Notice: Undefined index: player on line 43

Notice: Undefined index: player on line 44

Notice: Undefined index: words on line 50
Solution

  • You have got two options. Either change the while loop to a foreach loop as @NigelRen suggested or use fetch method to fetch each record one by one from DB. 

    foreach ( $data as $row) {
    // ...
}
// or 
// Remove $data = $stmt->fetchAll();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)){
    // ...
}