PHP/MySQLi Prepared Statements bind_param Question

[Christopher.Burkhouse]

Sorry for the lengthy title, I just wanted to be super clear for referencing! Anyways, I had a question about prepared statements in PHP/MySQLi (not PDO). Below is a SAMPLE query I modified for these boards. Don't worry, I always sanitize my user input, and ALWAYS encrypt my passwords extensively. This is purely an example.

 

My questions are...

 

1. Is this bad practice to use time() in this manner?

 

2. Will it use the time() captured at the moment I call bind_param, or will time() be called when $stmt->execute() is called? Reason I ask is because.. well, prepared statements and bind_param... binds to the variables. They then use the value of those variables at the time you call execute(). That's awesome! So does it call time() during execute(), or call execute() during the bind_param and store that somewhere for all calls? If I don't make sense please let me know and I can try to elaborate/clarify.

 

3. Is it bad practice to bind $_SESSION or other global values directly into bind_param?

 

I couldn't really find any documentation or answers to these questions, and don't see the point in wasting resources if I don't have to by setting time() or global vars to vars. Unless I'm really not supposed to, of course. Any help, advice, or suggestions are greatly appreciated!

$sql = 'INSERT INTO users (id, username, password, time_created) VALUES (NULL, ?, ?, ?)';if($stmt = $mysqli->prepare($sql)){    $stmt->bind_param("ssi", $username, $pass, time());    $username = $_POST['username'];    $pass = $_POST['password'];    // Execute statement    $stmt->execute();    // Close statement    $stmt->close();}else echo 'error';

4. Bonus/side question: Do you need to call close() for non-SELECT statements? I didn't have to for non-prepared statements, but wasn't sure about prepared statements.

[justsomeguy]

1. Is this bad practice to use time() in this manner?

Yes, the variables that you send to bind_param need to all be variables, not scalar values or function calls. You should bind a variable for the time and save the value of time() in that variable before calling execute.

3. Is it bad practice to bind $_SESSION or other global values directly into bind_param?

No. A variable is a variable.

Bonus/side question: Do you need to call close() for non-SELECT statements?

It depends, but it's a good practice to close the statement when you're done with it.

If the current statement has pending or unread results, this function cancels them so that the next query can be executed.

[Christopher.Burkhouse]

Awesome, thanks for the quick response justsomeguy!

 

I figured that was the case with close(). Better safe than sorry is how I felt about it.

 

I was honestly surprised time() even worked when called in bind_param() the way I used it.. since it's a function, not a variable. What about something such as...

$stmt->bind_param("ssi", $username, $pass, $time=time());

That worked for me as well, but that doesn't mean it's /supposed/ to work. Don't mean to ask silly q's, but in cases I only use time() once, I wonder if this kind of code is intended to work?

 

Thanks,

Chris

Edited April 9, 2015 by Christopher.Burkhouse

[justsomeguy]

I was honestly surprised time() even worked when called in bind_param() the way I used it

It would probably cause a warning if you have PHP configured to show those. The reason is because those variables are passed by reference, and you cannot pass a scalar value or function call by reference. Only variables can be passed by reference. It might use the value but it's not technically correct. PHP is forgiving like that. Strictly speaking, trying to pass a function call by reference should not work at all, but PHP doesn't treat it as a fatal error. Other languages might.

What about something such as...

That will work fine, it's assigning the value and binding the variable at the same time.