Architecture of Adminer

This article was published in the August 2009 issue of php|architect.

Adminer is a feature complete MySQL administration tool with the aim of compactness. The whole application consists of a single file as small as possible. This request influences the code design of Adminer and this article describes the way of reaching that target.

As a freelance PHP programmer, I often get a task to check some problem or implement new functionality at a strange site. I usually get only FTP credentials without further documentation about the application or about the client's hosting. If the task consists of some database manipulation then I have to discover or modify the database structure without any tools or remote access to the database server. I can usually install phpMyAdmin in the web space but it currently consists of 11 MB in 666 files and I became quickly tired of uploading this package with every new task (and removing it after the task completion).

Therefore, I started to create a compact alternative of phpMyAdmin consisting of a single file easy to upload and delete anywhere. Initially, it was a simple tool with basic commands such as the table structure, select, SQL command and export. Later, I have added more and more features so now it is a feature complete MySQL administration tool including even the MySQL 5.1 features (events and table partitioning). Adminer implements some features (such as triggers or stored routines) in a more comfortable way than the bigger competitor but the basic goal of a single file is still valid.

Note: The project's original name was phpMinAdmin. I chose that name as a joke to emphasize the fact that phpMinAdmin is a minimalistic version of the famous PHP MySQL tool. (By the way do you use Lynx or Links?) This name was however confusing and invoked the feeling that phpMinAdmin is only a weaker alternative of phpMyAdmin.

Compilation

When the size of adminer.php started to grow then I have realized that it would be wise to split the file for manageable development. Nevertheless, I still wanted the all-in-one file so I have created a compilation script that joins the files back together.

Because everything lies in one file, Adminer cannot use the usual URL alter.php?table=X and the action must be passed in the query string. Instead of common ?action=alter&table=X, I have used the more compact alternative ?alter=X. The central script is then a sequence of if (isset($_GET["..."])) instead of one big switch ($_GET["action"]). There is an include of the specific functionality inside the if and these includes are compiled to a one file in the build process:

<?php
function include_file($match) {
    $file = file_get_contents($match[1]);
    $token = end(token_get_all($file));
    $php = (is_array($token) && in_array($token[0], array(T_CLOSE_TAG, T_INLINE_HTML)));
    return "?>\n$file" . ($php ? "<?php" : "");
}
$file = preg_replace_callback('~include "([^"]+)";~', 'include_file', $file);
?>

This code is not universally usable because it finds only the include "" variant. It would be necessary to process the file with token_get_all function and find all include variants. The _once variants would be more difficult. There can be also a global level return.

Adminer uses also some external files – style sheet, shortcut icon and couple of images. There are several possible approaches of accessing these files:

1. They can be integrated in the main HTML code – style sheet by <style> tag, images by utilizing the data: protocol. The problem with this approach is that Internet Explorer < 8 does not support this protocol. The second problem is that the browser would need to transfer these data with each page repeatedly.
2. The files can be downloaded from an external server. It would be problematic if the server is unreachable or if the computer running Adminer is without the Internet connection. This approach is however used with syntax highlighting of SQL queries, which is an optional feature – Adminer uses the JavaScript Syntax Highlighter JUSH for this task.
3. The files can be integrated in the source code and a special parameter would serve them. Did you ever notice the PHP logo in the output of phpinfo? PHP downloads it from the local server with a special query string ?=PHP…. Adminer uses a similar approach and saves the files Base64 encoded in the source code (the encoding is not necessary but it simplifies the script editing in common text editors). This approach allows the HTTP caching of the external files which Adminer utilizes.

Note: PHP 5.3 comes with a support for PHP archives through the phar extension. With this extension, it is possible to pack several files to one archive and access the files inside the archive from PHP. Usage of the webPhar method allows packing most of the PHP applications to one archive and there are instructions for creating phar version of phpMyAdmin in the PHP manual. Adminer does not use this extension and works with PHP >= 4.3 and PHP >= 5.0.

Minification

The compilation script also minifies the code – it removes comments and trims white space. The minification code is based on a work of David Grudl who uses it for his brilliant libraries Texy, dibi and Nette. This function is more effective than the internal function php_strip_whitespace.

