Issues in database.lib.php (dependabot/npm_and_yarn/tar-7.5.2) - Issues in dependabot/npm_and_yarn/tar-7.5.2 - chamilo/chamilo-lms - Measure and Improve Code Quality continuously with Scrutinizer

Issues (2090)

public/main/inc/lib/database.lib.php (3 issues)

Labels

Severity

Critical 3

<?php

/* For licensing terms, see /license.txt */

use Doctrine\Common\EventManager;
use Doctrine\DBAL\Connection;
use Doctrine\DBAL\DriverManager;
use Doctrine\DBAL\Types\Type;
use Doctrine\ORM\Configuration;
use Doctrine\ORM\EntityManager;
use Doctrine\ORM\ORMSetup;
use Symfony\Bridge\Doctrine\Types\UuidType;

class Database
{
    private static EntityManager $em;
    private static Connection $connection;

    /**
     * Setup doctrine only for the installation.
     *
     * @throws \Doctrine\ORM\ORMException
     * @throws \Doctrine\DBAL\Exception
     */
    public static function connect(array $params = [], string $entityRootPath = ''): void
    {
        $config = self::getDoctrineConfig($entityRootPath);
        $config->setAutoGenerateProxyClasses(true);
        $config->setEntityNamespaces(
            [
                'ChamiloCoreBundle' => 'Chamilo\CoreBundle\Entity',
                'ChamiloCourseBundle' => 'Chamilo\CourseBundle\Entity',
            ]
        );

        $params['charset'] = 'utf8';

        $evm = new EventManager();
        $timestampableListener = new Gedmo\Timestampable\TimestampableListener();
        $evm->addEventSubscriber($timestampableListener);

        if (false === Type::hasType('uuid')) {
            Type::addType('uuid', UuidType::class);
        }

        $connection = DriverManager::getConnection($params, $config, $evm);

        $entityManager = new EntityManager($connection, $config, $evm);

        self::setConnection($connection);
        self::setManager($entityManager);
    }

    public static function setManager(EntityManager $em)
    {
        self::$em = $em;
    }

    public static function setConnection(Connection $connection)
    {
        self::$connection = $connection;
    }

    public static function getConnection(): Connection
    {
        return self::$connection;
    }

    public static function getManager(): EntityManager
    {
        return self::$em;
    }

    /**
     * Returns the name of the main database.
     *
     * @throws \Doctrine\DBAL\Exception
     */
    public static function get_main_database(): bool|string|null
    {
        return self::getManager()->getConnection()->getDatabase();
    }

    /**
     * Get main table.
     */
    public static function get_main_table(string $table): string
    {
        return $table;
    }

    /**
     * Get course table.
     */
    public static function get_course_table(string $table): string
    {
        return DB_COURSE_PREFIX.$table;
    }

    /**
     * Counts the number of rows in a table.
     *
     * @throws Exception
     * @throws \Doctrine\DBAL\Exception
     *
     * @deprecated
     */
    public static function count_rows(string $table): int
    {
        $result = self::query("SELECT COUNT(*) AS n FROM $table");

        return (int) $result->fetchOne();
    }

    /**
     * Returns the number of affected rows in the last database operation.
     *
     * @throws \Doctrine\DBAL\Exception
     */
    public static function affected_rows(\Doctrine\DBAL\Result $result): int
    {
        return $result->rowCount();
    }

    /**
     * Escapes a string to insert into the database as text.
     */
    public static function escape_string(mixed $string): string
    {
        $string = self::getManager()->getConnection()->quote($string);
        // The quote method from PDO also adds quotes around the string, which
        // is not how the legacy mysql_real_escape_string() was used in
        // Chamilo, so we need to remove the quotes around. Using trim will
        // remove more than one quote if they are sequenced, generating
        // broken queries and SQL injection risks
        return substr($string, 1, -1);
    }

    /**
     * Gets the (associative) array from a SQL result (as returned by Database::query).
     *
     * @throws \Doctrine\DBAL\Exception
     */
    public static function fetch_array(\Doctrine\DBAL\Result $result): mixed
    {
        $data = $result->fetchAssociative();

        if (empty($data)) {
            return [];
        }

        $return = $data;

        foreach ($data as $value) {
            $return[] = $value;
        }

        return $return;
    }

    /**
     * Gets an associative array from a SQL result (as returned by Database::query).
     *
     * @throws \Doctrine\DBAL\Exception
     */
    public static function fetch_assoc(\Doctrine\DBAL\Result $result): array|bool
    {
        return $result->fetchAssociative();
    }

    /**
     * Gets the next row of the result of the SQL query
     * (as returned by Database::query) in an object form.
     *
     * @throws \Doctrine\DBAL\Exception
     */
    public static function fetch_object(\Doctrine\DBAL\Result $result): ?stdClass
    {
        $data = $result->fetchAssociative();

        if (empty($data)) {
            return null;
        }

        $object = new stdClass();

        foreach ($data as $key => $value) {
            $object->$key = $value;
        }

        return $object;
    }

    /**
     * Gets the array from a SQL result (as returned by Database::query)
     * help to achieve database independence.
     *
     * @throws \Doctrine\DBAL\Exception
     */
    public static function fetch_row(\Doctrine\DBAL\Result $result): array
    {
        $row = $result->fetchNumeric();

        return empty($row) ? [] : $row;
    }

    /**
     * Gets the ID of the last item inserted into the database.
     *
     * @throws \Doctrine\DBAL\Exception
     */
    public static function insert_id(): int
    {
        return (int) self::getManager()->getConnection()->lastInsertId();
    }

    /**
     * @throws \Doctrine\DBAL\Exception
     */
    public static function num_rows(\Doctrine\DBAL\Result $result): int
    {
        return $result->rowCount();
    }

    /**
     * Acts as the relative *_result() function of most DB drivers and fetches a
     * specific line and a field.
     *
     * @throws \Doctrine\DBAL\Exception
     */
    public static function result(\Doctrine\DBAL\Result $resource, int $row, string $field): mixed
    {
        if ($resource->rowCount() > 0) {
            $result = $resource->fetchAllAssociative();

            $resultRow = $result[$row];

            foreach ($result[$row] as $value) {
                $resultRow[] = $value;
            }

            return $resultRow[$field];
        }

        return null;
    }

    /**
     * Wrapper to executes a query on the defined database handler.
     * @throws Exception
     */
    public static function query(string $query): ?\Doctrine\DBAL\Result
    {
        $connection = self::getManager()->getConnection();
        $result = null;
        try {
            $result = $connection->executeQuery($query);
        } catch (Exception $e) {
            self::handleError($e);
        }

        return $result;
    }

    /**
     * @throws Exception
     */
    public static function handleError(Exception $e)
    {
        $debug = 'test' === api_get_setting('server_type');
        if ($debug) {
            throw $e;
        } else {
            error_log($e->getMessage());
            api_not_allowed(false, get_lang('There happened an unknown error. Please contact the platform administrator.'));
        }
    }

    /**
     * Stores a query result into an array.
     *
     * @throws \Doctrine\DBAL\Exception
     */
    public static function store_result(\Doctrine\DBAL\Result $result, $option = 'BOTH'): array
    {
        if ('NUM' === $option) {
            return $result->fetchAllNumeric();
        }

        return $result->fetchAllAssociative();
    }

    /**
     * Database insert.
     *
     * @throws \Doctrine\DBAL\Exception
     * @throws Exception
     */
    public static function insert(string $table_name, array $attributes, bool $show_query = false, array $options = []): bool|int
    {
        if (empty($attributes) || empty($table_name)) {
            return false;
        }

        $params = array_keys($attributes);

        if (!empty($params)) {
            $prefix = (!empty($options['ignore'])) ? 'INSERT IGNORE INTO' : 'INSERT INTO';
            $sql = $prefix.' '.$table_name.' ('.implode(',', $params).')
                VALUES (:'.implode(', :', $params).')';

            if ($show_query) {
                var_dump($sql);

                error_log($sql);
            }

            try {
                self::getConnection()
                    ->prepare($sql)
                    ->executeQuery($attributes)
                ;
            } catch (Exception $e) {
                self::handleError($e);

                return false;
            }

            return (int) self::getManager()->getConnection()->lastInsertId();
        }

        return false;
    }

