dolibarr 20.0.2
mysqli.class.php
Go to the documentation of this file.
1<?php
2/* Copyright (C) 2001 Fabien Seisen <seisen@linuxfr.org>
3 * Copyright (C) 2002-2005 Rodolphe Quiedeville <rodolphe@quiedeville.org>
4 * Copyright (C) 2004-2011 Laurent Destailleur <eldy@users.sourceforge.net>
5 * Copyright (C) 2006 Andre Cianfarani <acianfa@free.fr>
6 * Copyright (C) 2005-2012 Regis Houssin <regis.houssin@inodbox.com>
7 * Copyright (C) 2015 Raphaƫl Doursenaud <rdoursenaud@gpcsolutions.fr>
8 * Copyright (C) 2024 MDW <mdeweerd@users.noreply.github.com>
9 *
10 * This program is free software; you can redistribute it and/or modify
11 * it under the terms of the GNU General Public License as published by
12 * the Free Software Foundation; either version 3 of the License, or
13 * (at your option) any later version.
14 *
15 * This program is distributed in the hope that it will be useful,
16 * but WITHOUT ANY WARRANTY; without even the implied warranty of
17 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
18 * GNU General Public License for more details.
19 *
20 * You should have received a copy of the GNU General Public License
21 * along with this program. If not, see <https://www.gnu.org/licenses/>.
22 */
23
29require_once DOL_DOCUMENT_ROOT.'/core/db/DoliDB.class.php';
30
34class DoliDBMysqli extends DoliDB
35{
37 public $db;
39 public $type = 'mysqli';
40
42 const LABEL = 'MySQL or MariaDB';
44 const VERSIONMIN = '5.0.3';
45
47 private $_results;
48
60 public function __construct($type, $host, $user, $pass, $name = '', $port = 0)
61 {
62 global $conf, $langs;
63
64 // Note that having "static" property for "$forcecharset" and "$forcecollate" will make error here in strict mode, so they are not static
65 if (!empty($conf->db->character_set)) {
66 $this->forcecharset = $conf->db->character_set;
67 }
68 if (!empty($conf->db->dolibarr_main_db_collation)) {
69 $this->forcecollate = $conf->db->dolibarr_main_db_collation;
70 }
71
72 $this->database_user = $user;
73 $this->database_host = $host;
74 $this->database_port = $port;
75
76 $this->transaction_opened = 0;
77
78 //print "Name DB: $host,$user,$pass,$name<br>";
79
80 if (!class_exists('mysqli')) {
81 $this->connected = false;
82 $this->ok = false;
83 $this->error = "Mysqli PHP functions for using Mysqli driver are not available in this version of PHP. Try to use another driver.";
84 dol_syslog(get_class($this)."::DoliDBMysqli : Mysqli PHP functions for using Mysqli driver are not available in this version of PHP. Try to use another driver.", LOG_ERR);
85 }
86
87 if (!$host) {
88 $this->connected = false;
89 $this->ok = false;
90 $this->error = $langs->trans("ErrorWrongHostParameter");
91 dol_syslog(get_class($this)."::DoliDBMysqli : Connect error, wrong host parameters", LOG_ERR);
92 }
93
94 // Try server connection
95 // We do not try to connect to database, only to server. Connect to database is done later in constructor
96 $this->db = $this->connect($host, $user, $pass, '', $port);
97
98 if ($this->db && empty($this->db->connect_errno)) {
99 $this->connected = true;
100 $this->ok = true;
101 } else {
102 $this->connected = false;
103 $this->ok = false;
104 $this->error = empty($this->db) ? 'Failed to connect' : $this->db->connect_error;
105 dol_syslog(get_class($this)."::DoliDBMysqli Connect error: ".$this->error, LOG_ERR);
106 }
107
108 $disableforcecharset = 0; // Set to 1 to test without charset forcing
109
110 // If server connection is ok, we try to connect to the database
111 if ($this->connected && $name) {
112 if ($this->select_db($name)) {
113 $this->database_selected = true;
114 $this->database_name = $name;
115 $this->ok = true;
116
117 // If client is old latin, we force utf8
118 $clientmustbe = empty($conf->db->character_set) ? 'utf8' : (string) $conf->db->character_set;
119 if (preg_match('/latin1/', $clientmustbe)) {
120 $clientmustbe = 'utf8';
121 }
122
123 if (empty($disableforcecharset) && $this->db->character_set_name() != $clientmustbe) {
124 try {
125 dol_syslog(get_class($this)."::DoliDBMysqli You should set the \$dolibarr_main_db_character_set and \$dolibarr_main_db_collation for the PHP to the same as the database default, so to ".$this->db->character_set_name(). " or upgrade database default to ".$clientmustbe.".", LOG_WARNING);
126 // To get current charset: USE databasename; SHOW VARIABLES LIKE 'character_set_database'
127 // or: USE databasename; SELECT schema_name, default_character_set_name FROM information_schema.SCHEMATA;
128 // To get current collation: USE databasename; SHOW VARIABLES LIKE 'collation_database'
129 // or: USE databasename; SELECT schema_name, default_character_set_name FROM information_schema.SCHEMATA;
130 // To upgrade database default, you can do: ALTER DATABASE databasename CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
131
132 $this->db->set_charset($clientmustbe); // This set charset, but with a bad collation (colllation is forced later)
133 } catch (Exception $e) {
134 print 'Failed to force character_set_client to '.$clientmustbe." (according to setup) to match the one of the server database.<br>\n";
135 print $e->getMessage();
136 print "<br>\n";
137 if ($clientmustbe != 'utf8') {
138 print 'Edit conf/conf.php file to set a charset "utf8"';
139 if ($clientmustbe != 'utf8mb4') {
140 print ' or "utf8mb4"';
141 }
142 print ' instead of "'.$clientmustbe.'".'."\n";
143 }
144 exit;
145 }
146
147 $collation = (empty($conf) ? 'utf8_unicode_ci' : $conf->db->dolibarr_main_db_collation);
148 if (preg_match('/latin1/', $collation)) {
149 $collation = 'utf8_unicode_ci';
150 }
151
152 if (!preg_match('/general/', $collation)) {
153 $this->db->query("SET collation_connection = ".$collation);
154 }
155 }
156 } else {
157 $this->database_selected = false;
158 $this->database_name = '';
159 $this->ok = false;
160 $this->error = $this->error();
161 dol_syslog(get_class($this)."::DoliDBMysqli : Select_db error ".$this->error, LOG_ERR);
162 }
163 } else {
164 // No selection of database done. We may only be connected or not (ok or ko) to the server.
165 $this->database_selected = false;
166
167 if ($this->connected) {
168 // If client is old latin, we force utf8
169 $clientmustbe = empty($conf->db->character_set) ? 'utf8' : (string) $conf->db->character_set;
170 if (preg_match('/latin1/', $clientmustbe)) {
171 $clientmustbe = 'utf8';
172 }
173 if (preg_match('/utf8mb4/', $clientmustbe)) {
174 $clientmustbe = 'utf8';
175 }
176
177 if (empty($disableforcecharset) && $this->db->character_set_name() != $clientmustbe) {
178 $this->db->set_charset($clientmustbe); // This set utf8_unicode_ci
179
180 $collation = $conf->db->dolibarr_main_db_collation;
181 if (preg_match('/latin1/', $collation)) {
182 $collation = 'utf8_unicode_ci';
183 }
184 if (preg_match('/utf8mb4/', $collation)) {
185 $collation = 'utf8_unicode_ci';
186 }
187
188 if (!preg_match('/general/', $collation)) {
189 $this->db->query("SET collation_connection = ".$collation);
190 }
191 }
192 }
193 }
194 }
195
196
203 public function hintindex($nameofindex)
204 {
205 return " FORCE INDEX(".preg_replace('/[^a-z0-9_]/', '', $nameofindex).")";
206 }
207
208
216 public function convertSQLFromMysql($line, $type = 'ddl')
217 {
218 return $line;
219 }
220
221
222 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
229 public function select_db($database)
230 {
231 // phpcs:enable
232 dol_syslog(get_class($this)."::select_db database=".$database, LOG_DEBUG);
233 $result = false;
234 try {
235 $result = $this->db->select_db($database);
236 } catch (Exception $e) {
237 // Nothing done on error
238 }
239 return $result;
240 }
241
242
254 public function connect($host, $login, $passwd, $name, $port = 0)
255 {
256 dol_syslog(get_class($this)."::connect host=$host, port=$port, login=$login, passwd=--hidden--, name=$name", LOG_DEBUG);
257
258 //mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
259
260 $tmp = false;
261 try {
262 if (!class_exists('mysqli')) {
263 dol_print_error(null, 'Driver mysqli for PHP not available');
264 return false;
265 }
266 if (strpos($host, 'ssl://') === 0) {
267 $tmp = new mysqliDoli($host, $login, $passwd, $name, $port);
268 } else {
269 $tmp = new mysqli($host, $login, $passwd, $name, $port);
270 }
271 } catch (Exception $e) {
272 dol_syslog(get_class($this)."::connect failed", LOG_DEBUG);
273 }
274 return $tmp;
275 }
276
282 public function getVersion()
283 {
284 return $this->db->server_info;
285 }
286
292 public function getDriverInfo()
293 {
294 return $this->db->client_info;
295 }
296
297
304 public function close()
305 {
306 if ($this->db) {
307 if ($this->transaction_opened > 0) {
308 dol_syslog(get_class($this)."::close Closing a connection with an opened transaction depth=".$this->transaction_opened, LOG_ERR);
309 }
310 $this->connected = false;
311 return $this->db->close();
312 }
313 return false;
314 }
315
316
317
328 public function query($query, $usesavepoint = 0, $type = 'auto', $result_mode = 0)
329 {
330 global $dolibarr_main_db_readonly;
331
332 $query = trim($query);
333
334 if (!in_array($query, array('BEGIN', 'COMMIT', 'ROLLBACK'))) {
335 $SYSLOG_SQL_LIMIT = 10000; // limit log to 10kb per line to limit DOS attacks
336 dol_syslog('sql='.substr($query, 0, $SYSLOG_SQL_LIMIT), LOG_DEBUG);
337 }
338 if (empty($query)) {
339 return false; // Return false = error if empty request
340 }
341
342 if (!empty($dolibarr_main_db_readonly)) {
343 if (preg_match('/^(INSERT|UPDATE|REPLACE|DELETE|CREATE|ALTER|TRUNCATE|DROP)/i', $query)) {
344 $this->lasterror = 'Application in read-only mode';
345 $this->lasterrno = 'APPREADONLY';
346 $this->lastquery = $query;
347 return false;
348 }
349 }
350
351 try {
352 $ret = $this->db->query($query, $result_mode);
353 } catch (Exception $e) {
354 dol_syslog(get_class($this)."::query Exception in query instead of returning an error: ".$e->getMessage(), LOG_ERR);
355 $ret = false;
356 }
357
358 if (!preg_match("/^COMMIT/i", $query) && !preg_match("/^ROLLBACK/i", $query)) {
359 // Si requete utilisateur, on la sauvegarde ainsi que son resultset
360 if (!$ret) {
361 $this->lastqueryerror = $query;
362 $this->lasterror = $this->error();
363 $this->lasterrno = $this->errno();
364
365 if (getDolGlobalInt('SYSLOG_LEVEL') < LOG_DEBUG) {
366 dol_syslog(get_class($this)."::query SQL Error query: ".$query, LOG_ERR); // Log of request was not yet done previously
367 }
368 dol_syslog(get_class($this)."::query SQL Error message: ".$this->lasterrno." ".$this->lasterror.self::getCallerInfoString(), LOG_ERR);
369 //var_dump(debug_print_backtrace());
370 }
371 $this->lastquery = $query;
372 $this->_results = $ret;
373 }
374
375 return $ret;
376 }
377
383 final protected static function getCallerInfoString()
384 {
385 $backtrace = debug_backtrace();
386 $msg = "";
387 if (count($backtrace) >= 1) {
388 $trace = $backtrace[1];
389 if (isset($trace['file'], $trace['line'])) {
390 $msg = " From {$trace['file']}:{$trace['line']}.";
391 }
392 }
393 return $msg;
394 }
395
396 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
403 public function fetch_object($resultset)
404 {
405 // phpcs:enable
406 // If the resultset was not provided, we get the last one for this connection
407 if (!is_object($resultset)) {
408 $resultset = $this->_results;
409 }
410 return $resultset->fetch_object();
411 }
412
413
414 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
421 public function fetch_array($resultset)
422 {
423 // phpcs:enable
424 // If resultset not provided, we take the last used by connection
425 if (!is_object($resultset)) {
426 $resultset = $this->_results;
427 }
428 return $resultset->fetch_array();
429 }
430
431 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
438 public function fetch_row($resultset)
439 {
440 // phpcs:enable
441 // If resultset not provided, we take the last used by connection
442 if (!is_bool($resultset)) {
443 if (!is_object($resultset)) {
444 $resultset = $this->_results;
445 }
446 return $resultset->fetch_row();
447 } else {
448 // si le curseur est un boolean on retourne la valeur 0
449 return 0;
450 }
451 }
452
453 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
461 public function num_rows($resultset)
462 {
463 // phpcs:enable
464 // If resultset not provided, we take the last used by connection
465 if (!is_object($resultset)) {
466 $resultset = $this->_results;
467 }
468 return isset($resultset->num_rows) ? $resultset->num_rows : 0;
469 }
470
471 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
479 public function affected_rows($resultset)
480 {
481 // phpcs:enable
482 // If resultset not provided, we take the last used by connection
483 if (!is_object($resultset)) {
484 $resultset = $this->_results;
485 }
486 // mysql necessite un link de base pour cette fonction contrairement
487 // a pqsql qui prend un resultset
488 return $this->db->affected_rows;
489 }
490
497 public function free($resultset = null)
498 {
499 // If resultset not provided, we take the last used by connection
500 if (!is_object($resultset)) {
501 $resultset = $this->_results;
502 }
503 // Si resultset en est un, on libere la memoire
504 if (is_object($resultset)) {
505 $resultset->free_result();
506 }
507 }
508
515 public function escape($stringtoencode)
516 {
517 return $this->db->real_escape_string((string) $stringtoencode);
518 }
519
526 public function escapeforlike($stringtoencode)
527 {
528 // We must first replace the \ char into \\, then we can replace _ and % into \_ and \%
529 return str_replace(array('\\', '_', '%'), array('\\\\', '\_', '\%'), (string) $stringtoencode);
530 }
531
537 public function errno()
538 {
539 if (!$this->connected) {
540 // Si il y a eu echec de connection, $this->db n'est pas valide.
541 return 'DB_ERROR_FAILED_TO_CONNECT';
542 } else {
543 // Constants to convert a MySql error code to a generic Dolibarr error code
544 $errorcode_map = array(
545 1004 => 'DB_ERROR_CANNOT_CREATE',
546 1005 => 'DB_ERROR_CANNOT_CREATE',
547 1006 => 'DB_ERROR_CANNOT_CREATE',
548 1007 => 'DB_ERROR_ALREADY_EXISTS',
549 1008 => 'DB_ERROR_CANNOT_DROP',
550 1022 => 'DB_ERROR_KEY_NAME_ALREADY_EXISTS',
551 1025 => 'DB_ERROR_NO_FOREIGN_KEY_TO_DROP',
552 1044 => 'DB_ERROR_ACCESSDENIED',
553 1046 => 'DB_ERROR_NODBSELECTED',
554 1048 => 'DB_ERROR_CONSTRAINT',
555 1050 => 'DB_ERROR_TABLE_ALREADY_EXISTS',
556 1051 => 'DB_ERROR_NOSUCHTABLE',
557 1054 => 'DB_ERROR_NOSUCHFIELD',
558 1060 => 'DB_ERROR_COLUMN_ALREADY_EXISTS',
559 1061 => 'DB_ERROR_KEY_NAME_ALREADY_EXISTS',
560 1062 => 'DB_ERROR_RECORD_ALREADY_EXISTS',
561 1064 => 'DB_ERROR_SYNTAX',
562 1068 => 'DB_ERROR_PRIMARY_KEY_ALREADY_EXISTS',
563 1075 => 'DB_ERROR_CANT_DROP_PRIMARY_KEY',
564 1091 => 'DB_ERROR_NOSUCHFIELD',
565 1100 => 'DB_ERROR_NOT_LOCKED',
566 1136 => 'DB_ERROR_VALUE_COUNT_ON_ROW',
567 1146 => 'DB_ERROR_NOSUCHTABLE',
568 1215 => 'DB_ERROR_CANNOT_ADD_FOREIGN_KEY_CONSTRAINT',
569 1216 => 'DB_ERROR_NO_PARENT',
570 1217 => 'DB_ERROR_CHILD_EXISTS',
571 1396 => 'DB_ERROR_USER_ALREADY_EXISTS', // When creating a user that already existing
572 1451 => 'DB_ERROR_CHILD_EXISTS',
573 1826 => 'DB_ERROR_KEY_NAME_ALREADY_EXISTS'
574 );
575
576 if (isset($errorcode_map[$this->db->errno])) {
577 return $errorcode_map[$this->db->errno];
578 }
579 $errno = $this->db->errno;
580 return ($errno ? 'DB_ERROR_'.$errno : '0');
581 }
582 }
583
589 public function error()
590 {
591 if (!$this->connected) {
592 // Si il y a eu echec de connection, $this->db n'est pas valide pour mysqli_error.
593 return 'Not connected. Check setup parameters in conf/conf.php file and your mysql client and server versions';
594 } else {
595 return $this->db->error;
596 }
597 }
598
599 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
607 public function last_insert_id($tab, $fieldid = 'rowid')
608 {
609 // phpcs:enable
610 return $this->db->insert_id;
611 }
612
621 public function encrypt($fieldorvalue, $withQuotes = 1)
622 {
623 global $conf;
624
625 // Type of encryption (2: AES (recommended), 1: DES , 0: no encryption)
626 $cryptType = (!empty($conf->db->dolibarr_main_db_encryption) ? $conf->db->dolibarr_main_db_encryption : 0);
627
628 //Encryption key
629 $cryptKey = (!empty($conf->db->dolibarr_main_db_cryptkey) ? $conf->db->dolibarr_main_db_cryptkey : '');
630
631 $escapedstringwithquotes = ($withQuotes ? "'" : "").$this->escape($fieldorvalue).($withQuotes ? "'" : "");
632
633 if ($cryptType && !empty($cryptKey)) {
634 if ($cryptType == 2) {
635 $escapedstringwithquotes = "AES_ENCRYPT(".$escapedstringwithquotes.", '".$this->escape($cryptKey)."')";
636 } elseif ($cryptType == 1) {
637 $escapedstringwithquotes = "DES_ENCRYPT(".$escapedstringwithquotes.", '".$this->escape($cryptKey)."')";
638 }
639 }
640
641 return $escapedstringwithquotes;
642 }
643
650 public function decrypt($value)
651 {
652 global $conf;
653
654 // Type of encryption (2: AES (recommended), 1: DES , 0: no encryption)
655 $cryptType = (!empty($conf->db->dolibarr_main_db_encryption) ? $conf->db->dolibarr_main_db_encryption : 0);
656
657 //Encryption key
658 $cryptKey = (!empty($conf->db->dolibarr_main_db_cryptkey) ? $conf->db->dolibarr_main_db_cryptkey : '');
659
660 $return = $value;
661
662 if ($cryptType && !empty($cryptKey)) {
663 if ($cryptType == 2) {
664 $return = 'AES_DECRYPT('.$value.',\''.$cryptKey.'\')';
665 } elseif ($cryptType == 1) {
666 $return = 'DES_DECRYPT('.$value.',\''.$cryptKey.'\')';
667 }
668 }
669
670 return $return;
671 }
672
673
674 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
680 public function DDLGetConnectId()
681 {
682 // phpcs:enable
683 $resql = $this->query('SELECT CONNECTION_ID()');
684 if ($resql) {
685 $row = $this->fetch_row($resql);
686 return $row[0];
687 } else {
688 return '?';
689 }
690 }
691
692 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
704 public function DDLCreateDb($database, $charset = '', $collation = '', $owner = '')
705 {
706 // phpcs:enable
707 if (empty($charset)) {
708 $charset = $this->forcecharset;
709 }
710 if (empty($collation)) {
711 $collation = $this->forcecollate;
712 }
713
714 // ALTER DATABASE dolibarr_db DEFAULT CHARACTER SET latin DEFAULT COLLATE latin1_swedish_ci
715 $sql = "CREATE DATABASE `".$this->escape($database)."`";
716 $sql .= " DEFAULT CHARACTER SET `".$this->escape($charset)."` DEFAULT COLLATE `".$this->escape($collation)."`";
717
718 dol_syslog($sql, LOG_DEBUG);
719 $ret = $this->query($sql);
720 if (!$ret) {
721 // We try again for compatibility with Mysql < 4.1.1
722 $sql = "CREATE DATABASE `".$this->escape($database)."`";
723 dol_syslog($sql, LOG_DEBUG);
724 $ret = $this->query($sql);
725 }
726
727 return $ret;
728 }
729
730 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
738 public function DDLListTables($database, $table = '')
739 {
740 // phpcs:enable
741 $listtables = array();
742
743 $like = '';
744 if ($table) {
745 $tmptable = preg_replace('/[^a-z0-9\.\-\_%]/i', '', $table);
746
747 $like = "LIKE '".$this->escape($tmptable)."'";
748 }
749 $tmpdatabase = preg_replace('/[^a-z0-9\.\-\_]/i', '', $database);
750
751 $sql = "SHOW TABLES FROM `".$tmpdatabase."` ".$like.";";
752 //print $sql;
753 $result = $this->query($sql);
754 if ($result) {
755 while ($row = $this->fetch_row($result)) {
756 $listtables[] = $row[0];
757 }
758 }
759 return $listtables;
760 }
761
762 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
770 public function DDLListTablesFull($database, $table = '')
771 {
772 // phpcs:enable
773 $listtables = array();
774
775 $like = '';
776 if ($table) {
777 $tmptable = preg_replace('/[^a-z0-9\.\-\_%]/i', '', $table);
778
779 $like = "LIKE '".$this->escape($tmptable)."'";
780 }
781 $tmpdatabase = preg_replace('/[^a-z0-9\.\-\_]/i', '', $database);
782
783 $sql = "SHOW FULL TABLES FROM `".$tmpdatabase."` ".$like.";";
784
785 $result = $this->query($sql);
786 if ($result) {
787 while ($row = $this->fetch_row($result)) {
788 $listtables[] = $row;
789 }
790 }
791 return $listtables;
792 }
793
794 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
801 public function DDLInfoTable($table)
802 {
803 // phpcs:enable
804 $infotables = array();
805
806 $tmptable = preg_replace('/[^a-z0-9\.\-\_]/i', '', $table);
807
808 $sql = "SHOW FULL COLUMNS FROM ".$tmptable.";";
809
810 dol_syslog($sql, LOG_DEBUG);
811 $result = $this->query($sql);
812 if ($result) {
813 while ($row = $this->fetch_row($result)) {
814 $infotables[] = $row;
815 }
816 }
817 return $infotables;
818 }
819
820 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
833 public function DDLCreateTable($table, $fields, $primary_key, $type, $unique_keys = null, $fulltext_keys = null, $keys = null)
834 {
835 // phpcs:enable
836 // @TODO: $fulltext_keys parameter is unused
837
838 if (empty($type)) {
839 $type = 'InnoDB';
840 }
841
842 $pk = '';
843 $sqlk = array();
844 $sqluq = array();
845
846 // Keys found into the array $fields: type,value,attribute,null,default,extra
847 // ex. : $fields['rowid'] = array(
848 // 'type'=>'int' or 'integer',
849 // 'value'=>'11',
850 // 'null'=>'not null',
851 // 'extra'=> 'auto_increment'
852 // );
853 $sql = "CREATE TABLE ".$this->sanitize($table)."(";
854 $i = 0;
855 $sqlfields = array();
856 foreach ($fields as $field_name => $field_desc) {
857 $sqlfields[$i] = $this->sanitize($field_name)." ";
858 $sqlfields[$i] .= $this->sanitize($field_desc['type']);
859 if (isset($field_desc['value']) && $field_desc['value'] !== '') {
860 $sqlfields[$i] .= "(".$this->sanitize($field_desc['value']).")";
861 }
862 if (isset($field_desc['attribute']) && $field_desc['attribute'] !== '') {
863 $sqlfields[$i] .= " ".$this->sanitize($field_desc['attribute'], 0, 0, 1); // Allow space to accept attributes like "ON UPDATE CURRENT_TIMESTAMP"
864 }
865 if (isset($field_desc['default']) && $field_desc['default'] !== '') {
866 if (in_array($field_desc['type'], array('tinyint', 'smallint', 'int', 'double'))) {
867 $sqlfields[$i] .= " DEFAULT ".((float) $field_desc['default']);
868 } elseif ($field_desc['default'] == 'null' || $field_desc['default'] == 'CURRENT_TIMESTAMP') {
869 $sqlfields[$i] .= " DEFAULT ".$this->sanitize($field_desc['default']);
870 } else {
871 $sqlfields[$i] .= " DEFAULT '".$this->escape($field_desc['default'])."'";
872 }
873 }
874 if (isset($field_desc['null']) && $field_desc['null'] !== '') {
875 $sqlfields[$i] .= " ".$this->sanitize($field_desc['null'], 0, 0, 1);
876 }
877 if (isset($field_desc['extra']) && $field_desc['extra'] !== '') {
878 $sqlfields[$i] .= " ".$this->sanitize($field_desc['extra'], 0, 0, 1);
879 }
880 if (!empty($primary_key) && $primary_key == $field_name) {
881 $sqlfields[$i] .= " AUTO_INCREMENT PRIMARY KEY"; // mysql instruction that will be converted by driver late
882 }
883 $i++;
884 }
885
886 if (is_array($unique_keys)) {
887 $i = 0;
888 foreach ($unique_keys as $key => $value) {
889 $sqluq[$i] = "UNIQUE KEY '".$this->sanitize($key)."' ('".$this->escape($value)."')";
890 $i++;
891 }
892 }
893 if (is_array($keys)) {
894 $i = 0;
895 foreach ($keys as $key => $value) {
896 $sqlk[$i] = "KEY ".$this->sanitize($key)." (".$value.")";
897 $i++;
898 }
899 }
900 $sql .= implode(', ', $sqlfields);
901 if ($unique_keys != "") {
902 $sql .= ",".implode(',', $sqluq);
903 }
904 if (is_array($keys)) {
905 $sql .= ",".implode(',', $sqlk);
906 }
907 $sql .= ")";
908 $sql .= " engine=".$this->sanitize($type);
909
910 if (!$this->query($sql)) {
911 return -1;
912 } else {
913 return 1;
914 }
915 }
916
917 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
924 public function DDLDropTable($table)
925 {
926 // phpcs:enable
927 $tmptable = preg_replace('/[^a-z0-9\.\-\_]/i', '', $table);
928
929 $sql = "DROP TABLE ".$this->sanitize($tmptable);
930
931 if (!$this->query($sql)) {
932 return -1;
933 } else {
934 return 1;
935 }
936 }
937
938 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
946 public function DDLDescTable($table, $field = "")
947 {
948 // phpcs:enable
949 $sql = "DESC ".$this->sanitize($table)." ".$this->sanitize($field);
950
951 dol_syslog(get_class($this)."::DDLDescTable ".$sql, LOG_DEBUG);
952 $this->_results = $this->query($sql);
953 return $this->_results;
954 }
955
956 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
966 public function DDLAddField($table, $field_name, $field_desc, $field_position = "")
967 {
968 // phpcs:enable
969 // cles recherchees dans le tableau des descriptions (field_desc) : type,value,attribute,null,default,extra
970 // ex. : $field_desc = array('type'=>'int','value'=>'11','null'=>'not null','extra'=> 'auto_increment');
971 $sql = "ALTER TABLE ".$this->sanitize($table)." ADD ".$this->sanitize($field_name)." ";
972 $sql .= $this->sanitize($field_desc['type']);
973 if (isset($field_desc['value']) && preg_match("/^[^\s]/i", $field_desc['value'])) {
974 if (!in_array($field_desc['type'], array('tinyint', 'smallint', 'int', 'date', 'datetime')) && $field_desc['value']) {
975 $sql .= "(".$this->sanitize($field_desc['value']).")";
976 }
977 }
978 if (isset($field_desc['attribute']) && preg_match("/^[^\s]/i", $field_desc['attribute'])) {
979 $sql .= " ".$this->sanitize($field_desc['attribute']);
980 }
981 if (isset($field_desc['null']) && preg_match("/^[^\s]/i", $field_desc['null'])) {
982 $sql .= " ".$field_desc['null'];
983 }
984 if (isset($field_desc['default']) && preg_match("/^[^\s]/i", $field_desc['default'])) {
985 if (in_array($field_desc['type'], array('tinyint', 'smallint', 'int', 'double'))) {
986 $sql .= " DEFAULT ".((float) $field_desc['default']);
987 } elseif ($field_desc['default'] == 'null' || $field_desc['default'] == 'CURRENT_TIMESTAMP') {
988 $sql .= " DEFAULT ".$this->sanitize($field_desc['default']);
989 } else {
990 $sql .= " DEFAULT '".$this->escape($field_desc['default'])."'";
991 }
992 }
993 if (isset($field_desc['extra']) && preg_match("/^[^\s]/i", $field_desc['extra'])) {
994 $sql .= " ".$this->sanitize($field_desc['extra'], 0, 0, 1);
995 }
996 $sql .= " ".$this->sanitize($field_position, 0, 0, 1);
997
998 dol_syslog(get_class($this)."::DDLAddField ".$sql, LOG_DEBUG);
999 if ($this->query($sql)) {
1000 return 1;
1001 }
1002 return -1;
1003 }
1004
1005 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
1014 public function DDLUpdateField($table, $field_name, $field_desc)
1015 {
1016 // phpcs:enable
1017 $sql = "ALTER TABLE ".$this->sanitize($table);
1018 $sql .= " MODIFY COLUMN ".$this->sanitize($field_name)." ".$this->sanitize($field_desc['type']);
1019 if (in_array($field_desc['type'], array('double', 'tinyint', 'int', 'varchar')) && $field_desc['value']) {
1020 $sql .= "(".$this->sanitize($field_desc['value']).")";
1021 }
1022 if (isset($field_desc['value']) && ($field_desc['null'] == 'not null' || $field_desc['null'] == 'NOT NULL')) {
1023 // We will try to change format of column to NOT NULL. To be sure the ALTER works, we try to update fields that are NULL
1024 if ($field_desc['type'] == 'varchar' || $field_desc['type'] == 'text') {
1025 $sqlbis = "UPDATE ".$this->sanitize($table)." SET ".$this->sanitize($field_name)." = '".$this->escape(isset($field_desc['default']) ? $field_desc['default'] : '')."' WHERE ".$this->sanitize($field_name)." IS NULL";
1026 $this->query($sqlbis);
1027 } elseif (in_array($field_desc['type'], array('tinyint', 'smallint', 'int', 'double'))) {
1028 $sqlbis = "UPDATE ".$this->sanitize($table)." SET ".$this->sanitize($field_name)." = ".((float) $this->escape(isset($field_desc['default']) ? $field_desc['default'] : 0))." WHERE ".$this->sanitize($field_name)." IS NULL";
1029 $this->query($sqlbis);
1030 }
1031
1032 $sql .= " NOT NULL";
1033 }
1034
1035 if (isset($field_desc['default']) && $field_desc['default'] != '') {
1036 if (in_array($field_desc['type'], array('tinyint', 'smallint', 'int', 'double'))) {
1037 $sql .= " DEFAULT ".((float) $field_desc['default']);
1038 } elseif ($field_desc['type'] != 'text') {
1039 $sql .= " DEFAULT '".$this->escape($field_desc['default'])."'"; // Default not supported on text fields
1040 }
1041 }
1042
1043 dol_syslog(get_class($this)."::DDLUpdateField ".$sql, LOG_DEBUG);
1044 if (!$this->query($sql)) {
1045 return -1;
1046 } else {
1047 return 1;
1048 }
1049 }
1050
1051 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
1059 public function DDLDropField($table, $field_name)
1060 {
1061 // phpcs:enable
1062 $tmp_field_name = preg_replace('/[^a-z0-9\.\-\_]/i', '', $field_name);
1063
1064 $sql = "ALTER TABLE ".$this->sanitize($table)." DROP COLUMN `".$this->sanitize($tmp_field_name)."`";
1065 if ($this->query($sql)) {
1066 return 1;
1067 }
1068 $this->error = $this->lasterror();
1069 return -1;
1070 }
1071
1072
1073 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
1083 public function DDLCreateUser($dolibarr_main_db_host, $dolibarr_main_db_user, $dolibarr_main_db_pass, $dolibarr_main_db_name)
1084 {
1085 // phpcs:enable
1086 $sql = "CREATE USER '".$this->escape($dolibarr_main_db_user)."' IDENTIFIED BY '".$this->escape($dolibarr_main_db_pass)."'";
1087 dol_syslog(get_class($this)."::DDLCreateUser", LOG_DEBUG); // No sql to avoid password in log
1088 $resql = $this->query($sql);
1089 if (!$resql) {
1090 if ($this->lasterrno != 'DB_ERROR_USER_ALREADY_EXISTS') {
1091 return -1;
1092 } else {
1093 // If user already exists, we continue to set permissions
1094 dol_syslog(get_class($this)."::DDLCreateUser sql=".$sql, LOG_WARNING);
1095 }
1096 }
1097
1098 // Redo with localhost forced (sometimes user is created on %)
1099 $sql = "CREATE USER '".$this->escape($dolibarr_main_db_user)."'@'localhost' IDENTIFIED BY '".$this->escape($dolibarr_main_db_pass)."'";
1100 $resql = $this->query($sql);
1101
1102 $sql = "GRANT ALL PRIVILEGES ON ".$this->escape($dolibarr_main_db_name).".* TO '".$this->escape($dolibarr_main_db_user)."'@'".$this->escape($dolibarr_main_db_host)."'";
1103 dol_syslog(get_class($this)."::DDLCreateUser", LOG_DEBUG); // No sql to avoid password in log
1104 $resql = $this->query($sql);
1105 if (!$resql) {
1106 $this->error = "Connected user not allowed to GRANT ALL PRIVILEGES ON ".$this->escape($dolibarr_main_db_name).".* TO '".$this->escape($dolibarr_main_db_user)."'@'".$this->escape($dolibarr_main_db_host)."'";
1107 return -1;
1108 }
1109
1110 $sql = "FLUSH Privileges";
1111
1112 dol_syslog(get_class($this)."::DDLCreateUser", LOG_DEBUG);
1113 $resql = $this->query($sql);
1114 if (!$resql) {
1115 return -1;
1116 }
1117
1118 return 1;
1119 }
1120
1128 public function getDefaultCharacterSetDatabase()
1129 {
1130 $resql = $this->query("SHOW VARIABLES LIKE 'character_set_database'");
1131 if (!$resql) {
1132 // version Mysql < 4.1.1
1133 return $this->forcecharset;
1134 }
1135 $liste = $this->fetch_array($resql);
1136 $tmpval = $liste['Value'];
1137
1138 return $tmpval;
1139 }
1140
1146 public function getListOfCharacterSet()
1147 {
1148 $resql = $this->query('SHOW CHARSET');
1149 $liste = array();
1150 if ($resql) {
1151 $i = 0;
1152 while ($obj = $this->fetch_object($resql)) {
1153 $liste[$i]['charset'] = $obj->Charset;
1154 $liste[$i]['description'] = $obj->Description;
1155 $i++;
1156 }
1157 $this->free($resql);
1158 } else {
1159 // version Mysql < 4.1.1
1160 return null;
1161 }
1162 return $liste;
1163 }
1164
1171 public function getDefaultCollationDatabase()
1172 {
1173 $resql = $this->query("SHOW VARIABLES LIKE 'collation_database'");
1174 if (!$resql) {
1175 // version Mysql < 4.1.1
1176 return $this->forcecollate;
1177 }
1178 $liste = $this->fetch_array($resql);
1179 $tmpval = $liste['Value'];
1180
1181 return $tmpval;
1182 }
1183
1189 public function getListOfCollation()
1190 {
1191 $resql = $this->query('SHOW COLLATION');
1192 $liste = array();
1193 if ($resql) {
1194 $i = 0;
1195 while ($obj = $this->fetch_object($resql)) {
1196 $liste[$i]['collation'] = $obj->Collation;
1197 $i++;
1198 }
1199 $this->free($resql);
1200 } else {
1201 // version Mysql < 4.1.1
1202 return null;
1203 }
1204 return $liste;
1205 }
1206
1212 public function getPathOfDump()
1213 {
1214 $fullpathofdump = '/pathtomysqldump/mysqldump';
1215
1216 $resql = $this->query("SHOW VARIABLES LIKE 'basedir'");
1217 if ($resql) {
1218 $liste = $this->fetch_array($resql);
1219 $basedir = $liste['Value'];
1220 $fullpathofdump = $basedir.(preg_match('/\/$/', $basedir) ? '' : '/').'bin/mysqldump';
1221 }
1222 return $fullpathofdump;
1223 }
1224
1230 public function getPathOfRestore()
1231 {
1232 $fullpathofimport = '/pathtomysql/mysql';
1233
1234 $resql = $this->query("SHOW VARIABLES LIKE 'basedir'");
1235 if ($resql) {
1236 $liste = $this->fetch_array($resql);
1237 $basedir = $liste['Value'];
1238 $fullpathofimport = $basedir.(preg_match('/\/$/', $basedir) ? '' : '/').'bin/mysql';
1239 }
1240 return $fullpathofimport;
1241 }
1242
1249 public function getServerParametersValues($filter = '')
1250 {
1251 $result = array();
1252
1253 $sql = 'SHOW VARIABLES';
1254 if ($filter) {
1255 $sql .= " LIKE '".$this->escape($filter)."'";
1256 }
1257 $resql = $this->query($sql);
1258 if ($resql) {
1259 while ($obj = $this->fetch_object($resql)) {
1260 $result[$obj->Variable_name] = $obj->Value;
1261 }
1262 }
1263
1264 return $result;
1265 }
1266
1273 public function getServerStatusValues($filter = '')
1274 {
1275 $result = array();
1276
1277 $sql = 'SHOW STATUS';
1278 if ($filter) {
1279 $sql .= " LIKE '".$this->escape($filter)."'";
1280 }
1281 $resql = $this->query($sql);
1282 if ($resql) {
1283 while ($obj = $this->fetch_object($resql)) {
1284 $result[$obj->Variable_name] = $obj->Value;
1285 }
1286 }
1287
1288 return $result;
1289 }
1290}
1291
1292
1293if (class_exists('myslqi')) {
1297 class mysqliDoli extends mysqli
1298 {
1310 public function __construct($host, $user, $pass, $name, $port = 0, $socket = "")
1311 {
1312 $flags = 0;
1313 if (PHP_VERSION_ID >= 80100) {
1314 parent::__construct();
1315 } else {
1316 // @phan-suppress-next-line PhanDeprecatedFunctionInternal
1317 parent::init();
1318 }
1319 if (strpos($host, 'ssl://') === 0) {
1320 $host = substr($host, 6);
1321 parent::options(MYSQLI_OPT_SSL_VERIFY_SERVER_CERT, false);
1322 // Suppress false positive @phan-suppress-next-line PhanTypeMismatchArgumentInternalProbablyReal
1323 parent::ssl_set(null, null, "", null, null);
1324 $flags = MYSQLI_CLIENT_SSL;
1325 }
1326 parent::real_connect($host, $user, $pass, $name, $port, $socket, $flags);
1327 }
1328 }
1329}
Class to manage Dolibarr database access.
lastqueryerror()
Return last query in error.
lasterror()
Return last error label.
lasterrno()
Return last error code.
lastquery()
Return last request executed with query()
Class to manage Dolibarr database access for a MySQL database using the MySQLi extension.
fetch_array($resultset)
Return datas as an array.
__construct($type, $host, $user, $pass, $name='', $port=0)
Constructor.
free($resultset=null)
Libere le dernier resultset utilise sur cette connection.
escapeforlike($stringtoencode)
Escape a string to insert data into a like.
num_rows($resultset)
Return number of lines for result of a SELECT.
const VERSIONMIN
Version min database.
$type
Database type.
error()
Return description of last error.
escape($stringtoencode)
Escape a string to insert data.
getVersion()
Return version of database server.
fetch_object($resultset)
Returns the current line (as an object) for the resultset cursor.
encrypt($fieldorvalue, $withQuotes=1)
Encrypt sensitive data in database Warning: This function includes the escape and add the SQL simple ...
convertSQLFromMysql($line, $type='ddl')
Convert a SQL request in Mysql syntax to native syntax.
affected_rows($resultset)
Return the number of lines in the result of a request INSERT, DELETE or UPDATE.
select_db($database)
Select a database.
decrypt($value)
Decrypt sensitive data in database.
fetch_row($resultset)
Return datas as an array.
last_insert_id($tab, $fieldid='rowid')
Get last ID after an insert INSERT.
const LABEL
Database label.
query($query, $usesavepoint=0, $type='auto', $result_mode=0)
Execute a SQL request and return the resultset.
hintindex($nameofindex)
Return SQL string to force an index.
connect($host, $login, $passwd, $name, $port=0)
Connect to server.
errno()
Return generic error code of last operation.
static getCallerInfoString()
Get caller info.
getDriverInfo()
Return version of database client driver.
close()
Close database connection.
print $script_file $mode $langs defaultlang(is_numeric($duration_value) ? " delay=". $duration_value :"").(is_numeric($duration_value2) ? " after cd cd cd description as description
Only used if Module[ID]Desc translation string is not found.
getDolGlobalInt($key, $default=0)
Return a Dolibarr global constant int value.
dol_print_error($db=null, $error='', $errors=null)
Displays error message system with all the information to facilitate the diagnosis and the escalation...
dol_syslog($message, $level=LOG_INFO, $ident=0, $suffixinfilename='', $restricttologhandler='', $logcontext=null)
Write log message into outputs.
if(preg_match('/crypted:/i', $dolibarr_main_db_pass)||!empty($dolibarr_main_db_encrypted_pass)) $conf db type
Definition repair.php:137
publicphonebutton2 phonegreen basiclayout basiclayout TotalHT VATCode TotalVAT TotalLT1 TotalLT2 TotalTTC TotalHT clearboth nowraponall TAKEPOS_SHOW_SUBPRICE right right right takeposterminal SELECT e rowid
Definition invoice.php:2010