LifeinAsia
msg:4519145 | 8:27 pm on Nov 14, 2012 (gmt 0) |
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
msg:4519185 | 11:08 pm on Nov 14, 2012 (gmt 0) |
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
msg:4519197 | 11:49 pm on Nov 14, 2012 (gmt 0) |
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
msg:4519208 | 1:09 am on Nov 15, 2012 (gmt 0) |
Either write the statement in PHP and then send it, or have the if:false option as 1:
"WHERE 1 AND `price`=$pricerange"
|
swa66
msg:4519399 | 5:02 pm on Nov 15, 2012 (gmt 0) |
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
msg:4519436 | 6:39 pm on Nov 15, 2012 (gmt 0) |
prepared statements? How do you mean?
|
swa66
msg:4519516 | 11:54 pm on Nov 15, 2012 (gmt 0) |
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
msg:4519679 | 12:22 pm on Nov 16, 2012 (gmt 0) |
swa66- what's the security risk of "WHERE 1 AND xyz"?
|
swa66
msg:4519736 | 4:33 pm on Nov 16, 2012 (gmt 0) |
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 ?
|
|
