I would like to build a PHP script that will validate an SQL query, but does not execute it. Not only should it validate syntax, but should, if possible, let you know if the query can be executed given the command that is in the query. Here's Pseudocode of what I would like it to do:
<?php
//connect user
//connect to database
//v_query = $_GET['usrinput'];
if(validate v_query == true){
echo "This query can be executed";
}
else{
echo "This query can't be executed because the table does not exist.";
}
//disconnect
?>
Something like this. I want it to simulate the query without it executing it. That's what I want and I can't find anything on this.
An example of why we wouldn't want the query to be executed is if the query adds something to a database. We just want it to simulate it without modifying the database.
Any links or examples would be greatly appreciated!
You could try this library: http://code.google.com/p/php-sql-parser/. I've not used it yet so I can't guarantee it but the code looks like it will be able to tell the difference between valid and invalid SQL.
Another option could be to use transactions if your SQL variant allows it. A transaction would allow you to execute the SQL and then cancel it afterwards reversing any damage that was done. I think I would prefer option 1 though.
You can't just parse and validate the SQL, because you need to check for the existence of tables, validity of JOINs, etc. The only way possible to do a full integrative test is to actually run the query. You could wrap it in a transaction and then rollback. However, a poorly designed or malicious query could still bring your server to its knees or destroy data.
From MySQL 5.6.3 on you can use EXPLAIN for most queries
I made this and it works lovely:
function checkMySqlSyntax($mysqli, $query) {
if ( trim($query) ) {
// Replace characters within string literals that may *** up the process
$query = replaceCharacterWithinQuotes($query, '#', '%') ;
$query = replaceCharacterWithinQuotes($query, ';', ':') ;
// Prepare the query to make a valid EXPLAIN query
// Remove comments # comment ; or # comment newline
// Remove SET @var=val;
// Remove empty statements
// Remove last ;
// Put EXPLAIN in front of every MySQL statement (separated by ;)
$query = "EXPLAIN " .
preg_replace(Array("/#[^
;]*([
;]|$)/",
"/[Ss][Ee][Tt]\s+\@[A-Za-z0-9_]+\s*=\s*[^;]+(;|$)/",
"/;\s*;/",
"/;\s*$/",
"/;/"),
Array("","", ";","", "; EXPLAIN "), $query) ;
foreach(explode(';', $query) as $q) {
$result = $mysqli->query($q) ;
$err = !$result ? $mysqli->error : false ;
if ( ! is_object($result) && ! $err ) $err = "Unknown SQL error";
if ( $err) return $err ;
}
return false ;
}
}
function replaceCharacterWithinQuotes($str, $char, $repl) {
if ( strpos( $str, $char ) === false ) return $str ;
$placeholder = chr(7) ;
$inSingleQuote = false ;
$inDoubleQuotes = false ;
for ( $p = 0 ; $p < strlen($str) ; $p++ ) {
switch ( $str[$p] ) {
case "'": if ( ! $inDoubleQuotes ) $inSingleQuote = ! $inSingleQuote ; break ;
case '"': if ( ! $inSingleQuote ) $inDoubleQuotes = ! $inDoubleQuotes ; break ;
case '\\': $p++ ; break ;
case $char: if ( $inSingleQuote || $inDoubleQuotes) $str[$p] = $placeholder ; break ;
}
}
return str_replace($placeholder, $repl, $str) ;
}
It wil return False if de query is OK (multiple ; separated statements allowed), or an error message stating the error if there is a syntax or other MySQL other (like non-existent table or column).
KNOWN BUGS: