PHP forms, sending only selected data

Question

I have this database of plants
a html form that sends the data a user enteres to a php file
php file that executes sql, which will list the plants from a database, based on user input

so for example they might select 'tree' type plants. they could also enter an english name for specific searching, now this is my php:

$data = mysql_query("SELECT * FROM Plants WHERE PlantType='$plantType' AND EnglishName='$englishName'")

but how can I make it, so if a user doesn't enter an English name to search by, it will ONLY search by plant type? Note: I will have lots of fields to search by, such as color of flower, soil type etc, and I only want to search by the ones that users select to change.

You should provide some radio for the user and check for the type of search, for examepl: if($rado1) $data = mysql_query($querry1) elseif($radio2) $data = mysql_query($querry2) elseif($radio2) $data = mysql_query($querry3)...and so on. — nvcnvn, Mar 14 '12 at 17:06

score 2 · Answer 1 · answered Mar 14 '12 at 17:06

Build your query string before you run the query:

$fields = array();
if ($plantType != "") {
    $fields["PlantType"] = $plantType;
}
if ($englishName != "") {
    $fields["EnglishName"] = $englishName;
}
if (count($fields) < 1) {
    echo "No fields submitted";
} else {
    $query = "SELECT * FROM Plants WHERE ";
    foreach($fields as $field => $value) {
        $query .= $field." = '".$value."' AND ";
    }
    $query = substr($query,0,-4);
    $data = mysql_query($query);
}

Note that since you didn't mention `$_POST` or `$_GET` in your question, I assume you've already sanitized anything in `$plantType` or `$englishName`. — WWW, Mar 14 '12 at 17:34

David Houde · Accepted Answer · 2012-03-14T17:15:15.230

1

$query = "SELECT * FROM Plants WHERE PlantType='$plantType'";
if(isset($_POST['englishName'])) $query .= " AND EnglishName='$englishName'";
if(isset($_POST['someForm']))    $query .= " AND someForm='$otherForm'";
if(isset($_POST['otherForm']))   $query .= " AND otherForm='$otherForm'";

$data = mysql_query($query);

You can also prepare the whole statement, and if the variable is empty use a wildcard (%).

if(isset($_POST['something']) { 
 $something = mysql_real_escape_string($_POST['something'];)
}else{ 
 $something = '%'; 
}

You will obviously want to check your POST vars for bad data, otherwise you open yourself up to a SQL injection attack. Also, these are much easier to do with PDO prepared statements. I would read up on PDO @ PHP.net

edited Mar 14 '12 at 17:15

answered Mar 14 '12 at 17:05

David Houde

4,835
1
20
29

1

Just because `$_POST['englishName']` is set doesn't necessarily mean that `$englishName` is set. ;) Also, never ever ever put `POST` variables directly into a SQL query! – WWW Mar 14 '12 at 17:09
Hopefully $englishName is not set ;) Otherwise he has bigger problems than he realizes. Nothing wrong with mysql_real_escape_string($_GET['EnglishName']) inside of a query string, I would just argue it may need more validation before it gets to that point. – Tim Wickstrom Mar 14 '12 at 17:36
If you call `example.com?englishName=&plantType=tree` your $_POST["englishName"] will be set (`isset(...)==true`) but it will be empty. You can check for `!empty($_POST["englishName"])` as well. – Basti Mar 14 '12 at 17:39
1

@Basti: you're mixing up `$_POST` and `$_GET`. – WWW Mar 14 '12 at 17:59
100% agree on the globals, and sanitizing input. I am limited on time, and just wanted to give an example of a solution. This code should run, but is no where near complete! – David Houde Mar 14 '12 at 18:18

Basti · Answer 3 · 2012-03-14T17:40:14.017

Save all conditions, that the user wants satisfied into and array and implode them into a string for your query:

$conditions = array();

