CVE-2021-34187: Improper Neutralization of Special Elements used in an SQL Command ('SQL Injection') (CWE-89)
value: main/inc/ajax/model.ajax.php in Chamilo through 1.11.14 allows SQL Injection via the searchField, filters, or filters2 parameter.
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: 9.8
Published: 2021-06-28T16:15Z
Repository: https://github.com/chamilo/chamilo-lms
Language: PHP
Files changed: public/main/inc/ajax/model.ajax.php, public/main/inc/lib/database.lib.php, public/main/inc/lib/extra_field.lib.php
Commit date: 2021-05-28 09:09:43 +0200
Commit message: Fix database field from 1.11.x

https://github.com/chamilo/chamilo-lms/commit/f7f93579ed64765c2667910b9c24d031b0a00571

Diff: diff --git a/public/main/inc/ajax/model.ajax.php b/public/main/inc/ajax/model.ajax.php
index a1599f5023..4e491dbed9 100644
--- a/public/main/inc/ajax/model.ajax.php
+++ b/public/main/inc/ajax/model.ajax.php
@@ -107,10 +107,12 @@ function getWhereClause($col, $oper, $val)
         'en' => 'NOT LIKE', //doesn't end with
         'cn' => 'LIKE', //contains
         'nc' => 'NOT LIKE',  //doesn't contain
     ];
 
+    $col = Database::escapeField($col);
+
     if (empty($col)) {
         return '';
     }
 
     if ($oper == 'bw' || $oper == 'bn') {
@@ -1440,12 +1442,13 @@ switch ($action) {
             $columns = array_merge($columns, $plagiarismColumns);
             $columns[] = 'actions';
         }
 
         $whereCondition = " AND $whereCondition ";
+        $columnOrderValidList = array_merge(['firstname', 'lastname'], $columns);
+        $sidx = in_array($sidx, $columnOrderValidList) ? $sidx : 'title';
 
-        $sidx = in_array($sidx, $columns) ? $sidx : 'title';
         $result = get_work_user_list(
             $start,
             $limit,
             $sidx,
             $sord,
@@ -2503,22 +2506,15 @@ switch ($action) {
                 if (!empty($keyword)) {
                     $options['where']['AND name like %?% '] = $keyword;
                 }
                 $result = $obj->getUserGroupNotInCourse(
                     $options,
-                    $groupFilter,
-                    false,
-                    true
+                    $groupFilter
                 );
                 break;
             case 'registered':
-                $result = $obj->getUserGroupInCourse(
-                    $options,
-                    $groupFilter,
-                    false,
-                    true
-                );
+                $result = $obj->getUserGroupInCourse($options, $groupFilter);
                 break;
         }
 
         $new_result = [];
         $currentUserId = api_get_user_id();
diff --git a/public/main/inc/lib/database.lib.php b/public/main/inc/lib/database.lib.php
index 1f91be98ae..06a377bb8b 100644
--- a/public/main/inc/lib/database.lib.php
+++ b/public/main/inc/lib/database.lib.php
@@ -749,6 +749,11 @@ class Database
      */
     public static function listTableColumns($table)
     {
         return self::getManager()->getConnection()->getSchemaManager()->listTableColumns($table);
     }
+
+    public static function escapeField($field)
+    {
+        return self::escape_string(preg_replace("/[^a-zA-Z0-9_]/", '', $field));
+    }
 }
diff --git a/public/main/inc/lib/extra_field.lib.php b/public/main/inc/lib/extra_field.lib.php
index 2dc244cb00..7087063334 100644
--- a/public/main/inc/lib/extra_field.lib.php
+++ b/public/main/inc/lib/extra_field.lib.php
@@ -1582,11 +1582,11 @@ class ExtraField extends Model
                         );
 
                         if ($freezeElement) {
                             $form->freeze('extra_'.$variable);
                         }
-                        break;
+                        break;                        
                     case self::FIELD_TYPE_FILE:
                         $fieldVariable = "extra_{$variable}";
                         $fieldTexts = [
                             $field_details['display_text'],
                         ];
