CODEWITHSUNDEEP
phpmysqlselect
Phiter
Phiter

Reputation: 14977

Filter MYSQL query with form options

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?

Upvotes: 0

Views: 203

Answers (2)

chris85
chris85

Reputation: 23880

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.

Upvotes: 2

CodeBoy
CodeBoy

Reputation: 3300

$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).

Upvotes: 1

Related Questions