Reputation: 14977
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
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
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