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');
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. 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();
                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.

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

    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.

  • http://www.snoxd.net BlaDe

    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.

  • http://vjeux.com vjeux

    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!

  • Chrigu

    Thats just what i was looking for, thanks and keep it up!

  • Timo

    Very good, ill try to use this class but why not ewxtending the mysqli class like class dbWrapper extends mysqli ?

  • http://vjeux.com vjeux

    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.

  • Cuke

    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;
    }
  • Cuke

    another thing to mention is to free the returned array...

  • Daniel

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

  • Daniel

    oh excuse my last comment, I forgot to actually pass it in the function. derp.

    Thanks for this, it's great!

  • http://blog.vjeux.com/ vjeux

    @Cuke:
    1) It returns false when there is an error. So you can do:

    $result = $db->q('...');
    if (!$result) { Error() }

    2) An object is automatically passed as reference, no need to add another one on top of it.

    3) Free the returned array !? PHP has a garbage collector, the array will be freed as soon as you don't use it anymore.

    @Timo:
    Yeah, extending the mysqli object is a good idea. I didn't think about it when writing it.

  • Me, myself and I

    I'm trying to do something similar right now. Unfortunately your class lacks a facility to pass parameters to the SQL. I started with PHP this afternoon, so I'm probably still stupid regarding PHP, but how about adding a second parameter to q, say an associative array, then use mysqli_STMT:bind_param() to add the parameters to the statement before executing it?

  • http://www.facebook.com/people/Brian-Zeligson/20803067 Brian Zeligson

    I love this class but for some reason I can't select data from a longtext field. I can see the data in phpmyadmin, but when I select it using the class and echo the variable in the array, I get blank space. Any ideas?

  • http://blog.vjeux.com/ Vjeux
  • Pingback: PHP one line prepared statements! Mysqli will never be the same again. « Working In Boxer Shorts()

  • http://www.facebook.com/people/Brian-Zeligson/20803067 Brian Zeligson

    Yup thanks, I ended up finding that same post through Google. Changed the column to text. Low tech but it worked.

  • JG Estiot

    Good job

  • http://www.facebook.com/people/Brian-Zeligson/20803067 Brian Zeligson

    Here's a fix for this, left by a commenter on my own blog. Credit and thanks to Peter Stacey for this one:

    I was having this problem on a database that was used by existing application and so couldn't change the column type. After much searching I discovered that if you store the result of the query with $stmt->store_result(); before $stmt->bind_result($var) it works!

    e.g.
    $stmt->execute();
    $stmt->store_result();
    $stmt->bind_result($content);

    Read more: PHP MySQLi returns empty variables from longtext column « Working In Boxer Shorts

  • Iamgollem

    Add to your code please:

    $types = ''; //initial sting with types
    foreach($args as $param) { //for each element, determine type and add
    if(is_int($param)) {
    $types .= 'i'; //integer
    } elseif (is_float($param)) {
    $types .= 'd'; //double
    } elseif (is_string($param)) {
    $types .= 's'; //string
    } else {
    $types .= 'b'; //blob and unknown
    }
    }
    $args_ref[]=$types;
    foreach($args as $k => &$arg) {
    $args_ref[$k+1] = &$arg;
    }

    Overall ... excellent job. I would expand this for users to run multiple queries with storing a prepared statement and just changing the values before execution.

  • Iamgollem

    see my response above.

  • http://www.facebook.com/vjeux Christopher Chedeau

    I want the developer to specify the types. Having them resolved automatically isn't optimal. For example, if you have "1234" as a user input, you want it to be considered as an integer and not a string.

  • Pingback: Using Exceptions and Try Catch Blocks in PHP | Jordan Hall()

  • Mike

    You're returning true or false from the constructor, but "constructors always return a new instance of the class. You cannot return any other type of value from a constructor."

    I guess you're trying to have a minimal API.. but how about a $db->connected or some other variable that people can check if they want?

  • Singh

    Thanks! I's looking for something like this.

  • Max

    How to execute multiple statements? (semicolon separated queries)
    For example I want to use user-defined variables: "SET @pos = 0; UPDATE ...."

  • ben

    i don't get why you do this return here, it looks like it always end the function before creating the array with results...

    if ($query->affected_rows > -1) {
    return $query->affected_rows;
    }

  • Jimmydorry

    Is it possible to modify this to provide transaction and multiple query support?

  • Eduardo Barros

    Thanks.. 😉

  • Pingback: Recommendation for mysqli batch queries - Tech Magazine()

 

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! (4)
    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 (23)
    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 […]