5 replies to this topic

[MFA]

I've just completed my first prepared statement, converted to using them for security reasons. I've tested it and it works however I am looking for feedback (constructive criticism welcomed) regarding the code itself although I understand it 's fairly basic. Here's teh code:

 

<?php
//prepared statement example
include 'database.php';
$query = "SELECT ID FROM users WHERE email = ?";
$email = 'myemail@gmail.com';
$statement = mysqli_stmt_init($connect);

mysqli_stmt_prepare($statement, $query);
mysqli_stmt_bind_param($statement, 's', $email);
mysqli_stmt_execute($statement);
mysqli_stmt_bind_result($statement, $id);
mysqli_stmt_fetch($statement);

echo $id;
?>

 

Also, is using mysqli_stmt_close necessary? Am I correct in saying that without using this function I will not be able to create another prepared statement within that script? - because I have tried the latter and it wouldn't work unless I did close the statement.

 

[gizmola]

No it's not necessary because everything will be garbage collected at the end of the script.

[MFA]

No it's not necessary because everything will be garbage collected at the end of the script.

Okay, but how come I can't use another prepared statement unless I use mysqli_stmt_close($etc); after the first one?

[jcbones]

Are you getting "out of sync" errors?

IF so, you need to do one of 3 things:

1. store the result:

2. finish using the results before you make another database call:

3. close the result:

[MFA]

I'm not sure what "out of sync" errors are. I don't get error messages if that's what you mean.This is my code at the moment.

<?php

//prepared statement example

include 'database.php';

$query = "SELECT ID FROM users WHERE email = ?";

$email = 'example@googlemail.com';

$statement = mysqli_stmt_init($condbmembers);

mysqli_stmt_prepare($statement, $query);

mysqli_stmt_bind_param($statement, 's', $email);

mysqli_stmt_execute($statement);

mysqli_stmt_bind_result($statement, $id);

mysqli_stmt_fetch($statement);

echo $id . "</br>"; // THIS WORKS!!


$querytwo = "SELECT fname FROM users WHERE ID = ?";

$uid = '4';


$statementone = mysqli_stmt_init($condbmembers);

mysqli_stmt_prepare($statementone, $querytwo);

mysqli_stmt_bind_param($statementone, 'i', $uid);

mysqli_stmt_execute($statementone);

mysqli_stmt_bind_result($statementone, $fname);

mysqli_stmt_fetch($statementone);

echo $fname; //THIS DOESN'T UNLESS I INSERT mysqli_stmt_close($statement); FOLLOWING echo $id . "</br>";



?>

[gizmola]

Yes, if you need to run multiple statements in a script you have to close each one in turn, before you can run the next one.

To quote the manual:

Every prepared statement occupies server resources. Statements should be closed explicitly immediately after use. If not done explicitly, the statement will be closed when the statement handle is freed by PHP.