    /**
     * Wrapper executing an SQL update query based on the given attributes array
     * @param string $tableName       use Database::get_main_table
     * @param array  $attributes      Values to updates
     *                                Example: $params['name'] = 'Julio'; $params['lastname'] = 'Montoya';
     * @param array  $whereConditions where conditions i.e. array('id = ?' =>'4')
     * @param bool   $showQuery
     *
     * @throws Exception
     *
     * @return bool|int
     */
    public static function update(
        string $tableName,
        array $attributes,
        array $whereConditions = [],
        bool $showQuery = false
    ): bool|int {
        if (!empty($tableName) && !empty($attributes)) {
            $updateSql = '';
            $count = 1;

            foreach ($attributes as $key => $value) {
                if ($showQuery) {
                    echo $key.': '.$value.PHP_EOL;
                }
                $updateSql .= "$key = :$key ";
                if ($count < count($attributes)) {
                    $updateSql .= ', ';
                }
                $count++;
            }

            if (!empty($updateSql)) {
                // Parsing and cleaning the where conditions
                $whereReturn = self::parse_where_conditions($whereConditions);
                $sql = "UPDATE $tableName SET $updateSql $whereReturn ";

                try {
                    $statement = self::getManager()->getConnection()->prepare($sql);
                    $result = $statement->executeQuery($attributes);
                } catch (Exception $e) {
                    self::handleError($e);

                    return false;
                }

                if ($showQuery) {
                    var_dump($sql);

                    var_dump($attributes);
                    var_dump($whereConditions);
                }

                return $result->rowCount();
            }
        }

        return false;
    }

    /**
     * Experimental useful database finder.
     *
     * @throws Exception
     *
     * @todo    lot of stuff to do here
     * @todo    known issues, it doesn't work when using LIKE conditions
     *
     * @example ['where'=> ['course_code LIKE "?%"']]
     * @example ['where'=> ['type = ? AND category = ?' => ['setting', 'Plugins']]]
     * @example ['where'=> ['name = "Julio" AND lastname = "montoya"']]
     */
    public static function select(
        string|array $columns,
        string $table_name,
        array $conditions = [],
        string $type_result = 'all',
        string $option = 'ASSOC',
        bool $debug = false
    ): int|array {
        if ('count' === $type_result) {
            $conditions['LIMIT'] = null;
            $conditions['limit'] = null;
        }
        $conditions = self::parse_conditions($conditions);

        //@todo we could do a describe here to check the columns ...
        if (is_array($columns)) {
            $clean_columns = implode(',', $columns);
        } else {
            if ('*' === $columns) {
                $clean_columns = '*';
            } else {
                $clean_columns = (string) $columns;
            }
        }

        if ('count' === $type_result) {
            $clean_columns = ' count(*) count ';
        }
        $sql = "SELECT $clean_columns FROM $table_name $conditions";
        if ($debug) {
            var_dump($sql);

        }
        $result = self::query($sql);
        if ('count' === $type_result) {
            $row = self::fetch_array($result);
            if ($row) {
                return (int) $row['count'];
            }

            return 0;
        }
        $array = [];

        if ('all' === $type_result) {
            while ($row = self::fetch_array($result)) {
                if (isset($row['id'])) {
                    $array[$row['id']] = $row;
                } else {
                    $array[] = $row;
                }
            }
        } else {
            $array = self::fetch_array($result);
        }

        return $array;
    }

