Phantom SQL Queries

Take a look at the PHP code:

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

Such code may provoke an UPDATE or INSERT SQL query, or it may not provoke if identical data is already installed in the database, which is why this functionality is called Phantom SQL Queries. A similar functionality is usually present in most CRMs, but let's look at 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 necessary) algorithm proceeds in three stages: reading data from the database, changing data, possibly multiple times, and generating real SQL queries to update the data in the database. Let's figure out the details ...

In total, you must have three methods, one for each stage. I will give the code of the first method that unpacks the data read from the database 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];
    }

Method input parameters:

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

Query templates can be of two types, based on sql (..) or sqlf (..) functions. I will give the second code completely, since it, when used correctly, guarantees the impossibility of SQL injection, it is fast and quite 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 sql () function, due to the relatively high speed of operation. 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 is 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 the magic methods __get () and __set (). By the prefix v_, the class understands that it is writing to 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 organizing the functional are SKY :: save (..) and SKY :: here (..). The first stores the data in an array, and the second generates and performs a real query in the database.

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

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

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

Advantages of the described functionality

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

Disadvantage

For ghost SQL variables, you cannot “screw” indexes; most likely, you cannot make LOCK TABLE or use other advanced MySQL features.

More information can be found on the SKY project website.