PHP class for convenient and safe work with MySQL

After writing an article about protection against injections, I took up writing a class that implements the ideas contained in it.
Or rather, since the key functionality was already used in the framework of the working framework, I started to allocate it into an independent class. Taking this opportunity, I want to thank the members of PHPClub for their help in correcting a few critical errors and helpful comments. Below I will try to describe the main features, but first a small
In a nutshell, a class is built around a set of helper functions that allow you to perform most database operations on a single line, while providing (unlike standard APIs) complete protection against SQL injections, implemented using an extended set of placeholders that protect any data types which may fall request.
The class is based on three basic principles:

1. 100% protection against SQL injection
2. At the same time, the protection is very easy to use, making the code shorter, not longer
3. Universality, portability and ease of development

I will dwell a little more on each of the points.

Security

provided by the very two rules that I formulated in the article:

1. Any - without exception! - dynamic elements are included in the request only through placeholders.
2. All that fails to substitute through placeholders is first driven through the white list.

Unfortunately, standard libraries do not provide complete protection against injections, protecting only the strings and numbers using the prepared statements.
Therefore, in order to make the protection complete, we had to abandon the obviously limited concept of prepared statements in favor of a broader concept - placeholders. And the placeholders are typed (this thing is known to all of us by the printf () family of functions:% d is the placeholder, which tells the parser how to handle the substituted value, in this case, as an integer). Innovation was so successful that at once solved many problems and greatly simplified the code. I will write more about typed placeholders below.
Support for filtering by whitelists is provided by two functions, which are somewhat far-fetched, but nevertheless necessary.

Convenience and brevity of the application code

Here, I was also greatly helped by typed placeholders, which made it possible to make function calls one-line, passing immediately both the request and the data for it. Plus a set of helpers resembling those in PEAR :: DB functions that immediately return a result of the desired type. All helpers are organized according to the same scheme: one mandatory parameter is passed to the function - a request with placeholders, and as many optional parameters, the number and order of which must match the number and order of location of the placeholders in the request. The functions of the Ind family use another mandatory parameter — the name of the field by which the returned array is indexed.
Based on my experience, I came to the following set of returned values ​​(and, as a result, helpers):

• query () - returns mysqli resource. Can be used traditionally, with fetch (), etc.
• getOne () - returns a scalar, the first element of the first line of the result.
• getRow () - one-dimensional array, first row of result
• getCol () - one-dimensional array of scalars - table column
• getAll () is a two-dimensional array indexed by numbers in order
• getInd () is a two-dimensional array indexed by the values ​​of the field specified by the first parameter
• getIndCol () is an array of scalars, indexed by the field from the first parameter. Indispensable for compiling dictionaries of the form key => value

As a result, most of the calls to the database are reduced to one or two line structures (instead of 5-10 with the traditional approach):

$data = $db->getAll("SELECT * FROM ?n WHERE mod=?s LIMIT ?i",$table,$mod,$limit); 

In this code there are only necessary and significant elements, but there is nothing superfluous and repetitive. All the offal is neatly tucked inside the class: the getAll () helper allows you to immediately get the desired result without writing cycles in the application code, and typed placeholders allow you to safely add any type of dynamic elements to the query without setting the bindings (bind_param) manually. Extra DRY code! In cases of use of placeholders? A and? U, the difference in the amount of code becomes even greater:

 $data = $db->getAll("SELECT * FROM table WHERE category IN (?a)",$ids); 

Universality and ease of learning

stand on three pillars:

1. A very small API - half a dozen placeholders and as many helpers.
2. We are working with good old SQL, which does not need to be re-learned.
3. At first glance, an inconspicuous, but incredibly useful function parse (), which was originally intended only for debugging, but eventually grew to a key element in the preparation of complex queries.

As a result, all complex queries are collected in the old-fashioned way - for example, in a cycle - but at the same time, while observing all safety rules!
I will give a small example (more complicated examples can be found in the documentation at the link below the article):
It is quite often the case when we need to add a condition to the query if there is a variable

 $sqlpart = ''; if (!empty($var)) { $sqlpart = $db->parse(" AND field = ?s", $var); } $data = $db->getAll("SELECT * FROM table WHERE a=?i ?p", $id, $sqlpart); 

Here it is important to note a few points.
First, since we are not bound by the native API, no one forbids us to parse not the entire request, but only a part of it. This turns out to be super-convenient for requests that are assembled according to some logic: we only parse part of the request, and then it is inserted into the main request through a special “idle” placeholder to avoid re-parsing (and follow the rule “any elements are substituted only placeholder ”).
But unfortunately, this is the weak point of the whole class. Unlike all other placeholders (which, even when used incorrectly, never lead to an injection), incorrect use of the placeholder? P can lead to it.
However, protection from a fool would greatly complicate the class, but it still would not protect against stupid variable insertion into the query string. So I decided to leave it as it is. But if you know a way to solve this problem without too much over-engineering, I would be grateful for the ideas.
')
Nevertheless, in the end we got a powerful and easy query generator, which more than justifies this small drawback.
Powerful because we are not limited to the syntax of the four-builder, "SQL written in PHP" - we write pure SQL.
Easy because the entire query design API consists of half a dozen placeholders and the parse () function
Here is my favorite example - pasting using Mysql functions

 $data = array('field'=>$value,'field2'=>$value); $sql = "INSERT INTO table SET ts=unix_timestamp(), ip=inet_aton(?s),?u"; $db->query($sql, $ip, $data); 

On the one hand, we keep the SQL syntax, on the other - we make it safe, and on the third, we reduce the amount of code.

Learn more about typed placeholders

