Products filtering options through mysqli_prepare

contemplatrice · November 1, 2020, 4:36pm

Hello there,

I’m looking for a tutorial or example about the correct way to handle this quite common query through a mysqli prepared statement.

$stmt = $con -> prepare ("SELECT * FROM products 
                          WHERE published = 'Yes' 
                          AND category IN ='dresses, skirts' 
                          AND color IN 'blue, pink, yellow' 
                          AND price BETWEEN '10' AND '15' 
                          ORDER by price ASC, BY new DESC");

Here is an exerpt from my code, but I’m stuck. I don’t know how to handle it.

if (isset($_POST['action']))
   {   
       $published='Y'; 
       $sort_products = $con -> prepare ( "select * from products where published = ?   ");   
		
		if ( isset ($_POST['product_category']))
		   { 
		     $array= $_POST['product_category'];
		     $product_category = implode(',', array_fill(0, count($array), '?')); // creates a string containing ?,?,? 
			 		   
			 $sort_products .= "AND p_cat_id  IN (". $product_category." )  ";
			 call_user_func_array(array($sort_products, 'bind_param'), $params_category);
		   }	 
		  
		
	 	if ( isset ($_POST['product_main_color']))
		   { $array= $_POST['product_main_color'];
		     $product_brand = implode(',', array_fill(0, count($array), '?')); // creates a string containing ?,?,? 
			 $array[] = $this->getTime();
             $types = str_repeat('i', count($array));			 
			 $params_color = array($types);
			 foreach ($array as $key => $value) 
					 {  $params_color[] = &$array[$key];
					 }
		     $sort_products .= "AND product_main_color  IN (". $product_main_color." )  ";
			 call_user_func_array(array($sort_products, 'bind_param'), $params_color);
		   }

        if ( isset ($_POST['price_from']) &&  isset ($_POST['price_upto']) )
		   { 
		      $sort_products .= "AND price  BETWEEN ? AND ? ";			  
		   }

        if ( isset ($_POST['order_1'])  )
		   { 
		      $sort_products .= "ORDER BY ? ";			  
		   }

        if ( isset ($_POST['order_2'])  )
		   { 
		      $sort_products .= ", ? ";			  
		   }

$sort_products -> bind_params   ///  How to bind all of these params ?

How to bind these params all together ? What’s the correct way of writing this query ?
Thanks

SamA74 · November 1, 2020, 5:03pm

FWIW, I wouldn’t do this with mysqli, because with PDO, you don’t have to bind parameters.
You simply pass in an array of values when you execute.
The array can be built up in your conditions, where you currently build up your string of ?.

$stmt->execute($values);

There is a simple example here with an IN clause. Yours has a few more conditions to build up the array of values. But the principles are the same.

contemplatrice · November 1, 2020, 5:17pm

Thanks. Truth is the link you provide is perfectly detailed and relevant to my case.
Thank u

I was thinking about proceeding with classical mysqli sentences like so :

$product_material = implode("', '", array_map(array($con, 'real_escape_string'), $_POST['product_material']));			 		   
$sort_products .= "AND product_material  IN (". $product_material." )  ";

Is it safe ? or not that much ?

mabismad · November 1, 2020, 5:22pm

A bunch of points -

You cannot concatenate onto the result of a prepare() call. You need to build the sql query statement in a php variable, then when you are done doing that, you would call prepare()
List out the columns you are SELECTing.
If you have static/always values, such as the published = ‘Y’, put them directly into the query.
Each term to be AND’ed should be added as an entry to an array. You can then implode the array using the ’ AND ’ string to build that part of the sql statement.
Since you are using a MySql (or similar) database, use FIND_IN_SET() rather than an IN() comparison. You can use a single prepare query place-holder with FIND_IN_SET().
Since you have a price_from and a price_upto and you are using isset() to test which one(s) were submitted, that implies you can have any combination of them. Write code for a separate term for each of them, using an explicate >= or <= operator.
You cannot supply column names via prepared query place-holders. For the ORDR BY… term, you must validate that the inputs are exactly and only permitted choices before putting them into the sql query statement.
If you switch to the much simpler and more consistent PDO extension, for each term you add to the query, you would simple add the corresponding values to an array, then just supply this array as a call-time parameter to the ->execute(…) call. This will work correctly even if there are no terms being added to the query.
If you continue using the mysqli extension, you would build both a string of the type(s) and an array of the input values. When you get to the point of calling bind_param(), you would use php’s … splat operator https://www.php.net/manual/en/migration56.new-features.php to supply the array of values to the bind_param() call. Also, with the mysqli extension, you would need to test if there are any dynamic terms being added to the query and just use the ->query() method if there are none.

mabismad · November 1, 2020, 5:29pm

Don’t waste your time trying to escape the data. If you haven’t set the character set that php is using to match your database tables, sql injection is still possible.

A prepared query is the simplest, fool-proof way of preventing sql special characters in all data types from breaking the sql query syntax.

contemplatrice · November 1, 2020, 5:32pm

Thanks for all of that.
I’m not sure if I can use mysqli on certains pages and pdo on other ones.

Don’t waste your time trying to escape the data. If you haven’t set the character set that php is using to match your database tables, sql injection is still possible.

Okay : understood.

Thank u

mabismad · November 1, 2020, 5:41pm

You can but you shouldn’t.

One of the great points of the PDO extension is you can directly fetch data from a prepared query and a non-prepared query in exactly the same way, using fetch statements like you are used to using. With the mysqli extension, fetching data from a prepared and a non-prepared query are completely different and if you are relying on mysqli_stmt_get_result(), don’t. It is dependent on the mysqlnd driver actually being used by your php installation, and results in non-portable code unless you build and manage all the servers where you will ever run your code.

contemplatrice · November 1, 2020, 5:49pm

Merci beaucoup for the info.
My website is 90% completed, and based on mysqli prepared stmts… so…I’m going to try to finish it with pdo , if u think it’s a good idea ?
Next website, I’ll build it according to your recommandations.
Thanks for your appreciated help.

mabismad · November 1, 2020, 6:01pm

Converting from mysqli prepared queries to using PDO is simple -

The ? place-holders are the same.
The ->prepare() call is the same syntax (requires a PDO connection object, rather than a msyqli connection object.)
Take the variables you are supplying to the bind_param() call and supply them as an array to the ->execute([…]) call.
Convert any code fetching data to use one of the PDOstatement fetch methods. fetch(), fetchAll() and sometimes fetchColumn() are all the ones you normally need.

A P.S. for the PDO connection -

Set the character set to match your database tables (this insures that no character conversion will occur.)
Set the error mode to exceptions. The PDO connection always uses an exception for a connection error. By setting the error mode to exceptions, all the other statements that can fail - query, prepare, execute, exec, … will also use exceptions.
Set emulated prepared queries to false (you want to run real prepared queries.)
Set the default fetch mode to assoc, so that you don’t need to specify the fetch mode in each fetch statement.

contemplatrice · November 1, 2020, 6:13pm

Thank u very much ! You’re wonderful. People like you make the world much better, sincerely.
I’ll follow every advice u’ve given to me, together with the tut on phpdelusions. Merci

mabismad · November 1, 2020, 6:55pm

Here’s what your example code would end up looking like -

<?php

if(isset($_POST['action']))
{
	$where_terms = [];
	$params = [];
	
	$where_terms[] = "published = 'Y'";

	if(isset($_POST['product_category']))
	{
		$where_terms[] = 'FIND_IN_SET(p_cat_id,?)';
		$params[] = implode(',',$_POST['product_category']);
	}
	
	if(isset($_POST['product_main_color']))
	{
		$where_terms[] = 'FIND_IN_SET(product_main_color,?)';
		$params[] = implode(',',$_POST['product_main_color']);
	}
	
	if(isset($_POST['price_from']))
	{
		$where_terms[] = 'price >= ?';
		$params[] = $_POST['price_from'];
	}

	if(isset($_POST['price_upto']))
	{
		$where_terms[] = 'price <= ?';
		$params[] = $_POST['price_upto'];
	}

	// validate order_1 and order_2 and build the ORDER BY term
	// this would typically use an array of permitted values and an in_array() comparison
	$order_by = '';


	// build the where term
	$where = '';
	if(!empty($where_terms))
	{
		$where = 'WHERE ' . implode(' AND ',$where_terms);
	}
	
	$sql = "SELECT list your columns FROM products $where $order_by";
	$stmt = $pdo->prepare($sql);
	$stmt->execute($params);
	$product_data = $stmt->fetchAll(); // this will either be an empty array (a false value) or an array of rows of data from the query

}

In typing that, I realized you should use $_GET inputs for this, so that if someone finds a result they want to come back to or share with someone else, they can book-mark the page.

contemplatrice · November 3, 2020, 1:05pm

Hello
Thank u very much again for this example and idea about working with get

I’ve tried several options but I don’t have knowledge yet with pdo to be able to make it work.

SQL: [440] SELECT product_id, product_page, product_title, product_brand, 
product_price, product_image_1, variation_1_page, variation_2_page, variation_3_page, 
variation_4_page, variation_5_page, variation_1_color_icon, variation_2_color_icon, 
variation_3_color_icon, variation_4_color_icon, variation_5_color_icon 
FROM products 
WHERE published = 'Y' AND FIND_IN_SET(p_cat_id,?) AND product_price >= ? AND
product_price <= ? Params: 3 Key: Position #0: paramno=0 name=[0] "" is_param=1 
param_type=2 Key: Position #1: paramno=1 name=[0] "" is_param=1 param_type=2 Key: 
Position #2: paramno=2 name=[0] "" is_param=1 param_type=2 

 PDOStatement::errorInfo(): Array ( [0] => 00000 [1] => [2] => )

var_dump($_GET[‘product_category’]) and other selected options return the correct values.
echo $count, duly returns the number of products corresponding to the selected category, but all of my variables proceeding from while ($row_products = $stmt → fetchAll()) are said undefined. when I used exactly the same ones to display all of my products with mysqli stmts, without filter.

In your example, you write : $where_terms[] = "published = 'Y'"; but you don’t include it in $params[]. therefore, like mysql stmt, I’m wondering how to pass it as a param in execute…
Same question about “ORDER BY” and LIMIT.

With reference to “ORDER BY or LIMIT”, I don’t know if I’m supposed to write " ORDER BY ?" or “ORDER BY $order” and if I have to, how to correctly join these params to the execute function ?
I verify the content of ORDER By this way :

$order_by = '';		 
if ( isset ($_POST['order_by']) && $_POST['order_by'] == "price_asc" )
   { $order_by =  'ORDER BY product_price ASC';		
    // or $order_by = 'ORDER BY ?'; ?
    // $params[] = $order_by;
   }

Anyway, without implementing order by nor limit, my query is not fetching the expected results.

Thank u very much

mabismad · November 3, 2020, 4:11pm

fetchAll() returns an array of rows. To loop over those rows at the point of producing the output -

foreach($row_products as $row)
{
    // use elements in $row here...
}

There’s no place-holder in that term, so there’s nothing to add to $params. If you want the ‘Y’ value to be from external, unknown, dynamic data, change the ‘Y’ to a ? place-holder and write logic to add the input value to the $params array when building that part of the sql query statement.

Yes, the un-commented code in that will work to verify a permitted $_POST['order_by'] value and build the corresponding $order_by string. Note; ASC is the default direction and isn’t needed in the sql syntax.

The parameters for a LIMIT term are values and can be supplied via prepared query ? place-holders and by adding the values to the $params array.

ahundiak · November 3, 2020, 4:58pm

Be aware that you will have trouble with the IN (?) clauses because SQL prepared statements have no support for arrays. So if your filter array has say 4 items then you need to generate IN (?,?,?,?) and basically merge individual filter arrays into one big array. Quite painful.

I would suggest going one step further and using another library called the Doctrine Database Access Layer which sits on top of PDO and takes care of all the messy details of handling IN statements.

I might add that you also have to deal with IN clauses with no parameters. The Doctrine library also has a query builder which makes it a bit easier.

contemplatrice · November 3, 2020, 6:21pm

Okay. Thanks. I understand, see clearer now.

However, when I read Ahundiak I’m not sure what he or she means. Does this mean that the way we’re handling the code is unsafe ?

I can’t figure out why it’s that complicated to deal with arrays IN in mysqli prepared stmts

contemplatrice · November 3, 2020, 6:28pm

Thanks for your contribution. As pdo is totally new for me, and that I don’t master 100% of php functionnalities, I don’t know whether u’re saying that the previous code is unsafe as it is, or if if could be shorter to write by using Doctrine.

ahundiak · November 3, 2020, 6:48pm

Let me try explaining again.

SELECT * FROM products WHERE color IN (?) AND category IN (?)
$colors = ['red','white','blue'];
$cats = ['lawn','furniture'];

You would think that you could prepare the sql and then pass $colors and $cats as parameters. But you can’t. sql itself does not support array parameters. Just a fundamental limitation.

Instead you need to count the number of actual elements in your data and build the sql based on it.

SELECT * FROM products WHERE color IN (?,?,?) AND category IN (?,?);
$params = array_merge($colors,$cats);

The more parameters you have the more complicated it can be to build your sql and keep everything in order. And of course you can’t do WHERE color IN() so if the user does not care about the colors then you have to ensure the color clause is completely eliminated.

The Doctrine library I linked to takes care of handling these array limitations.

You did have some code which implodes array values into a string but then you lose the sql injection protected associated with prepared statements.

mabismad · November 3, 2020, 6:48pm

The use of FIND_IN_SET() accomplishes the same functionality as IN() -

In fact, IN(), FIND_IN_SET(), and FIELD(), after you parse the sql syntax, all do the same thing and probably share common code. The only real difference between those three methods is in the return value. IN() returns just a one or a zero, while FIND_IN_SET() and FIELD() return the position number (a true value) if found or a zero if not found.

ahundiak · November 3, 2020, 6:52pm

Example please. FIND_IN_SET has nothing to do with the IN clause. Would love to be proven wrong but I’m pretty sure I am not.

mabismad · November 3, 2020, 7:12pm

Both of the following work as expected, which you can test yourself -

// IN() usage
// sql fragment 
WHERE p_cate_id IN(?,?)

// corresponding prepared query input parameters
$params[] = some_value_1;
$params[] = some_value_2;

// find_in_set usage
// sql fragment
WHERE FIND_IN_SET(p_cat_id,?)

// corresponding prepared query input parameter
$params[] = implode(',',[some_value_1,some_value_2]);