Creating conditional sql query

Question

I am having a problem getting a query to work (and also am questioning the security of the query).

  if(isset($_POST)){
        $sql = "SELECT * FROM members WHERE";

     if($_POST['FirstName_S'] !== ''){
        $sql .= " FirstName LIKE '%" . $_POST['FirstName_S'] . "%'";
     }
     if($_POST['LastName_S'] !== ''){
        $sql .= " OR LastName LIKE '%" . $_POST['LastName_S'] . "%'";
     }
     if($_POST['Firm_S'] !== ''){
        $sql .= " OR Firm LIKE '%" . $_POST['Firm_S'] . "%'";
     }
     if($_POST['Country_S'] !== ''){
        $sql .= " OR Country LIKE '%" . $_POST['Country_S'] . "%'";
     }
     if($_POST['City_S'] !== ''){
        $sql .= " OR City LIKE '%" . $_POST['City_S'] . "%'";
     }
     if($_POST['State_S'] !== '' AND $_POST['State_S'] !== 'other'){
        $sql .= " OR State LIKE '%" . $_POST['State_S'] . "%'";
     }
  }

Obviously, if FirstName_S is undefined, the query breaks saying "WHERE OR". It seems like it would have a logical fix, but I've been staring at it for a little too long.

Also, sql injection was brought up as a concern, and as a side-question, would sanitizing the input be enough? Or is this altogether bad practice?

no, it's not. It's just a search function where users can look up members in a database. You can just enter any one of these alone, or with others. — aceslowman, Aug 12 '13 at 17:20
To protect from SQL Injections, prepared statements are best: http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php/60496#60496 — General_Twyckenham, Aug 12 '13 at 17:20
Just end the query with `WHERE 1=1`. Then you can append any number of `OR something` without being concerned about omitting the `OR` from the first one. (There are some query builder tools that use this technique.) If you are using mysql interface, yes, sanitize the post variables. If you are using mysqli or PDO (and you should be) use parameterized queries. See my answer. — spencer7593, Aug 12 '13 at 17:44

Hanky Panky · Accepted Answer · 2013-08-12T17:31:03.237

3

  if(isset($_POST)){
        $sql = "SELECT * FROM members WHERE";

     if($_POST['FirstName_S'] !== ''){
        $sql .= "OR FirstName LIKE '%" . $_POST['FirstName_S'] . "%'";
     }
     if($_POST['LastName_S'] !== ''){
        $sql .= " OR LastName LIKE '%" . $_POST['LastName_S'] . "%'";
     }
     if($_POST['Firm_S'] !== ''){
        $sql .= " OR Firm LIKE '%" . $_POST['Firm_S'] . "%'";
     }
     if($_POST['Country_S'] !== ''){
        $sql .= " OR Country LIKE '%" . $_POST['Country_S'] . "%'";
     }
     if($_POST['City_S'] !== ''){
        $sql .= " OR City LIKE '%" . $_POST['City_S'] . "%'";
     }
     if($_POST['State_S'] !== '' AND $_POST['State_S'] !== 'other'){
        $sql .= " OR State LIKE '%" . $_POST['State_S'] . "%'";
     }

    $sql=str_replace("WHERE OR","WHERE",$sql);   // quick dirty fix

  }

Ofcourse you'd need to sanitize the input, but since you didn't mention which MySQL API you use, I did not add any sanitization functions yet. You can look at http://php.net/mysqli_real_escape_string

edited Aug 12 '13 at 17:31

answered Aug 12 '13 at 17:25

Hanky Panky

46,730
8
72
95

If first-name is not defined - the query will fail – Nir Alfasi Aug 12 '13 at 17:30
Nope it will not, can you tell me how? – Hanky Panky Aug 12 '13 at 17:31
Now that you added your "quick dirty fix" it won't fail – Nir Alfasi Aug 12 '13 at 17:33
I didn't add it Now, that was my original answer that quick dirty fix, review the edit history. – Hanky Panky Aug 12 '13 at 17:34
Yep that OR still is only cosmetic, that query will still work with or without it – Hanky Panky Aug 12 '13 at 17:40

score 2 · Answer 2 · answered Aug 12 '13 at 17:26

do it other way as follow

