CODEWITHSUNDEEP
phpmysql
Duc Anh
Duc Anh

Reputation: 581

search with # % _ + in mysql query LIKE

I have this code ( just some part of it) :

<?php
if(isset($_GET['act']) && $_GET['act']== 'do')  {
$key= $_POST['key'];
}
else {
$key= '';
}
?>

<input class='inputField' type='text' name='key' size=45 value='<?php echo $key;?>' > 

<script>$(document).ready(function(){
key= $("#mainTop input").val();
if(key!= null || key== '%') {
    showPage(1,key);
}
else    {
    showPage(1);
}
});
</script>

The showpage() will get parameter and pass to php file :

if(isset($_GET['key'])) {
$key= addslashes($_GET['key']);
}
else {
$key= null;
}
$result= $db->query("SELECT * FROM information WHERE stuId LIKE '%$key%' OR stuName LIKE '%$key%' LIMIT  $start,10");

The php file will get the key to search. I escape the key by addslashes,the do the mysql query to search. But if I search with key = % # _ +,it still print our all the table instead there are no matching result. I guess that I did not escape the key in right way,so how do I escape character such as % # _ + to do mysql query with LIKE ? Please help me out ? I had a look at some another question but I still dont get it

Upvotes: 0

Views: 428

Answers (2)

Michael Benjamin
Michael Benjamin

Reputation: 2915

A solution to this problem is simply to use parameterized queries.

You wont have to worry about escaping.
You protect yourself from SQL injection.
Your code will be taken more seriously by your peers and people looking to hire you.

Keep in mind that you are dealing with three levels that need to be escaped. E.G. in PHP you must escape ' unless you want it to be interpreted as a quote, the mysql query itself must escape the same ', manually (\') with something like addslashes or using prepared statements, and the regular expression within the mysql query must be escaped if it is a reserved character like % or _ are in a LIKE statement.

Upvotes: 1

Explosion Pills
Explosion Pills

Reputation: 191789

As far as I know, # and + are not special characters in a LIKE match string, but you simply need to escape the others

$key = str_replace(array('%', '_'), array('\%', '\_'), $key);

Upvotes: 3

Related Questions