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
– aceslowman, Commented Aug 12, 2013 at 17:20
To protect from SQL Injections, prepared statements are best: stackoverflow.com/questions/60174/… — General_Twyckenham
– General_Twyckenham, Commented Aug 12, 2013 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
– spencer7593, Commented Aug 12, 2013 at 17:44

Hanky Panky · Accepted Answer · 2013-08-12 17:31:03Z

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, 2013 at 17:31

answered Aug 12, 2013 at 17:25

Hanky Panky

46.9k9 gold badges76 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Nir Alfasi Over a year ago

If first-name is not defined - the query will fail

Hanky Panky Over a year ago

Nope it will not, can you tell me how?

Nir Alfasi Over a year ago

Now that you added your "quick dirty fix" it won't fail

Hanky Panky Over a year ago

I didn't add it Now, that was my original answer that quick dirty fix, review the edit history.

Hanky Panky Over a year ago

Yep that OR still is only cosmetic, that query will still work with or without it

Garry · Accepted Answer · 2013-08-12 17:26:54Z

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 · Accepted Answer · 2013-08-12 17:46:46Z

2

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.

edited Aug 12, 2013 at 17:46

answered Aug 12, 2013 at 17:41

spencer7593

109k15 gold badges122 silver badges148 bronze badges

Comments

Mirco Widmer · Accepted Answer · 2013-08-12 19:51:03Z

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, 2013 at 19:51

answered Aug 12, 2013 at 17:27

Mirco Widmer

2,1492 gold badges21 silver badges48 bronze badges

Comments

emco · Accepted Answer · 2013-08-12 17:25:54Z

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.

Bluecandy · Accepted Answer · 2013-08-12 18:05:08Z

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):(''));
}

Collectives™ on Stack Overflow

Creating conditional sql query

6 Answers 6

5 Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

5 Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related