<?php
function php_shrink($input) {
    $set = array_flip(preg_split('//', '!"#$&\'()*+,-./:;<=>?@[\]^`{|}'));
    $space = '';
    $output = '';
    foreach (token_get_all($input) as $token) {
        if (!is_array($token)) {
            $token = array(0, $token);
        }
        if ($token[0] == T_COMMENT || $token[0] == T_WHITESPACE) {
            $space = "\n";
        } else {
            if (isset($set[substr($output, -1)]) || isset($set[$token[1][0]])) {
                $space = '';
            }
            $output .= $space . $token[1];
            $space = '';
        }
    }
    return $output;
}
?>

Current version also shortens the names of user variables and functions. It finds the variables and functions in the source code with token_get_all function and replaces them by shorter identifiers. The shortening process skips the internal PHP variables and functions. It would be possible to shorten them too but it would be more complicated and it would have a performance penalty. Some PHP variables have their super global feature so it would be necessary to globalize their aliases inside the functions.

The internal functions can be enclosed in envelope functions with shorter names. This approach is however problematic because the PHP functions can have optional parameters without the default values (example of this function is fwrite which detects the number of passed arguments instead of their values). Universal function func_get_args does not work with the references on the contrary.

Alternative approach would be to define a variable for an internal function (e.g. $fw = 'fwrite') and call the function through this variable: $fw($fp, $string). Adminer however does not use neither approach because it would slow the application down.

The minification trims white space also from CSS and PHP version of JSMin minifies the JavaScript code too.

The file would be much smaller if it would be compressed. PHP supports several compression algorithms but only through an extension. I was thinking about a simple decompression function written in PHP which will unpack the rest of the file but it will need to eval the decompressed code and everybody knows that eval is evil (one reason is that it is not compatible with PHP accelerators). Therefore, the code is not compressed but only minified.

Translations

I develop Adminer in English. There is also a Czech version (which is my native language) from the beginning and Adminer is currently available in 11 languages. All messages are served by a simple function, which checks the current language and returns the correspondent translation.

A simple function detects the language from the Accept-Language header:

<?php
function acceptable_language($translations) {
    $accept_language = array();
    preg_match_all('~([-a-z]+)(;q=([0-9.]+))?~', strtolower($_SERVER["HTTP_ACCEPT_LANGUAGE"]), $matches, PREG_SET_ORDER);
    foreach ($matches as $match) {
        $accept_language[$match[1]] = (isset($match[3]) ? $match[3] : 1);
    }
    arsort($accept_language);
    foreach ($accept_language as $lang => $q) {
        if (isset($translations[$lang])) {
            return $lang;
        }
        $lang = preg_replace('~-.*~', '', $lang);
        if (!isset($accept_language[$lang]) && isset($translations[$lang])) {
            return $lang;
        }
    }
    return false;
}
?>

The caller of this function saves the language to a cookie after the first detection. A user can change the language anytime while staying on the same page. Saving the page language to a cookie is a very bad practice on a public site because the search engines then can index only one version of the page. It is however acceptable in Adminer because the search engines do not index the pages and the main content of the page (e.g. the table structure) is the same in all languages. The reward for storing the language in a cookie is a simpler URL while it is still possible to enforce the language by passing the language identifier in the query string.

Translations are stored in a simple array where the key is a message identifier (which is an English version of the message and it is used if the translation does not exist) and the value is a string with the translation. The value is more interesting with messages containing a number (e.g. “1 row” or “2 rows”). Most languages have different singular and plural form but some languages (e.g. Czech or Russian) use more plural forms depending on the number (e.g. Czech “1 pivo”, “2 piva” or “5 piv” for beers). The messages with numbers are stored in an array instead of string and Adminer contains a simple logic for each language choosing the right form depending on the number.

By default, Adminer comes with all languages. The compilation converts the translation array to values only and changes the identifiers to numbers to save some bytes so each translation takes about 4 KB. The compilation script is able to create also a single language version, which removes the translating function and the language detection completely. This file takes only 111 KB for the English version of Adminer 1.10.1.

Database extension abstraction

PHP allows connecting to a MySQL database through three extensions: mysql, mysqli and pdo_mysql. Adminer supports all of them and contains a simple abstraction layer for these extensions. The abstraction layer emulates the subset of mysqli and mysqli_result classes for all three extensions.

