MYSQL can a WHERE = anything?

Question

This is what worked for me in the end. Its a small project so efficiency is not a big deal. Just wondering how it looks now.

I tried doing it the suggested way but I did not really understand it and as such I could not trouble shoot it when I could not get it working :P.

This I do understand.

if(!empty($_POST)) 
    { 

$project = $_POST['project'];
if ($project == "All") {
    $project = '';
}

$category = $_POST['category'];
if ($category == "All") {
    $category = '';
}

$services = $_POST['services'];
if ($services == "All") {
    $services = '';
}

$priority =  $_POST['priority']; 
if ($priority == "All") {
    $priority = '';
}

$query = $db->prepare
        ("SELECT * FROM event 
        WHERE Project LIKE '%$project%' AND
        EventCategory LIKE '%$category%' AND 
        EventServices LIKE '%$services%' AND
        EventPriority LIKE '%$priority%';");
$query->execute();
$result = $query;
$db= NULL;
        }

`$project = $_POST['priority'];`. Is that an error? `$project` -> `$priority` — GolezTrol, Aug 23 '14 at 18:35
http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?lq=1 — user2864740, Aug 23 '14 at 18:35
it is an error but only in the post, I had that fixed already :P — Gavio101, Aug 23 '14 at 20:18
Are you sure that these are meant to be `and`s? Your question implies that you want to return something if there is only one hit, which would require ORs. — Ben, Aug 23 '14 at 20:52
Don't use LIKE and wildcards for this. That type of solution is to be used if the user can type some free text to search with. If the user is selecting the value from a list of possible values, then you should use the = operator for an exact match. That allows MySQL to use indexes and optimize your query. Also make sure you use parameters so as to mimimize the possibility of SQL injection. — Frazz, Aug 24 '14 at 09:31

GolezTrol · Answer 1 · 2014-08-24T09:19:30.093

One possibility is to build the query based on the values you have. In this code, I add conditions to the query based on the post values. The filter values themselves are put in an array to be used as parameters for the query, since this removes the risk of SQL injection without too much hassle.

The result looks like this:

$extraConditions = '';
$params = array();

if ($project !== 'ALL') {
  $extraConditions .= ' AND project = :project';
  $params[':project'] = $project;
}
if ($priority !== 'ALL') {
  $extraConditions .= ' AND priority = :priority';
  $params[':priority'] = $priority;
}
if ($category !== 'ALL') {
  $extraConditions .= ' AND category = :category';
  $params[':category'] = $category;
}


$query = $db->prepare
        ("SELECT * FROM event WHERE 1 = 1 $extraConditions");
$query->execute($params); // <- This is where you pass the filter values.

The 1=1 is added because of lazyness. Every extra condition has AND in front of it. Without WHERE 1=1 already in the query, it would be invalid, and you'd have to change the first AND to WHERE. I think this is cleaner.

You could change it a little to define a list of filters. If they match the column names, then you can create a simple loop, and you can allow filtering on extra columns by just adding them to the filters array.

$extraConditions = '';
$params = array();

$filters = array('project', 'category', 'priority');

foreach ($filters as $filter)
{
  if (array_key_exists($filter, $_POST)
  {
    $value = $_POST[$filter];
    $extraConditions .= " AND $filter = :$filter";
    $params[":$filter"] = $value;
  }
}

$query = $db->prepare
        ("SELECT * FROM event WHERE 1 = 1 $extraConditions");
$query->execute($params);

Either solution uses query parameters, so it's a bit safer than just putting the post values straight away into the query.

This is IMHO the best answer proposed... dynamic SQL with parameters. The 1=1 can be eliminated with a bit of extra code. I think MySQL should be capable of optimizing it out, but I wouldn't want to find out the contrary ;) — Frazz, Aug 24 '14 at 09:28
Thanks, @Frazz. Admittedly, it is a bit lazy to do it like this, and it takes only a little more code to eliminate `1=1` altogether. I think MySQL is smart enough to optimize this, but it never hurts to check before using it in a production environment. — GolezTrol, Aug 24 '14 at 16:46

score -1 · Answer 2 · answered Aug 23 '14 at 18:42

-1

In sql wheris no wildcards in WHERE condition except LIKE for text fields, for your task you need to build your query depends on conditions

answered Aug 23 '14 at 18:42

Dmitry Bezik

349
1
5

score -1 · Answer 3 · answered Aug 23 '14 at 18:47

-1

You could use LIKE operator

SELECT * FROM event 
WHERE Project = '$project' AND --where your project variable can have the value '%' instead of All
EventCategory = '$category' AND 
EventServices = '$services' AND
EventPriority = '$priority';

answered Aug 23 '14 at 18:47

Logar

1,248
9
17

1

this is VERY dangerous. you shouldn't post an answer that is vunerable to sql injection – John Ruddell Aug 23 '14 at 18:49
1

Right, my point there was just to use like operator, and i was editing my answer when i realized i put the $_POST directly in the query – Logar Aug 23 '14 at 18:54
kewl.. I just wanted to give you the opportunity to fix it before ppl downvoted :) – John Ruddell Aug 23 '14 at 18:56

MYSQL can a WHERE = anything?

3 Answers3