First, answer the question, why placeholders in general?
This, in general, is already a common place, but, nevertheless, I repeat - any dynamic data should get into the query only through placeholders for the following reasons:

• the most important thing is security. By adding a variable cherz placeholder, we can be sure that it will be correctly formatted.
• formatting locality. This is no less important point. First, the data is formatted just before it hits the query, and does not affect the original variable, which can then be used elsewhere. Secondly, the data is formatted exactly where it is needed, and not before the script starts working, as with magic quotes, and not in ten possible places of code by several developers, each of whom can rely on the other.

Developing this concept further, we come to the idea that paceholders must be typed . But why?
Here I would like to briefly stop and trace the history of the development of programmer thought in the field of protection from injections.
At first there was chaos - no protection at all, we shove everything as it is.
Then it became not much better, with the paradigm of “searching for everything that came into the script from the user” and culminating in the form of magic quotes.
Then the best minds came to the conclusion that it is correct to speak not about screening, but about formatting. Since the formatting is not always reduced to one iskapeyngu. This is how the quote () method appeared in PDO, which did complete line formatting - not only escaped special characters in it, but also enclosed it in quotes without relying on the programmer. As a result, even if the programmer used this function out of place (for example, for a number), the injection still did not pass (and in the case of bare escaping via mysql_real_escape_string, it easily passes if we put the number in the request without enclosing it in quotes ). Being used to format the identifier, this function led to an error at the development stage, which prompted the author of the code that he was a little wrong.
Unfortunately, the PDO authors stopped at this point, since the idea that the query only needs to be formatted is still firmly seated in the heads of the developers. But in fact, in the query there are much more elements of various types. And everyone needs their own type of formatting! That is, the only quote () method doesn’t suit us - you need a lot of different quotes. And not as an exception, “here you are quoteName ()”, but as one of the main concepts: each type has its own format. Well, since there are a lot of formatting types, the type must be specified somehow. And a typed placeholder is best suited for this.

In addition, a typed placeholder is VERY convenient!
First, because it becomes unnecessary a special operator to bind a value to a placeholder (but it is still possible to specify the type of the transmitted value!)
Secondly, since we invented a typed placeholder - we can stick a huge amount of these placeholders to solve a variety of routine tasks of composing SQL queries.
First of all, we will make a placeholder for identifiers - we desperately lack it in real life, and not in the imagination of the standard API, by the authors. As soon as the developer is faced with the need to dynamically add the name of the field to the request, everyone starts to pervert in their own way, who is in the forest, who is on firewood. Here, everything is unified with the rest of the query elements, and adding an identifier becomes as simple as adding a string. But at the same time, the identifier is not formatted as a string, but in accordance with its own rules - is enclosed in reverse quotes, and inside these quotes are escaped by doubling.
Further more. The next headache of any developer who has ever tried to use standard prepared statements in real life is the IN () operator. Voila, we have a placeholder and for this operation! Array substitution becomes no more difficult than any other elements, plus it is unified with them - no separate functions, only the letter in the placeholder changes.
In the same way we do the placeholder for SET. I won’t hold back and demonstrate how simple the code becomes for such a confused query as INSERT ... ON DUPLICATE:

 $data = array('offers_in' => $in, 'offers_out' => $out); $sql = "INSERT INTO stats SET pid=?i,dt=CURDATE(),?u ON DUPLICATE KEY UPDATE ?u"; $db->query($sql,$pid,$data,$data); 

Currently, the class supports 6 types of placeholders

• ? s ("string") - strings (as well as DATE, FLOAT and DECIMAL).
• ? i ("integer") - integers.
• ? n ("name") - the names of fields and tables
• ? p ("parsed") - to insert parts of the request already processed
• ? a ("array") - a set of values ​​for IN (a string of the form 'a', 'b', 'c')
• ? u ("update") - a set of values ​​for SET (a string of the form `field` = 'value',` field` = 'value')

That is quite enough for my tasks, but this set can always be expanded with any other placeholder, for example, for fractional numbers. I don’t see any point in making a separate placeholder for NULL - you can always enter it directly into the query.
I decided not to do automatic translation of PHP-NULL to SQL-YUSKY NULL. Perhaps this will complicate the code a little (in those rare cases when it is needed), but it will reduce its ambiguity.

By the way, as many may have noticed, this class is in many ways reminiscent of Dmitry Koterov’s DbSimple library. But I have fundamental differences with some of the ideas embedded in it.
Firstly, I am against any magic, when the same function can return a different result depending on the type of data transferred. This, perhaps, slightly simplifies writing, but at the same time makes it extremely monotonous to maintain and debug the code. Therefore, in my class, all magic is minimized, and all operations and data types are always written explicitly.
Secondly, in DbSimple, a little, in my opinion, overcomplicated syntax. On the one hand, braces are a great idea. On the other hand, why bother if we have all the power of PHP at our disposal? Therefore, I decided to go the other way and Westo “internal” - obviously limited - logic introduced the “external”, limited only by the PHP syntax. The main thing is that any dynamic elements get into the request only through placeholders, and the rest depends only on the developer's imagination (and the function parse ()).

Class code is available on Github, github.com/colshrapnel/safemysql/blob/master/safemysql.class.php
Cheat sheet with the main commands and examples: phpfaq.ru/misc/safemysql_cheatsheet_ru.pdf
A good idea of ​​the possibilities can be found on the sample documentation page (unfortunately, not yet finished), phpfaq.ru/safemysql
There are also answers to frequently asked questions, such as “why don't you use your native prepared statements?” And so forth.
Nevertheless, I will be glad to answer any questions in the comments, as well as improve on your comments both the class itself and this article.