Further improvements to parameterised MySQLi helper class
Sunday, September 28th, 2008Following my previous post on MySQLi parameterised queries, yet another major site has fallen victim to an automated SQL injection attack…
I’ve made some further changes to the class I presented back in that post. A quick recap of the functionality I presented originally:
1) Execution of prepared SQL statements that do not return a result set:
$sql = "UPDATE tblCustomers SET LastLogin = NOW() WHERE UserID = ?"; $db->ExecuteSQL($sql, "i", $userid);
replaced
$sql = "UPDATE tblCustomers SET LastLogin = NOW() WHERE UserID = ?";
if ($query = $db->prepare($sql))
{
$query->bind_param("i", $userid);
$query->execute();
$query->close();
}
2) Fetch a single row using a prepared query:
$sql = "SELECT Username, UNIX_TIMESTAMP(LastLogin) AS LastLogin FROM tblUsers WHERE UserID = ? LIMIT 1"; list($username, $lastlogin) = $db->FetchRow($sql, "i", $userid);
replaced
$sql = "SELECT Username, UNIX_TIMESTAMP(LastLogin) FROM tblUsers WHERE UserID = ? LIMIT 1";
if ($query= $db->prepare($sql))
{
$query->bind_param("i", $userid);
$query->execute();
$query->bind_result($username, $lastlogin);
$query->fetch();
$query->close();
}
3) Fetch multiple rows as objects using a prepared query:
$sql = "SELECT UserID, Username FROM tblUsers WHERE LastLogin >= FROM_UNIXTIME(?)";
$users = $db->FetchAll($sql, "i", $lastweek);
foreach ($users as $user)
echo "$user->UserID: $user->Username";
replaced
$sql = "SELECT UserID, Username FROM tblUsers WHERE LastLogin >= FROM_UNIXTIME(?)";
if ($query = $db->prepare($sql))
{
$query->bind_parem("i", $lastweek);
$query->execute();
$query->bind_result($userid, $username);
while ($query->fetch())
echo "$userid: $username";
$query->close();
}
This last item works extremely well for small result sets, however for large sets of data (eg if we had a very active website with, say 1,000,000 users who logged in within the last week), we’d quickly run into problems. To get around such situations, I’ve added an additional method to my extended MySQLi class:
4) Iterate over multiple rows in a result set from a prepared query:
$sql = "SELECT UserID, Username FROM tblUsers WHERE LastLogin >= FROM_UNIXTIME(?)";
while ($user = $db->FetchObj($sql, "i", $lastweek))
echo "$user->UserID: $user->Username";
This method iterates over the result set and returns an object which exposes properties with the same names as the fields selected from the database. When the last record in the result set has been returned, clean-up code is automatically and transparently called to close the result set.
I’ve been using this code extensively over the last few weeks, and have not found any problems so far. YMMV, but feel free to test it out and drop me a line if you find any bugs, or have any suggestions for improvements.
Update:
One user has commented that he kept getting “bind_param failed” messages when using table joins. This is most likely because he was selecting two fields with the same name eg:
SELECT A.UserID, B.UserID FROM tblUsers A INNER JOIN tblUsers B ON A.UserID = B.UserID
In this case, the two identical field names cannot be resolved, and causes the code to raise an error. To fix this, give your field names an alias where required:
SELECT A.UserID, B.UserID AS UserID_B FROM tblUsers A INNER JOIN tblUsers B ON A.UserID = B.UserID