Presenting a simple extension for MySQLi that provides convenience functions similar to mysql->fetch_array() and mysql->fetch_assoc() for prepared MySQL statements.
It’s the middle of 2008, and web servers everywhere are still being hacked with simple SQL injection attacks. If you’re a web developer, and you don’t know what an SQL injection attack is, you should not write a single line of code until you have read and fully understood this. Allowing an SQL injection attack to occur in your code is just plain lazy, and you can do better.
Since about 50% of all web servers are powered by Apache, it’s a fairly safe bet that the majority of the dynamic sites they host run PHP and MySQL. These free tools make it easy to produce interactive, database-driven websites even for non-programmers, but unfortunately make it too easy for anyone (developer or not) to generate code which opens up your site to SQL injection possibilities.
Before the advent of MySQLi and prepared statements, MySQL statements in PHP queries to a MySQL database would look like:
/* * Example 1 */ $sql ="SELECT ID, Firstname FROM Users WHERE Username = 'myusername' AND Password = SHA1(CONCAT('mypasswordsalt', 'mypassword'))";
(By the way, you’re not storing passwords in plain text, are you? You are adding a salt and storing the hashed password? If not, this explains what you are risking.)
So in PHP, your code might look something like:
/* * Example 2 */ $username = $_POST["username"]; $password = $_POST["password"]; $sql = sprintf("SELECT ID, Firstname FROM Users WHERE Username = `%s` AND Password = SHA1(CONCAT(Salt, `%s`))", mysql_real_escape_string($username, $password); $query = mysql_query($sql); // etc
That’s all quite a bit of hassle to type out though. In a large application, it’s easy to imagine that a developer might forget to escape the user inputs, leaving their database vulnerable to attack.
Enter MySQLi: This PHP extension provides the solution to all our SQL injection woes: the prepared statement. This article is not about prepared statements however, so if you don’t know what they are, read this.
I think PHP developers are used to the convenience that functions such as mysql->fetch_array() and mysql->fetch_assoc() provide. MySQLi also provides similarly named functions, but they don’t work with prepared statements. The only way to use these functions is if you resort to the old-style query string generation, where you have to remember to escape any data provided from the user. To obtain results from a prepared statement, the process goes something like this:
/* * Example 3 */ $sql = "SELECT ID, Firstname FROM Users WHERE Username = ? AND Password = SHA1(CONCAT(Salt, ?))"; $query = $db->prepare($sql); $query->bind_param("ss", $username, $password); $query->execute(); $query->bind_result($userid, $firstname); $query->fetch(); $query->close();
Quite a mouthful, huh? I have tired of writing out all this code, time and time again, just to provide secure, SQL injection-proof code. I initially resorted to defining a code template in Eclipse, but it was a poor solution. It’s frustrating that MySQLi doesn’t provide a nice easy implementation of fetch_array, fetch_assoc or similar for prepared statements.
Extending the MySQLi class
Below I will present my solution to the problem. I have extended the mysqli class to:
- Execute prepared INSERT, UPDATE and DELETE statements from a single line of code
- Fetch a single row from a result set as an associative array, using a prepared query from a single line of code
- Fetch a result set as an array of objects, using a prepared query fom a single line of code
- Automatically close queries upon completion to help save resources
The code from Example 3 can now be re-written as:
/* * Example 4 */ $sql = "SELECT id, Firstname FROM Users WHERE Username = ? AND Password = SHA1(CONCAT(Salt, ?))"; list($id, $firstname) = $db->FetchRow($sql, "ss", $_POST["username"], $_POST["password"]);
The FetchRow function will return either an array containing the results in the order specified by your SQL statement, or a single value if you only selected a single field, for example:
/* * Example 5 */ $sql = "SELECT COUNT(*) AS Count FROM Users WHERE SignupDate > NOW() - INTERVAL 1 DAY"; $NewMembers = $db->FetchRow($sql);
Note that I need to provide an alias for the expression COUNT(*).
I can also select multiple rows from the database, obtaining the results as an array of objects:
/* * Example 6 * Find all users that signed up today */ $date = mktime(0, 0, 0, idate("m"), idate("d"), idate("Y")); $sql = "SELECT id, Username, Firstname, UNIX_TIMESTAMP(SignupDate) AS SignupDate FROM tblUsers WHERE SignupDate > FROM_UNIXTIME(?)"; $NewMembersList = $db->FetchAll($sql, "i", $date); foreach ($NewMemberList as $member) echo $member->Firstname;
The array $NewMemberList in example 6 above contains an object representing every row in the result set from the query. Each object contains properties with the same name as fields in the SQL select statement. This is why we need to provide an alias for expressions such as COUNT(*) or UNIX_TIMESTAMP(SignupDate) – these are not valid property names for a PHP object.
I can also execute prepared SQL statements which do not return a result set (eg INSERT, UPDATE, DELETE or any DDL type statements). For example, if I wanted to disable a particular users account:
/* * Example 8 */ $db->ExecuteSQL("UPDATE Users SET Enabled = 0 WHERE id = ?", "i", $userid);
If I execute an INSERT statement on a table with an auto-increment field, the ExecuteSQL method will return the auto-increment value, otherwise it will return null.
This is just a first pass attempt at making my code easier to read and maintain, and also significantly less verbose. I know there are a few areas where I could make improvements, but please feel free to post any suggestions or comments you might have.