@@ -2606,10 +2606,11 @@ JAVASCRIPT;
                     if (isset($rule->data) && is_string($rule->data) && -1 != $rule->data) {
                         $conditionArray[] = $this->get_where_clause($field, $rule->op, $rule->data);
                     }
                 } else {
                     // Extra fields
+                    $ruleField = Database::escapeField($rule->field);
                     if (false === strpos($rule->field, '_second')) {
                         //No _second
                         $original_field = str_replace($stringToSearch, '', $rule->field);
                         $field_option = $this->get_handler_field_info_by_field_variable($original_field);
 
@@ -2628,23 +2629,24 @@ JAVASCRIPT;
 
                             if (!isset($rule->data)) {
                                 $conditionArray[] = ' ('
                                 .$this->get_where_clause($rule->field, $rule->op, $rule->data)
                                 .') ';
-                                $extraFields[] = ['field' => $rule->field, 'id' => $field_option['id']];
+                                $extraFields[] = ['field' => $ruleField, 'id' => $field_option['id']];
                             }
                                 break;
                             case self::FIELD_TYPE_TAG:
                             if (isset($rule->data)) {
                                 if (is_int($rule->data) && -1 == $rule->data) {
                                     break;
                                 }
 
+                                    // Where will be injected in the parseConditions()
                                 //$where = $this->get_where_clause($rule->field, $rule->op, $rule->data, 'OR');
                                 //$conditionArray[] = " ( $where ) ";
                                 $extraFields[] = [
-                                'field' => $rule->field,
+                                        'field' => $ruleField,
                                 'id' => $field_option['id'],
                                 'data' => $rule->data,
                             ];
                             }
                                 break;
@@ -2654,11 +2656,11 @@ JAVASCRIPT;
                                         break;
                                     }
                                     $where = $this->get_where_clause($rule->field, $rule->op, $rule->data, 'OR');
                                     $conditionArray[] = " ( $where ) ";
                                     $extraFields[] = [
-                                        'field' => $rule->field,
+                                        'field' => $ruleField,
                                         'id' => $field_option['id'],
                                         'data' => $rule->data,
                                     ];
                                 }
                                 break;
@@ -2666,11 +2668,11 @@ JAVASCRIPT;
                     } else {
                         $my_field = str_replace('_second', '', $rule->field);
                         $original_field = str_replace($stringToSearch, '', $my_field);
                         $field_option = $this->get_handler_field_info_by_field_variable($original_field);
                         $extraFields[] = [
-                        'field' => $rule->field,
+                            'field' => $ruleField,
                         'id' => $field_option['id'],
                     ];
                     }
                 }
             }
@@ -2687,10 +2689,12 @@ JAVASCRIPT;
      *
      * @return string
      */
     public function get_where_clause($col, $oper, $val, $conditionBetweenOptions = 'OR')
     {
+        $col = Database::escapeField($col);
+
         if (empty($col)) {
             return '';
         }
         $conditionBetweenOptions = in_array($conditionBetweenOptions, ['OR', 'AND']) ? $conditionBetweenOptions : 'OR';
         if ('bw' === $oper || 'bn' === $oper) {
@@ -2753,11 +2757,11 @@ JAVASCRIPT;
                         case self::FIELD_TYPE_SELECT:
                         case self::FIELD_TYPE_DOUBLE_SELECT:
                             $inject_extra_fields .= " fvo$counter.display_text as {$extra['field']}, ";
                             break;
                         case self::FIELD_TYPE_TAG:
-                            //$inject_extra_fields .= " tag$counter.tag as {$extra['field']}, ";
+                            // If using OR
                             // If using AND
                             $newCounter = 1;
                             $fields = [];
                             $tagAlias = $extra['field'];
                             foreach ($extra['data'] as $data) {
@@ -3199,11 +3203,11 @@ JAVASCRIPT;
             [],
             ['id' => 'extra_'.$fieldDetails['variable']]
         );
 
         if (empty($defaultValueId)) {
-            $slct->addOption(get_lang('Please select an option'), '');
+            $slct->addOption(get_lang('Please select an option'));
         }
 
         foreach ($options as $value => $text) {
             if (empty($value)) {
                 $slct->addOption($text, $value);

Vulnerable code:         $sidx = in_array($sidx, $columns) ? $sidx : 'title';
                    $groupFilter,
                    false,
                    true
                $result = $obj->getUserGroupInCourse(
                    $options,
                    $groupFilter,
                    false,
                    true
                );
                        break;
                                $extraFields[] = ['field' => $rule->field, 'id' => $field_option['id']];
                                'field' => $rule->field,
                                        'field' => $rule->field,
                        'field' => $rule->field,
                            //$inject_extra_fields .= " tag$counter.tag as {$extra['field']}, ";
            $slct->addOption(get_lang('Please select an option'), '');
Fixed code:     $col = Database::escapeField($col);

        $columnOrderValidList = array_merge(['firstname', 'lastname'], $columns);
        $sidx = in_array($sidx, $columnOrderValidList) ? $sidx : 'title';
                    $groupFilter
                $result = $obj->getUserGroupInCourse($options, $groupFilter);

    public static function escapeField($field)
    {
        return self::escape_string(preg_replace("/[^a-zA-Z0-9_]/", '', $field));
    }
                        break;                        
                    $ruleField = Database::escapeField($rule->field);
                                $extraFields[] = ['field' => $ruleField, 'id' => $field_option['id']];
                                    // Where will be injected in the parseConditions()
                                        'field' => $ruleField,
                                        'field' => $ruleField,
                            'field' => $ruleField,
        $col = Database::escapeField($col);

                            // If using OR
            $slct->addOption(get_lang('Please select an option'));