PHP有没有一种方法可以在不执行SQL语法的情况下对其进行验证? [英] Is there a way for PHP to validate an SQL syntax without executing it?

问题描述

我想构建一个PHP脚本来验证SQL查询，但不执行它.它不仅应验证语法，而且还应在可能的情况下让您知道是否可以通过查询中的命令来执行查询.这是我想要执行的操作的伪代码:

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!

推荐答案

从MySQL 5.6.3开始，您可以对大多数查询使用EXPLAIN

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) ;
 }

如果查询成功(返回多个；允许使用分隔的语句)，则返回False；如果存在语法或其他MySQL其他语法(例如，不存在的表或列)，则会返回一条错误消息，指出错误.

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小提琴

已知错误:

• 带有行号的MySQL错误:大多数行号将不匹配.
• 不适用于除SELECT，UPDATE，REPLACE，INSERT，DELETE之外的MySQL语句

这篇关于PHP有没有一种方法可以在不执行SQL语法的情况下对其进行验证?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！