0

I have an HTML form which is processed with PHP.

The input in the form is used as a condition in a SQL WHERE clause.

When there is no input the WHERE clause looks for an empty string and won't return anything. Instead I want to return ALL rows.

How can this be achieved? I thought about using LIKE instead but I want to match only exact input when there is indeed input.

To be clear:

I want to check if the variable that the input is stored in is empty. If so, I want to return all rows.

I wanted to know if it is possible to do such thing in SQL without having to change the statement around. Rather, by just changing the variable.

Note that there can be multiple fields of input.

EDIT:

Ignore the possibility of security risks. Is there a SQL command that can achieve this without changing the query itself, just the variables? For the record I am already checking if the variables are empty strings before the query. Also, where would the security risk be if I am checking if the variables are empty or not and I am doing proper validation otherwise?

Mars
  • 4,677
  • 8
  • 43
  • 65
  • 1
    what you're describing sounds like an SQL Injection waiting to happen – Uri Goren May 20 '15 at 19:53
  • 1
    check the input from the form first? Depending on if the string is empty or not (you should also run other validation), run a SQL query that gets all rows or use the input as a variable in the WHERE clause. – Gil May 20 '15 at 19:54
  • too broad and unclear. – Funk Forty Niner May 20 '15 at 19:56
  • you've an answer below / Edit: a few now. – Funk Forty Niner May 20 '15 at 19:57
  • Why don't you want to have a conditional for each input? You will have to touch each input anyways to prevent users from hijacking your database. – TimoStaudinger May 20 '15 at 20:04
  • @TimoSta. I did not want a conditional for each input because for a second I ignored the security risks and was only concentrating on if there was a single SQL statement that can achieve it. – Mars May 20 '15 at 20:07
  • people who have given you answers below, may not be aware of your edited question/requirements. You should be posting a comment under them instead and letting them know that it may not suit your requirements. Plus, showing no code, doesn't help to expedite things ;-) so it's kind of anybody's guess right now. – Funk Forty Niner May 20 '15 at 20:07
  • @Fred-ii-. Fair point. I had begun to write a comment, but the two answers (thus far) are basically saying the same thing so I put the edit in the question. – Mars May 20 '15 at 20:10
  • @Zaphod You definitely can achieve this via SQL only, too, with the right combination of `CASE` statements (https://dev.mysql.com/doc/refman/5.0/en/case.html) and string functions (https://dev.mysql.com/doc/refman/5.0/en/string-functions.html). It would be a lot more complicated and a lot less maintainable, though. – TimoStaudinger May 20 '15 at 20:11
  • *"I wanted to know if it is possible to do such thing in SQL without having to change the statement around. Rather, by just changing the variable."* - You'll really need to post some code. Even I can't wrap my head around it and there are many ways you can go about this. Have a look at this Q&A on Stack http://stackoverflow.com/q/3190464/ it could give you some ideas. Good luck, *cheers* – Funk Forty Niner May 20 '15 at 20:14

3 Answers3

2

Since you should not use user generated strings directly inside an SQL query anyways (See PHP: SQL Injection), I would handle that in the PHP script, not in SQL:

if(isset($user_input) && !empty($user_input)) {
    // add WHERE clause
}

Edit: isset() is redundant if checked for !empty(). This will do, too:

if(!empty($user_input)) {
    // add WHERE clause
}

Thanks AbraCadaver!

TimoStaudinger
  • 41,396
  • 16
  • 88
  • 94
2

A common method to dynamically add filters on a query is:

$sql ='select * from table where (1=1) ';
if (array_key_exists($_POST,'email'))
    {
    $email=mysql_real_escape_string($_POST['email']);
    $sql.=" and (email='$email')";
    }
if (array_key_exists($_POST,'city'))
    {
    $city=mysql_real_escape_string($_POST['city']);
    $sql.=" and (city='$city')";
    }
//.....
mysql_query($sql);
Uri Goren
  • 13,386
  • 6
  • 58
  • 110
0

This can allow you to use the same query, and effectively ignore the parameter if blank. I am not sure I would actually recommend it in lieu of constructing a (still parameterized) query, since it might not play nice with indexes all the time, but it is possible.

WHERE ([my_parameter] = '' OR the_field = [my_parameter])

even more generalized:

WHERE ([my_parameter] = [ignore value] OR the_field = [my_parameter])
Uueerdo
  • 15,723
  • 1
  • 16
  • 21