99% of the time you're better off using stored procedures over queries for security, performance and scalability.
Ideally the only permission the MySQL user needs is "execute" providing a huge security boost to the application and it also prevents another "lazy" developers mashing in bad code which could open up the application to SQL Injection.
A really rough example of what I am working out how to do within a stored procedure is the following:
public function search($filters)
{
//sample data
$filters = array('size'=>'10', 'color'=>'blue');
foreach ($filters as $name=>$value)
{
//potential SQL INJECTION vulnerability
$sqlExtras .= " AND (`a`.`".$name."`=':".$name."')";
$array[':'.$name] = $value;
}
$query = "SELECT `p`.`id`, `p`.`name`
FROM `products` `p`
INNER JOIN `attributesEntity` `ae` ON `p`.`id`=`ae`.`product_id`
INNER JOIN `attributes` `a` ON `a`.`id`.``ae`.attribute_id`
WHERE `p`.`delete`!='1'
".$sqlExtras;
$stmt = $this->db->prepare($query);
$stmt->execute($array);
}
However this would require opening up SELECT permissions and then involves having part of application using stored procedures and part using general queries which is a complete mess in my personal opinion.
Currently however there doesn't seem to be an effective way of doing this in Stored Procedures without allowing the Application Code to pass through its own generated section of the query to append at the WHERE stage.
The only way I can work out at the moment would be along the lines of:
CREATE DEFINER=`USER`@`IP` PROCEDURE `getProductsByAttributes`(
IN `attr_name` VARCHAR(50),
IN `attr_value` VARCHAR(255)
)
COMMENT 'Get a list of products matching the attribute and value'
BEGIN
SELECT
`p`.`id`
FROM `products` `p`
INNER JOIN `attributes_entity` `ae` ON `p`.`id` = `ae`.`product_id`
INNER JOIN `attributes` `a` ON `a`.`id` = `ae`.`attribute_id`
WHERE `a`.`name` = `attr_name`
AND `ae`.`value` = `attr_value`;
END;
public function filter($filters)
{
foreach ($filters as $name=>$value)
{
$stmt = $this->db->prepare("CALL getProductsByAttributes(:name, :value)");
$stmt->bindParam(':name', $name, PDO:PARAM_STR, 50);
$stmt->bindParam(':value', $name, PDO:PARAM_STR, 150);
$stmt->execute();
$array[$name] = $stmt->fetchAll();
}
//build a comma delimited string of product id's matching all filter sets ($list)
//call a SP to retrieve products matching $list
$stmt = $this->db->prepare("CALL getProductsByIdList(:list)");
$stmt->bindParam(':list', $list);
$stmt->execute();
return $stmt->fetchAll();
}
The above code is a bit messy but the general idea. The problem is 15+ individual queries could be preformed depending on the number of filters where it could essentially be done by 1 more "complex" query.
Cheers
John
