Vjeux

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

The bracket notation for string is incomplete in Javascript and does not work in IE7. This is really painful to migrate to the .charAt(pos) equivalent, this is why i recommend you not to use it.

// Bracket Notation
"Hello World!"[6]
// > "W"
 
// Real Implementation
"Hello World!".charAt(6)
// > "W"

The bracket notation to get a character from a string is a shortcut to .charAt(pos) added by the vast majority of the browsers. However, i would not recommend to use it for several reasons.

This notation does not work in IE7. The first code snippet will return undefined in IE7. If you happen to use the bracket notation for strings all over your code and you want to migrate to .charAt(pos), this is a real pain: Brackets are used all over your code and there's no easy way to detect if that's for a string or an array/object.

You can't set the character using this notation. As there is no warning of any kind, this is really confusing and frustrating. If you were using the .charAt(pos) function, you would not have been tempted to do it.

var string = "Hello World!";
string[6] = '?';
console.log(string);
// > "Hello World!";

By overriding the toString Object prototype, it is possible to speed up by 5x the sort function. This is an easy to implement trick that gives astonishing results

I wanted to know if there were ways to speed up the Javascript Sort function. I came across an interesting article (Yet another faster Javascript Sorting) that presents a way to boost the builtin sort function. However, the link with the detailed explanation is dead, so i make you a summary here.

To sort some data, you are likely to do something that looks like that:

data.sort(function (a, b) { return b.key - a.key; });

The comparison function is being called n log n times. Since it's a javascript function, it is slow. sort() with no parameters will first convert all elements into strings and then use native (therefore faster) string comparison.

To make this work, we just have to override the toString method of the Object prototype to return the key.

var save = Object.prototype.toString;
Object.prototype.toString = function () { return this.key; };
 
data.sort();
 
Object.prototype.toString = save;

You have to make sure that the key variable is a string. In my application, the key range is [0, 100] so the it is written as String.fromCharCode(key). If you have to deal with larger key range, the best solution is to convert the number into base 256. Make sure the number is padded with 0 because of the string comparison.

I made a little benchmark of the implementation to see how well it performs

*: Script time limit has been exceeded

It gives about a 5x increase of all the browsers I have tested with except in Chrome with a 3x decrease.

Since Chrome is already times faster than all the browsers, it doesn't look slowed by this feature. However it gives a real boost to all other browsers.

