Escaping mysql search string php

Question

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?

score 2 · Accepted Answer · edited May 23 '17 at 12:13

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?

Escaping mysql search string php

1 Answers1