Avoiding SQL Injection in PDO and using the like clause

Question

I am using prepared statements for a search functionality using PDO and I am using the like clause. Mysql is 5.5.32

function dblink(){
    # hidden #
    $conn = new PDO("mysql:host=localhost;dbname=$database", 
     $username, $password,  array(
     PDO::ATTR_PERSISTENT => true, 
     PDO::ATTR_EMULATE_PREPARES => false, 
     PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
     PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ
    ));
    return $conn;
}

$conn    = dblink();
$query   = "select * from tablename where attrib like ? ;";
$stmt    = $conn->prepare($query);

$stmt->execute(array($_POST['field']."%"));

$results = $stmt->fetchAll(PDO::FETCH_OBJ);

This dumps all the table contents when user enters % for field in the html form. I thought prepared statement would handle it and there is % in execute so that it matches the substring entered.

How do I use the POST field as normal text only so that it doesn't cause such problem?

It looks like you can [replace `%` with `[%]`](http://stackoverflow.com/questions/7191449/how-do-i-escape-a-percentage-sign-in-t-sql) but this seems like a hack. — Waleed Khan, Jul 15 '13 at 19:08
Maybe the accepted answer to this question might help http://stackoverflow.com/questions/3683746/escaping-mysql-wild-cards — Ma3x, Jul 15 '13 at 19:14

score 1 · Accepted Answer · answered Jul 15 '13 at 19:16

1

This dumps all the table contents when user enters % for field in the html form.

Yes. That's the exact purpose of LIKE operator.
No, it has nothing to do with prepared statement. The latter is used to format your data, not to interfere with query logic.

If you don't like the way your code works - change it. But at the moment it works exactly the way you coded, with no flaws.

answered Jul 15 '13 at 19:16

Your Common Sense

156,878
40
214
345

Ya i thought so too but didn't know if there was a better way. So now i am using it like this: $stmt->execute(addcslashes(array($_POST['field']),"_%")); – Akshay Patil Jul 15 '13 at 19:25
I see no point in this replacement. Though it won't do any harm either – Your Common Sense Jul 15 '13 at 19:32
I did this so that if user enters % for field, it would be escaped. Without the change, the complete table was outputed using % – Akshay Patil Jul 15 '13 at 19:41
Well, user wants a whole table then. What's the problem? – Your Common Sense Jul 15 '13 at 19:42
Thats true. but I don't want the user to have that feature. Thanks for the help! – Akshay Patil Jul 15 '13 at 19:49
Then you have to write some code that implements restrictions, not just escape arbitrary characters. – Your Common Sense Jul 15 '13 at 19:56

Avoiding SQL Injection in PDO and using the like clause

1 Answers1