0

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?

Improve this question
3
  • 1
    you are missing the where clause, "SELECT * FROM solicitacoes WHERE $parametros" Commented Sep 21, 2015 at 1:16
  • Yeah i already have that, I forgot, sorry! I have added this line. Commented Sep 21, 2015 at 1:21
  • Any luck/issues with posted answers? Commented Sep 24, 2015 at 19:28

2 Answers 2

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Isn't mysqli_stmt_bind_param() 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()
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.
1 
$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).

Improve this answer

1 Comment

This will throw warnings if the $_POST's aren't set. The index's will be undefined. You need the issets or emptys. Also you should read up on SQL injections if unfamiliar. This answer is wide open to SQL injections (as the OPs code was).

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.