2

I am developing a real estate script. The script has a search option where an user can specify the price range eg. user_price_low to user_price_high and the listings have to be fetched from a database, which has eg. db_price_high and db_price_low.

I used a BETWEEN statement trying to find the matches,

WHERE price_low BETWEEN '.$_REQUEST['minprice_buy'].' AND '.$_REQUEST['maxprice_buy']

but to my surprise...

If an user has user_price_high = 60 and user_price_low = 20 AND the record has db_price_low = 30 and db_price_high = 120

my query is invalid in this case.

i tried looking at MySql Query- Date Range within a Date Range but does that match what I want?

Community
  • 1
  • 1
chandan
  • 123
  • 13

3 Answers3

4

Chandan, never ever input $_* functions directly into a query. It's an SQL-injection hole.

Change the code to: 

$min_price = mysql_real_escape_string($_REQUEST['minprice_buy']);
$max_price = mysql_real_escape_string($_REQUEST['maxprice_buy']);
$query = "SELECT whatever 
          FROM whichever 
          WHERE price_low BETWEEN '$min_price' AND '$max_price' ";
// Dont forget these quotes       ^          ^     ^          ^
// Or mysql_real_escape_string() will not work!.

With regards to your question, change the query to something like:

WHERE '$min_price' BETWEEN price_low AND price_high 
  AND '$max_price' BETWEEN price_low AND price_high

You may also want to consider:

WHERE ('$min_price' BETWEEN price_low AND price_high) 
  OR  ('$max_price' BETWEEN price_low AND price_high)

Here min_price and max_price don't need to both be in the range at all times.

See: How does the SQL injection from the "Bobby Tables" XKCD comic work?

Community
  • 1
  • 1
Johan
  • 74,508
  • 24
  • 191
  • 319
  • Your answer isn't quite right, I don't think. It needs to be inclusive (OR), not exclusive (AND). – Doug Kress Sep 07 '11 at 16:17
  • Thanks for the quick answer. I tried putting in the query as you suggested and everything seems to be working fine. However I will test it a bit more to make sure that there are no more bugs. I appriciate you also reminded me about the SQL Injection. However I did the check by $_REQUEST['minprice_buy'] = mysql_real_escape_string($_REQUEST['minprice_buy']); Wont that be fine? – chandan Sep 07 '11 at 16:28
  • That's just complication stuff for no reason, I recommend code like above, it's much easier to read and understand. Besides there's no need to reject the code beforehand, let the query run, otherwise an attacker will know what you're testing for by examining the timing differences in your code and focussing his attack on the code that took longer to return i.e. the code where you forgot to put in the test. – Johan Sep 07 '11 at 16:38
  • 1
    Great! Now what happens when user_low = 80, user_high = 200 and db_low = 64, db_high = 121 doesnt that listing fall in the range? Meaning the builder has got a flat with him within that range right? We were thinking of something where we check both ways. WHERE (user_low between db_low AND db_high) OR (user_high BETWEEN db_high AND db_low) OR (db_low BETWEEN user_low AND user_high) OR (db_high BETWEEN user_low AND user_high) – chandan Sep 07 '11 at 16:51
  • @chandan, if you want a match on **that**, then use the last option in the answer. – Johan Sep 07 '11 at 16:55
2

If you want to check if ranges have any common points you should use

WHERE greatest(price_low,user_price_low)<=least(price_high,user_price_high)

If you want to check if user range is in all range you may use
Undefined behaviour for user_preice_low>user_price_high

WHERE user_price_low>=price_low && user_price_high<=price_high
RiaD
  • 46,822
  • 11
  • 79
  • 123
  • in SQL the max() function does not work like that, you need to use the greatest() function. And if `user_price_low = 50000 and user_price_high = 1`, your query will return true, even though the values do not fall inside the range. – Johan Sep 07 '11 at 16:45
  • @Johan: function names are fixed. for `user_price_low = 50000 and user_price_high = 1` res first query will ok. Second query will return some rows, but It may be checked on client( I will add info) – RiaD Sep 07 '11 at 17:01
  • I dropped the -1, because the top line is correct, however the bottom line is not. Note that using a function will kill any chance of using an index. Which will not make for happy queries. – Johan Sep 07 '11 at 17:07
-2

so accourding to what i understand, shouldnt it be

WHERE price_low >= '.$_REQUEST['minprice_buy'].' AND price_high <='.$_REQUEST['maxprice_buy']'

Validate your Input using mysql_real_escape_string(); before using them in the query, dont use $_GET, $_REQUEST, $_POST variables directly in any query.

Pheonix
  • 6,049
  • 6
  • 30
  • 48