PDO support is little tricky because PDO uses exceptions for reporting connection errors. Adminer cannot use the usual try block to catch these exceptions because it runs also on PHP 4. Everything is in a single file so the PDO support cannot be conditionally included only in PHP 5. The solution is to use set_exception_handler function to process the connection errors. PHP 4 also does not support class constants on the syntax level so the source code uses the numerical values of the constants instead.

Security

The most important part of the security in Adminer is surprisingly the defense against Cross-site Request Forgery. CSRF allows an attacker to perform actions in the name of a logged-in user. Adminer stores the login information in a standard session so PHP can send the session identifier in a cookie and the defense against CSRF is necessary. Adminer sends a token with each form and checks this token before performing the operation of the form. A token protects even the logout action which is often forgotten in other web applications.

Very important is also the protection against Cross-site Scripting. Outputting the HTML code stored in the database could reveal sensitive information to the attacker so htmlspecialchars function escapes every output.

Adminer has also a protection against the SQL injection but it is not in the name of security – if the attacker already logs-in to the Adminer then she could execute any SQL code by the SQL command page. Therefore, the protection against the SQL injection is only a side effect of proper manipulation with the user input – a user can pass any data to the database. If Adminer detects the magic_quotes_gpc then it neutralizes this directive and uses a proper escaping function only when passing the data to MySQL.

<?php
if (get_magic_quotes_gpc()) {
    $process = array(&$_GET, &$_POST, &$_COOKIE, &$_REQUEST);
    while (list($key, $val) = each($process)) {
        foreach ($val as $k => $v) {
            unset($process[$key][$k]);
            if (is_array($v)) {
                $process[$key][stripslashes($k)] = $v;
                $process[] = &$process[$key][stripslashes($k)];
            } else {
                $process[$key][stripslashes($k)] = stripslashes($v);
            }
        }
    }
    unset($process);
}
?>

Calling of session_regenerate_id after the login prevents session fixation.

Every page of Adminer contains the robots: noindex meta information to hide the page from search engines.

Login form

Adminer saves the database credentials to a session and checks them on every page. If the database cannot authorize the user then the login form is displayed directly on the page. This approach is better then redirecting the user to a login page for several reasons:

1. It saves one request, the login form is sent directly to the target page.
2. User can bookmark any page, which displays directly after the login. The redirection to a login form approach can achieve this too but it is much more complicated (the login form needs to get the returning URL, check it for validity and redirect back after the login because the Referer header is not reliable).
3. If the session expires before sending any application form then the posted data prepopulates the login form with the hidden fields.

<?php
function hidden_fields($process) {
    while (list($key, $val) = each($process)) {
        if (is_array($val)) {
            foreach ($val as $k => $v) {
                $process[$key . "[$k]"] = $v;
            }
        } else {
            echo '<input type="hidden" name="' . htmlspecialchars($key) . '" value="' . htmlspecialchars($val) . '" />';
        }
    }
}
hidden_fields($_POST);
?>

The sent files are also stored to hidden fields and there is a little layer serving the files from the $_FILES variable or from the posted hidden fields.

It is possible to log in without filling the form by passing the ?username= parameter. A user can use this feature in a secure environment where he authenticates by some other mean (e.g. on localhost without a remote access or with HTTP authentication). However, it is not possible to pass the password in a GET parameter which would be very insecure. Adminer uses the value of mysql.default_password configuration directive in this case. The demo application uses this approach to automatically log in the users.

Form processing

Adminer sends all action forms by the POST method. If the action is successful then it redirects the browser to a page showing the result with a confirmation message. This message is stored to a session variable after performing the action and the beginning of each HTML page processes this session variable. There is a theoretical chance that other Adminer page opened simultaneously in a different browser tab can catch the message and display it instead of the original page. However, the period between setting the message and displaying it is very short so this probability is low. Besides, the consequence of this coincidence would be small – the user would get informed about the result, just in a different tab.

An alternative to this approach would be to send a message ID or the message itself in the URL but it would clutter the browser history. In addition, the page refresh would maintain the message on the page, which is not desirable.

