Mysqli Wrapper is shortening the code required to run queries, make them 100% safe against SQL Injections and gives a handy Array as result. No more pain writing queries!
When developing a PHP application, SQL queries is the most dangerous area. This is because the built-in tools are either not secure by conception or too much complicated to use. I've made a little wrapper to mysqli that solves all these problems and make you enjoy writing queries!
You are probably writing your queries like this. We assume we have $zip(string) and $pop(int) variables previously declared in the code.
// Basic Method mysql_connect('host', 'user', 'pass'); mysql_select_db('database'); $query = "SELECT Name, CountryCode FROM City WHERE Zip = '".mysql_real_escape_string($zip)."' AND Population > ".(int) $pop; $result = mysql_query($query); while ($row = mysql_fetch_array($result)) { // $row['Name'], $row['CountryCode'] }
With this method, you have to secure all the fields yourself. To do it, you have to type the loooong function mysql_real_escape_string and you must not forget the quotes around the parameter. The code is unreadable, not safe by concept because you may forget to sanitize a field ... And, it is annoying to write!
Mysqli was intended to be a wrapper around Mysql that would provide safe queries. You may wonder why this has not become the default way to write queries. Have a look at the exact same code written with mysqli.
// Mysqli Method $mysqli = new mysqli('host', 'user', 'pass', 'database'); $query = "SELECT Name, CountryCode FROM City WHERE Zip = ? AND Population > ?"; if ($stmt = $mysqli->prepare($query)) { $stmt->bind_param('si', $zip, $pop); $stmt->execute(); $stmt->bind_result($name, $code); while ($stmt->fetch()) { // $name, $code } $stmt->close(); }
Yes, 10 lines to make a SELECT. Obviously they failed to design an easy to use API. It is also too restrictive: you have to assign a variable for each of the field you want to select. This seems to be a really waste of time: it's already in the query, why would you write it another time? Also, that's not possible to use '*' to select all the fields.
Here is my attempt to easily to write and safe queries:
// My method $db = new dbWrapper('host', 'user', 'pass', 'database', true); $result = $db->q("SELECT Name, CountryCode FROM City WHERE Zip = ? AND Population > ?", 'si', $zip, $pop); foreach ($result as $key => $city) { // $city['Name'], $city['CountryCode'] }
What's good about this:
- A single 6 characters function
- Secure because parameterized
- Returns an Array
- Easy migration from the basic method
I'm really happy with that wrapper. It is faster to write the queries because you don't have to sanitize the parameters neither you have to think about safety and there's only a 6 character function to remember. The code is smaller and easier to use because it returns a real Array! Also, this allows to display mysql errors on the development server only and doesn't requires to type or die(mysql_error()); everytime.
If you don't know by advance the number of parameters (A custom search where field may be selected or not by the user for example), this implementation becomes really tricky to deal with. I have no solution at this time for this problem. The v8cgi Query API would handle this problem really well, however i am not confident enough in letting a script writing my queries.
The wrapper code is really hacky but fits in 60 lines so can be embedded really easily.
Class dbWrapper { protected $_mysqli; protected $_debug; public function __construct($host, $username, $password, $database, $debug) { $this->_mysqli = new mysqli($host, $username, $password, $database); $this->_debug = (bool) $debug; if (mysqli_connect_errno()) { if ($this->_debug) { echo mysqli_connect_error(); debug_print_backtrace(); } return false; } return true; } public function q($query) { if ($query = $this->_mysqli->prepare($query)) { if (func_num_args() > 1) { $x = func_get_args(); $args = array_merge(array(func_get_arg(1)), array_slice($x, 2)); $args_ref = array(); foreach($args as $k => &$arg) { $args_ref[$k] = &$arg; } call_user_func_array(array($query, 'bind_param'), $args_ref); } $query->execute(); if ($query->errno) { if ($this->_debug) { echo mysqli_error($this->_mysqli); debug_print_backtrace(); } return false; } if ($query->affected_rows > -1) { return $query->affected_rows; } $params = array(); $meta = $query->result_metadata(); while ($field = $meta->fetch_field()) { $params[] = &$row[$field->name]; } call_user_func_array(array($query, 'bind_result'), $params); $result = array(); while ($query->fetch()) { $r = array(); foreach ($row as $key => $val) { $r[$key] = $val; } $result[] = $r; } $query->close(); return $result; } else { if ($this->_debug) { echo $this->_mysqli->error; debug_print_backtrace(); } return false; } } public function handle() { return $this->_mysqli; } }
Update February 17 2010: Now works for php >= 5.3.0. Added debug_print_backtrace to get the sql query when there is an error.
I seem to have some problems with your wrapper on PHP 5.3.1, I keep getting errors like
Warning: Parameter 2 to mysqli_stmt::bind_param() expected to be a reference, value given in E:\xampplite\htdocs\Okiru\libs\dbWrapper\dbWrapper.class.php on line 24
Any ides? I tried fixing the code by myself but with no luck.
Hi Rasmus,
You need to pass your parameters as pointers e.g.
$name = 'Bob';
$rows = $db->q('SELECT * FROM People WHERE Name=?', 's', &$name);
Hope this helps,
BlaDe.
They changed the way they handle the references with call_user_func_array in php 5.3.0. The script is updated, you can copy and paste it again to make it work.
Thanks for the bug report!
Thats just what i was looking for, thanks and keep it up!
Very good, ill try to use this class but why not ewxtending the mysqli class like class dbWrapper extends mysqli ?
Well, I wanted something really simple and not bloated with mysqli.
The goal is to have a single function q() that does everything you need. If that's not enough, you have access to the real mysql handle.
Hi,
first good wrapper i'm using it right now!
I've two things to say:
1) for checking if there's been an error on the pages where you invoke the q() method you have to check if the return param of the method is an array like this:
$result = $this->db->q('SELECT ...');
if(!is_array($result))
{
die('Error:');
}
2) isn't better to return a reference instead in the handle method? like this:
//Return a reference
public function &handle() {
return $this->_mysqli;
}
another thing to mention is to free the returned array...
Whenever I run this I get
Number of elements in type definition string doesn't match number of bind variables
from the line
call_user_func_array(array($query, 'bind_param'), $args_ref);
Doesn't bind_param have to be told what type each var that is passed is supposed to be? like string, integer, etc..
oh excuse my last comment, I forgot to actually pass it in the function. derp.
Thanks for this, it's great!