Using Custom Variables with PHP Prepare statement

Question

Here I go... I have a search function for users, like this:

if ($_GET['s_country']){    
   $s_country = htmlentities($_GET['s_country'],ENT_QUOTES,"UTF-8");
   $s_country=trim($s_country);
   $s_country_row = " and country= ?";
} else {
   $s_country="";
   $s_country_row = " and (country= ? or not country= '')";
}
$s_city = "";
$s_city_row = " and (city= ? or not city= '')";
if ($_GET['s_city']){        
   $s_city = strip_tags($_GET['s_city']);
   $s_city=trim($s_city);
   $s_city_row = " and city= ?";
} 

$search = mysqli_prepare($dbconnect, "SELECT id FROM user WHERE gender=? $s_country_row $s_city_row");
mysqli_stmt_bind_param($search, 'iss', $s_gender, $s_country, $s_city);

In the above example I have used my own way to dismiss variables. I need to dismiss/remove variables that are not searched for or have no input.

If "country" is not searched for, it should return all rows with all "country" values.

Is there any better way to do this? (Without prepare statement it is quite easy to customize everything, but I hope security does not mean less flexibility).

Thank you for reading this

Deepak Kedia · Accepted Answer · 2017-03-08 15:34:48Z

1

$country = isset($_GET['s_country'])? " AND country=\'$_GET['s_country']\'" : '';
$query = " SELECT id FROM user WHERE gender=? $country ";

This is the pseudo idea here query is forming in such a way that - if it contains country then : SELECT id FROM user WHERE gender=? and country = 'US'; - if country is not defined then : SELECT id FROM user WHERE gender=?

as no conditions for country in the second query this will throw all country rows with particular gender

edited Mar 8, 2017 at 15:34

answered Mar 8, 2017 at 14:56

Deepak Kedia

1749 bronze badges

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

2 Comments

Tom Over a year ago

This would not work if I add any more rows and will throw an error, updated my question with more rows.

Tom Over a year ago

This is the flexibility that I want, but as a 100% prepared statement (this is what I meant with my last sentence), in this example the variable $country is a huge security risk, I want this without MySQL injection probability.

Collectives™ on Stack Overflow

Using Custom Variables with PHP Prepare statement

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related