2013-04-07 12:40
浏览 33

转义mysql搜索字符串php [复制]

This question already has an answer here:

I have a question regarding best practices involving a search form with PHP/MySql

Consider the following:

  • A search form to search for book titles
  • A jQuery / AJAX request to "auto-suggest" titles
  • Need to escape and how?

The mysql user which connects to the database only has the SELECT privilege at the moment but I might add the INSERT privilege in the future (thus potential for injections).

The search form is simple, such as this:

<form id="search" method="GET" action="/search/">
  <input type="text" value="" id="s" name="s" />

The form sends via GET to search.php?s=Search Query. Once there, the PHP file is something like the following:


  $s = $_GET['s']; // the search request

  $search = new Search($s); // creates new search object and sends the $s query

  echo $search->output;  // returns results


My Search class has the following:

class Search {

  // Database stuff omitted

 $stmt->bindParam(':search', $this->query, PDO::PARAM_STR)
 $res = $stmt->fetchAll(PDO::FETCH_ASSOC);
 $this->output = $res;


My sql query is this: SELECT booktitle FROM books WHERE booktitle LIKE '%:search%'

What problems might I get into? Do you have any suggestions as to what needs to be escaped and where? Do you see potential problems with my setup? Concerns such as sql injections?


图片转代码服务由CSDN问答提供 功能建议

此问题已经存在 这里有一个答案:

  • 如何在PHP中阻止SQL注入? 28 answers

    我对涉及使用PHP / MySql的搜索表单的最佳做法有疑问


    • 搜索图书标题的搜索表单
    • jQuery / AJAX请求“自动建议”标题
    • 需要转义以及如何转义?

      连接数据库的mysql用户 目前只有 SELECT 权限,但我可能会在将来添加 INSERT 权限(因此可以进行注入)。

      Ť 搜索表单很简单,例如:

       &lt; form id =“search”method =“GET”action =“/ search /”&gt; 
      &lt;  input type =“text”value =“”id =“s”name =“s”/&gt; 
      &lt; / form&gt; 

      表单通过 GET到 search.php?s =搜索查询。 在那里,PHP文件如下所示:

       $ s = $ _GET ['s'];  //搜索请求
       $ search = new Search($ s);  //创建新的搜索对象并发送$ s查询
       echo $ search-&gt;输出;  //返回结果


        class 搜索{
       $ stmt-&gt; bindParam(':search',$ this-&gt; query,PDO :: PARAM_STR)
       $ stmt-&gt; execute; 
        $ res = $ stmt-&gt; fetchAll(PDO :: FETCH_ASSOC); 
       $ this-&gt; output = $ res; 

      我的SQL查询是这样的: SELECT booktitle FROM books WHERE booktitle LIKE'%:search%'

      我可能遇到什么问题? 你有什么需要逃脱的建议吗? 您是否看到我的设置存在潜在问题? 诸如sql注入等问题?

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • dpswo40440 2013-04-07 13:51

    Parameters are automatically escaped in prepared PDO statements, you're doing it right.

    Just be noticed that you don't need the quotes in your query:

    $stmt = $myPDO->prepare("SELECT booktitle FROM books WHERE booktitle LIKE :search");
    $stmt->bindParam(':search', "%".$this->query."%", PDO::PARAM_STR);

    Or even simpler:

    $stmt = $myPDO->prepare("SELECT booktitle FROM books WHERE booktitle LIKE ?");
    $stmt->execute( array("%".$this->query."%") );

    More info: Are PDO statements automatically escaped?

    打赏 评论

相关推荐 更多相似问题