Converting to MySQLi - Forming Select Queries

[scaiferw]

I'm converting old MySQL code to MySQLi, and running into some questions.

When selecting data from the database, is it necessary or strongly advised to use prepared statements, or can I safely escape my user-derived parameters like the sample below?

In a perfect world I'd have all the time I needed and my middle aged brain would be as excited by all this as it was when I wrote my original code 8 years ago, but neither of those conditions exist right now. ;(

Many thanks in advance.

function listRecords() {

    global $db; // bring db connection into scope

    $lname = mysqli_real_escape_string($db, $_GET['lastname']);

    $sql = "SELECT * FROM members WHERE lastname='$lname'";

    if(!$result = $db->query($sql)){
      die($db->error);
    }

    while($row = $result->fetch_assoc()){
      echo "<p>$firstname $lastname</p>";
    }

    $result->free();

}

[diafol]

Use prepared statements. They're quicker than queries and will not let you down with escaping if you use binding properly.

[scaiferw]

Aargh. I was afraid of that.

Can you point me to a tutorial or sample code to show me how to convert that simple example to a prepared statement? The tutorials I've found seem to use more complex examples.

I find that once I have a basic template I can expand it for more complex requirements. That's why I stripped my code down to such a basic code sample above.

Many thanks for your response.

(BTW, it's absolutely worth learning the PS route, I did it for updates and deletes, but is the example above dangerous or just not as good as a full on prepared statement?)

Rob

[diafol]

https://www.daniweb.com/web-development/php/code/462098/mysqli-binding-example

[scaiferw]

I tried a prepared statement, but I get no result whatsoever. I know the $id is available and in scope on the line preceding the IF statement.

Any ideas? From what I can see this should work. I'm trying to be intelligent about this, but on days like today it seems hard!

echo "ID is $id"; // this works here

if($stmt = $mysqli -> prepare("SELECT * FROM members WHERE id = ?")) {

    echo "ID is $id"; // this does NOT work here

    // bind parameters, execute the statement, ...
    $stmt -> bind_param("i", $id);
    $stmt -> execute();
    $stmt -> bind_result($result);

    // fetch the value
    while($stmt -> fetch()){
        print_r($result);
    }
    // close statement 
    $stmt -> close();
}

[scaiferw]

(Sorry, forget my last post, I found an error I've corrected. Resubmitted...)

I tried a prepared statement, but I get no result whatsoever.

Any ideas? From what I can see this should work. I'm trying to be intelligent about this, but on days like today it seems hard!

The echo at line 10 returns one row, what I expect, so it appears my query was successful. I just can't access the contents.

if ($stmt = $db -> prepare("SELECT * FROM members WHERE id = ?")) {

    // bind parameters, execute the statement, ...
    $stmt -> bind_param("s", $id);
    $stmt -> execute();
    $stmt -> bind_result($result);

    // fetch the value
    while($stmt -> fetch()){
        echo "...<br />";
        print_r($result);

    }
    // close statement 
    $stmt -> close();
}-