1<?php
2/**
3 * Copyright since 2007 PrestaShop SA and Contributors
4 * PrestaShop is an International Registered Trademark & Property of PrestaShop SA
5 *
6 * NOTICE OF LICENSE
7 *
8 * This source file is subject to the Open Software License (OSL 3.0)
9 * that is bundled with this package in the file LICENSE.md.
10 * It is also available through the world-wide-web at this URL:
11 * https://opensource.org/licenses/OSL-3.0
12 * If you did not receive a copy of the license and are unable to
13 * obtain it through the world-wide-web, please send an email
14 * to license@prestashop.com so we can send you a copy immediately.
15 *
16 * DISCLAIMER
17 *
18 * Do not edit or add to this file if you wish to upgrade PrestaShop to newer
19 * versions in the future. If you wish to customize PrestaShop for your
20 * needs please refer to https://devdocs.prestashop.com/ for more information.
21 *
22 * @author    PrestaShop SA and Contributors <contact@prestashop.com>
23 * @copyright Since 2007 PrestaShop SA and Contributors
24 * @license   https://opensource.org/licenses/OSL-3.0 Open Software License (OSL 3.0)
25 */
26
27/**
28 * Class DbPDOCore.
29 *
30 * @since 1.5.0.1
31 */
32class DbPDOCore extends Db
33{
34    /** @var PDO */
35    protected $link;
36
37    /** @var PDOStatement */
38    protected $result;
39
40    /**
41     * Returns a new PDO object (database link).
42     *
43     * @deprecated use getPDO
44     *
45     * @param string $host
46     * @param string $user
47     * @param string $password
48     * @param string $dbname
49     * @param int $timeout
50     *
51     * @return PDO
52     */
53    protected static function _getPDO($host, $user, $password, $dbname, $timeout = 5)
54    {
55        return static::getPDO($host, $user, $host, $dbname, $timeout);
56    }
57
58    /**
59     * Returns a new PDO object (database link).
60     *
61     * @param string $host
62     * @param string $user
63     * @param string $password
64     * @param string $dbname
65     * @param int $timeout
66     *
67     * @return PDO
68     */
69    protected static function getPDO($host, $user, $password, $dbname, $timeout = 5)
70    {
71        $dsn = 'mysql:';
72        if ($dbname) {
73            $dsn .= 'dbname=' . $dbname . ';';
74        }
75        if (preg_match('/^(.*):([0-9]+)$/', $host, $matches)) {
76            $dsn .= 'host=' . $matches[1] . ';port=' . $matches[2];
77        } elseif (preg_match('#^.*:(/.*)$#', $host, $matches)) {
78            $dsn .= 'unix_socket=' . $matches[1];
79        } else {
80            $dsn .= 'host=' . $host;
81        }
82        $dsn .= ';charset=utf8mb4';
83
84        return new PDO(
85            $dsn,
86            $user,
87            $password,
88            [
89                PDO::ATTR_TIMEOUT => $timeout,
90                PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true,
91                PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8mb4',
92            ]
93        );
94    }
95
96    /**
97     * Tries to connect and create a new database.
98     *
99     * @param string $host
100     * @param string $user
101     * @param string $password
102     * @param string $dbname
103     * @param bool $dropit if true, drops the created database
104     *
105     * @return bool|int
106     */
107    public static function createDatabase($host, $user, $password, $dbname, $dropit = false)
108    {
109        try {
110            $link = DbPDO::getPDO($host, $user, $password, false);
111            $success = $link->exec('CREATE DATABASE `' . str_replace('`', '\\`', $dbname) . '`');
112            if ($dropit && ($link->exec('DROP DATABASE `' . str_replace('`', '\\`', $dbname) . '`') !== false)) {
113                return true;
114            }
115        } catch (PDOException $e) {
116            return false;
117        }
118
119        return $success;
120    }
121
122    /**
123     * Tries to connect to the database.
124     *
125     * @see DbCore::connect()
126     *
127     * @return PDO
128     *
129     * @throws PrestaShopException
130     */
131    public function connect()
132    {
133        try {
134            $this->link = $this->getPDO($this->server, $this->user, $this->password, $this->database, 5);
135        } catch (PDOException $e) {
136            throw new PrestaShopException('Link to database cannot be established: ' . $e->getMessage());
137        }
138
139        $this->link->exec('SET SESSION sql_mode = \'\'');
140
141        return $this->link;
142    }
143
144    /**
145     * Destroys the database connection link.
146     *
147     * @see DbCore::disconnect()
148     */
149    public function disconnect()
150    {
151        unset($this->link);
152    }
153
154    /**
155     * Executes an SQL statement, returning a result set as a PDOStatement object or true/false.
156     *
157     * @see DbCore::_query()
158     *
159     * @param string $sql
160     *
161     * @return PDOStatement
162     */
163    protected function _query($sql)
164    {
165        return $this->link->query($sql);
166    }
167
168    /**
169     * Returns the next row from the result set.
170     *
171     * @see DbCore::nextRow()
172     *
173     * @param bool $result
174     *
175     * @return array|false|null
176     */
177    public function nextRow($result = false)
178    {
179        if (!$result) {
180            $result = $this->result;
181        }
182
183        if (!is_object($result)) {
184            return false;
185        }
186
187        return $result->fetch(PDO::FETCH_ASSOC);
188    }
189
190    /**
191     * Returns all rows from the result set.
192     *
193     * @see DbCore::getAll()
194     *
195     * @param bool $result
196     *
197     * @return array|false|null
198     */
199    protected function getAll($result = false)
200    {
201        if (!$result) {
202            $result = $this->result;
203        }
204
205        if (!is_object($result)) {
206            return false;
207        }
208
209        return $result->fetchAll(PDO::FETCH_ASSOC);
210    }
211
212    /**
213     * Returns row count from the result set.
214     *
215     * @see DbCore::_numRows()
216     *
217     * @param PDOStatement $result
218     *
219     * @return int
220     */
221    protected function _numRows($result)
222    {
223        return $result->rowCount();
224    }
225
226    /**
227     * Returns ID of the last inserted row.
228     *
229     * @see DbCore::Insert_ID()
230     *
231     * @return string|int
232     */
233    public function Insert_ID()
234    {
235        return $this->link->lastInsertId();
236    }
237
238    /**
239     * Return the number of rows affected by the last SQL query.
240     *
241     * @see DbCore::Affected_Rows()
242     *
243     * @return int
244     */
245    public function Affected_Rows()
246    {
247        return $this->result->rowCount();
248    }
249
250    /**
251     * Returns error message.
252     *
253     * @see DbCore::getMsgError()
254     *
255     * @param bool $query
256     *
257     * @return string
258     */
259    public function getMsgError($query = false)
260    {
261        $error = $this->link->errorInfo();
262
263        return ($error[0] == '00000') ? '' : $error[2];
264    }
265
266    /**
267     * Returns error code.
268     *
269     * @see DbCore::getNumberError()
270     *
271     * @return int
272     */
273    public function getNumberError()
274    {
275        $error = $this->link->errorInfo();
276
277        return isset($error[1]) ? $error[1] : 0;
278    }
279
280    /**
281     * Returns database server version.
282     *
283     * @see DbCore::getVersion()
284     *
285     * @return string
286     */
287    public function getVersion()
288    {
289        return $this->getValue('SELECT VERSION()');
290    }
291
292    /**
293     * Escapes illegal characters in a string.
294     *
295     * @see DbCore::_escape()
296     *
297     * @param string $str
298     *
299     * @return string
300     */
301    public function _escape($str)
302    {
303        $search = ['\\', "\0", "\n", "\r", "\x1a", "'", '"'];
304        $replace = ['\\\\', '\\0', '\\n', '\\r', "\Z", "\'", '\"'];
305
306        return str_replace($search, $replace, $str);
307    }
308
309    /**
310     * Switches to a different database.
311     *
312     * @see DbCore::set_db()
313     *
314     * @param string $db_name
315     *
316     * @return int
317     */
318    public function set_db($db_name)
319    {
320        return $this->link->exec('USE ' . pSQL($db_name));
321    }
322
323    /**
324     * Try a connection to the database and check if at least one table with same prefix exists.
325     *
326     * @see Db::hasTableWithSamePrefix()
327     *
328     * @param string $server Server address
329     * @param string $user Login for database connection
330     * @param string $pwd Password for database connection
331     * @param string $db Database name
332     * @param string $prefix Tables prefix
333     *
334     * @return bool
335     */
336    public static function hasTableWithSamePrefix($server, $user, $pwd, $db, $prefix)
337    {
338        try {
339            $link = DbPDO::getPDO($server, $user, $pwd, $db, 5);
340        } catch (PDOException $e) {
341            return false;
342        }
343
344        $sql = 'SHOW TABLES LIKE \'' . $prefix . '%\'';
345        $result = $link->query($sql);
346
347        return (bool) $result->fetch();
348    }
349
350    /**
351     * Tries to connect to the database and create a table (checking creation privileges).
352     *
353     * @param string $server
354     * @param string $user
355     * @param string $pwd
356     * @param string $db
357     * @param string $prefix
358     * @param string|null $engine Table engine
359     *
360     * @return bool|string True, false or error
361     */
362    public static function checkCreatePrivilege($server, $user, $pwd, $db, $prefix, $engine = null)
363    {
364        try {
365            $link = DbPDO::getPDO($server, $user, $pwd, $db, 5);
366        } catch (PDOException $e) {
367            return false;
368        }
369
370        $enginesToTest = ['InnoDB', 'MyISAM'];
371        if ($engine !== null) {
372            $enginesToTest = [$engine];
373        }
374
375        foreach ($enginesToTest as $engineToTest) {
376            $result = $link->query('
377            CREATE TABLE `' . $prefix . 'test` (
378                `test` tinyint(1) unsigned NOT NULL
379            ) ENGINE=' . $engineToTest);
380
381            if ($result) {
382                $link->query('DROP TABLE `' . $prefix . 'test`');
383
384                return true;
385            }
386        }
387
388        $error = $link->errorInfo();
389
390        return $error[2];
391    }
392
393    /**
394     * Tries to connect to the database and select content (checking select privileges).
395     *
396     * @param string $server
397     * @param string $user
398     * @param string $pwd
399     * @param string $db
400     * @param string $prefix
401     * @param string|null $engine Table engine
402     *
403     * @return bool|string True, false or error
404     */
405    public static function checkSelectPrivilege($server, $user, $pwd, $db, $prefix, $engine = null)
406    {
407        try {
408            $link = DbPDO::getPDO($server, $user, $pwd, $db, 5);
409        } catch (PDOException $e) {
410            return false;
411        }
412
413        if ($engine === null) {
414            $engine = 'MyISAM';
415        }
416
417        // Create a table
418        $link->query('
419		CREATE TABLE `' . $prefix . 'test` (
420			`test` tinyint(1) unsigned NOT NULL
421		) ENGINE=' . $engine);
422
423        // Select content
424        $result = $link->query('SELECT * FROM `' . $prefix . 'test`');
425
426        // Drop the table
427        $link->query('DROP TABLE `' . $prefix . 'test`');
428
429        if (!$result) {
430            $error = $link->errorInfo();
431
432            return $error[2];
433        }
434
435        return true;
436    }
437
438    /**
439     * Try a connection to the database.
440     *
441     * @see Db::checkConnection()
442     *
443     * @param string $server Server address
444     * @param string $user Login for database connection
445     * @param string $pwd Password for database connection
446     * @param string $db Database name
447     * @param bool $new_db_link
448     * @param string|bool $engine
449     * @param int $timeout
450     *
451     * @return int Error code or 0 if connection was successful
452     */
453    public static function tryToConnect($server, $user, $pwd, $db, $new_db_link = true, $engine = null, $timeout = 5)
454    {
455        try {
456            $link = DbPDO::getPDO($server, $user, $pwd, $db, $timeout);
457        } catch (PDOException $e) {
458            // hhvm wrongly reports error status 42000 when the database does not exist - might change in the future
459            return ($e->getCode() == 1049 || (defined('HHVM_VERSION') && $e->getCode() == 42000)) ? 2 : 1;
460        }
461        unset($link);
462
463        return 0;
464    }
465
466    /**
467     * Selects best table engine.
468     *
469     * @return string
470     */
471    public function getBestEngine()
472    {
473        $value = 'InnoDB';
474
475        $sql = 'SHOW VARIABLES WHERE Variable_name = \'have_innodb\'';
476        $result = $this->link->query($sql);
477
478        if (!$result) {
479            $value = 'MyISAM';
480        } else {
481            $row = $result->fetch();
482            if (!$row || strtolower($row['Value']) != 'yes') {
483                $value = 'MyISAM';
484            }
485        }
486
487        /* MySQL >= 5.6 */
488        $sql = 'SHOW ENGINES';
489        $result = $this->link->query($sql);
490        while ($row = $result->fetch()) {
491            if ($row['Engine'] == 'InnoDB') {
492                if (in_array($row['Support'], ['DEFAULT', 'YES'])) {
493                    $value = 'InnoDB';
494                }
495
496                break;
497            }
498        }
499
500        return $value;
501    }
502
503    /**
504     * Try a connection to the database and set names to UTF-8.
505     *
506     * @see Db::checkEncoding()
507     *
508     * @param string $server Server address
509     * @param string $user Login for database connection
510     * @param string $pwd Password for database connection
511     *
512     * @return bool
513     */
514    public static function tryUTF8($server, $user, $pwd)
515    {
516        try {
517            $link = DbPDO::getPDO($server, $user, $pwd, false, 5);
518        } catch (PDOException $e) {
519            return false;
520        }
521        $result = $link->exec('SET NAMES utf8mb4');
522        unset($link);
523
524        return ($result === false) ? false : true;
525    }
526}
527