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

Issues (1783)

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

Labels

Severity

Critical 3

<?php

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

use Doctrine\Common\Annotations\PsrCachedReader;
use Doctrine\Common\EventManager;
use Doctrine\DBAL\Connection;
use Doctrine\DBAL\Types\Type;
use Doctrine\ORM\Configuration;
use Doctrine\ORM\EntityManager;
use Symfony\Bridge\Doctrine\Types\UuidType;
use Symfony\Component\Cache\Adapter\ArrayAdapter;

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';

        // standard annotation reader
        $annotationReader = new Doctrine\Common\Annotations\AnnotationReader();
        $cachedAnnotationReader = new PsrCachedReader(
            $annotationReader,
            new ArrayAdapter()
        );

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

        $driverChain = new \Doctrine\Persistence\Mapping\Driver\MappingDriverChain();
        // load superclass metadata mapping only, into driver chain
        // also registers Gedmo annotations.NOTE: you can personalize it
        Gedmo\DoctrineExtensions::registerAbstractMappingIntoDriverChainORM(
            $driverChain, // our metadata driver chain, to hook into
            $cachedAnnotationReader // our cached annotation reader
        );

        $entityManager = EntityManager::create($params, $config, $evm);

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

        $connection = $entityManager->getConnection();

        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('An error has occurred. Please contact your system 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): bool|int
    {
        if (empty($attributes) || empty($table_name)) {
            return false;
        }

        $params = array_keys($attributes);

        if (!empty($params)) {
            $sql = 'INSERT INTO '.$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 \Doctrine\ORM\Tools\Setup::createAnnotationMetadataConfiguration(
            $paths,
            true, // Forces doctrine to use ArrayCache instead of apc/xcache/memcache/redis
            $proxyDir,
            $cache,
            false // related to annotations @Entity
        );
    }

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

chamilo / chamilo-lms

Issues (1783)

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

Labels

Severity

Introduced By

Duplication Side-by-Side

Filter issues like