if (isset($_POST['plantType']) && is_string($_POST['plantType']))
    $conditions[] = "PlantType = '".mysql_real_escape_string($_POST['plantType'])."'";

if (isset($_POST['englishName']) && is_string($_POST['englishName']))
    $conditions[] = "EnglishName = '".mysql_real_escape_string($_POST['englishName'])."'";

// repeat for color, soilType, ... 

$query = "SELECT * FROM Plants";
if (count($conditions) > 0)
    $query .= " WHERE ".implode(" AND ", $conditions);

$data = mysql_query($query);

A shorter version that does the same:

$conditions = array();
$validColumns = array(
    // Name of the column in DB => name of the parameter in URL
    "PlantType"                 => "plantType",
    "EnglishName"               => "englishName",
    "Color"                     => "color",
    // add more here
);

// Loop through all valid columns the user might input.
foreach ($validColumns as $column => $param)
{
    // If it is set and maybe if it is valid (add validation here).
    // add this condition to our array
    if (isset($_POST[$param]) && is_string($_POST[$param]) && !empty($_POST[$param]))
         $conditions[] = "`$column` = '" . 
             // Don't forget to escape to prevent SQL-Injection.
             mysql_real_escape_string($_POST[$param])."'"; 
}

$query = "SELECT * FROM Plants";

// Check if there are any conditions. Otherwise display all plants.
if (count($conditions) > 0)
    $query .= " WHERE ".implode(" AND ", $conditions);

$data = mysql_query($query);

$_REQUEST is bad the scope should be known... $_REQUEST will include $_COOKIES which could be malicious. — Tim Wickstrom, Mar 14 '12 at 17:30
So what harm can be done? `$_GET` and `$_POST` can be freely modified by an attacker, as well. I added an extra check to not make this script report warnings if the params aren't strings. — Basti, Mar 14 '12 at 17:32
See this post http://stackoverflow.com/questions/2142497/whats-wrong-with-using-request — Tim Wickstrom, Mar 14 '12 at 17:34

score 0 · Answer 4 · answered Mar 14 '12 at 17:13

0

based on the user input yuo can build the query.

    $query_string =  "SELECT * FROM Plants WHERE PlantType='$plantType'";
    if(!empty($_POST["engName"]))
       $query_string .= "AND EnglishName=".$_POST['engName'];

Of course do sanitize $_POST["engName"]!!

answered Mar 14 '12 at 17:13

ab_dev86

1,952
16
21

Why don't you sanitize your POST data in your example? I wouldn't want to be given an example that is prune to SQL injection. – Basti Mar 14 '12 at 17:19
See my example below... I am scoping the var with $_GET as register globals SHOULD BE OFF! and I am using mysql_real_escape_string on the get var, this could be expanded to do additional checks and other santization but it is a start – Tim Wickstrom Mar 14 '12 at 17:31

score 0 · Answer 5 · answered Mar 14 '12 at 17:28

There are plenty of advanced techniques to handle this in MySQL alone. Such as an OR statement after ever Where clause or even coalesce.

With that being said I would dynamically build your WHERE clause (PS don't for get security!)

<?php
    $sql = "SELECT * FROM Plants WHERE";

    $where = array();

    if(!empty($_GET['plantType'])){
        'PlantType=' . mysql_real_escape_string($_GET['plantType']);
    }

    if(!empty($_GET['EnglishName'])) {
        'EnglishName=' . mysql_real_escape_string($_GET['EnglishName']);
    }

    if(!empty($_GET['color'])) {
        'color=' . mysql_real_escape_string($_GET['color']);
    }

    if(!empty($_GET['soilType'])) {
        'soilType=' . mysql_real_escape_string($_GET['soilType']);
    };

    foreach($where as $key => $value) {
        $sql = $sql . ' ' . $value;
        if($key+1 < count($where)) {
            $sql = $sql . ' AND'
        }
    }

    $data = mysql_query($sql);
?>

PHP forms, sending only selected data

5 Answers5