dongmu4591
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" />
</form>

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

<?php

  $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)
 $stmt->execute;
 $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?

</div>

图片转代码服务由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文件如下所示:

       &lt;?php 
       
       $ s = $ _GET ['s'];  //搜索请求
       
       $ search = new Search($ s);  //创建新的搜索对象并发送$ s查询
       
       echo $ search-&gt;输出;  //返回结果
       
      ?&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);
    $stmt->execute();
    

    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?

    打赏 评论

相关推荐 更多相似问题