Multiple operators in a sql SELECT statement

Question

I want my search bar to find specific properties based on the parameters the user enters. This is my code so far:

if (isset($_POST["Search"])) {
      $SearchInput = $_POST["Search"];
      $SearchInput = trim($SearchInput);
      $SearchInput = stripslashes($SearchInput);
      $SearchInput = htmlspecialchars($SearchInput);
      $price1=$_POST['amount1'];
      $price2=$_POST['amount2'];

      if ($CH == 'on' || $CF == 'on') {
        $critera .= " AND (street LIKE '%" . $SearchInput . "%' OR postcode LIKE '%" . $SearchInput . "%') AND price BETWEEN '$price1' AND '$price2'";
      }
      else{
        $critera .= " AND type='House' AND (street LIKE '%" . $SearchInput . "%' OR postcode LIKE '%" . $SearchInput . "%') AND price BETWEEN ('$price1' AND '$price2')";
      }
    }
    if ($SearchInput == ''){
      $query = "SELECT * FROM property WHERE type='House' AND city='Sheffield' AND price BETWEEN ('$price1' AND '$price2') Order By postcode ASC";
    }
  else {
    $query = "SELECT * FROM property WHERE city='Sheffield'" . $critera . " Order By postcode ASC";
  }
  }

No matter how I order the operators it either ignores the type='House' parameter or ignores the price parameters.

Your code is vulnerable to [SQL-Injections](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). Please start using Prepared, Parameterized Queries. — Charlotte Dunois, Aug 04 '16 at 17:44
there's thing using parenthesis you should have learned in a basic math class called something like precedence of operators — developerwjk, Aug 04 '16 at 17:44
it ignored `AND type='House'` because of the lack of a space (maybe). change `$critera .= "AND type='House'` to `$critera .= " AND type='House'` — developerwjk, Aug 04 '16 at 17:45
uses brackets: and (xy or yz). But even better: user prepared statements! — Jeff, Aug 04 '16 at 17:45
`htmlspecialchars` and `stripslashes` have absolutely no place in your SQL calls. They mangle data and don't even protect you from the serious problems. As Charlotte says, use **prepared statements** with placeholder values. — tadman, Aug 04 '16 at 17:46
You need to learn about [operator precedence](http://dev.mysql.com/doc/refman/5.7/en/operator-precedence.html). mixing `and` and `or` clauses without `()` almost never works out the way you want. — Marc B, Aug 04 '16 at 17:48
@developerwjk I didn't come here for sly remarks. Thank you too all others for your help! — Lewis Seddon, Aug 04 '16 at 17:49
Your code is unsecure. if you are still using `mysql_` extension then you should move to `PDO` to secure your website — Syed Aqeel, Aug 04 '16 at 17:51
@Jeff Are these paranthesis correct? `$critera .= " AND (street LIKE '%" . $SearchInput . "%' OR postcode LIKE '%" . $SearchInput . "%') AND price BETWEEN '$price1' AND '$price2'";` — Lewis Seddon, Aug 04 '16 at 17:55
@Jeff sorry to bother you, but it still isn't working as intended, it is now displaying none of the properties — Lewis Seddon, Aug 04 '16 at 18:02
not sure how it works with the 'between', maybe add paranthesis around that too. Or try to make it work without price first... — Jeff, Aug 04 '16 at 18:08
@Jeff it did work without the price parameter and paranthesis around the between statement has not caused a fix, I have editted my post according to what I have now. — Lewis Seddon, Aug 04 '16 at 18:19

score 0 · Answer 1 · answered Aug 04 '16 at 18:54

0

AND type='House' AND (street LIKE '%" . $SearchInput . "%' OR postcode LIKE '%" . $SearchInput . "%') AND price BETWEEN ('$price1' AND '$price2')

this statement is wrong, since in between you can't use paranthesis

BETWEEN ('$price1' AND '$price2')

answered Aug 04 '16 at 18:54

Ambika

594
2
11

Multiple operators in a sql SELECT statement

1 Answers1