Each time phpMyAdmin transmits a query, the MySQL server interprets it and provides feedback. The syntax of the query must follow MySQL rules, which are not the same as the SQL Standard. However, conforming to SQL Standard ensures that our queries are usable on other SQL implementations.
A free external service, the Mimer SQL Validator, is available to us, thanks to Mimer Information Technology AB. It validates our query according to the Core SQL-99 rules and generates a report. The Validator is available directly from phpMyAdmin, and its home page is located at http://developer.mimer.com/validator/index.htm.
Note
For statistical purposes, this service anonymously stores on its server, the queries it receives. When storing the queries, it replaces database, table, and column names with generic names. Strings and numbers that are part of the query are replaced with generic values so as to protect the original information.
This Validator is available as a SOAP service. Our PHP server must have XML, PCRE, and SOAP support. SOAP support is offered by either a PHP extension or by a PEAR module. If we choose the PEAR way, the following command (executed on the server by the system administrator) installs the modules we need:
pear install Net_Socket Net_URL HTTP_Request Mail_Mime Net_DIME SOAP
If we have problems with this command due to some of the modules being in a beta state, we can execute the following command, which installs SOAP and other dependent modules:
pear -d preferred_state=beta install -a SOAP
Some parameters must be configured in config.inc.php. Setting $cfg['SQLQuery']['Validate'] to TRUE enables the Validate SQL link.
We should also enable the Validator itself (as other validators might be available on future phpMyAdmin versions). This is done by setting $cfg['SQLValidator']['use'] to TRUE.
The Validator is accessed with an anonymous Validator account by default, as configured using the following commands:
$cfg['SQLValidator']['username'] = ''; $cfg['SQLValidator']['password'] = '';
Instead, if Mimer Information Technology has provided us with an account, we can use that account information here.
There are two kinds of reports returned by the Validator—one if the query conforms to the standard, and the other if it does not conform.
We will try a simple query: SELECT COUNT(*) FROM book. As usual, we enter this query in the query box and send it. On the results page, we now see an additional link— Validate SQL as shown in the following screenshot:
Clicking on Validate SQL produces a report as shown in the following screenshot:
We have the option of clicking on Skip Validate SQL to see our original query.
Let us try another query, which works correctly in MySQL: SELECT * FROM book WHERE language = 'en'. Sending it to the Validator produces a report as shown in the following screenshot:
Each time the Validator finds a problem, it adds a message such as {error: 1} at the point of the error and adds a footnote in the report. In this query, the language column name is non-standard. Hence, the Validator tells us that it was expecting an identifier at this point. Another non-standard error is reported about the use of a LIMIT clause, which was added to the query by phpMyAdmin.
Another case is that of the backquote. If we just click on Browse for the book table, phpMyAdmin generates SELECT * FROM `book`, enclosing the table name with backquote. This is MySQL's way of protecting identifiers, which might contain special characters, such as spaces, international characters, or reserved words. However, sending this query to the Validator shows us that the backquotes do not conform to standard SQL. We may even get two errors, one for each backquote.