php - 在数据库选择查询中转义特殊字符

Question

如果名称包含特殊字符，我有以下查询不起作用:

 case 'byMovie':

  $items_per_page = 20;
  $offset = 0;
  $page_count = 0;

  include('Connection.php');
  $query1 = $conn->prepare("
SELECT DISTINCT s.starName
              , s.starImdbID
              , s.movieName
              , p.posterLink 
           FROM star_film as s 
           LEFT 
           JOIN star_Posters as p 
             ON s.starImdbID = p.starImdbID  
          WHERE s.movieName LIKE :q
");
  $query1->execute(array(':q' => '%' . $searchText . '%'));
  $row_count = $query1->rowCount();
  $page_count = (int)ceil($row_count / $items_per_page);
  $page = min($page_count, filter_input(INPUT_GET, 'page', FILTER_VALIDATE_INT, array(
           'options' => array(
           'default'   => 1,
           'min_range' => 1,
          ),
      )));
       if($page > $page_count) { //double check that request page is in range
           $page = 1;     // error to user, maybe set page to 1
       }

   $offset = ($page - 1) * $items_per_page;
   $query = $conn->prepare("SELECT DISTINCT s.starName, s.starImdbID, s.movieName, p.posterLink FROM star_film as s LEFT JOIN star_Posters as p ON s.starImdbID = p.starImdbID  WHERE s.movieName LIKE :q LIMIT " . $offset . "," . $items_per_page);
   $query->execute(array(':q' => '%' . $searchText . '%'));

break;
}

例如，如果电影名称是“鱼的故事”，它会向我显示此错误:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-20,20' at line 1' in /var/www/searchcast.php:90 Stack trace: #0 /var/www/searchcast.php(90): PDO->prepare('SELECT DISTINCT...') #1 {main} thrown in /var/www/searchcast.php on line 90

我尝试按照建议添加 mysql_real_escape_string(s.movi​​eName) here , 但它也不起作用。

有人可以帮助我吗？

谢谢

Answer 1

正如您在 documentation 中所读到的那样:

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants (except when using prepared statements).

我认为该错误与您使用的 offset 值有关(显然是 -20)。您必须想办法避免该数字为负数。

看到你的代码，当 $page = 0 转化为:

$offset = (0 - 1) * 20 = -20;

因此出现错误。当 $page 为 0 或类似值时，您可以强制其值为 1。

希望对您有所帮助。