Is there a way for PHP to validate an SQL syntax without executing it?

Question

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!

Answer 1

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("/#[^\n\r;]*([\n\r;]|$)/",
                              "/[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).

PHP Fiddle

KNOWN BUGS:

• MySQL errors with linenumbers: the linenumbers mostly will not match.
• Does not work for MySQL statements other than SELECT, UPDATE, REPLACE, INSERT, DELETE

Answer 2

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.