Update (24 December 2009): Chrome Array.sort() function is written directly in javascript and calls the ToString function everytime when a comparison is needed. Therefore, it is making 2 function calls (ToString(x), ToString(y) instead of one (compare(x, y)).

In order to check if that optimization will indeed give an actual boost, we can count the number of time the ToString method is being executed for 3 values. 3 times means that it is executed n time and more means that it is executed n log n times.

var need_custom_sort = (function () {
  // Fill the array with 3 values
  var array = new Array(3);
  for (var i = 0; i < 3; ++i) {
    array[i] = new Object();
  }
 
  // Override the toString method that counts how many times it is being called
  var count = 0;
  var save = Object.prototype.toString;
  Object.prototype.toString = function () { count += 1; return ""; };
 
  // Sort
  array.sort();
  Object.prototype.toString = save;
 
  // 3 times is good, more is bad!
  return (count === 3);
}());

SmallHash encodes any range of integers into the smallest possible string. This way, you can use the hash part of your url with efficiency.

My problem is having these options stored in the minimum characters as possible.

• Faction: Alliance, Horde
• Region: US, Europe
• Type: PvE, PvP, RP
• Lang: EN, FR, ES, DE, RU

The two faction and region could be stored in base 2 with no problem. However, if we wanted to store the others in base 2, there would have been space left. So i started digging up into the base conversion.

Here is the code to do a base2 to base10 conversion.

base10 = 0
foreach (bit in base2) {
  base10 *= 2
  base10 += bit
}

As you can see, we multiply the final number by 2, which is the number of possibilities. So, instead of multiplying by 2, we multiply by the number of possible options and it works! The decoding process is using the same technique by changing the divisor.

To get back to our example. [Alliance, US, PvP, DE] can be expressed as [0,0,1,3] over [2,2,3,5]. It will be encoded and decoded easily with the SmallHash library:

var input = [0,0,1,3];
var encoded = SmallHash.encode(input, [2,2,3,5], 'abcdefghijklmnopqrstuvwxyz');
var decoded = SmallHash.decode(encoded, [2,2,3,5], 'abcdefghijklmnopqrstuvwxyz');
console.log(input, encoded, decoded);
// Result: [0, 0, 1, 3], "bo", [0, 0, 1, 3]

As you can see, it fits into 2 characters instead of 4 with the easy way. The gain increases with the number of data you have to encode. This can also be improved by enlarging the base characters (uppercase letter, digits and special characters).

The algorithm is fairly easy, it is the same one explain before but using the range instead of 2 (when converting in base 2). This is the pseudo-code version.

 
SmallHash = {
  // encode( [2, 4], [10, 15], '0123456789' ) : '42'
  encode: function (input, ranges, base) {
    var result = 0
    for offset = ranges.length - 1 downto 0
      result = result * ranges[offset]
      result = result + input[offset]
 
    return int2str(result, base)
  },
 
  // decode( '42', [10, 15], '0123456789' ) : [2, 4]
  decode: function (input, ranges, base) {
    input = str2int(input, base)
    var result = []
 
    for offset = 0 to ranges - 1
      result[offset] = inputs % ranges[offset]
      inputs = inputs / ranges[offset]
 
    return result;
  }
};

Here is the full source code. This is the same code but being less readable due to the use of BigInt and the need of managing the allocation size.

// Requires BigInt.js ( https://blog.vjeux.com/wp-content/uploads/2009/08/BigInt.js )
SmallHash = {
  encode: function (input, ranges, base) {
    // Rough majoration of the final result size
    // It makes the sum of all the minimum of bits required for each range
    var size = 0;
    for (var i = 0, len = ranges.length; i < len; i = i + 1) {
      size += Math.ceil(Math.log(ranges[i]) / Math.LN2);
    }
    var result = bigInt.int2bigInt(0, size);
    for (var bit = ranges.length - 1, pos = 0; bit >= 0; bit = bit - 1, pos = pos + 1) {
      // If the value is higher than the expected range, the value is maximized
      // Therefore the result is always valid, even if the input is not
      var parsed_bit = Math.min(Math.abs(Math.floor(input[bit])), ranges[bit] - 1);
      bigInt.mult_(result, bigInt.int2bigInt(ranges[bit], 32));
      bigInt.add_(result, bigInt.int2bigInt(parsed_bit, 32));
    }
    return bigInt.bigInt2str(result, base.length, base);
  },
  decode: function (input, ranges, base) {
    input = bigInt.str2bigInt(input, base.length, base);
    var remainder = bigInt.dup(input); // Allocates enough room for the remainder
    var result = [];
    for (var pos = 0, len = ranges.length; pos < len; pos = pos + 1) {
      bigInt.divide_(input, bigInt.int2bigInt(ranges[pos], 32), input, remainder);
      result[pos] = Number(bigInt.bigInt2str(remainder, 10, '0123456789'));
    }
    return result;
  }
};

This script is using the BigInt library from Leemon Baird. I made some changes in order not to pollute the global namespace and added the possibility to modify the base string.

Update January 2010 - SmallHash is now being used on production at wowtal.com and you can download the source at http://static.mmo-champion.com/db/js/smallhash.js.

hgf.fooo.fr

Presentation

Fooo is my first year school project written in Delphi. During 8 months, Vladimir Nachbaur, Alban Perillat-Merceroz, Felix Abecassis and I developed a game that mimics Warcraft III and achieved to be major of the promotion.

We tried to make no use of copyrighted Warcraft files, however, a large majority of custom models are based on Warcraft files at some point and use some of their textures. All the interface graphic, icons, models has been made by fans and we are using them with their approbation.

What's the game able to do :

• Animated 3D Models
• Interface in XML/Lua
• Building & Unit creation
• Group and Subgroup management
• Group movement with formations
• Ranged & Melee Attacks
• Multiplayer over LAN or Internet
• Ingame Chat
• 2 Races: Treants and Rats
• And much more ...

Demonstration Video

My work in the project

Interface

A RTS game is requiring a lot of user interaction, so it needs a complex interface. I decided to make an interface engine like the one used by World of Warcraft. The content is displayed through XML files and scripted with Lua. If you are not familiar with this, you can think the XML as HTML and Lua as Javascript.

The interface engine is able to do the following:

• Supports Frame, Texture and FontString elements
• Ability to draw Backgrounds and Borders from images
• Mouse and Keyboard Interaction
• Inheritance and Virtual Frames
• Position through 2 Anchor points (element and its relative)

3D Engine

The 3D Engine is written on top of OpenGL. I've been confronted to two major problems. The first one is the Warcraft III models: there is really few documentation on them so i had to spent quite some time to reverse engineer them and figure out how animation were working.
The engine had to be optimized in many ways. At first, displaying one building was freezing the PC, now we are able to show more than 100 units and the game still runs smoothly.

The 3d engine is able to do the following:

• Animated Warcraft III Models
• Camera: Zoom and Rotation
• 3D Picking
• Frustum Culling
• Vertex Array

Learn more

If you want to learn more, for each presentation we made a 30 page document (french) explaining in detail the progression. You can read them at hgf.fooo.fr.