Build dynamic WHERE clause in mySQL

Question

I have this code and it works great, if I just want to search by office name. However I need to be able to search by "Office and/or First Name and/or Last Name", any combination of the three.

$firstName      = $_POST["firstName"];
$lastName       = $_POST["lastName"];
$officeName     = $_POST ["officeName"];

$query = "SELECT 
e.*,
e.id emp_id,
o.*
";
$query .= "FROM 
employee_data e,
office o,
employee_office_pivot p
"; 
$query .= "WHERE 
1=1
AND e.id=p.employee_id
AND p.office_id=o.id
AND o.office_name= '".$officeName."'
";

How can I build the WHERE clause, so that it will accept any of the three columns, or none if they are null.

Thanks, Richard

First you start by reading about [SQL injection attacks](http://bobby-tables.com) and learning why your code is dangerous. — Marc B, Dec 02 '13 at 15:46

score 2 · Accepted Answer · answered Dec 02 '13 at 15:50

2

Something like this?

$query .= "WHERE 
    1=1
    AND e.id=p.employee_id
    AND p.office_id=o.id
    AND (o.office_name= '".mysqli_real_escape_string($officeName)."'
        OR o.office_name= '".mysqli_real_escape_string($firstName)."'
        OR o.office_name= '".mysqli_real_escape_string($lastName)."')
    ";

I used mysqli_real_escape_string() here as an example, you should use the correct and necessary precautions to avoid SQL injection in your system.

answered Dec 02 '13 at 15:50

Aioros

4,373
1
18
21

This works, but only if I take out the mysqli_real_escape_string – rwrogers Dec 02 '13 at 15:56
That's probably because you're not using the `mysqli` extension. Which one are you using to connect to the DB? – Aioros Dec 02 '13 at 15:59
This is my connection string. $connection = mysqli_connect($dbhost, $dbuser, $dbpass, $dbname); – rwrogers Dec 02 '13 at 16:00
Could you add these two lines at the beginning of your script and try again? `error_reporting(E_ALL); ini_set('display_errors', 1);` – Aioros Dec 02 '13 at 16:16
It doesn't know which connection to use. You have to either use `mysqli_real_escape_string($connection, $officeName)` or `$connection->real_escape_string($officeName)`, where the latter probably fits better with your apparently object oriented style. Source: http://php.net/manual/de/mysqli.real-escape-string.php – Christian Sep 24 '15 at 17:05

score 1 · Answer 2 · answered Dec 02 '13 at 16:07

You can use arrays to dynamically construct your SQL:

/**
 *  The items you expect to receive from $_POST. I prefer defining these ahead of time - when feasible - 
 *  so that you can reference them without worrying about throwing an error if they are not set.
 */
$options = array_fill_keys(array('firstName', 'lastName', 'officeName'), false);
$post = array_merge($options, $_POST);
/**
 *  Your base SQL query.
 */
$sql = 'SELECT ...columns... FROM ...tables... WHERE 1 = 1';
$where = array();
/**
 *  If $_POST items are present, sanitize and create SQL
 */
if ( $post['firstName'] ) {
    $where[] = "employee_first_name = '".mysqli_real_escape_string($post['firstName'])."'";
}
if ( $post['lastName'] ) {
    $where[] = "employee_last_name = '".mysqli_real_escape_string($post['lastName'])."'";
}
if ( $post['officeName'] ) {
    $where[] = "office_name = '".mysqli_real_escape_string($post['officeName'])."'";
}
/**
 *  One or more $_POST items were found, so add them to the query
 */
if ( sizeof($where) > 0 ) {
    $sql .= ' AND '.implode(' AND ', $where);   
}

You can use the same technique to dynamically add columns, joined tables, etc. to the SQL. (Hint: build the entire SQL statement using an array.) You can also very easily modify this to use combinations of AND and OR.

score 0 · Answer 3 · answered Dec 02 '13 at 15:42

0

$values = array(
    'firstName'  => 'someFirstName',
    'lastName'   => 'someLastName',
    'officeName' => 'someOfficeName'
);

foreach( $values as $col => $val )
{
    $where .= "$key = '$balue' ";
}

Though this is SQL injection vulnerable.

answered Dec 02 '13 at 15:42

Virus721

8,061
12
67
123

"Though this is SQL injection vulnerable." Why even give an answer if you know it's horribly insecure? – Snowburnt Dec 02 '13 at 15:44
3

Because data sanitizing depends on what the author is using (i.e PDO or mysql or whatever). – Virus721 Dec 02 '13 at 15:45
Just a glance 90% of the questions coming in have SQL injection vulnerabilities. I'd wager you're in the 1% of the group of people who even know what SQL injection is and why it's an issue. Spread some knowledge and show people how to protect themselves rather than hiding behind database drivers. – Snowburnt Dec 04 '13 at 19:49
1

As you said, "90% of the questions coming in have SQL injection vulnerabilities", which means that one would have to rewrite the same sanitizing code in 90% of his answers to such questions, wow, you seem to be an efficient programmer... I wrote it in bold, it is vulnerable. If the guy actually cares about it, he will easily find more about it in one of the thousands of questions talking about it. If not i may write the sanitizing code, he won't pay attention to it. – Virus721 Dec 05 '13 at 19:46
It's really not that difficult to write a prepare statement or tack on real_escape_string for every question and really that's all the sanitizing that needs to happen for SQL injection. SQL injection is a very dangerous vulnerability and is very common and very easy to mitigate only adding a few characters to your code. Obviously, there's more sanitizing that should happen, but that's more subjective, fixing SQL injection is simple and straight-forward. You've wasted much more time being petty in the comment section than you would have not leaving a SQL injection vulnerability. – Snowburnt Dec 06 '13 at 14:38
This isn't injection vulnerable with the comparison values being in quotes and the values could be "sanitized" before it gets to this point.... – G-Man Aug 27 '15 at 21:30

Build dynamic WHERE clause in mySQL

3 Answers3

Linked