CVE-2023-30849: Improper Neutralization of Special Elements used in an SQL Command ('SQL Injection') (CWE-89)
value: Pimcore is an open source data and experience management platform. Prior to version 10.5.21, A SQL injection vulnerability exists in the translation export API. Users should update to version 10.5.21 to receive a patch or, as a workaround, or apply the patch manually.
CWE description: The product constructs all or part of an SQL command using externally-influenced input from an upstream component, but it does not neutralize or incorrectly neutralizes special elements that could modify the intended SQL command when it is sent to a downstream component. Without sufficient removal or quoting of SQL syntax in user-controllable inputs, the generated SQL query can cause those inputs to be interpreted as SQL instead of ordinary user data.
CVSS v3 base score: 8.8
Published: 2023-04-27T16:15Z
Repository: https://github.com/pimcore/pimcore
Language: PHP
Files changed: bundles/AdminBundle/Controller/Admin/TranslationController.php
Commit date: 2023-04-20 10:07:32 +0200
Commit message: [Security] Fix Admin Translation Export SQL Injection (#14968)

* Fix admin translation export sql injection

* Change to setConditon

* Add SyntaxError Exception, replace sql comments

* Remove empty line

* Use const, remove unecessary line
Diff: diff --git a/bundles/AdminBundle/Controller/Admin/TranslationController.php b/bundles/AdminBundle/Controller/Admin/TranslationController.php
index 2e78979caf0e..16a5a132ca2d 100644
--- a/bundles/AdminBundle/Controller/Admin/TranslationController.php
+++ b/bundles/AdminBundle/Controller/Admin/TranslationController.php
@@ -13,10 +13,11 @@
  *  @license    http://www.pimcore.org/license     GPLv3 and PCL
  */
 
 namespace Pimcore\Bundle\AdminBundle\Controller\Admin;
 
+use Doctrine\DBAL\Exception\SyntaxErrorException;
 use Doctrine\DBAL\Query\QueryBuilder as DoctrineQueryBuilder;
 use Pimcore\Bundle\AdminBundle\Controller\AdminController;
 use Pimcore\File;
 use Pimcore\Localization\LocaleServiceInterface;
 use Pimcore\Logger;
@@ -47,10 +48,12 @@ use Symfony\Contracts\Translation\TranslatorInterface;
  *
  * @internal
  */
 class TranslationController extends AdminController
 {
+    protected const PLACEHOLDER_NAME = 'placeHolder';
+
     /**
      * @Route("/import", name="pimcore_admin_translation_import", methods={"POST"})
      *
      * @param Request $request
      * @param LocaleServiceInterface $localeService
@@ -181,23 +184,28 @@ class TranslationController extends AdminController
         $joins = [];
 
         $list->setOrder('asc');
         $list->setOrderKey($tableName . '.key', false);
 
-        $condition = $this->getGridFilterCondition($request, $tableName, false, $admin);
-        if ($condition) {
-            $list->setCondition($condition);
+        $conditions = $this->getGridFilterCondition($request, $tableName, false, $admin);
+        if (!empty($conditions)) {
+            $list->setCondition($conditions['condition'], $conditions['params']);
         }
 
         $filters = $this->getGridFilterCondition($request, $tableName, true, $admin);
 
         if ($filters) {
             $joins = array_merge($joins, $filters['joins']);
         }
 
         $this->extendTranslationQuery($joins, $list, $tableName, $filters);
-        $list->load();
+
+        try {
+            $list->load();
+        } catch (SyntaxErrorException $syntaxErrorException) {
+            throw new \InvalidArgumentException('Check your arguments.');
+        }
 
         $translations = [];
         $translationObjects = $list->getTranslations();
 
         // fill with one dummy translation if the store is empty
@@ -458,23 +466,28 @@ class TranslationController extends AdminController
             }
 
             $list->setLimit($request->get('limit'));
             $list->setOffset($request->get('start'));
 
-            $condition = $this->getGridFilterCondition($request, $tableName, false, $admin);
+            $conditions = $this->getGridFilterCondition($request, $tableName, false, $admin);
             $filters = $this->getGridFilterCondition($request, $tableName, true, $admin);
 
             if ($filters) {
                 $joins = array_merge($joins, $filters['joins']);
             }
-            if ($condition) {
-                $list->setCondition($condition);
+
+            if (!empty($conditions)) {
+                $list->setCondition($conditions['condition'], $conditions['params']);
             }
 
             $this->extendTranslationQuery($joins, $list, $tableName, $filters);
 
-            $list->load();
+            try {
+                $list->load();
+            } catch (SyntaxErrorException $syntaxErrorException) {
+                throw new \InvalidArgumentException('Check your arguments.');
+            }
 
             $translations = [];
             $searchString = $request->get('searchString');
             foreach ($list->getTranslations() as $t) {
                 //Reload translation to get complete data,
@@ -572,15 +585,16 @@ class TranslationController extends AdminController
 
     /**
      * @param Request $request
      * @param string $tableName
      * @param bool $languageMode
-     *
-     * @return array|null|string
+     * @param bool $admin
+     * @return array
      */
     protected function getGridFilterCondition(Request $request, $tableName, $languageMode = false, $admin = false)
     {
+        $placeHolderCount = 0;
         $joins = [];
         $conditions = [];
         $validLanguages = $admin ? Tool\Admin::getLanguages() : $this->getAdminUser()->getAllowedLanguagesForViewingWebsiteTranslations();
 
         $db = \Pimcore\Db::get();
@@ -590,19 +604,21 @@ class TranslationController extends AdminController
         if ($filterJson) {
             $propertyField = 'property';
             $operatorField = 'operator';
 
             $filters = $this->decodeJson($filterJson);
+
             foreach ($filters as $filter) {
                 $operator = '=';
                 $field = null;
                 $value = null;
 
                 $fieldname = $filter[$propertyField];
                 if (in_array(ltrim($fieldname, '_'), $validLanguages)) {
                     $fieldname = ltrim($fieldname, '_');
                 }
+                $fieldname = str_replace('--', '', $fieldname);
 
                 if (!$languageMode && in_array($fieldname, $validLanguages)
                     || $languageMode && !in_array($fieldname, $validLanguages)) {
                     continue;
                 }
@@ -639,35 +655,51 @@ class TranslationController extends AdminController
                         $conditions[$fieldname] = $condition;
                         $joins[] = [
                             'language' => $fieldname,
                         ];
                     } else {
-                        $conditionFilters[] = $condition;
+                        $placeHolderName = self::PLACEHOLDER_NAME . $placeHolderCount;
+                        $placeHolderCount++;
+                        $conditionFilters[] = [
+                            'condition' => $field . ' ' . $operator . ' :' . $placeHolderName,
+                            'field' => $placeHolderName,
+                            'value' => $value
+                        ];
                     }
                 }
             }
         }
 
         if ($request->get('searchString')) {
-            $filterTerm = $db->quote('%' . mb_strtolower($request->get('searchString')) . '%');
-            $conditionFilters[] = '(lower(' . $tableName . '.key) LIKE ' . $filterTerm . ' OR lower(' . $tableName . '.text) LIKE ' . $filterTerm . ')';
+            $conditionFilters[] = [
+                'condition' => '(lower(' . $tableName . '.key) LIKE :filterTerm OR lower(' . $tableName . '.text) LIKE :filterTerm)',
+                'field' => 'filterTerm',
+                'value' => '%' . mb_strtolower($request->get('searchString')) . '%'
+            ];
         }
 
         if ($languageMode) {
-            $result = [
+            return [
                 'joins' => $joins,
                 'conditions' => $conditions,
             ];
+        }
 
-            return $result;
-        } else {
-            if (!empty($conditionFilters)) {
-                return implode(' AND ', $conditionFilters);
+        if(!empty($conditionFilters)) {
+            $conditions = [];
+            $params = [];
+            foreach($conditionFilters as $conditionFilter) {
+                $conditions[] = $conditionFilter['condition'];
+                $params[$conditionFilter['field']] = $conditionFilter['value'];
             }
 
-            return null;
+            $conditionFilters = [
+                'condition' => implode(' AND ', $conditions),
+                'params' => $params
+            ];
         }
+        return $conditionFilters;
     }
 
     /**
      * @Route("/cleanup", name="pimcore_admin_translation_cleanup", methods={"DELETE"})
      *

Vulnerable code:         $condition = $this->getGridFilterCondition($request, $tableName, false, $admin);
        if ($condition) {
            $list->setCondition($condition);
        $list->load();
            $condition = $this->getGridFilterCondition($request, $tableName, false, $admin);
            if ($condition) {
                $list->setCondition($condition);
            $list->load();
     *
     * @return array|null|string
                        $conditionFilters[] = $condition;
            $filterTerm = $db->quote('%' . mb_strtolower($request->get('searchString')) . '%');
            $conditionFilters[] = '(lower(' . $tableName . '.key) LIKE ' . $filterTerm . ' OR lower(' . $tableName . '.text) LIKE ' . $filterTerm . ')';
            $result = [
            return $result;
        } else {
            if (!empty($conditionFilters)) {
                return implode(' AND ', $conditionFilters);
            return null;
Fixed code: use Doctrine\DBAL\Exception\SyntaxErrorException;
    protected const PLACEHOLDER_NAME = 'placeHolder';

        $conditions = $this->getGridFilterCondition($request, $tableName, false, $admin);
        if (!empty($conditions)) {
            $list->setCondition($conditions['condition'], $conditions['params']);

        try {
            $list->load();
        } catch (SyntaxErrorException $syntaxErrorException) {
            throw new \InvalidArgumentException('Check your arguments.');
        }
            $conditions = $this->getGridFilterCondition($request, $tableName, false, $admin);

            if (!empty($conditions)) {
                $list->setCondition($conditions['condition'], $conditions['params']);
            try {
                $list->load();
            } catch (SyntaxErrorException $syntaxErrorException) {
                throw new \InvalidArgumentException('Check your arguments.');
            }
     * @param bool $admin
     * @return array
        $placeHolderCount = 0;

                $fieldname = str_replace('--', '', $fieldname);
                        $placeHolderName = self::PLACEHOLDER_NAME . $placeHolderCount;
                        $placeHolderCount++;
                        $conditionFilters[] = [
                            'condition' => $field . ' ' . $operator . ' :' . $placeHolderName,
                            'field' => $placeHolderName,
                            'value' => $value
                        ];
            $conditionFilters[] = [
                'condition' => '(lower(' . $tableName . '.key) LIKE :filterTerm OR lower(' . $tableName . '.text) LIKE :filterTerm)',
                'field' => 'filterTerm',
                'value' => '%' . mb_strtolower($request->get('searchString')) . '%'
            ];
            return [
        }
        if(!empty($conditionFilters)) {
            $conditions = [];
            $params = [];
            foreach($conditionFilters as $conditionFilter) {
                $conditions[] = $conditionFilter['condition'];
                $params[$conditionFilter['field']] = $conditionFilter['value'];
            $conditionFilters = [
                'condition' => implode(' AND ', $conditions),
                'params' => $params
            ];
        return $conditionFilters;