tests/phpunit/CRM/Core/DAOTest.php

   1 <?php
   2
   3 require_once 'CiviTest/CiviUnitTestCase.php';
   4 class CRM_Core_DAOTest extends CiviUnitTestCase {
   5   function composeQueryExamples() {
   6     $cases = array();
   7     // $cases[] = array('Input-SQL', 'Input-Params', 'Expected-SQL');
   8
   9     // CASE: No params
  10     $cases[] = array(
  11       'SELECT * FROM whatever',
  12       array(),
  13       'SELECT * FROM whatever',
  14     );
  15
  16     // CASE: Integer param
  17     $cases[] = array(
  18       'SELECT * FROM whatever WHERE id = %1',
  19       array(
  20         1 => array(10, 'Integer'),
  21       ),
  22       'SELECT * FROM whatever WHERE id = 10',
  23     );
  24
  25     // CASE: String param
  26     $cases[] = array(
  27       'SELECT * FROM whatever WHERE name = %1',
  28       array(
  29         1 => array('Alice', 'String'),
  30       ),
  31       'SELECT * FROM whatever WHERE name = \'Alice\'',
  32     );
  33
  34     // CASE: Two params
  35     $cases[] = array(
  36       'SELECT * FROM whatever WHERE name = %1 AND title = %2',
  37       array(
  38         1 => array('Alice', 'String'),
  39         2 => array('Bob', 'String'),
  40       ),
  41       'SELECT * FROM whatever WHERE name = \'Alice\' AND title = \'Bob\'',
  42     );
  43
  44     // CASE: Two params with special character (%1)
  45     $cases[] = array(
  46       'SELECT * FROM whatever WHERE name = %1 AND title = %2',
  47       array(
  48         1 => array('Alice %2', 'String'),
  49         2 => array('Bob', 'String'),
  50       ),
  51       'SELECT * FROM whatever WHERE name = \'Alice %2\' AND title = \'Bob\'',
  52     );
  53
  54     // CASE: Two params with special character ($1)
  55     $cases[] = array(
  56       'SELECT * FROM whatever WHERE name = %1 AND title = %2',
  57       array(
  58         1 => array('Alice $1', 'String'),
  59         2 => array('Bob', 'String'),
  60       ),
  61       'SELECT * FROM whatever WHERE name = \'Alice $1\' AND title = \'Bob\'',
  62     );
  63
  64     return $cases;
  65   }
  66
  67   /**
  68    * @dataProvider composeQueryExamples
  69    */
  70   function testComposeQuery($inputSql, $inputParams, $expectSql) {
  71     $actualSql = CRM_Core_DAO::composeQuery($inputSql, $inputParams);
  72     $this->assertEquals($expectSql, $actualSql);
  73   }
  74
  75   // CASE: Two params where the %2 is already present in the query
  76   // NOTE: This case should rightly FAIL, as using strstr in the replace mechanism will turn
  77   // the query into: SELECT * FROM whatever WHERE name = 'Alice' AND title = 'Bob' AND year LIKE ''Bob'012'
  78   // So, to avoid such ERROR, the query should be framed like:
  79   // 'SELECT * FROM whatever WHERE name = %1 AND title = %3 AND year LIKE '%2012'
  80   // $params[3] = array('Bob', 'String');
  81   // i.e. the place holder should be unique and should not contain in any other operational use in query
  82   function testComposeQueryFailure() {
  83     $cases[] = array(
  84       'SELECT * FROM whatever WHERE name = %1 AND title = %2 AND year LIKE \'%2012\' ',
  85       array(
  86         1 => array('Alice', 'String'),
  87         2 => array('Bob', 'String'),
  88       ),
  89       'SELECT * FROM whatever WHERE name = \'Alice\' AND title = \'Bob\' AND year LIKE \'%2012\' ',
  90     );
  91     list($inputSql, $inputParams, $expectSql) = $cases[0];
  92     $actualSql = CRM_Core_DAO::composeQuery($inputSql, $inputParams);
  93     $this->assertFalse(($expectSql == $actualSql));
  94   }
  95 }