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

Question

This question already has an answer here:

• How can I prevent SQL injection in PHP? 28 answers

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>

Answer 1

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?