How to convert mysqli calls to use prepared statements?

Question

I've got a query which I want to convert to use prepared statements, but I can't figure out how!

The code below is working code, but not safe because it's not using prepared statements. I want the function getUser to return the same result as it does now; can anyone tell me how to convert this?

$results = getUser('myUser@test.com');
foreach($results as $result) {
    echo $result['email'];
}
function getUser($email){
    $con = DBConnect();
    $result = mysqli_query($con,"SELECT * FROM tbl_appl_users WHERE email='".$email."'");
    $resultArray = array(); 
    while($row = mysqli_fetch_array($result)) {
        array_push($resultArray,$row);
    }
    return $resultArray;
    mysqli_close($con);
}

I know how to set up the prepared statement, but don't know how to process it. In the example code the query is requesting a single value but I want to get all values returned. The function as far as I got it is below:

function getUser($email){
    $con = DBConnect();
    $resultArray = array(); 
    if ($stmt = $con->prepare("SELECT * FROM tbl_appl_users WHERE email=?")) {
        $stmt->bind_param("s", $email);
        $stmt->execute();
        //$stmt->bind_result($district); // how to do this for a * result set.
        $stmt->fetch();
        while($row = mysqli_fetch_array($stmt)) {
            array_push($resultArray,$row);
        }
        //printf("%s is in district %s\n", $city, $district);
        $stmt->close();
    }
    return $resultArray;
    mysqli_close($con);
}

You need to share why in concrete you do not know how to convert this. That information is totally missing. Flosculus already has pointed out that the PHP manual shows code-examples already. — hakre, May 08 '14 at 10:03
Would you explain `// how to do this for a * result set` a bit more? You've bound your single input parameter fine, do you _need_ to bind output columns to variables? You can just deal with the whole `$row` as it is read in the loop, I think. — halfer, May 08 '14 at 10:17
@halfer When I leave out the bind-result, and I leave the rest of the code as is, I get the following error: `Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, object given in /WWW/A/.5c1/k/korte8/htdocs/onwijs_licenses/index.php on line 27`. So this `mysqli_fetch_array($stmt)` needs to be replaced with something else. — Jeroen, May 08 '14 at 10:24
(Note to downvoters: now this question has been improved, please consider retracting your downvote). — halfer, May 08 '14 at 11:41
By the way, your `mysqli_close($con)` will never be executed, since you always `return` before that code is reached. — halfer, May 08 '14 at 16:47

halfer · Accepted Answer · 2014-05-10T08:38:10.527

0

I use MySQL PDO rather than MySQLi, but I've done some digging to show how I'd go about researching this. Here is the problematic method:

http://www.php.net/manual/en/mysqli-stmt.get-result.php

Several of the comments on that page refer to the same problem - MySQLi appears to want all columns to be bound to variables, which is a bit cumbersome if you are trying to write a generic routine. However one comment mentioned this:

http://php.net/manual/en/mysqli-stmt.bind-result.php

Aha! That reveals that you can now do the following, without having to bind output columns:

    $stmt->execute();
    $result = $stmt->get_result();
    while ($row = $result->fetch_array(MYSQLI_NUM))
    {
        foreach ($row as $r)
        {
            print "$r ";
        }
        print "\n";
    }

Give that a try?

From the comments, you discovered that get_result is not always available on shared hosting, as it requires the non-standard PHP module mysqlnd. In that case, you're either stuck with output column binding, or you can switch to PDO, which allows whole array rows/objects to be read.

edited May 10 '14 at 08:38

answered May 08 '14 at 10:52

halfer

19,824
17
99
186

I need an extra driver for 'get_result' wich is not installed on the server :(. So my only option appears to be with the bind_result and fetch. [get_result stackoverflow](http://stackoverflow.com/questions/8321096/call-to-undefined-method-mysqli-stmtget-result). No generic routine on my server. I'm going to try to fix it with bind_result and fetch and post my solution. – Jeroen May 08 '14 at 11:09
Ah, good spot. If you don't have much to rewrite, I wonder if MySQL/PDO would be better? I've always used it, and (from seeing this trouble) I wonder if it would be easier. – halfer May 08 '14 at 11:37
Also, what version of PHP are you on? If it is before 5.3 it is in need of an upgrade anyway, and `get_result` is only available in 5.3+. Are you on shared hosting? – halfer May 08 '14 at 11:39
Yes I'm on a shared hosting, php version is 5.3.28. I rewrited my script to PDO, much better! From now on I'll Always use that :). – Jeroen May 08 '14 at 16:43

score 0 · Answer 2 · answered May 08 '14 at 16:46

0

With the help of halfer, I came to the conclusion that rewriting with MySQL/PDO is much better.

function getUser($mail){
    $dsn = 'mysql:host=***;dbname=***';
    $dbh = new PDO($dsn, 'user', 'password');
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
    $stmt = $dbh->prepare('SELECT * FROM tbl_appl_users WHERE email=?');
    $stmt->bindParam(1, $mail);
    $stmt->execute();
    while ($row = $stmt->fetch()) {
        return $row;
    }           
    $dbh = null;
}

answered May 08 '14 at 16:46

Jeroen

241
3
21

1

Great! The only change I'd make to that is to accept a PDO connection as the second parameter, so that (a) the function is not hardwired to a particular set of credentials, and (b) you use just the one connection per request. Ah, and you can change `while` to `if` here too, since you don't need a loop. – halfer May 08 '14 at 16:49
1

Thanks for the suggestions. I will process the first one, but I'll leave in the `while`, because sometimes it actually can be a loop. I made some changes to the function for that. – Jeroen May 08 '14 at 17:59

How to convert mysqli calls to use prepared statements?

2 Answers2