CODEWITHSUNDEEP
phpmysqldatabase
Haren Sarma
Haren Sarma

Reputation: 2553

mysql like statement is not working as expected

I have a table with 4 record.

Records: 1) arup Sarma
         2) Mitali Sarma
         3) Nisha
         4) haren Sarma

And I used the below SQL statement to get records from a search box.

$sql = "SELECT id,name FROM ".user_table." WHERE name LIKE '%$q' LIMIT 5";

But this retrieve all records from the table. Even if I type a non-existence word (eg.: hgasd or anything), it shows all the 4 record above. Where is the problem ? plz any advice..

This is my full code:

$q = ucwords(addslashes($_POST['q']));
$sql = "SELECT id,name FROM ".user_table." WHERE name LIKE '%".$q."' LIMIT 5";
$rsd = mysql_query($sql);

Upvotes: 0

Views: 171

Answers (3)

Johan
Johan

Reputation: 76537

Use the following code to
A - Prevent SQL-injection
B - Prevent like with an empty $q 

//$q = ucwords(addslashes($_POST['q']));
//Addslashes does not work to prevent SQL-injection!
$q = mysql_real_escape_string($_POST['q']);
if (isset($q)) {
  $sql = "SELECT id,name FROM user_table WHERE name LIKE '%$q' 
          ORDER BY id DESC
          LIMIT 5 OFFSET 0";
  $result = mysql_query($sql);
  while ($row = mysql_fetch_row($result)) {
    echo "id: ".htmlentities($row['id']);
    echo "name: ".htmlentities($row['name']);
  }
} else { //$q is empty, handle the error }

A few comments on the code.

  1. If you are not using PDO, but mysql instead, only mysql_real_escape_string will protect you from SQL-injection, nothing else will.
  2. Always surround any $vars you inject into the code with single ' quotes. If you don't the escaping will not work and syntax error will hit you.
  3. You can test an var with isset to see if it's filled.
  4. Why are you concatenating the tablename? Just put the name of the table in the string as usual.
  5. If you only select a few rows, you really need an order by clause so the outcome will not be random, here I've order the newest id, assuming id is an auto_increment field, newer id's will represent newer users.
  6. If you echo data from the database, you need to escape that using htmlentities to prevent XSS security holes.

Upvotes: 0

Nishu Tayal
Nishu Tayal

Reputation: 20820

In mysql, like operator use '$' regex to represent end of any string.. and '%' is for beginning.. so any string will fall under this regex, that's why it returms all records. Please refer to http://dev.mysql.com/doc/refman/5.0/en/pattern-matching.html once. Hope, this will help you.

Upvotes: -1

Icarus
Icarus

Reputation: 63956

Your query is fine. Your problem is that $q does not have any value or you are appending the value incorrectly to your query, so you are effectively doing:

"SELECT id,name FROM ".user_table." WHERE name LIKE '%' LIMIT 5";

Upvotes: 3

Related Questions