Friday, January 20, 2017

How to implode these conditions to achieve this query structure?

By Hường Hana 2:30 AM , 8 comments

Below is my working query that gets into action after a form submit. My query works only if all textboxes are filled, so currently everything is required.

WORKING QUERY

SELECT behaviour.hash,         Sum(behaviour.timespent) AS timeSpent,         new_table.percentile_rank,         Count(*) AS total  FROM   behaviour,         audience,         new_table  WHERE  ( $url )         AND behaviour.timestamp >= Date_sub(Curdate(), INTERVAL $last_visit day) AND behaviour.timestamp < Date_add(Curdate(), INTERVAL 1 day)         AND behaviour.hash = audience.hash         AND behaviour.hash = new_table.hash         AND audience.country = '$from_country'  GROUP  BY behaviour.hash  HAVING Count(*) >= $more_than         AND timespent >= $time_spent         AND new_table.percentile_rank >= $lead_scoring

What I want to achieve is not to require the user to fill all the textboxes in order to submit, but only those he prefers. So I built the following, but it has some errors.

My problem is that my query has a having clause so not every condition is connected with AND as I have for now (look below my code). So, the first of the $more_than or $time_spent or $lead_scoring textboxes that submitted, it must have HAVING instead of AND.

How to edit my code in order to achieve this "special conditions" ?

MY CODE

$url= 'url="'.implode('" OR url="', $vals).'"';  $conditions = array();  if (!empty($last_visit)) $conditions[] = "behaviour.TIMESTAMP >= DATE_SUB( CURDATE( ) , INTERVAL '".$last_visit."' DAY) AND behaviour.TIMESTAMP < DATE_ADD( CURDATE( ) , INTERVAL 1 DAY ) "; if (!empty($from_country)) $conditions[] = "audience.country = '".$from_country."'"; if (!empty($more_than)) $conditions[] = "COUNT( * ) >= '".$more_than."'";  if (!empty($time_spent)) $conditions[] = "timeSpent >= '".$time_spent."'"; if (!empty($lead_scoring)) $conditions[] = "new_table.percentile_rank >= '".$lead_scoring."'";   $conditionString = implode(' AND ', $conditions);   $sql = "SELECT behaviour.hash,         Sum(behaviour.timespent) AS timeSpent,         new_table.percentile_rank,         Count( * ) AS total  FROM   behaviour,         audience,         new_table  WHERE  ($url) AND ".$conditionString;

CURRENT OUTPUT

In the example below, all textboxes except more_than have been filled. The thing is that instead AND timespent >= '20' should be HAVING timespent >= '20'

SELECT behaviour.hash,         SUM(behaviour.timespent) AS timeSpent,         new_table.percentile_rank,         Count(*) AS total  FROM   behaviour,         audience,         new_table  WHERE  ( url = "/10369" )         AND behaviour.timestamp >= Date_sub(Curdate(), interval '3' day)         AND behaviour.timestamp < Date_add(Curdate(), interval 1 day)         [missing]     AND behaviour.hash = audience.hash        [missing]     AND behaviour.hash = new_table.hash        AND audience.country = 'it'         [missing]     GROUP BY behaviour.hash        [wrong]       AND timespent >= '20' ////// it should be HAVING /////        AND new_table.percentile_rank >= '30'

6 Answers

Answers 1

First, you must ensure SQL injection is not possible. To do that, lets use PDO.

Next, to solve your actual problem, you simple need to create two lists with conditions. One with the conditions you want to have in the WHERE part of the query, and one with the conditions that need to go in the HAVING part of the query.

    $pdo = new PDO(/* See http://php.net/manual/en/pdo.construct.php */);      $whereConditions = [];     $havingConditions = [];     $parameters = [];      if (!empty($last_visit)) {         $whereConditions[] = "behaviour.TIMESTAMP >= DATE_SUB( CURDATE( ) , INTERVAL :last_visit DAY) AND behaviour.TIMESTAMP < DATE_ADD( CURDATE( ) , INTERVAL 1 DAY ) ";         $parameters['last_visit'] = $last_visit;     }     if (!empty($from_country)) {         $whereConditions[] = "audience.country = :from_country";         $parameters['from_country'] = $from_country;     }     if (!empty($more_than)) {         $havingConditions[] = "COUNT( * ) >= :more_than";         $parameters['more_than'] = $more_than;     }     if (!empty($time_spent)) {         $havingConditions[] = "timeSpent >= :time_spent";         $parameters['time_spent'] = $time_spent;      }     if (!empty($lead_scoring)) {         $havingConditions[] = "new_table.percentile_rank >= :lead_scoring";         $parameters['lead_scoring'] = $lead_scoring;     }      if (count($vals)) {         $escapedUrlList = implode(', ', array_map(function ($url) use ($pdo) {             return $pdo->quote($url);         }, $vals));         $whereConditions[] = "url IN($escapedUrlList)";     }      $whereClause = count($whereConditions) ? ' AND ' . implode(' AND ', $whereConditions) : '';     $havingClause = count($havingConditions) ? ' HAVING ' . implode(' AND ', $havingConditions) : '';      $statement = $pdo->prepare("         SELECT behaviour.hash,              Sum(behaviour.timespent) AS timeSpent,              new_table.percentile_rank,              Count(*) AS total          FROM behaviour,              audience,              new_table          WHERE behaviour.hash = audience.hash              AND behaviour.hash = new_table.hash              {$whereClause}         GROUP  BY behaviour.hash         {$havingClause}     ");      $result = $statement->execute($parameters);

