How to properly run and check parameterized database queries with PHP/MySQLi

Question

After reading related questions about parameter-ized queries, I realize that they are the way to go to completely (well, unless you're interpolating table values or something) prevent SQL injection, even if they are somewhat (okay, very much) verbose and repetitive. However, I've been wondering how to properly test them for error handling, and also if error-handling in some cases is even necessary.

Sorry if this is a newbie question, but aren't PHP and MySQL installed on the same server anyways? Unless one is concerned with the syntax of the connection query or the database connection codes or whatever, I'm not entirely certain that it's necessary to check for errors with every statement like:

if ($stmt->bind_param('s',$logon)) {
   if ($stmt->execute()) {
      if ($stmt->bind_result()) {

      } else { return 'bat'; }
   } else { return 'bar'; }
} else { return 'foo'; }

// or else:

$stmt->bind_param('s',$logon)) or trigger_error('Could not bind parameters!',E_USER_ERROR);

// etc.

As a side note, if the $stmt connection isn't closed for some reason, while the mysqli connection is, does that cause some some sort of internal server error? Or does closing the actual mysqli connection do the trick as well? Why should you close $stmt in the first place?

Anyways, thanks for responding.

Update: MySQL Parameterized Queries - Cache Duration [duplicate] // closing $stmt isn't necessary as PHP will do it in the end anyway.

Notes: $stmt -> prepare(...) interacts with the server.

Exabytes · Answer 1 · 2014-08-27T06:13:00.393

The very helpful AND operator can help simplify all those if-else statements. While I'm sure there are other fancier ways to prevent multiple uncaught exceptions at once, this will hopefully help others who are looking for a reasonably simple way to write parameterized queries in the future, or in fact nested queries in general:

// Procedural style:

$con = mysqli_connect('localhost','blah','foo','bar');
$query1 = "SELECT xyz FROM blah WHERE xyz = ?";
$username = 'ABC';


if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
} else {
    if (($stmt =
        mysqli_stmt_init($con) ) and
        mysqli_stmt_prepare($stmt,$query1) and
        mysqli_stmt_bind_param($stmt,'s',$username) and
        mysqli_stmt_execute($stmt) and
        mysqli_stmt_bind_result($stmt,$name) and
       ( mysqli_stmt_fetch($stmt) !== FALSE) ) { 
          // Be careful of the functions that can return null
          var_dump($name);
       }
}

// Object Oriented Style:

$con = new mysqli('localhost','blah','foo','bar');  
    if ($con->connect_errno) {
        printf("Connect failed: %s\n", $con->connect_error);
    } else {
        if ( ($stmt = $con->prepare($query1)) and 
            $stmt->bind_param('s',$username) and 
            $stmt->execute() and
            $stmt->bind_result($name) and 
            ($stmt->fetch() !== FALSE)) {
             var_dump($name);

        }
    }

How to properly run and check parameterized database queries with PHP/MySQLi

1 Answers1