if(isset($_POST)){
        $sql = "SELECT * FROM members WHERE";

     if($_POST['FirstName_S'] !== ''){
        $sql_arr[]=" FirstName LIKE '%" . $_POST['FirstName_S'] . "%'";

     }
     if($_POST['LastName_S'] !== ''){
        $sql_arr[]= " LastName LIKE '%" . $_POST['LastName_S'] . "%'";
     }
     if($_POST['Firm_S'] !== ''){
        $sql_arr[]= " Firm LIKE '%" . $_POST['Firm_S'] . "%'";
     }
     if($_POST['Country_S'] !== ''){
        $sql_arr[]= " Country LIKE '%" . $_POST['Country_S'] . "%'";
     }
     if($_POST['City_S'] !== ''){
        $sql_arr[]= " City LIKE '%" . $_POST['City_S'] . "%'";
     }
     if($_POST['State_S'] !== '' AND $_POST['State_S'] !== 'other'){
        $sql_arr[]= " State LIKE '%" . $_POST['State_S'] . "%'";
     }
     if(!empty($sql_arr)){
       $sql.=implode(' OR ',$sql_arr);
}

  }

spencer7593 · Answer 3 · 2013-08-12T17:46:46.330

The quick fix is to add 1=1 to your query, so your query ends with WHERE 1=1. That allows you to be free to append any number of OR something to your query, without being concerned with omitting the OR on the first one.

(That 1=1 predicate doesn't cause any problem; that will be evaluated at parse time, and doesn't appear in the execution plan.)

As to SQL Injection, yes, this code is susceptible. If you are using mysql interface, then sanitize the post variables with mysql_real_escape_string function. If you are using mysqli or PDO (and you should be), then use parameterized queries.

Mirco Widmer · Answer 4 · 2013-08-12T19:51:03.360

1

$stmt = $dbConnection->prepare('SELECT * FROM members WHERE FirstName LIKE ? OR LastName LIKE ? OR FIRM LIKE ? OR Country LIKE ? OR CITY LIKE ? OR STATE LIKE ?');


if($_POST['FirstName_S'] !== ''){
  $stmt->bind_param('FirstName', '%'.$_POST['FirstName_S'].'%');
} else {
  $stmt->bind_param('FirstName', '%');
}

… // do this for all your parameters

$stmt->execute();

edited Aug 12 '13 at 19:51

answered Aug 12 '13 at 17:27

Mirco Widmer

2,139
1
20
44

score 0 · Answer 5 · answered Aug 12 '13 at 17:25

I think this could help you:

if(isset($_POST)){
    $sql = "SELECT * FROM members";

 if($_POST['FirstName_S'] !== ''){
    $sql .= " WHERE FirstName LIKE '%" . $_POST['FirstName_S'] . "%'";
 }
 else {
   $sql .= " WHERE FirstName LIKE '%'";
 }
 if($_POST['LastName_S'] !== ''){
    $sql .= " OR LastName LIKE '%" . $_POST['LastName_S'] . "%'";
 }
 if($_POST['Firm_S'] !== ''){
    $sql .= " OR Firm LIKE '%" . $_POST['Firm_S'] . "%'";
 }
 if($_POST['Country_S'] !== ''){
    $sql .= " OR Country LIKE '%" . $_POST['Country_S'] . "%'";
 }
 if($_POST['City_S'] !== ''){
    $sql .= " OR City LIKE '%" . $_POST['City_S'] . "%'";
 }
 if($_POST['State_S'] !== '' AND $_POST['State_S'] !== 'other'){
    $sql .= " OR State LIKE '%" . $_POST['State_S'] . "%'";
 }

}

and for the SQL injections, you can check General_Twyckenham comment.

score 0 · Answer 6 · answered Aug 12 '13 at 18:05

You could compose the WHERE command based on what parameters is entered...

if(isset($_POST)){
    $sql_where = '';
    $sql = "SELECT * FROM members ";

    if($_POST['FirstName_S'] !== ''){
        $sql_where .= (($sql_where != '')?('OR '):(''))." FirstName LIKE '%" . $_POST['FirstName_S'] . "%' ";
    }
    if($_POST['LastName_S'] !== ''){
        $sql_where .= (($sql_where != '')?('OR '):(''))." LastName LIKE '%" . $_POST['LastName_S'] . "%' ";
    }
    if($_POST['Firm_S'] !== ''){
        $sql_where .= (($sql_where != '')?('OR '):(''))." Firm LIKE '%" . $_POST['Firm_S'] . "%' ";
    }
    if($_POST['Country_S'] !== ''){
        $sql_where .= (($sql_where != '')?('OR '):(''))." Country LIKE '%" . $_POST['Country_S'] . "%' ";
    }
    if($_POST['City_S'] !== ''){
        $sql_where .= (($sql_where != '')?('OR '):(''))." City LIKE '%" . $_POST['City_S'] . "%' ";
    }
    if($_POST['State_S'] !== '' AND $_POST['State_S'] !== 'other'){
        $sql_where .= (($sql_where != '')?('OR '):(''))." State LIKE '%" . $_POST['State_S'] . "%' ";
    }
    $sql .= (($sql_where != '')?('WHERE '.sql_where):(''));
}

Creating conditional sql query

6 Answers6