Rolling out prepared statements using bind_result & fetch

Question

I am in the process of migrating to php7 & msqli. I have a lot of old php files that will need prepared statements using bind_result & fetch. Thus, before I modify all these files I want to be sure I am coding prepared statements properly using bind_result & fetch, such that they are reasonably safe from sql injection. The code in my example works for me (binds & fetches properly), but I just want to be sure I coded them safely. I am still learning to code prepared statements for other implementations as well.

I also tried get_result instead of bind_result, but for my purposes (db interactions) I believe bind_result will suffice.

Here is the example of a php file that I will be using as the template for all my other php files that will need to be modified with prepared statements using bind_result & fetch:

<?php

//mysqli object oriented - bind_result prepared statement

//connect to database

require 'con_db.php';

//prepare, bind_result and fetch

$stmt = $con->prepare("SELECT image, caption FROM tblimages
WHERE tblimages.catID = 6 ORDER by imageID");
$stmt->execute();
$stmt->bind_result($image, $caption);

while ($stmt->fetch()) {
echo "{$image} <br> {$caption} <br> <br>";    
}

$stmt->close();

//close connection

mysqli_close($con);

?>

And here is the php file that makes the db connection via "require", i.e. con_db.php:

<?php

//mysqli object oriented connect to db

//MySQL errors get transferred into PHP exceptions in error log

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

//establish connection, any connection errors go to php.errors

$con = new mysqli('localhost','uid','pw',
'db');

?>

I'm hoping I coded the prepared statements in a reasonably secure fashion to prevent sql injection. But any comments or suggestions are welcome. Thank you.

UPDATE: I decided to show (below) an example of the current code I was going to modify with prepared statements (with the bind_result, fetch example above). So below is a representation of the majority of php/mysqli code that currently exists that lives in many php files that would need to be modified. It is the existing mysql SELECT statements that vary the most. However, based on the feedback I have received I believe since there are no variables being passed there is no reason to use prepared statements with binding. However, I DO have some forms that DO pass variables (GET & POST), and I will modify those php files using prepared statements (bind_param, bind_result & fetch). I hope that made sense :-) I just thought it would be more useful to show an example of the code I originally was planning to modify since I may not need to modify much of it based on feedback I have received here, plus what I have read since my original post (on my concern re: sql injection). But please feel free to correct me if I'm wrong. Thank you.

<?php

//mysqli object oriented - mysqli_query & mysqli_fetch_array

//connect to database

require 'con_db.php';

//query and fetch

$result = mysqli_query($con,"SELECT image, caption FROM
tblimages WHERE tblimages.catid = 1");      

while($row = mysqli_fetch_array($result))
{
echo $row['image'];
echo "<br />";
echo $row['caption'];
echo "<br />";
}
mysqli_close($con); 
?>

If you can try to move to PDO instead. It has slightly simpler syntax and offers more. As for SQL injection your example does not have any data to be bound, you could use just query. Prepared statement makes sense if you have variable date to be passed to DB. — Dharman
Thank you for the reply. I may eventually try PDO. But I am not a very good php coder right now. Regarding sql injection, I think I see what you mean about no data being bound. Is that because there is no bind_param, i.e. no data being input or altered? If so, is there any reason NOT to use bind_result & fetch in this example? I thought I read using prepared statements for all sql queries, including those that just select and output results (like in this case) was strongly encouraged? Or maybe using mysqli_query is more efficient in this case? Sorry, I'm still learning. Thank you. — obcbeatle

Your Common Sense Your Common Sense · Accepted Answer · 2019-07-04T05:25:27

You don't actually need bind_result() and fetch().

With PHP7, almost certainly you will have get_result() that will give you a familiar resource-type variable from which you can get the familiar array.

$stmt = $con->prepare("SELECT image, caption FROM tblimages
WHERE catID = 6 ORDER by imageID");
$stmt->execute();
$res = $stmt->get_result();
while ($row = $res->fetch_assoc()) {
    echo "{$row['image']} <br> {$row['caption']} <br> <br>";    
}

so you can keep a lot of your old code intact.

A couple notes:

Like @Dharman said, you don't really need a prepare/bind/execute routine if no placeholder marks are used i the query.
Like @Dharman said, better try PDO instead, it is much easier to use.

That said, you can greatly reduce the overhead with a simple mysqli helper function. Instead of writing this monster of a code (let's pretend the id in the query is dynamical)

$sql = "SELECT image, caption FROM tblimages WHERE catID = ? ORDER by imageID";
$stmt = $con->prepare($sql);
$stmt->bind_param("s", $catId);
$stmt->execute();
$res = $stmt->get_result();

you can have it in just two lines:

$sql = "SELECT image, caption FROM tblimages WHERE catID = ? ORDER by imageID";
$res = mysqli_select($con, $sql, [$id]);

Rolling out prepared statements using bind_result & fetch

1 Answers