Filter MYSQL query with form options

Question

I have a form with multiple inputs which are my filters. This is my code (not all of it, just the part I want to fix):

$req_resumo = '';
$req_status = '';
$req_usuario = '';
$n_req = 0;
$parametros = "";

// Checks which fields are filled and increases the number of filters for future usage
if (isset($_POST['usuario']) && $_POST['usuario'] != "") {
    $req_usuario = $_POST['usuario'];
    $n_req++;
}

if (isset($_POST['resumo']) && $_POST['resumo'] != "") {
    $req_resumo = $_POST['resumo'];
    $n_req++;
}

if (isset($_POST['status']) && $_POST['status'] != "") {
    $req_status = $_POST['status'];
    $n_req++;
}

// Then (there is some code between these parts)
if ($n_req > 0 && $funcao != 'usuario') $parametros.= " where ";
if ($req_usuario != "") {
    $parametros.= " usuario = '$req_usuario' ";
    if ($n_req > 1) $parametros.= " and ";
}

if ($req_resumo != "") {
    $parametros.= " resumo = '$req_resumo' ";
    if ($n_req > 1 && ($req_status != "") || ($req_data_inicial != "")) $parametros.= " and ";
}

if ($req_status != "") {
    $parametros.= " status = '$req_status' ";
}

// This will create the query and add the parameters string at the end.
$tot = mysqli_query($con, "SELECT * FROM solicitacoes $parametros");

This code looks ugly, and even for me (begginer), it doesn't feels right, does not sounds like the way of coding.

So, is there any better and easier way of building this code?

you are missing the where clause, "SELECT * FROM solicitacoes WHERE $parametros" — Cesar Mtz, Sep 21 '15 at 01:16
Yeah i already have that, I forgot, sorry! I have added this line. — Phiter, Sep 21 '15 at 01:21

score 2 · Answer 1 · edited May 23 '17 at 11:59

Give this a try. From my testing locally (without db) looked right.

$n_req = 0;
$_POST['usuario'] = 'test';
$_POST['resumo'] = 'test2';
$_POST['status'] = 'test3';
if (!empty($_POST['usuario'])) {
$req_usuario = $_POST['usuario'];
$where[] = " usuario = ? ";
$params[] = $req_usuario;
$n_req++;
}
if (!empty($_POST['resumo'])) {
$req_resumo = $_POST['resumo'];
$where[] = " resumo = ? ";
$params[] = $req_resumo;
$n_req++;
}
if (!empty($_POST['status'])) {
    $req_status = $_POST['status'];
$where[] = " status = ? ";
$params[] = $req_status;
$n_req++;
}
$sql_where = !empty($where) ? ' where ' . implode(' and ', $where) : '';
echo $sql_where;
$tot = mysqli_prepare($con, "SELECT * FROM solicitacoes $sql_where");
if(!empty($params)) {
//foreach($params as $param) {
//  mysqli_stmt_bind_param($tot, "s", $param);
    //echo $param;
//}
$params = array_merge(array($tot),
                  array(str_repeat('s', count($params))), 
                  array_values($params));
print_r($params);
call_user_func_array('mysqli_stmt_bind_param', $params);
// adapated from https://stackoverflow.com/questions/793471/use-one-bind-param-with-variable-number-of-input-vars and http://www.pontikis.net/blog/dynamically-bind_param-array-mysqli may need to be altered
}
echo "SELECT * FROM solicitacoes $sql_where";
mysqli_execute($tot);

If all three values are populated your query should be

SELECT * FROM solicitacoes where usuario = ? and resumo = ? and status = ?

The ? are populated with the values by the driver later in the process. This prevents the user(s) from adding in malicious code to manipulate the SQLs processing.

https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet#Defense_Option_1:_Prepared_Statements_.28Parameterized_Queries.29
How can I prevent SQL injection in PHP?

I also didn't see where $funcao was set..

You can comment out the mysqli functions and decomment out the echo lines to see what the code does. That is how I confirmed queries were being built as expected.

Isn't [`mysqli_stmt_bind_param()`](http://php.net/manual/en/mysqli-stmt.bind-param.php) supposed to be called once with all of the params? I didn't think it could be called in a loop like PDO's `bindParam()` — Sean, Sep 21 '15 at 03:15
Hmm yea, you might be right. I thought this method worked, but I'm not seeing any other doc showing that now. I'll alter that. Thanks. — chris85, Sep 21 '15 at 03:36

score 1 · Accepted Answer · answered Sep 21 '15 at 02:08

$predicates = array();
if ($_POST['usuario'] != "") {
    $predicates[] = "usuario = '{$_POST["usuario"]}'";
}
if ($_POST['resumo'] != "") {
    $predicates[] = "resumo = '{$_POST["resumo"]}'"
}
if ($_POST['status'] != "") {
    $predicates[] = "status = '{$_POST["status"]}'"
}
if (count($predicates) == 0) {
    // handle case when nothing specified in POST
} else {
    $tot = mysqli_query($con, "SELECT * FROM solicitacoes WHERE "
        . implode(" and ", $predicates) );
}

I may not have all your logic exactly as required ... but the ideas are there. Use implode() to insert and between the predicates of your WHERE clause (it'll figure out how many are needed, if any). Also, since it is your HTML form that is submitting the POST, you can be certain that at least some value is being passed for each POST variable (so isset() is not required).

This will throw warnings if the `$_POST`'s aren't set. The index's will be undefined. You need the `isset`s or `empty`s. Also you should read up on SQL injections if unfamiliar. This answer is wide open to SQL injections (as the OPs code was). — chris85, Sep 21 '15 at 02:22

Filter MYSQL query with form options

2 Answers2

Linked