Asked  1 Year ago    Answers:  5   Viewed   9 times

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>";

 Answers

3

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.

Saturday, May 29, 2021
 
PHPWDev
 
3

NO, A prepared statement would not be a solution because it is not possible to bind the table name. So avoid to use prepared statement for Truncate Table.

You cannot bind any SQL literal but data one. So keywords, operators and any identifier can not be bind using prepared statement. You can only bind data.

PDO prepared statements are useful when running queries with user input as they allow you to use features such as bound parameters to sanitise user input.

So In my suggestion you should not use prepared statement for truncate table.

If you really want to truncate using prepared , In case of Opencart which you are using, Use the code:

$sql = sprintf('TRUNCATE TABLE %s%s', DB_PREFIX, $table);
$this->db->query($sql); 

try with this once and let me know

Thursday, April 1, 2021
 
Angolao
 
4

I'm assuming you want to get the users info where their id is the same as user_id

You might do something like this;

$query = $db->prepare('SELECT * FROM table WHERE id=:id');
//using bindParam helps prevent SQL Injection
$query->bindParam(':id', $_SESSION['user_id']);
$query->execute();
//$results is now an associative array with the result
$result = $query->fetch(PDO::FETCH_ASSOC);

I wasn't sure how user_id is set so I just used bindParam just in case.

Saturday, May 29, 2021
5

Do it on command line:

mysql -u USER -p DBNAME < SQLFILE.SQL

Enter password when prompted and there you go.

Saturday, May 29, 2021
 
5

Nothing wrong with Darwin's answer, but wanted to point out PDO as an alternative with much lighter syntax:

<?php
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];
$link = new PDO("mysql:host=$host;dbname=$db", $username, $password, $options);
$stmt = $link->prepare("SELECT * from `wp_posts` WHERE ID=?");
$stmt->execute([$pid]);
$result = $stmt->fetchAll();

// Now you have a plain array to work with, database work is over
foreach ($result as $row):
?>

<h2 style="text-align:center;margin:0 auto">
    <?=$row["post_title"]?>
</h2>
<br/>
<div class="paracenter">
    <p id="cont">
        <?=$row["post_content"]?>
    </p>
    <hr style="color:black;width:10%"/>
</div>

<?php endforeach;?>

No need for any binding at all, and personally I find it much easier to work with.

Wednesday, August 18, 2021
 
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :