Ghostly SQL queries

Take a look at the PHP code:

$user->v_useragent = 'coresky.agent'; 

Such code may provoke a SQL UPDATE or INSERT query, or may not provoke if identical data is already installed in the database, which is why this functionality is called Ghost SQL queries. Similar functionality is usually present in most CRM, but let's consider how it can be implemented without CRM. Phantom requests have the potential to be quite widely used in web applications, especially in terms of configurations. A typical (but not necessarily) algorithm takes place in three stages: reading data from a database, changing data, possibly repeated, and creating real SQL queries to update the data in the database. Let's get into the details ...

In total it is necessary to have three methods, one for each stage. The code of the first method that performs the unpacking of the data read from the database is given completely, since it is quite simple:

 class SKY //    coresky { ... static function &ghost($char, $original, $sql = '', $flag = 0) { SKY::$mem[$char] = [$flag, $flag & 4 ? null : $original, $sql, []]; if ($sql) trace('GHOST SQL: ' . (is_array($sql) ? end($sql) : $sql), false, 1); if ($original) foreach (explode("\n", unl($original)) as $v) { list($k, $v) = explode(' ', $v, 2); SKY::$mem[$char][3][$k] = unescape($v, true); } return SKY::$mem[$char][3]; } 

Input parameters of the method:
')

• $ char - one small letter of the English alphabet, indicates the type of ghost queries functionality, can be used in variable prefixes, as in the example above;
• $ original - the text content of the database cell (`mediumtext` for MySQL), where all variables of ghostly $ char queries are stored in bulk. An empty string can also be input;
• $ sql - query template that will be used to generate a real SQL query, for example, in the callback function for register_shutdown_function ();
• $ flag - flag. The preset value 0 is most commonly used;

Query templates can be of two types, based on the sql (..) or sqlf (..) functions. I will quote the second code completely, as it, if used correctly, guarantees the impossibility of SQL injection, it is quick and fairly simple:

 function sqlf() { # quick parsing, using printf syntax. No SQL injection! $in = func_get_args(); $tpl = array_shift($in); if ($pos = strpos($tpl, '$')) $tpl = preg_replace('/\$_(\w+)/', T_PREFIX . '$1', $tpl); $sql = vsprintf($tpl, array_map(function ($a) { if (!is_array($a)) return is_num($a) ? $a : escape($a); # escape ALL if not numeric return implode(', ', array_map(function ($v) { return is_num($v) ? $v : escape($v); # escape ALL if not numeric }, $a)); }, $in)); return sql(1, $sql); } 

Unfortunately, the sqlf () function is not universal in terms of compiling arbitrary SQL queries in terms of protection against injection. However, it exists in parallel with the universal function sql (), due to the relatively high speed of work. Templates for sqlf () are used for the case of working with only one mediumtext cell. The second template is used when you need to organize such pending queries for many columns of the table.

How does the processing of the specified PHP code at the very beginning of the article

The $ user variable contains a pointer to a USER object, which, in turn, has magic methods __get () and __set (). By the v_ class prefix, it understands that there is an entry in the `visitors` session table and calls the SKY :: save (..) method which saves the code in the SKY :: $ mem array. At the end of the script, the callback function for register_shutdown_function () is called, in which the actual SQL query in the database is actually performed (or not).

So, the other two methods that are necessary for the organization of the functional are SKY :: save (..) and SKY :: here (..). The first saves data in an array, and the second generates and executes the actual query in the database.

The coresky code (reusable framework or CMF code) uses 8 types of ghostly SQL:

• system configuration, which is stored in the database;
• visitor configuration;
• configuration of authorized users;
• admin system configuration;
• system configuration of console launches;
• three types of ghost SQL are used to organize the i18n utility;

As you can see, “ghostly SQL” is relevant for almost all web applications.

Advantages of the described functional

• There is no need to do ALTER TABLE ... (or INSERT new rows) to add new configuration variables as the application evolves. You can add new variables simply in PHP code, without changing the database structure;
• the functionality can reduce the number of queries to the database to one, for the case when writing to the same row of the same table occurs;

Disadvantage

For ghost SQL variables, it is impossible to “screw” indexes, most likely, you cannot make LOCK TABLE or use other advanced features of MySQL.

More information can be found on the SKY project website.