How to turn vulnerable sql queries into parameterized queries PHP

Question

I have this code which I understand is vulnerable but I'm having trouble making a safe prepared statement out of it. Anyone with any idea of how to arrive to the point of getting an array of member records stored in variable $userrecord and number of rows stored in $rowsnumber help me out. I've not used MySQLi prepared statements before

/*This query returns member records in an array format*/
$querymember = "SELECT * FROM members WHERE phone='$providedphone' "; 
$member = mysqli_query($conn,$querymember);

// Number of rows
$rowsnumber = $member->num_rows;

// User record (Entity)
$userrecords = $member->fetch_array(MYSQLI_NUM);

What I've tried

$stmt = $mysqli->prepare("SELECT * FROM members WHERE phone = ?");
$stmt->bind_param("s", $providedphone);
$stmt->execute();

// To get number of rows
$rowsnumber = $stmt->num_rows;

// To get user records
$userrecords = $stmt->get_result();

Alternatively [There is a manual for learning this stuff from](https://www.php.net/manual/en/book.mysqli.php) — RiggsFolly, May 08 '19 at 15:35
That is the wrong duplicate fellows - the OP is preventing injection correctly, just not fetching the results. — Jay Blanchard, May 08 '19 at 15:37

RiggsFolly · Accepted Answer · 2019-05-08T15:45:23.693

The $userrecords = $stmt->get_result(); converts a mysqli::statement object to a mysqli::result object so now all you have to do is process the resultset as you would have done before

$stmt = $mysqli->prepare("SELECT * FROM members WHERE phone = ?");
$stmt->bind_param("s", $providedphone);
$stmt->execute();
//To get number of rows
$rowsnumber = $stmt->num_rows;
//To get user records
$result = $stmt->get_result();
$userrecords = $result->fetch_array(MYSQLI_NUM);

Alternatively you can use the mysqli::statement object as it is like this by binding PHP varibales to the column names and then doing a mysqli::statement->fetch()

$stmt = $mysqli->prepare("SELECT * FROM members WHERE phone = ?");
$stmt->bind_param("s", $providedphone);
$stmt->execute();

// bind returned columns to PHP variables
// as I dont know what your column names are this is just an example
$stmt->bind_result($name, $code);

// this will fetch the columns into the PHP varibles
$stmt->fetch();

How to turn vulnerable sql queries into parameterized queries PHP

1 Answers1