CODEWITHSUNDEEP
phpmysqlsqlpdophpmyadmin
user3024303
user3024303

Reputation: 3

PDO - Putting all sql data into an html table using prepared statement

I want to fetch all the data from my table and display it in an html table but I'm not sure how to do with a prepared statement that will protect me from sql injection.

I've read that it should look something like:

$getPlayers = $db->prepare("SELECT * FROM Player WHERE name = :name");

But I'm not sure how to use the method using 'WHERE' to get all my data from the database.

This is my code so far. It works and gets me all my data but I dont think its secure from sql injection, is it?

$getPlayers = $db->prepare("SELECT * FROM Player");
$getPlayers->execute();

$players = $getPlayers->fetchAll();

echo "<table>";
echo "<tr><th>Name</th><th>Games Played</th><th>Tries</th></tr>";


foreach( $players as $row) {
    echo "<tr>";
    echo "<td>".$row['name']."</td>";
    echo "<td>".$row['games_played']."</td>";
    echo "<td>".$row['tries']."</td>";
    echo "</tr>";
}

echo "</table>";

Upvotes: 0

Views: 5091

Answers (2)

user1864610
user1864610

Reputation:

SQL Injection is only a problem where user-entered data needs to be sent to your database. A query like SELECT * FROM Player includes no user data. It's entirely safe from SQL Injection for that reason.

In fact, in this case, there's no benefit in using a prepared statement. A prepared statement here will make two calls to the database when one will do.

You can do this:

$getPlayers = $db->query("SELECT * FROM Player");
foreach ($getPlayers as $player) {
  // do something
}

No preparation, no binding, and just one call to the database.

Upvotes: 2

aconrad
aconrad

Reputation: 586

SQL injection uses submitted data. See http://en.wikipedia.org/wiki/SQL_injection for examples. I dont see any usage of submitted data in your example.

If you want to search by name, then the text that is searched can be a sql injection but given the fact that you will use bind parameters it will not be a issue.

$search  = $_GET['search'];
//mysql injection danger:
$getPlayers = $db->prepare("SELECT * FROM Player WHERE name = '$search'");

//because i can search for the string ' OR 1=1 OR 1='

//binded, no mysql injection
$search  = $_GET['search'];
$getPlayers = $db->prepare("SELECT * FROM Player WHERE name = ?");
$db->bindParam(1, $search, PDO::PARAM_STR);

See http://www.php.net/manual/en/pdostatement.bindparam.php for more info on binding.

Upvotes: 0

Related Questions