Kerrial Beckett Newham Kerrial Beckett Newham - 4 months ago 16
PHP Question

Securing mysqli query

I have been trying to figure out how I can secure some code. All it does is get the id of the row and then populate the page from the database respective of the row. all the examples I look at talks about bind_param, but I have no idea what that relates to in terms of my case.

The OLD code that is vulnerable:

<?php

$con=mysqli_connect("HOST","USER","PASS","DBNAME");
$con->set_charset("utf8");
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

if(isset($_GET['id'])) {
$txt= $_GET['id'];
$result = mysqli_query($con,"SELECT * FROM news where article_id=" . $txt);

while($row = mysqli_fetch_array($result))
{
?>

// Show something . . .

<?php }

}

mysqli_close($con);
?>


With this on a separate page:

<a target="_blank" class="text-center article_hover" href="article.php?id=<?= $row['article_id'] ?>">


The first (pathetic) attempt (with error):

<?php

$dbConnection = new PDO('mysql:dbname=DBNAME;host=HOST;charset=utf8', 'USER', 'PASS');


$dbConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stmt = $dbConnection->prepare('SELECT * FROM news WHERE article_id = ?');

$stmt->bind_param('article_id', $_GET['article_id']);


$stmt->execute();


$result = $stmt->get_result();


while ($row = $result->fetch_assoc()) {?>


// Show something . . .


<?php }?>


The error thrown is: Fatal error: Call to undefined method PDOStatement::bind_param()

UPDATE:
after using: bindParam instead of bind_param. new error is thrown:


Fatal error: Call to undefined method PDOStatement::get_result()


I apologise if my code is horrible, thank you for any suggestions.

Answer

bind_param() method does not exist that you are making mistake. You should try bindParam() or bindValue() not bind_param.

You can also use named placeholders with bindValue() as the given example below:

$stmt = $dbConnection->prepare('SELECT * FROM news WHERE article_id = :id');
$stmt->bindValue(":id", $_GET['id'], PDO::PARAM_INT);
$stmt->execute();

make sure that: the placeholder should't be quoted with any inverted commas because it always be send as string else when you are filtering it as int

Comments