ZF3 - Zend\Db\Sql\Predicate\Expression - Wrong escaping on SQL DATE_FORMAT

Question

I'm trying to filter a radius accounting database based on the submitted session start time. The field "acctstartime" is defined as DATETIME in the MySQL database.

This is the working SQL query I'd like to build:

SELECT * FROM radacct WHERE DATE_FORMAT(acctstarttime, '%Y-%m-%d') = DATE_FORMAT('2019-08-10' , '%Y-%m-%d') ORDER BY "radacctid" DESC LIMIT 100

In my ZF3-Controller, I'm building the SQL-query based on the submitted parameters using the Zend\Db abstraction layer. But unforunately the Db layer escapes the %-Signs and produces a SQL-string which is not working:

SELECT * FROM radacct WHERE DATE_FORMAT(acctstarttime, '%%Y-%%m-%%d') = DATE_FORMAT('2019-01-01' , '%%Y-%%m-%%d') AND "acctstarttime" = '2019-01-01' ORDER BY "radacctid" DESC LIMIT 100

In the file zend-db/src/Sql/Expression.php on line 136:

$expression = str_replace('%', '%%', $this->expression);

Has anybody a clue how to generate the query above? Or maybe another way to do the same thing? I haven't found any workaround for this type of expression using DATE_TIME.

---

The controller:

class AccountingController extends AbstractRestfulJsonController {
    protected $service;

    public function __construct(RadiusService $service) {
        $this->service = $service;
    }

    public function get($id) {
        return $this->getList();
    }

    public function getList() {
        $viewModel = new JsonModel();

        try {
            $filter = [];
            $paramsRoute = $this->params()->fromRoute();
            $paramsQuery = $this->params()->fromQuery();
            $params = (OBJECT) array_merge($paramsRoute, $paramsQuery);

            if(isset($params->id)) {
                $filter['radacctid'] = (int) $params->id;
            }

            // ...

            if(isset($params->sessionStartTime) && strlen($params->sessionStartTime) > 0) {
                $filter[] = new Expression("DATE_FORMAT(acctstarttime, '%Y-%m-%d') = DATE_FORMAT('" . $params->sessionStartTime . "' , '%Y-%m-%d')");
            }

            // ...

            $data = $this->service->getAccounting($filter);

            $viewModel->setVariable('state', 'ok');
            $viewModel->setVariable('count', $data['totalItems']);
            $viewModel->setVariable('data', $data['items']);
        } catch (\Exception $e) {
            $viewModel->setVariable('state', 'nok');
            $viewModel->setVariable('message', $e->getMessage());
        }

        return $viewModel;
    }
}

Answer 1

so what about "SUBSTRING(acctstarttime, 1, 10) = '". substr($params->sessionStartTime, 0, 10) . "'" ?

Answer 2

The other way, maybe "cleaner" is writing your own class and override getExpressionData method to not escape values. For example:

    use Zend\Db\Sql\Predicate\Expression;

    public class NonEscapedExpression extends Expression
    {
        /**
         * @return array
         * @throws Exception\RuntimeException
        */
        public function getExpressionData()
        {
            $parameters = (is_scalar($this->parameters)) ? [$this->parameters] : $this->parameters;
            $parametersCount = count($parameters);

            if ($parametersCount == 0) {
                return [
                    str_ireplace(self::PLACEHOLDER, '', $expression)
                ];
            }
            // assign locally, escaping % signs
            $expression = str_replace(self::PLACEHOLDER, '%s', $expression, $count);
            // test number of replacements without considering same variable begin used many times first, which is
            // faster, if the test fails then resort to regex which are slow and used rarely
            if ($count !== $parametersCount && $parametersCount === preg_match_all('/\:[a-zA-Z0-9_]*/', $expression)) {
                throw new Exception\RuntimeException(
                    'The number of replacements in the expression does not match the number of parameters'
                );
            }
            foreach ($parameters as $parameter) {
                list($values[], $types[]) = $this->normalizeArgument($parameter, self::TYPE_VALUE);
            }
            return [[
                $expression,
                $values,
                $types
            ]];
        }
    }

But you shuould use it carefully.