Answers 2

Here is a less complicated way using string concatenation instead of implode. The "trick" is to start the conditions with 1=1. So every following condition can begin with AND.

$andWhere  = ''; $andHaving = ''; $params = [];   if (!empty($last_visit)) {     $andWhere .= " AND behaviour.TIMESTAMP >= CURDATE() - INTERVAL :last_visit DAY AND behaviour.TIMESTAMP < CURDATE() + INTERVAL 1 DAY";     $params['last_visit'] = $last_visit; } if (!empty($from_country)) {     $andWhere .= " AND audience.country = :from_country";     $params['from_country'] = $from_country; } if (!empty($more_than)) {     $andHaving .= " AND COUNT( * ) >= :more_than";     $params['more_than'] = $more_than; } if (!empty($time_spent)) {     $andHaving .= " AND timeSpent >= :time_spent";     $params['time_spent'] = $time_spent; } if (!empty($lead_scoring)) {     $andHaving .= " AND new_table.percentile_rank >= :lead_scoring";     $params['lead_scoring'] = $lead_scoring; }  $urlPlaceholders = []; foreach ($vals as $key => $val) {     $urlPlaceholders[] = ":url_$key";     $params["url_$key"] = $val; } if (count($vals) > 0) {     $inUrl = implode(',', $urlPlaceholders);     $andWhere .= " AND url IN ($inUrl)"; }  $sql = "     SELECT behaviour.hash,             Sum(behaviour.timespent) AS timeSpent,             new_table.percentile_rank,             Count(*) AS total      FROM   behaviour      JOIN   audience  ON behaviour.hash = audience.hash     JOIN   new_table ON behaviour.hash = new_table.hash     WHERE  1=1 {$andWhere}     GROUP  BY behaviour.hash      HAVING 1=1 {$andHaving} ";  #var_export($sql); #var_export($params);  $sth = $dbh->prepare($sql); $sth->execute($params); $data = $sth->fetchAll(PDO::FETCH_ASSOC);  #var_export($data);

Having sample data like

$last_visit   = ''; $from_country = 'UK'; $more_than    = '5'; $time_spent   = '3'; $lead_scoring = ''; $vals = ['u1', 'u2'];

You would get the following query:

SELECT behaviour.hash,         Sum(behaviour.timespent) AS timeSpent,         new_table.percentile_rank,         Count(*) AS total  FROM   behaviour  JOIN   audience  ON behaviour.hash = audience.hash JOIN   new_table ON behaviour.hash = new_table.hash WHERE  1=1  AND audience.country = :from_country AND url IN (:url_0,:url_1) GROUP  BY behaviour.hash  HAVING 1=1  AND COUNT(*) >= :more_than AND timeSpent >= :time_spent

with these bindings:

array (   'from_country' => 'UK',   'more_than' => '5',   'time_spent' => '3',   'url_0' => 'u1',   'url_1' => 'u2', )

Demo on rextester.com

Answers 3

Here's a bit "tricky" approach (looks clean though) that is using prepared statements. I've added some general purpose "features" in case of future changes. Read the comments with explanations (will be more convenient this way I think):

