Welcome to WebmasterWorld Guest from 54.226.147.190

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

search for all results in mysql

not possible - so how is it done

     

whatson

8:20 pm on Nov 14, 2012 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



So if you have a search feature, and drop down boxes are included, but you want to list an option in the drop down box as ALL, displaying all e.g. price ranges, and in your mysql query you have:
SELECT * FROM `products` WHERE `price`=$priceRange AND `locaction`=$location

Then what if you want to choose all? Obviously this will not work. So how is this usually done? Do you have to use switch statements for if isset each variable, and the combination of each, as you must also enter the AND into the query too.
And what if you have 10 or even more search parameters? There are too many combinations.

How is this done? I can't work out the logic.

LifeinAsia

8:27 pm on Nov 14, 2012 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



You'll need to include some sort of conditional logic in building the SELECT statement. Use the "`price`=$priceRange" unless all is selected. (I don't know PHP so I can't give you the exact way to write it.) Include the same logic for all your other conditionals.

If every conditional can be selected for all, you'll also need an initial "1=1" conditional. Something like the following in pseudo code:
SELECT *
FROM `products`
WHERE 1=1
IF ($priceRange<>'*') AND `price`=$priceRange
IF ($location<>'*') AND `locaction`=$location
...

(NOTE: the psuedocode is used to build the SQL statement in PHP.)

swa66

11:08 pm on Nov 14, 2012 (gmt 0)

WebmasterWorld Senior Member swa66 is a WebmasterWorld Top Contributor of All Time 10+ Year Member



You can extend the logic to detect if there's none, 1 or more options to include and that way avoid that 1=1 - it feels to much like a hacker got to you.

whatson

11:49 pm on Nov 14, 2012 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



oh awesome I can do an if statement in the mysql, but then I have the issue of the 'AND', what if it becomes WHERE AND `price`=$priceRange?

vincevincevince

1:09 am on Nov 15, 2012 (gmt 0)

WebmasterWorld Senior Member vincevincevince is a WebmasterWorld Top Contributor of All Time 10+ Year Member



Either write the statement in PHP and then send it, or have the if:false option as 1:
"WHERE 1 AND `price`=$pricerange"

swa66

5:02 pm on Nov 15, 2012 (gmt 0)

WebmasterWorld Senior Member swa66 is a WebmasterWorld Top Contributor of All Time 10+ Year Member



In pseudo code:


...
$sql = "SELECT field1, field2, ... fieldN FROM tablename";
$counter=0;
if (test to decide you want to output a price restriction) {
$sql .= "WHERE `price`= $price";
$counter++;
}
if (test to decide you want to output a location restriction) {
if ($counter ==0) {
$sql .= "WHERE ";
} else {
$sql .= "AND ";
}
$sql .= "`location`= $location";
$counter++;
}
...


And that way you have no 1=1 that will scare any security minded person to soil their pants.

now that said .. I'd still prefer prepared statements over this.

whatson

6:39 pm on Nov 15, 2012 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



prepared statements? How do you mean?

swa66

11:54 pm on Nov 15, 2012 (gmt 0)

WebmasterWorld Senior Member swa66 is a WebmasterWorld Top Contributor of All Time 10+ Year Member



Prepared statements: [php.net...]

The huge advantage is that you do not need to worry about mysql mixing commands and data (and start to execute what you think is data as if it were commands.)

The disavantage is that you need a slightly bit more effort to be able to tell mysql what is data and what is the command.

vincevincevince

12:22 pm on Nov 16, 2012 (gmt 0)

WebmasterWorld Senior Member vincevincevince is a WebmasterWorld Top Contributor of All Time 10+ Year Member



swa66- what's the security risk of "WHERE 1 AND xyz"?

swa66

4:33 pm on Nov 16, 2012 (gmt 0)

WebmasterWorld Senior Member swa66 is a WebmasterWorld Top Contributor of All Time 10+ Year Member



There's no direct risk in it as such.

It's just that if you /a security minded person / anomaly detection software sees that going to the database, there's an almost automatic assumption some SQL injection is happening.

There's a (small) indirect risk in that it would dull the senses and make it harder than it needs to be to put in place anomaly detection (or make it less effective.

It's easy enough to output sql commands that do not have it in them, so why do it then ?
 

Featured Threads

Hot Threads This Week

Hot Threads This Month