How to remove potentially bad data from PHP and PDO MySql

Question

I'm using MySQL like so for executing queries:

public function query($query, $params = array(), $format = array()) {

    if ( !is_array( $params ) ) return false;

    $dbh = parent::$dbh;

    if ( empty($dbh) ) return false;

    $stmt = $dbh->prepare($query);

    if ( !empty($format)) {
        $values = array_values($params);
        foreach ( $format as $key => $bind ) {
            switch ($bind) {
                case '%d':
                    $stmt->bindValue($key + 1, $values[$key], PDO::PARAM_INT);
                    break;
                case '%s':
                    $stmt->bindValue($key + 1, $values[$key], PDO::PARAM_STR);
                    break;
                default:
                    $stmt->bindValue($key + 1, $values[$key], PDO::PARAM_STR);
                    break;
            }

        }
    }

    $stmt->execute($params);

    return $stmt;

}

How can I safely remove invalid characters from a search that uses LIKE:

For instance:

 $filter = "filter's";

 if (isset($filter)) {
    $search_filter = 'content LIKE \'%'.$filter.'%\'';

    $sql = "SELECT $search_filter FROM  messages";

    $stmt = $this->query($sql);
}

you could just do `$search_filter = 'content like '.$dbh->quote("%".$filter."%");` — Orangepill, Jul 17 '13 at 16:06
http://stackoverflow.com/questions/3683746/escaping-mysql-wild-cards — bitWorking, Jul 17 '13 at 16:09
possible duplicate of [How to prevent SQL injection in PHP?](http://stackoverflow.com/questions/60174/how-to-prevent-sql-injection-in-php) — Marc B, Jul 17 '13 at 16:10
@YourCommonSense What am I missing... he looking specifically on how to use a like clause in a prepared statement? — Orangepill, Jul 17 '13 at 16:13
yes, I did. and the answer is to NOT remove the invalid characters and use proper query building techniques. — Marc B, Jul 17 '13 at 16:19
I have to agree, the question is quite vague, but one shouldn't use quote() with prepared statements. I thought at first he is talking on LIKE search meta characters — Your Common Sense, Jul 17 '13 at 16:22
@YourCommonSense He's not using prepared statement on the bottom example, that's why I opted for using quote :) — Orangepill, Jul 17 '13 at 16:24

Your Common Sense · Answer 1 · 2013-07-17T17:01:37.723

2

I am not sure I am following your sophisticated code, but something like this

$filter = "filter's";

$params = array();
$sql = "SELECT * FROM messages";
if (isset($filter)) {
    $sql .= ' WHERE content LIKE ?';
    $params[] = "%$filter%";
} 
$stmt = $this->query($sql, $params);

the idea is to create a query with placeholders dynamically but keep all variable parts go via placeholders only.

edited Jul 17 '13 at 17:01

answered Jul 17 '13 at 16:25

Your Common Sense

156,878
40
214
345

score 1 · Accepted Answer · answered Jul 17 '13 at 16:56

1

The simplest way is to use PDO::quote

Change the assignment of $search_field

$search_filter = 'content like '.$dbh->quote("%".$filter."%");

If you want to move to parameterized queries you would do

$sql = "select * from messages where content like ?";
$params = "%".$filter."%";
$stmt = $dbh->prepare($sql);
$stmt->execute($params);

answered Jul 17 '13 at 16:56

Orangepill

24,500
3
42
63

Using `$dbh->quote()` should be a last resort. Parameterized queries are the way to go. – tadman Jul 17 '13 at 17:18

How to remove potentially bad data from PHP and PDO MySql

2 Answers2