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!

You can view the source at the MysqliWrapper Github Repository.

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');
$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->bind_result($name, $code);
    while ($stmt->fetch()) {
        // $name, $code

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. Download

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();
            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);
            if ($query->errno) {
              if ($this->_debug) {
                echo mysqli_error($this->_mysqli);
              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;
            return $result;
        } else {
            if ($this->_debug) {
                echo $this->_mysqli->error;
            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.

If you liked this article, you might be interested in my Twitter feed as well.

Related Posts

  • September 22, 2011 URLON: URL Object Notation (43)
    #json, #urlon, #rison { width: 100%; font-size: 12px; padding: 5px; height: 18px; color: #560061; } I am in the process of rewriting MMO-Champion Tables and I want a generic way to manage the hash part of the URL (#table__search_results_item=4%3A-slot). I no longer […]
  • April 3, 2014 Hack is to PHP what ES6 is to JavaScript (11)
    PHP and JavaScript are both renowned to be languages with a lot of quirks. However two major initiatives on both sides, Hack for PHP and ES6 for JavaScript made the languages much better and modern. In this article I'm going to show all the ES6 features that are also in Hack. Arrow […]
  • August 27, 2011 Start a technical blog, it’s worth it! (5)
    Lately, I've been advocating to all my student friends to start a blog. Here's an article with the most common questions answered :) What are the benefits? Being known as an expert. The majority of my blog posts are about advanced Javascript topics. As a result, I'm being tagged as […]
  • March 6, 2012 Github Oauth Login – Browser-Side (15)
    I'm working on an application in the browser that lets you take notes. I don't want to have the burden to save them on my own server therefore I want to use Github Gists as storage. The challenge is to be able to communicate with the Github API 100% inside the browser. Since it is a […]
  • December 26, 2015 Challenge: Best JavaScript Setup for Quick Prototyping (26)
    Yesterday, there was a big discussion on Twitter on how hard it is to start hacking on a js project. One comment by Dan Abramov struck me in particular: "Right: don’t use tools, face a problem, choose a tool or roll your own. Wrong: learn tools that don’t solve your problems, hate the […]