    /**
     * Parses WHERE/ORDER conditions i.e. array('where'=>array('id = ?' =>'4'), 'order'=>'id DESC').
     *
     * @todo known issues, it doesn't work when using
     * LIKE conditions example: array('where'=>array('course_code LIKE "?%"'))
     */
    public static function parse_conditions(array $conditions): string
    {
        if (empty($conditions)) {
            return '';
        }
        $return_value = $where_return = '';
        foreach ($conditions as $type_condition => $condition_data) {
            if (false == $condition_data) {
                continue;
            }
            $type_condition = strtolower($type_condition);
            switch ($type_condition) {
                case 'where':
                    foreach ($condition_data as $condition => $value_array) {
                        if (is_array($value_array)) {
                            $clean_values = [];
                            foreach ($value_array as $item) {
                                $item = self::escape_string($item);
                                $clean_values[] = $item;
                            }
                        } else {
                            $value_array = self::escape_string($value_array);
                            $clean_values = [$value_array];
                        }

                        if (!empty($condition) && '' != $clean_values) {
                            $condition = str_replace('%', "'@percentage@'", $condition); //replace "%"
                            $condition = str_replace("'?'", "%s", $condition);
                            $condition = str_replace("?", "%s", $condition);

                            $condition = str_replace("@%s@", "@-@", $condition);
                            $condition = str_replace("%s", "'%s'", $condition);
                            $condition = str_replace("@-@", "@%s@", $condition);

                            // Treat conditions as string
                            $condition = vsprintf($condition, $clean_values);
                            $condition = str_replace('@percentage@', '%', $condition); //replace "%"
                            $where_return .= $condition;
                        }
                    }

                    if (!empty($where_return)) {
                        $return_value = " WHERE $where_return";
                    }
                    break;
                case 'order':
                    $order_array = $condition_data;

                    if (!empty($order_array)) {
                        // 'order' => 'id desc, name desc'
                        $order_array = self::escape_string($order_array);
                        $new_order_array = explode(',', $order_array);
                        $temp_value = [];

                        foreach ($new_order_array as $element) {
                            $element = explode(' ', $element);
                            $element = array_filter($element);
                            $element = array_values($element);

                            if (!empty($element[1])) {
                                $element[1] = strtolower($element[1]);
                                $order = 'DESC';
                                if (in_array($element[1], ['desc', 'asc'])) {
                                    $order = $element[1];
                                }
                                $temp_value[] = ' `'.$element[0].'` '.$order.' ';
                            } else {
                                //by default DESC
                                $temp_value[] = ' `'.$element[0].'` DESC ';
                            }
                        }
                        if (!empty($temp_value)) {
                            $return_value .= ' ORDER BY '.implode(', ', $temp_value);
                        }
                    }
                    break;
                case 'limit':
                    $limit_array = explode(',', $condition_data);
                    if (!empty($limit_array)) {
                        if (count($limit_array) > 1) {
                            $return_value .= ' LIMIT '.intval($limit_array[0]).' , '.intval($limit_array[1]);
                        } else {
                            $return_value .= ' LIMIT '.intval($limit_array[0]);
                        }
                    }
                    break;
            }
        }

        return $return_value;
    }

    public static function parse_where_conditions(array $conditions): string
    {
        return self::parse_conditions(['where' => $conditions]);
    }

    /**
     * @throws Exception
     */
    public static function delete(string $tableName, array $where_conditions, bool $show_query = false): int
    {
        $where_return = self::parse_where_conditions($where_conditions);
        $sql = "DELETE FROM $tableName $where_return ";
        if ($show_query) {
            echo $sql;
            echo '<br />';
        }
        $result = self::query($sql);

        return self::affected_rows($result);
    }

    /**
     * Get Doctrine configuration.
     */
    public static function getDoctrineConfig(string $path): Configuration
    {
        $cache = null;
        $path = !empty($path) ? $path : api_get_path(SYMFONY_SYS_PATH);

        $paths = [
            $path.'src/Chamilo/CoreBundle/Entity',
            $path.'src/Chamilo/CourseBundle/Entity',
        ];

        $proxyDir = $path.'var/cache/';

        return ORMSetup::createAttributeMetadataConfiguration(
            $paths,
            true,
            $proxyDir,
            $cache
        );
    }

    /**
     * @throws \Doctrine\DBAL\Exception
     */
    public static function tableExists(string $table): bool
    {
        return self::getManager()->getConnection()->createSchemaManager()->tablesExist($table);
    }

    /**
     * @throws \Doctrine\DBAL\Exception
     */
    public static function listTableColumns(string $table): array
    {
        return self::getManager()->getConnection()->createSchemaManager()->listTableColumns($table);
    }

    public static function escapeField($field): string
    {
        return self::escape_string(preg_replace("/[^a-zA-Z0-9_.]/", '', $field));
    }
}