If the form action produces an error then the page directly displays the error message with the form prefilled by the sent data. The code does not redirect the browser in this case, which allows a user to refresh the page with the sent data if the error was only temporary (for example if it could be fixed in other browser tab).

Database schema

Adminer contains a simple interface for visualization of the database schema. It displays all tables with their columns. Colors differentiate the column types and arrows symbolize the table references. Tables can be moved around the screen with a mouse, which is achieved by JavaScript and CSS. The database schema page uses a cookie to save the table placement.

Export

Adminer supports SQL and CSV import and export. The most interesting variant of export is the one using the ALTER command. There is often a different database on the production and the development server. If you have made some changes in the database structure which should go to the production server during a release then dropping the old table and creating the new one is not an option because there are live data in this table. Adminer is able to generate a set of ALTER commands synchronizing the databases. It utilizes a stored procedure which explores the production database and alters it to conform to the development version. New tables are created as usual, old tables are dropped, missing fields and indexes are added.

Performance

Adminer always queries all data directly from the database with one exception. Getting the database list could take a very long time if there are many databases on the server even if the user has access only to couple of them. Because each page displays the database list then it is cached to a session variable. This variable is refreshed if a user creates or drops a database either from the user interface or by the SQL command.

Closing a session by session_write_close is a performance optimization with a little impact. The default PHP session handler locks the file with a session data, which prevents the web server to serve multiple requests of the same user at the same time. This is very important on sites using frames where it is a common situation. Adminer does not use frames but a user can still open several browser tabs with Adminer at the same time. Adminer writes all the session data and closes the session as soon as possible to allow the maximum concurrency.

Most users feel the smoothness of Adminer and one user even created the test suite, which measures the performance with comparison to phpMyAdmin:

• Performance of the same operations is 28% faster in average (2 × 23 tests).
• Start after login is 56% faster (2 × 4 tests).
• Finally, understanding of the source code was 100% faster.

Demonstration

The demo is one of the best ways to present the features of some product. I have created some sample database and made it accessible from the website through Adminer but users have started corrupting it quickly. I was thinking about periodically restoring the database but it would not work because in the meantime the database would be still corrupted. On the other side, users working with the database during the reset would be surprised what is happening. Restricting the actions would cripple the demo, so it was not an option either. Other possibility was to emulate the database access in a session for each user but it would be a very complex task and it will not present all the features of Adminer.

Finally, I have ended with creating a separate database for each user entering the demo. The script initializes this database by a sample data after login to the demo and destroys it after logout. A cron job drops the databases of users who did not log out. The demo user has powerful privileges to present the most features of Adminer so it was necessary to restrict the list of databases to disallow accessing databases of other users. Each MySQL user can also modify her password and users of the demo application quickly discovered it. Thus, the demo disables also this feature.

I did not want to create a separate version of Adminer just for the demo so I have made all customizations by auto_prepend_file. The prepended script restricts the database list, disallows changing of password, initializes the demo databases and drops them.

Tests and code coverage

Nearly all parts of the application have tests. I have created the tests in Selenium IDE, which is a very convenient Firefox plug-in for creating complex web applications tests. The main advantage of Selenium is that it can test the whole application – from PHP on the server side to HTML on the client side, it can test even the JavaScript interactions. Adminer works well without JavaScript but some features are more comfortable with the JavaScript enabled – for example adding a field in a table does not require sending the page to the server.

Selenium IDE can create the tests very easily. It is possible to just push the record button and work in a browser as usually. The recorded test can be modified afterwards so if you are checking some feature in the application then why not to record a test for it?

I was curious how much code is tested and I was interested in which parts of Adminer need tests that are more thorough. It can be find out by the code coverage. I have used the Xdebug extension, which has the ability to measure the code coverage. It is well suited for running unit tests because the code coverage is computed for the whole run of the tests. But the web application tests are different as they compose from several requests and the code coverage is computed for each request separately. The solution was quite easy – I have registered a shutdown function saving the code coverage to a session and joining it with the previous results.

The code coverage of Adminer is around 75 %, which is satisfactory. For comparison, the code coverage of PHP source codes is around 70 %. I was very glad that the tests exist when I refactored some parts of the application. I was quite sure that the refactoring did not break anything when all the tests have passed.

Version checker

