SafeMySQL class

  1. Type-hinted placeholders
  2. Comments (11)

This is not actually a delusion but rather a solution.

SafeMysql is a PHP class to make Mysql queries safe and convenient.

TL;DR: code on Github:

Type-hinted placeholders

Is the main feature of the class, whe idea is taken from the Nikic's PDO wrapper.

And it's a really great step further from just ordinal placeholders used in prepared statements. Simply because dynamical parts of the query aren't limited to just scalar data! In the real life we have to add identifiers, arrays for IN operator, arrays for INSERT and UPDATE queries.

So - we need many different types of data formatting. Thus, we need the way to tell the driver how to format this particular data. Conventional prepared statements use toilsome and repeating bind_* functions. But there is a way more sleek and useful way - to set the type along with placeholder itself. It is not something new - well-known printf() function uses exactly the same mechanism. So, I hesitated not to borrow such a brilliant idea.

To implement such a feature, no doubt one have to have their own query parser. No problem, it's not a big deal. But the benefits are innumerable. Look at all the questions on Stackoverflow where developers trying in vain to bind a field name. Voila - with identifier placeholder it is as easy as adding a field value:

$field $_POST['field'];
$value $_POST['value'];
$sql   "SELECT * FROM table WHERE ?n LIKE ?s";
$data  $db->query($sql,$field,"%$value%");

Nothing could be easier!

Of course we will have placeholders for the common types - strings and numbers. But as we started inventing new placeholders - let's make some more!

Another trouble in creating prepared queries - arrays going to IN operator. Everyone is trying to do it their own way but the type-hinted placeholder makes it as simple as adding a string:

$array = array(1,2,3);
$data  $db->query("SELECT * FROM table WHERE id IN (?a)",$array);

Same goes for such toilsome queries like INSERT and UPDATE.

And, of course, we have a set of helper functions to turn type-hinted placeholders into real brilliant, making almost every call to database as simple as 1 or 2 lines of code for all the regular real life tasks.

This class has been designed to serve 3 main purposes:

Unfortunately, none of the PHP's bundled libraries serve a purpose of the database wrapper, nor provide sufficient safety. Writing API calls in the application code is messy and toilsome. So, we need a database wrapper class, to do all the dirty job. Yet we don't want a gargantuan framework for a such simple task as running queries against database.

There are some things to keep in mind before we start:

  1. No doubts, first and foremost purpose of this class is safety and protection from SQL injections. But to I have to st most of people take injections wrong. They take it as a self. while it's just a side effect. side effect of improperly formatted query.

Related articles: