2

I've been investing the time to learn Prepared Statements in MySQLi. I'm using PHP Solutions Second Edition by David Powers as a reference.

This query pulls a random photo filename from a specific gallery. The gallery number is a variable ($i). This is working, I simply want to know if it is written properly. My websites are fairly simple but I want to avoid SQL injection.

$conn = dbConnect('query');
$randPic = "SELECT p_fname
        FROM photos
        WHERE g_id = ?
        ORDER by RAND()
        LIMIT 1";
$gid = $i;
$stmt = $conn->prepare($randPic);
$stmt->bind_param('i', $gid);
$stmt->bind_result($p_fname);
$stmt->execute();
$stmt->store_result();
$stmt->fetch();

On the page where the random photo is needed, I am able to call it by the variable:

echo $p_fname;

And again, it all works. I just want to know if this is coded properly.

wordman
  • 581
  • 2
  • 6
  • 20

1 Answers1

0

Yes, your query is written fine.

Be careful when you output data. If outputting variable data for an HTML page, use htmlspecialchars() to avoid accidentally injecting HTML into your pages.

Brad
  • 159,648
  • 54
  • 349
  • 530
  • Brad, many thanks to you! The value contained in $p_fname is simply a filename. There's still plenty of PHP I need to learn, can you give me an example of what you mean? – wordman Aug 06 '12 at 00:31
  • Also...I read that after such a query, I should use either free_result() or close() but am not getting a good idea as to which one is best. Any advice would be gratefully received. Thanks! – wordman Aug 06 '12 at 00:34
  • @wordman, The risk is very low for file names, but it is good practice to follow in any case. As an example, suppose you were outputting some text that had a quote mark `"` in it. Your HTML could look something like: `Image for ` This confuses the parser, and is not valid HTML. Using `htmlspecialchars()` will change that output to `Image for "something"` – Brad Aug 06 '12 at 00:35
  • @wordman, If your script doesn't do much after running that query, there really isn't a need to close anything. Your script will end, and memory will be freed automatically. See also: http://stackoverflow.com/questions/1046614/do-sql-connections-opened-with-pdo-in-php-have-to-be-closed – Brad Aug 06 '12 at 00:36
  • That's exactly the type of explanation I needed, thank you! I'll have to do some additional research on this topic. – wordman Aug 06 '12 at 01:00
  • @Brad...I updated my original question with another query. I'm flying by the seat of my pants with this one. – wordman Aug 06 '12 at 01:25
  • @wordman, Post new questions as new questions. That's what keeps StackOverflow organized. – Brad Aug 06 '12 at 01:26
  • I thought as much. Deleted and posted as new. Thanks! – wordman Aug 06 '12 at 01:37