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!

I'm curious about what the use case is here. If the query is invalid, SQL is going to return an error anyway... really the only thing you avoid by doing it in-script is the network connection time. But you loose that benefit in the overhead created by PHP having to parse the query. (DB server can parse it much faster than PHP can.) All this to say, why do you need this? — Matt Farmer, Nov 08 '11 at 03:23
HI Matt, thanks for taking a moment review my question. I'm writing an app that will generate PHP scripts around the query. While the user is writing the query, he or she would like to check if the query is valid, you know make sure that the row exists and of course the syntax, but not necessarily execute the query. 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. — Philll_t, Nov 08 '11 at 03:35
Hi Ignacio, thank you for taking the time as well. The query will vary depending on the user's input. — Philll_t, Nov 08 '11 at 03:35

Roemer · Accepted Answer · 2022-07-05T23:51:22.517

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 ;
    $inBackQuotes = false ;
    for ( $p = 0 ; $p < strlen($str) ; $p++ ) {
        switch ( $str[$p] ) {
            case "'": if ( ! $inDoubleQuotes && ! $inBackquotes ) $inSingleQuote = ! $inSingleQuote ; break ;
            case '"': if ( ! $inSingleQuote && ! $inBackquotes ) $inDoubleQuotes = ! $inDoubleQuotes ; break ;
            case '`': if ( ! $inSingleQuote && ! $inDoubleQuotes ) $inBackquotes  = ! $inBackquotes ; break ;
            case '\\': $p++ ; break ;
            case $char: if ( $inSingleQuote || $inDoubleQuotes || $inBackQuotes) $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

score 2 · Answer 2 · answered Nov 08 '11 at 03:37

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.

answered Nov 08 '11 at 03:37

Godwin

9,739
6
40
58

I came across `SET NOEXEC ON` Sounds kinda what I'm looking for. Let me do a little more research on it. – Philll_t Nov 08 '11 at 04:53
Note that some SQL statements (like table changes) auto-commit the transaction, so that could be dangerous. – Black Mantha Nov 10 '21 at 10:45

score 2 · Answer 3 · answered Nov 08 '11 at 03:54

2

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.

answered Nov 08 '11 at 03:54

sbeam

4,622
6
33
43

I came across `SET NOEXEC ON` Sounds kinda what I'm looking for. Let me do a little more research on it. – Philll_t Nov 08 '11 at 04:53

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

3 Answers3

Linked