//assume established PDO connection - example: try {     $pdo = new PDO("mysql:dbname={$database_name};host=localhost", $user, $password); } catch (PDOException $e) {     echo 'Connection failed: ' . $e->getMessage(); }  //static: conditional strings without parameters check (no keys required) //conditional: assoc - keys should match both query placeholders and variable names $static_where = []; $optional_where = [     'last_visit'   => 'behaviour.TIMESTAMP >= DATE_SUB(CURDATE(), INTERVAL :last_visit DAY) AND behaviour.TIMESTAMP < DATE_ADD(CURDATE(), INTERVAL 1 DAY)',     'from_country' => 'audience.country = :from_country' ];  $static_having = []; $optional_having = [     'more_than'    => 'COUNT(*) >= :more_than',     'time_spent'   => 'timeSpent >= :time_spent',     'lead_scoring' => 'new_table.percentile_rank >= :lead_scoring' ];  //params: query parameters array - assigned manually + resolved from optional variables $params = [];  //resolve condition from $urls array if (count($urls) == 1) {     $static_where[] = 'url = :url';     $params['url'] = reset($urls); } else if (!empty($urls)) {     foreach ($urls as $idx => $url) {         $params['url' . $idx] = $url;     }     $static_where[] = 'url IN(:' . implode(', :', array_keys($params)) . ')'; }  //filtering existing params used in query //empty() is not a good idea for general purpose though, //because some valid values might be recognised as empty (int 0, string '0') $params += array_filter(     compact(array_keys($optional_where), array_keys($optional_having)),     function ($value) { return !empty($value); } );  //concatenating conditional strings //with corresponding params that weren't filtered out //or these without params (static) $where_clause = implode(' AND ', $static_where + array_intersect_key($optional_where, $params)); $having_clause = implode(' AND ', $static_having + array_intersect_key($optional_having, $params));  //don't need clauses without conditions - same as if (!empty($where)) {...} empty($where_clause) or $where_clause = 'WHERE ' . $where_clause; empty($having_clause) or $having_clause = 'HAVING ' . $having_clause;  $sql = "SELECT            behaviour.hash,           Sum(behaviour.timespent) AS timeSpent,           new_table.percentile_rank,           Count( * ) AS total          FROM behaviour,         INNER JOIN audience ON behaviour.hash = audience.hash,         INNER JOIN new_table ON behaviour.hash = new_table.hash          {$where_clause}         GROUP BY behaviour.hash          {$having_clause}";  //PDO part $query = $pdo->prepare($sql); $result = $query->execute($params); //...

Answers 4

If having is the only problem why not splitting it into different block like this: 

$conditions = array();  if (!empty($last_visit)) $conditions[] = "behaviour.TIMESTAMP >= DATE_SUB( CURDATE( ) , INTERVAL '".$last_visit."' DAY) AND behaviour.TIMESTAMP < DATE_ADD( CURDATE( ) , INTERVAL 1 DAY ) "; if (!empty($from_country)) $conditions[] = "audience.country = '".$from_country."'";   $conditionString = implode(' AND ', $conditions);  $conditions_having = array(); if (!empty($more_than)) $conditions_having[] = "COUNT( * ) >= '".$more_than."'";  if (!empty($time_spent)) $conditions_having[] = "timeSpent >= '".$time_spent."'"; if (!empty($lead_scoring)) $conditions_having[] = "new_table.percentile_rank >= '".$lead_scoring."'";  $conditionString .= " GROUP  BY behaviour.hash"  if(count($conditions_having)) $conditionString .= " HAVING ".implode(' AND ', $conditions_having);   $sql = "SELECT behaviour.hash,         Sum(behaviour.timespent) AS timeSpent,         new_table.percentile_rank,         Count( * ) AS total  FROM   behaviour,         audience,         new_table  WHERE  ($url) AND ".$conditionString;

Answers 5

You can change only your implode function to this code

$conditionString = implode(' ', array_map(function($item) { if ((strpos($item, 'timeSpent') !== false))     return 'HAVING '.$item; return 'AND '.$item; }, $conditions));

Be aware that your code is vulnerable.

for more information see this: SQL Injection In CAPEC

Answers 6

  $url= 'url="'.implode('" OR url="', $vals).'"';    $conditions = array();   $havings = array();      if (!empty($last_visit)) $conditions[] = "behaviour.TIMESTAMP >= DATE_SUB( CURDATE( ) , INTERVAL '".$last_visit."' DAY) AND behaviour.TIMESTAMP < DATE_ADD( CURDATE( ) , INTERVAL 1 DAY ) ";     if (!empty($from_country)) $conditions[] = "audience.country = '".$from_country."'";     if (!empty($more_than)) $havings[] = "COUNT( * ) >= '".$more_than."'";      if (!empty($time_spent)) $havings[] = "timeSpent >= '".$time_spent."'";     if (!empty($lead_scoring)) $havings[] = "new_table.percentile_rank >= '".$lead_scoring."'";       $conditionString = implode(' AND ', $conditions);      $havingString = '';     if(count($havings)>0) {          $havingString = ' having '.implode(', ', $havings);         }      $sql = "SELECT behaviour.hash,             Sum(behaviour.timespent) AS timeSpent,             new_table.percentile_rank,             Count( * ) AS total      FROM   behaviour,             audience,             new_table      WHERE  ($url) AND ".$conditionString.$havingString;
If You Enjoyed This, Take 5 Seconds To Share It

8 comments: