0

I am building a query based on filters that are being applied by the user. Everything works the way I want to with pulling the data. I have come now to the point of security. How can I make this secure when my "WHERE" could have multiple filters added in.

$sql_Year = $_GET['year'];
$sql_Model = $_GET['model'];
$sql_Style = $_GET['style'];
$sql_Color = $_GET['color'];

if ( !empty($sql_Year) ) $insertY .= " and Year='$sql_Year'";
if ( !empty($sql_Model) ) $insertY .= " and Model='$sql_Model'";
if ( !empty($sql_Style) ) $insertY .= " and Body='$sql_Style'";
if ( !empty($sql_Color) ) $insertY .= " and Colour='$sql_Color'";

$stmt = $con->prepare("SELECT DISTINCT(`Year`) FROM `cars` WHERE `New/Used` =  'N' ".$insertY." ORDER BY `Year` ASC ");
$stmt->execute();
$stmt->bind_result($Year);


while ($row = $stmt->fetch()) {

}

I followed your advice and made arrays. I am now getting a error: mysqli_stmt::bind_param() [mysqli-stmt.bind-param]: Number of elements in type definition string doesn't match number of bind variables. My new code is:

$get_Year = $_GET['year'];
$get_Model = $_GET['model'];
$get_Style = $_GET['style'];
$get_Color = $_GET['color'];

$YearArray = array();
$YearValues .= "WHERE `New/Used`=?";
$YearTypes .= "s";
array_push($YearArray, "U");
if ($get_Year != "") {
    $YearValues .= " and `Year`=?";
    $YearTypes .= "s";
    array_push($YearArray, "2004");
}
if ($get_Model != "") {
    $YearValues .= " and Model=?";
    $YearTypes .= "s";
    array_push($YearArray, $get_Model);
}
if ($get_Style != "") {
    $YearValues .= " and Body=?";
    $YearTypes .= "s";
    array_push($YearArray, $get_Style);
}
if ($get_Color != "") {
    $YearValues .= " and Colour=?";
    $YearTypes .= "s";
    array_push($YearArray, $get_Color);
}
$YearVariables = implode(',', $YearArray);

$stmt = $con->prepare("SELECT DISTINCT(`Year`) FROM `cars` ".$YearValues." ORDER BY `Year` ASC ");
$stmt->bind_param($YearTypes, $YearVariables); 
$stmt->execute();
$stmt->bind_result($Year);

Can I use the arrays like this for bind_param?

1wayrocker
  • 1
  • 1
  • YOu need to build two parallel arrays: one for the "values" you're using, and one for the "placeholders". in the end you slap it all together and end up with a dynamic prepared statement,w hich is somewhat of a contradiction in terms. – Marc B Mar 04 '14 at 20:42
  • I made some updates using your advice. Please see my edits with new code. Thank you! Seeking further help. – 1wayrocker Mar 21 '14 at 19:21
  • http://stackoverflow.com/questions/11231597/ignore-particular-where-criteria – Your Common Sense Mar 21 '14 at 19:53

1 Answers1

-2

You need to sanitize the inputs! Here's the sanitation function I typically use:

function sanitize($string){
    $string = str_replace(array('"',"'"), array(""","'"),$string);
    $string = trim($string);
    $string = stripslashes($string);
    $string = mysql_real_escape_string($string);
    return $string;
}

Use like so:

if ( !empty($sql_Year) ) $insertY .= " and Year='".sanitize($sql_Year)."'";
if ( !empty($sql_Model) ) $insertY .= " and Model='".sanitize($sql_Model)."'";
if ( !empty($sql_Style) ) $insertY .= " and Body='".sanitize($sql_Style)."'";
if ( !empty($sql_Color) ) $insertY .= " and Colour='".sanitize($sql_Color)."'";
Jonny
  • 80
  • 4