There is a RSS feed with the project news and e-mail subscriptions for the new releases. Some users however just install the current version and forget about it. For this reason, the Adminer contains a version checker.

The version checker must be as unobtrusive as possible because sometimes the Adminer is run in an environment without the Internet connection or the server could be unreachable. So checking the current version from PHP code was not an option because it is blocking. Furthermore, an administrator can disable the remote connections in PHP by allow_url_fopen configuration directive. Thus, the version checker runs from JavaScript. A simple <script src> is blocking too (a user can work with the page but the page is still in the loading state) which is the same for images too. So the script is loaded in an onload event which is non-blocking. The server part is quite simple:

<?php
header("Content-Type: text/javascript");
if (version_compare($_GET["version"], $VERSION) < 0) {
    echo "document.getElementById('version').innerHTML = '$VERSION';\n";
}
echo "document.cookie = 'adminer_version=$VERSION';\n";
?>

If the current version is old then the script modifies the document to display the new version number. The client side checks the sent cookie so the communication with the server is only occasional.

Design

I am not an artist so the style sheet comes from freelance graphic designer Ondřej Válka. The design is simple and tidy as the whole Adminer so I like it. Other users however like more fancy designs so they created their own. The design can be easily integrated to the single file by own compilation but users like to test several designs so Adminer checks if the external file adminer.css exists. If it does exist then the script uses it instead of the integrated CSS.

Conclusion

This article is about the Adminer architecture, not about its features. However, it should be clear that Adminer supports all MySQL features from a simple table editing, through multiple-columns foreign keys, triggers, stored routines, export, user and processes management to the MySQL 5.1 events and table partitioning. There is of course a universal SQL command for non-standard tasks.

Adminer is not just a compact version of phpMyAdmin, it tries to be a fully competitive alternative for the MySQL administration and the small footprint is only a pleasant bonus.

About the author

Jakub Vrána is one of the authors of official PHP manual, and he uses MySQL for developing web applications in PHP. He also teaches the PHP and MySQL basics at Charles University and conducts commercial trainings. He is the founder of compact MySQL management tool Adminer. Jakub lives in Prague, Czech Republic.

Jakub Vrána, Adminer, 28.12.2009, comments: 24 (new: 0)

Comments

Diskuse je zrušena z důvodu spamu.

Navigace

PHP triky

• Archiv (704), English articles
• RSS, Atom, Atom diskusí
• O autorovi, o webu, Twitter

Kupte si mou knihu

Reklama

• Hledáte-li programátora nebo naopak sami programujete a nemáte do čeho píchnout, využijte služeb portálu nezávislých profesionálů Na volné noze.

Web běží na serveru

• Váš hosting

Články podle skupin

• Dobře míněné rady (111)
• Řešení problému (170)
• Výuka (100)
• Seznámení s oblastí (102)
• Ze zákulisí (69)
• Školení (31)
• Verze PHP (8)
• Osobní (46)
• Adminer (67)

Výběr článků

• Traverzování kolem stromu prakticky
• Vlastní pořadí řádků v tabulce
• Ukázka použití indexů
• Struktura stránek
• Unikátnost návštěvníka
• Vyhledávání v JavaScriptu
• Kontrola e-mailové adresy
• Vytvoření přátelského URL
• HTTP cachování
• Atomicita operací
• Vypnutí magic_quotes_gpc
• Ukládání souborů od uživatele
• Cross Site Scripting
• AJAX
• Přihlašování uživatelů
• MySQL 4.1 – kódování
• Vzájemné propojení souborů
• Diskuse s reakcemi
• Šablony
• HTTP metody GET a POST
• Escapování
• Ukládání hesel
• Využití unikátních klíčů v databázi
• Obrana proti SQL Injection

Nejnovější články

• Adminer 4.8.1
• Adminer 4.8.0
• Adminer 4.7.9
• Vypnutí chyby při přístupu k nedefinovanému prvku pole
• Users of Adminer 3.7.1 and older might have been hacked

Normy a manuály

• PHP
• MySQL
• HTTP
• Apache
• HTML, XHTML
• JavaScript

Další projekty

• Adminer
• NotORM
• JavaScript Syntax Highlighter
• php-initialized
• Čtyřka