1			<?php
2
3			/* For licensing terms, see /license.txt */
4
5			use Doctrine\Common\EventManager;
6			use Doctrine\DBAL\Connection;
7			use Doctrine\DBAL\DriverManager;
8			use Doctrine\DBAL\Types\Type;
9			use Doctrine\ORM\Configuration;
10			use Doctrine\ORM\EntityManager;
11			use Doctrine\ORM\ORMSetup;
12			use Symfony\Bridge\Doctrine\Types\UuidType;
13
14			class Database
15			{
16			private static EntityManager $em;
17			private static Connection $connection;
18
19			/**
20			* Setup doctrine only for the installation.
21			*
22			* @throws \Doctrine\ORM\ORMException
23			* @throws \Doctrine\DBAL\Exception
24			*/
25			public static function connect(array $params = [], string $entityRootPath = ''): void
26			{
27			$config = self::getDoctrineConfig($entityRootPath);
28			$config->setAutoGenerateProxyClasses(true);
29			$config->setEntityNamespaces(
30			[
31			'ChamiloCoreBundle' => 'Chamilo\CoreBundle\Entity',
32			'ChamiloCourseBundle' => 'Chamilo\CourseBundle\Entity',
33			]
34			);
35
36			$params['charset'] = 'utf8';
37
38			$evm = new EventManager();
39			$timestampableListener = new Gedmo\Timestampable\TimestampableListener();
40			$evm->addEventSubscriber($timestampableListener);
41
42			if (false === Type::hasType('uuid')) {
43			Type::addType('uuid', UuidType::class);
44			}
45
46			$connection = DriverManager::getConnection($params, $config, $evm);
47
48			$entityManager = new EntityManager($connection, $config, $evm);
49
50			self::setConnection($connection);
51			self::setManager($entityManager);
52			}
53
54			public static function setManager(EntityManager $em)
55			{
56			self::$em = $em;
57			}
58
59			public static function setConnection(Connection $connection)
60			{
61			self::$connection = $connection;
62			}
63
64			public static function getConnection(): Connection
65			{
66			return self::$connection;
67			}
68
69			public static function getManager(): EntityManager
70			{
71			return self::$em;
72			}
73
74			/**
75			* Returns the name of the main database.
76			*
77			* @throws \Doctrine\DBAL\Exception
78			*/
79			public static function get_main_database(): bool\|string\|null
80			{
81			return self::getManager()->getConnection()->getDatabase();
82			}
83
84			/**
85			* Get main table.
86			*/
87			public static function get_main_table(string $table): string
88			{
89			return $table;
90			}
91
92			/**
93			* Get course table.
94			*/
95			public static function get_course_table(string $table): string
96			{
97			return DB_COURSE_PREFIX.$table;
98			}
99
100			/**
101			* Counts the number of rows in a table.
102			*
103			* @throws Exception
104			* @throws \Doctrine\DBAL\Exception
105			*
106			* @deprecated
107			*/
108			public static function count_rows(string $table): int
109			{
110			$result = self::query("SELECT COUNT(*) AS n FROM $table");
111
112			return (int) $result->fetchOne();
113			}
114
115			/**
116			* Returns the number of affected rows in the last database operation.
117			*
118			* @throws \Doctrine\DBAL\Exception
119			*/
120			public static function affected_rows(\Doctrine\DBAL\Result $result): int
121			{
122			return $result->rowCount();
123			}
124
125			/**
126			* Escapes a string to insert into the database as text.
127			*/
128			public static function escape_string(mixed $string): string
129			{
130			$string = self::getManager()->getConnection()->quote($string);
131			// The quote method from PDO also adds quotes around the string, which
132			// is not how the legacy mysql_real_escape_string() was used in
133			// Chamilo, so we need to remove the quotes around. Using trim will
134			// remove more than one quote if they are sequenced, generating
135			// broken queries and SQL injection risks
136			return substr($string, 1, -1);
137			}
138
139			/**
140			* Gets the (associative) array from a SQL result (as returned by Database::query).
141			*
142			* @throws \Doctrine\DBAL\Exception
143			*/
144			public static function fetch_array(\Doctrine\DBAL\Result $result): mixed
145			{
146			$data = $result->fetchAssociative();
147
148			if (empty($data)) {
149			return [];
150			}
151
152			$return = $data;
153
154			foreach ($data as $value) {
155			$return[] = $value;
156			}
157
158			return $return;
159			}
160
161			/**
162			* Gets an associative array from a SQL result (as returned by Database::query).
163			*
164			* @throws \Doctrine\DBAL\Exception
165			*/
166			public static function fetch_assoc(\Doctrine\DBAL\Result $result): array\|bool
167			{
168			return $result->fetchAssociative();
169			}
170
171			/**
172			* Gets the next row of the result of the SQL query
173			* (as returned by Database::query) in an object form.
174			*
175			* @throws \Doctrine\DBAL\Exception
176			*/
177			public static function fetch_object(\Doctrine\DBAL\Result $result): ?stdClass
178			{
179			$data = $result->fetchAssociative();
180
181			if (empty($data)) {
182			return null;
183			}
184
185			$object = new stdClass();
186
187			foreach ($data as $key => $value) {
188			$object->$key = $value;
189			}
190
191			return $object;
192			}
193
194			/**
195			* Gets the array from a SQL result (as returned by Database::query)
196			* help to achieve database independence.
197			*
198			* @throws \Doctrine\DBAL\Exception
199			*/
200			public static function fetch_row(\Doctrine\DBAL\Result $result): array
201			{
202			$row = $result->fetchNumeric();
203
204			return empty($row) ? [] : $row;
205			}
206
207			/**
208			* Gets the ID of the last item inserted into the database.
209			*
210			* @throws \Doctrine\DBAL\Exception
211			*/
212			public static function insert_id(): int
213			{
214			return (int) self::getManager()->getConnection()->lastInsertId();
215			}
216
217			/**
218			* @throws \Doctrine\DBAL\Exception
219			*/
220			public static function num_rows(\Doctrine\DBAL\Result $result): int
221			{
222			return $result->rowCount();
223			}
224
225			/**
226			* Acts as the relative *_result() function of most DB drivers and fetches a
227			* specific line and a field.
228			*
229			* @throws \Doctrine\DBAL\Exception
230			*/
231			public static function result(\Doctrine\DBAL\Result $resource, int $row, string $field): mixed
232			{
233			if ($resource->rowCount() > 0) {
234			$result = $resource->fetchAllAssociative();
235
236			$resultRow = $result[$row];
237
238			foreach ($result[$row] as $value) {
239			$resultRow[] = $value;
240			}
241
242			return $resultRow[$field];
243			}
244
245			return null;
246			}
247
248			/**
249			* Wrapper to executes a query on the defined database handler.
250			* @throws Exception
251			*/
252			public static function query(string $query): ?\Doctrine\DBAL\Result
253			{
254			$connection = self::getManager()->getConnection();
255			$result = null;
256			try {
257			$result = $connection->executeQuery($query);
258			} catch (Exception $e) {
259			self::handleError($e);
260			}
261
262			return $result;
263			}
264
265			/**
266			* @throws Exception
267			*/
268			public static function handleError(Exception $e)
269			{
270			$debug = 'test' === api_get_setting('server_type');
271			if ($debug) {
272			throw $e;
273			} else {
274			error_log($e->getMessage());
275			api_not_allowed(false, get_lang('There happened an unknown error. Please contact the platform administrator.'));
276			}
277			}
278
279			/**
280			* Stores a query result into an array.
281			*
282			* @throws \Doctrine\DBAL\Exception
283			*/
284			public static function store_result(\Doctrine\DBAL\Result $result, $option = 'BOTH'): array
285			{
286			if ('NUM' === $option) {
287			return $result->fetchAllNumeric();
288			}
289
290			return $result->fetchAllAssociative();
291			}
292
293			/**
294			* Database insert.
295			*
296			* @throws \Doctrine\DBAL\Exception
297			* @throws Exception
298			*/
299			public static function insert(string $table_name, array $attributes, bool $show_query = false, array $options = []): bool\|int
300			{
301			if (empty($attributes) \|\| empty($table_name)) {
302			return false;
303			}
304
305			$params = array_keys($attributes);
306
307			if (!empty($params)) {
308			$prefix = (!empty($options['ignore'])) ? 'INSERT IGNORE INTO' : 'INSERT INTO';
309			$sql = $prefix.' '.$table_name.' ('.implode(',', $params).')
310			VALUES (:'.implode(', :', $params).')';
311
312			if ($show_query) {
313			var_dump($sql);
			0 ignored issues – show Security Debugging Code introduced 2018-01-05 13:33 UTC by Report Bug Copy Issue Report Show Similar Issues like this `var_dump($sql)` looks like debug code. Are you sure you do not want to remove it? Loading history...
314			error_log($sql);
315			}
316
317			try {
318			self::getConnection()
319			->prepare($sql)
320			->executeQuery($attributes)
321			;
322			} catch (Exception $e) {
323			self::handleError($e);
324
325			return false;
326			}
327
328			return (int) self::getManager()->getConnection()->lastInsertId();
329			}
330
331			return false;
332			}
333
334			/**
335			* Wrapper executing an SQL update query based on the given attributes array
336			* @param string $tableName use Database::get_main_table
337			* @param array $attributes Values to updates
338			* Example: $params['name'] = 'Julio'; $params['lastname'] = 'Montoya';
339			* @param array $whereConditions where conditions i.e. array('id = ?' =>'4')
340			* @param bool $showQuery
341			*
342			* @throws Exception
343			*
344			* @return bool\|int
345			*/
346			public static function update(
347			string $tableName,
348			array $attributes,
349			array $whereConditions = [],
350			bool $showQuery = false
351			): bool\|int {
352			if (!empty($tableName) && !empty($attributes)) {
353			$updateSql = '';
354			$count = 1;
355
356			foreach ($attributes as $key => $value) {
357			if ($showQuery) {
358			echo $key.': '.$value.PHP_EOL;
359			}
360			$updateSql .= "$key = :$key ";
361			if ($count < count($attributes)) {
362			$updateSql .= ', ';
363			}
364			$count++;
365			}
366
367			if (!empty($updateSql)) {
368			// Parsing and cleaning the where conditions
369			$whereReturn = self::parse_where_conditions($whereConditions);
370			$sql = "UPDATE $tableName SET $updateSql $whereReturn ";
371
372			try {
373			$statement = self::getManager()->getConnection()->prepare($sql);
374			$result = $statement->executeQuery($attributes);
375			} catch (Exception $e) {
376			self::handleError($e);
377
378			return false;
379			}
380
381			if ($showQuery) {
382			var_dump($sql);
			0 ignored issues – show Security Debugging Code introduced 2018-01-05 13:33 UTC by Report Bug Copy Issue Report Show Similar Issues like this `var_dump($sql)` looks like debug code. Are you sure you do not want to remove it? Loading history...
383			var_dump($attributes);
384			var_dump($whereConditions);
385			}
386
387			return $result->rowCount();
388			}
389			}
390
391			return false;
392			}
393
394			/**
395			* Experimental useful database finder.
396			*
397			* @throws Exception
398			*
399			* @todo lot of stuff to do here
400			* @todo known issues, it doesn't work when using LIKE conditions
401			*
402			* @example ['where'=> ['course_code LIKE "?%"']]
403			* @example ['where'=> ['type = ? AND category = ?' => ['setting', 'Plugins']]]
404			* @example ['where'=> ['name = "Julio" AND lastname = "montoya"']]
405			*/
406			public static function select(
407			string\|array $columns,
408			string $table_name,
409			array $conditions = [],
410			string $type_result = 'all',
411			string $option = 'ASSOC',
412			bool $debug = false
413			): int\|array {
414			if ('count' === $type_result) {
415			$conditions['LIMIT'] = null;
416			$conditions['limit'] = null;
417			}
418			$conditions = self::parse_conditions($conditions);
419
420			//@todo we could do a describe here to check the columns ...
421			if (is_array($columns)) {
422			$clean_columns = implode(',', $columns);
423			} else {
424			if ('*' === $columns) {
425			$clean_columns = '*';
426			} else {
427			$clean_columns = (string) $columns;
428			}
429			}
430
431			if ('count' === $type_result) {
432			$clean_columns = ' count(*) count ';
433			}
434			$sql = "SELECT $clean_columns FROM $table_name $conditions";
435			if ($debug) {
436			var_dump($sql);
			0 ignored issues – show Security Debugging Code introduced 2018-01-05 13:33 UTC by Report Bug Copy Issue Report Show Similar Issues like this `var_dump($sql)` looks like debug code. Are you sure you do not want to remove it? Loading history...
437			}
438			$result = self::query($sql);
439			if ('count' === $type_result) {
440			$row = self::fetch_array($result);
441			if ($row) {
442			return (int) $row['count'];
443			}
444
445			return 0;
446			}
447			$array = [];
448
449			if ('all' === $type_result) {
450			while ($row = self::fetch_array($result)) {
451			if (isset($row['id'])) {
452			$array[$row['id']] = $row;
453			} else {
454			$array[] = $row;
455			}
456			}
457			} else {
458			$array = self::fetch_array($result);
459			}
460
461			return $array;
462			}
463
464			/**
465			* Parses WHERE/ORDER conditions i.e. array('where'=>array('id = ?' =>'4'), 'order'=>'id DESC').
466			*
467			* @todo known issues, it doesn't work when using
468			* LIKE conditions example: array('where'=>array('course_code LIKE "?%"'))
469			*/
470			public static function parse_conditions(array $conditions): string
471			{
472			if (empty($conditions)) {
473			return '';
474			}
475			$return_value = $where_return = '';
476			foreach ($conditions as $type_condition => $condition_data) {
477			if (false == $condition_data) {
478			continue;
479			}
480			$type_condition = strtolower($type_condition);
481			switch ($type_condition) {
482			case 'where':
483			foreach ($condition_data as $condition => $value_array) {
484			if (is_array($value_array)) {
485			$clean_values = [];
486			foreach ($value_array as $item) {
487			$item = self::escape_string($item);
488			$clean_values[] = $item;
489			}
490			} else {
491			$value_array = self::escape_string($value_array);
492			$clean_values = [$value_array];
493			}
494
495			if (!empty($condition) && '' != $clean_values) {
496			$condition = str_replace('%', "'@percentage@'", $condition); //replace "%"
497			$condition = str_replace("'?'", "%s", $condition);
498			$condition = str_replace("?", "%s", $condition);
499
500			$condition = str_replace("@%s@", "@-@", $condition);
501			$condition = str_replace("%s", "'%s'", $condition);
502			$condition = str_replace("@-@", "@%s@", $condition);
503
504			// Treat conditions as string
505			$condition = vsprintf($condition, $clean_values);
506			$condition = str_replace('@percentage@', '%', $condition); //replace "%"
507			$where_return .= $condition;
508			}
509			}
510
511			if (!empty($where_return)) {
512			$return_value = " WHERE $where_return";
513			}
514			break;
515			case 'order':
516			$order_array = $condition_data;
517
518			if (!empty($order_array)) {
519			// 'order' => 'id desc, name desc'
520			$order_array = self::escape_string($order_array);
521			$new_order_array = explode(',', $order_array);
522			$temp_value = [];
523
524			foreach ($new_order_array as $element) {
525			$element = explode(' ', $element);
526			$element = array_filter($element);
527			$element = array_values($element);
528
529			if (!empty($element[1])) {
530			$element[1] = strtolower($element[1]);
531			$order = 'DESC';
532			if (in_array($element[1], ['desc', 'asc'])) {
533			$order = $element[1];
534			}
535			$temp_value[] = ' `'.$element[0].'` '.$order.' ';
536			} else {
537			//by default DESC
538			$temp_value[] = ' `'.$element[0].'` DESC ';
539			}
540			}
541			if (!empty($temp_value)) {
542			$return_value .= ' ORDER BY '.implode(', ', $temp_value);
543			}
544			}
545			break;
546			case 'limit':
547			$limit_array = explode(',', $condition_data);
548			if (!empty($limit_array)) {
549			if (count($limit_array) > 1) {
550			$return_value .= ' LIMIT '.intval($limit_array[0]).' , '.intval($limit_array[1]);
551			} else {
552			$return_value .= ' LIMIT '.intval($limit_array[0]);
553			}
554			}
555			break;
556			}
557			}
558
559			return $return_value;
560			}
561
562			public static function parse_where_conditions(array $conditions): string
563			{
564			return self::parse_conditions(['where' => $conditions]);
565			}
566
567			/**
568			* @throws Exception
569			*/
570			public static function delete(string $tableName, array $where_conditions, bool $show_query = false): int
571			{
572			$where_return = self::parse_where_conditions($where_conditions);
573			$sql = "DELETE FROM $tableName $where_return ";
574			if ($show_query) {
575			echo $sql;
576			echo '<br />';
577			}
578			$result = self::query($sql);
579
580			return self::affected_rows($result);
581			}
582
583			/**
584			* Get Doctrine configuration.
585			*/
586			public static function getDoctrineConfig(string $path): Configuration
587			{
588			$cache = null;
589			$path = !empty($path) ? $path : api_get_path(SYMFONY_SYS_PATH);
590
591			$paths = [
592			$path.'src/Chamilo/CoreBundle/Entity',
593			$path.'src/Chamilo/CourseBundle/Entity',
594			];
595
596			$proxyDir = $path.'var/cache/';
597
598			return ORMSetup::createAttributeMetadataConfiguration(
599			$paths,
600			true,
601			$proxyDir,
602			$cache
603			);
604			}
605
606			/**
607			* @throws \Doctrine\DBAL\Exception
608			*/
609			public static function tableExists(string $table): bool
610			{
611			return self::getManager()->getConnection()->createSchemaManager()->tablesExist($table);
612			}
613
614			/**
615			* @throws \Doctrine\DBAL\Exception
616			*/
617			public static function listTableColumns(string $table): array
618			{
619			return self::getManager()->getConnection()->createSchemaManager()->listTableColumns($table);
620			}
621
622			public static function escapeField($field): string
623			{
624			return self::escape_string(preg_replace("/[^a-zA-Z0-9_.]/", '', $field));
625			}
626			}
627

chamilo / chamilo-lms

Issues (2090)

public/main/inc/lib/database.lib.php (3 issues)

Labels

Severity

Introduced By

Duplication Side-by-Side

Filter issues like