dolibarr 20.0.0
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 // If server connection is ok, we try to connect to the database
109 if ($this->connected && $name) {
110 if ($this->select_db($name)) {
111 $this->database_selected = true;
112 $this->database_name = $name;
113 $this->ok = true;
114
115 // If client is old latin, we force utf8
116 $clientmustbe = empty($conf->db->character_set) ? 'utf8' : (string) $conf->db->character_set;
117 if (preg_match('/latin1/', $clientmustbe)) {
118 $clientmustbe = 'utf8';
119 }
120
121 $disableforcecharset = 0; // Set to 1 to test without charset forcing
122 if (empty($disableforcecharset) && $this->db->character_set_name() != $clientmustbe) {
123 try {
124 //print "You should set the \$dolibarr_main_db_character_set and \$dolibarr_main_db_collation for the PHP to the one of the database ".$this->db->character_set_name();
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 one of the database ".$this->db->character_set_name(), LOG_WARNING);
126 $this->db->set_charset($clientmustbe); // This set charset, but with a bad collation
127 } catch (Exception $e) {
128 print 'Failed to force character_set_client to '.$clientmustbe." (according to setup) to match the one of the server database.<br>\n";
129 print $e->getMessage();
130 print "<br>\n";
131 if ($clientmustbe != 'utf8') {
132 print 'Edit conf/conf.php file to set a charset "utf8"';
133 if ($clientmustbe != 'utf8mb4') {
134 print ' or "utf8mb4"';
135 }
136 print ' instead of "'.$clientmustbe.'".'."\n";
137 }
138 exit;
139 }
140
141 $collation = (empty($conf) ? 'utf8_unicode_ci' : $conf->db->dolibarr_main_db_collation);
142 if (preg_match('/latin1/', $collation)) {
143 $collation = 'utf8_unicode_ci';
144 }
145
146 if (!preg_match('/general/', $collation)) {
147 $this->db->query("SET collation_connection = ".$collation);
148 }
149 }
150 } else {
151 $this->database_selected = false;
152 $this->database_name = '';
153 $this->ok = false;
154 $this->error = $this->error();
155 dol_syslog(get_class($this)."::DoliDBMysqli : Select_db error ".$this->error, LOG_ERR);
156 }
157 } else {
158 // No selection of database done. We may only be connected or not (ok or ko) to the server.
159 $this->database_selected = false;
160
161 if ($this->connected) {
162 // If client is old latin, we force utf8
163 $clientmustbe = empty($conf->db->character_set) ? 'utf8' : (string) $conf->db->character_set;
164 if (preg_match('/latin1/', $clientmustbe)) {
165 $clientmustbe = 'utf8';
166 }
167 if (preg_match('/utf8mb4/', $clientmustbe)) {
168 $clientmustbe = 'utf8';
169 }
170
171 if ($this->db->character_set_name() != $clientmustbe) {
172 $this->db->set_charset($clientmustbe); // This set utf8_unicode_ci
173
174 $collation = $conf->db->dolibarr_main_db_collation;
175 if (preg_match('/latin1/', $collation)) {
176 $collation = 'utf8_unicode_ci';
177 }
178 if (preg_match('/utf8mb4/', $collation)) {
179 $collation = 'utf8_unicode_ci';
180 }
181
182 if (!preg_match('/general/', $collation)) {
183 $this->db->query("SET collation_connection = ".$collation);
184 }
185 }
186 }
187 }
188 }
189
190
197 public function hintindex($nameofindex)
198 {
199 return " FORCE INDEX(".preg_replace('/[^a-z0-9_]/', '', $nameofindex).")";
200 }
201
202
210 public function convertSQLFromMysql($line, $type = 'ddl')
211 {
212 return $line;
213 }
214
215
216 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
223 public function select_db($database)
224 {
225 // phpcs:enable
226 dol_syslog(get_class($this)."::select_db database=".$database, LOG_DEBUG);
227 $result = false;
228 try {
229 $result = $this->db->select_db($database);
230 } catch (Exception $e) {
231 // Nothing done on error
232 }
233 return $result;
234 }
235
236
248 public function connect($host, $login, $passwd, $name, $port = 0)
249 {
250 dol_syslog(get_class($this)."::connect host=$host, port=$port, login=$login, passwd=--hidden--, name=$name", LOG_DEBUG);
251
252 //mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
253
254 $tmp = false;
255 try {
256 if (!class_exists('mysqli')) {
257 dol_print_error(null, 'Driver mysqli for PHP not available');
258 }
259 if (strpos($host, 'ssl://') === 0) {
260 $tmp = new mysqliDoli($host, $login, $passwd, $name, $port);
261 } else {
262 $tmp = new mysqli($host, $login, $passwd, $name, $port);
263 }
264 } catch (Exception $e) {
265 dol_syslog(get_class($this)."::connect failed", LOG_DEBUG);
266 }
267 return $tmp;
268 }
269
275 public function getVersion()
276 {
277 return $this->db->server_info;
278 }
279
285 public function getDriverInfo()
286 {
287 return $this->db->client_info;
288 }
289
290
297 public function close()
298 {
299 if ($this->db) {
300 if ($this->transaction_opened > 0) {
301 dol_syslog(get_class($this)."::close Closing a connection with an opened transaction depth=".$this->transaction_opened, LOG_ERR);
302 }
303 $this->connected = false;
304 return $this->db->close();
305 }
306 return false;
307 }
308
309
310
321 public function query($query, $usesavepoint = 0, $type = 'auto', $result_mode = 0)
322 {
323 global $dolibarr_main_db_readonly;
324
325 $query = trim($query);
326
327 if (!in_array($query, array('BEGIN', 'COMMIT', 'ROLLBACK'))) {
328 $SYSLOG_SQL_LIMIT = 10000; // limit log to 10kb per line to limit DOS attacks
329 dol_syslog('sql='.substr($query, 0, $SYSLOG_SQL_LIMIT), LOG_DEBUG);
330 }
331 if (empty($query)) {
332 return false; // Return false = error if empty request
333 }
334
335 if (!empty($dolibarr_main_db_readonly)) {
336 if (preg_match('/^(INSERT|UPDATE|REPLACE|DELETE|CREATE|ALTER|TRUNCATE|DROP)/i', $query)) {
337 $this->lasterror = 'Application in read-only mode';
338 $this->lasterrno = 'APPREADONLY';
339 $this->lastquery = $query;
340 return false;
341 }
342 }
343
344 try {
345 $ret = $this->db->query($query, $result_mode);
346 } catch (Exception $e) {
347 dol_syslog(get_class($this)."::query Exception in query instead of returning an error: ".$e->getMessage(), LOG_ERR);
348 $ret = false;
349 }
350
351 if (!preg_match("/^COMMIT/i", $query) && !preg_match("/^ROLLBACK/i", $query)) {
352 // Si requete utilisateur, on la sauvegarde ainsi que son resultset
353 if (!$ret) {
354 $this->lastqueryerror = $query;
355 $this->lasterror = $this->error();
356 $this->lasterrno = $this->errno();
357
358 if (getDolGlobalInt('SYSLOG_LEVEL') < LOG_DEBUG) {
359 dol_syslog(get_class($this)."::query SQL Error query: ".$query, LOG_ERR); // Log of request was not yet done previously
360 }
361 dol_syslog(get_class($this)."::query SQL Error message: ".$this->lasterrno." ".$this->lasterror.self::getCallerInfoString(), LOG_ERR);
362 //var_dump(debug_print_backtrace());
363 }
364 $this->lastquery = $query;
365 $this->_results = $ret;
366 }
367
368 return $ret;
369 }
370
376 final protected static function getCallerInfoString()
377 {
378 $backtrace = debug_backtrace();
379 $msg = "";
380 if (count($backtrace) >= 1) {
381 $trace = $backtrace[1];
382 if (isset($trace['file'], $trace['line'])) {
383 $msg = " From {$trace['file']}:{$trace['line']}.";
384 }
385 }
386 return $msg;
387 }
388
389 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
396 public function fetch_object($resultset)
397 {
398 // phpcs:enable
399 // If the resultset was not provided, we get the last one for this connection
400 if (!is_object($resultset)) {
401 $resultset = $this->_results;
402 }
403 return $resultset->fetch_object();
404 }
405
406
407 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
414 public function fetch_array($resultset)
415 {
416 // phpcs:enable
417 // If resultset not provided, we take the last used by connection
418 if (!is_object($resultset)) {
419 $resultset = $this->_results;
420 }
421 return $resultset->fetch_array();
422 }
423
424 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
431 public function fetch_row($resultset)
432 {
433 // phpcs:enable
434 // If resultset not provided, we take the last used by connection
435 if (!is_bool($resultset)) {
436 if (!is_object($resultset)) {
437 $resultset = $this->_results;
438 }
439 return $resultset->fetch_row();
440 } else {
441 // si le curseur est un boolean on retourne la valeur 0
442 return 0;
443 }
444 }
445
446 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
454 public function num_rows($resultset)
455 {
456 // phpcs:enable
457 // If resultset not provided, we take the last used by connection
458 if (!is_object($resultset)) {
459 $resultset = $this->_results;
460 }
461 return isset($resultset->num_rows) ? $resultset->num_rows : 0;
462 }
463
464 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
472 public function affected_rows($resultset)
473 {
474 // phpcs:enable
475 // If resultset not provided, we take the last used by connection
476 if (!is_object($resultset)) {
477 $resultset = $this->_results;
478 }
479 // mysql necessite un link de base pour cette fonction contrairement
480 // a pqsql qui prend un resultset
481 return $this->db->affected_rows;
482 }
483
490 public function free($resultset = null)
491 {
492 // If resultset not provided, we take the last used by connection
493 if (!is_object($resultset)) {
494 $resultset = $this->_results;
495 }
496 // Si resultset en est un, on libere la memoire
497 if (is_object($resultset)) {
498 $resultset->free_result();
499 }
500 }
501
508 public function escape($stringtoencode)
509 {
510 return $this->db->real_escape_string((string) $stringtoencode);
511 }
512
519 public function escapeforlike($stringtoencode)
520 {
521 // We must first replace the \ char into \\, then we can replace _ and % into \_ and \%
522 return str_replace(array('\\', '_', '%'), array('\\\\', '\_', '\%'), (string) $stringtoencode);
523 }
524
530 public function errno()
531 {
532 if (!$this->connected) {
533 // Si il y a eu echec de connection, $this->db n'est pas valide.
534 return 'DB_ERROR_FAILED_TO_CONNECT';
535 } else {
536 // Constants to convert a MySql error code to a generic Dolibarr error code
537 $errorcode_map = array(
538 1004 => 'DB_ERROR_CANNOT_CREATE',
539 1005 => 'DB_ERROR_CANNOT_CREATE',
540 1006 => 'DB_ERROR_CANNOT_CREATE',
541 1007 => 'DB_ERROR_ALREADY_EXISTS',
542 1008 => 'DB_ERROR_CANNOT_DROP',
543 1022 => 'DB_ERROR_KEY_NAME_ALREADY_EXISTS',
544 1025 => 'DB_ERROR_NO_FOREIGN_KEY_TO_DROP',
545 1044 => 'DB_ERROR_ACCESSDENIED',
546 1046 => 'DB_ERROR_NODBSELECTED',
547 1048 => 'DB_ERROR_CONSTRAINT',
548 1050 => 'DB_ERROR_TABLE_ALREADY_EXISTS',
549 1051 => 'DB_ERROR_NOSUCHTABLE',
550 1054 => 'DB_ERROR_NOSUCHFIELD',
551 1060 => 'DB_ERROR_COLUMN_ALREADY_EXISTS',
552 1061 => 'DB_ERROR_KEY_NAME_ALREADY_EXISTS',
553 1062 => 'DB_ERROR_RECORD_ALREADY_EXISTS',
554 1064 => 'DB_ERROR_SYNTAX',
555 1068 => 'DB_ERROR_PRIMARY_KEY_ALREADY_EXISTS',
556 1075 => 'DB_ERROR_CANT_DROP_PRIMARY_KEY',
557 1091 => 'DB_ERROR_NOSUCHFIELD',
558 1100 => 'DB_ERROR_NOT_LOCKED',
559 1136 => 'DB_ERROR_VALUE_COUNT_ON_ROW',
560 1146 => 'DB_ERROR_NOSUCHTABLE',
561 1215 => 'DB_ERROR_CANNOT_ADD_FOREIGN_KEY_CONSTRAINT',
562 1216 => 'DB_ERROR_NO_PARENT',
563 1217 => 'DB_ERROR_CHILD_EXISTS',
564 1396 => 'DB_ERROR_USER_ALREADY_EXISTS', // When creating a user that already existing
565 1451 => 'DB_ERROR_CHILD_EXISTS',
566 1826 => 'DB_ERROR_KEY_NAME_ALREADY_EXISTS'
567 );
568
569 if (isset($errorcode_map[$this->db->errno])) {
570 return $errorcode_map[$this->db->errno];
571 }
572 $errno = $this->db->errno;
573 return ($errno ? 'DB_ERROR_'.$errno : '0');
574 }
575 }
576
582 public function error()
583 {
584 if (!$this->connected) {
585 // Si il y a eu echec de connection, $this->db n'est pas valide pour mysqli_error.
586 return 'Not connected. Check setup parameters in conf/conf.php file and your mysql client and server versions';
587 } else {
588 return $this->db->error;
589 }
590 }
591
592 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
600 public function last_insert_id($tab, $fieldid = 'rowid')
601 {
602 // phpcs:enable
603 return $this->db->insert_id;
604 }
605
614 public function encrypt($fieldorvalue, $withQuotes = 1)
615 {
616 global $conf;
617
618 // Type of encryption (2: AES (recommended), 1: DES , 0: no encryption)
619 $cryptType = (!empty($conf->db->dolibarr_main_db_encryption) ? $conf->db->dolibarr_main_db_encryption : 0);
620
621 //Encryption key
622 $cryptKey = (!empty($conf->db->dolibarr_main_db_cryptkey) ? $conf->db->dolibarr_main_db_cryptkey : '');
623
624 $escapedstringwithquotes = ($withQuotes ? "'" : "").$this->escape($fieldorvalue).($withQuotes ? "'" : "");
625
626 if ($cryptType && !empty($cryptKey)) {
627 if ($cryptType == 2) {
628 $escapedstringwithquotes = "AES_ENCRYPT(".$escapedstringwithquotes.", '".$this->escape($cryptKey)."')";
629 } elseif ($cryptType == 1) {
630 $escapedstringwithquotes = "DES_ENCRYPT(".$escapedstringwithquotes.", '".$this->escape($cryptKey)."')";
631 }
632 }
633
634 return $escapedstringwithquotes;
635 }
636
643 public function decrypt($value)
644 {
645 global $conf;
646
647 // Type of encryption (2: AES (recommended), 1: DES , 0: no encryption)
648 $cryptType = (!empty($conf->db->dolibarr_main_db_encryption) ? $conf->db->dolibarr_main_db_encryption : 0);
649
650 //Encryption key
651 $cryptKey = (!empty($conf->db->dolibarr_main_db_cryptkey) ? $conf->db->dolibarr_main_db_cryptkey : '');
652
653 $return = $value;
654
655 if ($cryptType && !empty($cryptKey)) {
656 if ($cryptType == 2) {
657 $return = 'AES_DECRYPT('.$value.',\''.$cryptKey.'\')';
658 } elseif ($cryptType == 1) {
659 $return = 'DES_DECRYPT('.$value.',\''.$cryptKey.'\')';
660 }
661 }
662
663 return $return;
664 }
665
666
667 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
673 public function DDLGetConnectId()
674 {
675 // phpcs:enable
676 $resql = $this->query('SELECT CONNECTION_ID()');
677 if ($resql) {
678 $row = $this->fetch_row($resql);
679 return $row[0];
680 } else {
681 return '?';
682 }
683 }
684
685 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
697 public function DDLCreateDb($database, $charset = '', $collation = '', $owner = '')
698 {
699 // phpcs:enable
700 if (empty($charset)) {
701 $charset = $this->forcecharset;
702 }
703 if (empty($collation)) {
704 $collation = $this->forcecollate;
705 }
706
707 // ALTER DATABASE dolibarr_db DEFAULT CHARACTER SET latin DEFAULT COLLATE latin1_swedish_ci
708 $sql = "CREATE DATABASE `".$this->escape($database)."`";
709 $sql .= " DEFAULT CHARACTER SET `".$this->escape($charset)."` DEFAULT COLLATE `".$this->escape($collation)."`";
710
711 dol_syslog($sql, LOG_DEBUG);
712 $ret = $this->query($sql);
713 if (!$ret) {
714 // We try again for compatibility with Mysql < 4.1.1
715 $sql = "CREATE DATABASE `".$this->escape($database)."`";
716 dol_syslog($sql, LOG_DEBUG);
717 $ret = $this->query($sql);
718 }
719
720 return $ret;
721 }
722
723 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
731 public function DDLListTables($database, $table = '')
732 {
733 // phpcs:enable
734 $listtables = array();
735
736 $like = '';
737 if ($table) {
738 $tmptable = preg_replace('/[^a-z0-9\.\-\_%]/i', '', $table);
739
740 $like = "LIKE '".$this->escape($tmptable)."'";
741 }
742 $tmpdatabase = preg_replace('/[^a-z0-9\.\-\_]/i', '', $database);
743
744 $sql = "SHOW TABLES FROM `".$tmpdatabase."` ".$like.";";
745 //print $sql;
746 $result = $this->query($sql);
747 if ($result) {
748 while ($row = $this->fetch_row($result)) {
749 $listtables[] = $row[0];
750 }
751 }
752 return $listtables;
753 }
754
755 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
763 public function DDLListTablesFull($database, $table = '')
764 {
765 // phpcs:enable
766 $listtables = array();
767
768 $like = '';
769 if ($table) {
770 $tmptable = preg_replace('/[^a-z0-9\.\-\_%]/i', '', $table);
771
772 $like = "LIKE '".$this->escape($tmptable)."'";
773 }
774 $tmpdatabase = preg_replace('/[^a-z0-9\.\-\_]/i', '', $database);
775
776 $sql = "SHOW FULL TABLES FROM `".$tmpdatabase."` ".$like.";";
777
778 $result = $this->query($sql);
779 if ($result) {
780 while ($row = $this->fetch_row($result)) {
781 $listtables[] = $row;
782 }
783 }
784 return $listtables;
785 }
786
787 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
794 public function DDLInfoTable($table)
795 {
796 // phpcs:enable
797 $infotables = array();
798
799 $tmptable = preg_replace('/[^a-z0-9\.\-\_]/i', '', $table);
800
801 $sql = "SHOW FULL COLUMNS FROM ".$tmptable.";";
802
803 dol_syslog($sql, LOG_DEBUG);
804 $result = $this->query($sql);
805 if ($result) {
806 while ($row = $this->fetch_row($result)) {
807 $infotables[] = $row;
808 }
809 }
810 return $infotables;
811 }
812
813 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
826 public function DDLCreateTable($table, $fields, $primary_key, $type, $unique_keys = null, $fulltext_keys = null, $keys = null)
827 {
828 // phpcs:enable
829 // @TODO: $fulltext_keys parameter is unused
830
831 if (empty($type)) {
832 $type = 'InnoDB';
833 }
834
835 $pk = '';
836 $sqlk = array();
837 $sqluq = array();
838
839 // Keys found into the array $fields: type,value,attribute,null,default,extra
840 // ex. : $fields['rowid'] = array(
841 // 'type'=>'int' or 'integer',
842 // 'value'=>'11',
843 // 'null'=>'not null',
844 // 'extra'=> 'auto_increment'
845 // );
846 $sql = "CREATE TABLE ".$this->sanitize($table)."(";
847 $i = 0;
848 $sqlfields = array();
849 foreach ($fields as $field_name => $field_desc) {
850 $sqlfields[$i] = $this->sanitize($field_name)." ";
851 $sqlfields[$i] .= $this->sanitize($field_desc['type']);
852 if (isset($field_desc['value']) && $field_desc['value'] !== '') {
853 $sqlfields[$i] .= "(".$this->sanitize($field_desc['value']).")";
854 }
855 if (isset($field_desc['attribute']) && $field_desc['attribute'] !== '') {
856 $sqlfields[$i] .= " ".$this->sanitize($field_desc['attribute'], 0, 0, 1); // Allow space to accept attributes like "ON UPDATE CURRENT_TIMESTAMP"
857 }
858 if (isset($field_desc['default']) && $field_desc['default'] !== '') {
859 if (in_array($field_desc['type'], array('tinyint', 'smallint', 'int', 'double'))) {
860 $sqlfields[$i] .= " DEFAULT ".((float) $field_desc['default']);
861 } elseif ($field_desc['default'] == 'null' || $field_desc['default'] == 'CURRENT_TIMESTAMP') {
862 $sqlfields[$i] .= " DEFAULT ".$this->sanitize($field_desc['default']);
863 } else {
864 $sqlfields[$i] .= " DEFAULT '".$this->escape($field_desc['default'])."'";
865 }
866 }
867 if (isset($field_desc['null']) && $field_desc['null'] !== '') {
868 $sqlfields[$i] .= " ".$this->sanitize($field_desc['null'], 0, 0, 1);
869 }
870 if (isset($field_desc['extra']) && $field_desc['extra'] !== '') {
871 $sqlfields[$i] .= " ".$this->sanitize($field_desc['extra'], 0, 0, 1);
872 }
873 if (!empty($primary_key) && $primary_key == $field_name) {
874 $sqlfields[$i] .= " AUTO_INCREMENT PRIMARY KEY"; // mysql instruction that will be converted by driver late
875 }
876 $i++;
877 }
878
879 if (is_array($unique_keys)) {
880 $i = 0;
881 foreach ($unique_keys as $key => $value) {
882 $sqluq[$i] = "UNIQUE KEY '".$this->sanitize($key)."' ('".$this->escape($value)."')";
883 $i++;
884 }
885 }
886 if (is_array($keys)) {
887 $i = 0;
888 foreach ($keys as $key => $value) {
889 $sqlk[$i] = "KEY ".$this->sanitize($key)." (".$value.")";
890 $i++;
891 }
892 }
893 $sql .= implode(', ', $sqlfields);
894 if ($unique_keys != "") {
895 $sql .= ",".implode(',', $sqluq);
896 }
897 if (is_array($keys)) {
898 $sql .= ",".implode(',', $sqlk);
899 }
900 $sql .= ")";
901 $sql .= " engine=".$this->sanitize($type);
902
903 if (!$this->query($sql)) {
904 return -1;
905 } else {
906 return 1;
907 }
908 }
909
910 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
917 public function DDLDropTable($table)
918 {
919 // phpcs:enable
920 $tmptable = preg_replace('/[^a-z0-9\.\-\_]/i', '', $table);
921
922 $sql = "DROP TABLE ".$this->sanitize($tmptable);
923
924 if (!$this->query($sql)) {
925 return -1;
926 } else {
927 return 1;
928 }
929 }
930
931 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
939 public function DDLDescTable($table, $field = "")
940 {
941 // phpcs:enable
942 $sql = "DESC ".$this->sanitize($table)." ".$this->sanitize($field);
943
944 dol_syslog(get_class($this)."::DDLDescTable ".$sql, LOG_DEBUG);
945 $this->_results = $this->query($sql);
946 return $this->_results;
947 }
948
949 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
959 public function DDLAddField($table, $field_name, $field_desc, $field_position = "")
960 {
961 // phpcs:enable
962 // cles recherchees dans le tableau des descriptions (field_desc) : type,value,attribute,null,default,extra
963 // ex. : $field_desc = array('type'=>'int','value'=>'11','null'=>'not null','extra'=> 'auto_increment');
964 $sql = "ALTER TABLE ".$this->sanitize($table)." ADD ".$this->sanitize($field_name)." ";
965 $sql .= $this->sanitize($field_desc['type']);
966 if (isset($field_desc['value']) && preg_match("/^[^\s]/i", $field_desc['value'])) {
967 if (!in_array($field_desc['type'], array('tinyint', 'smallint', 'int', 'date', 'datetime')) && $field_desc['value']) {
968 $sql .= "(".$this->sanitize($field_desc['value']).")";
969 }
970 }
971 if (isset($field_desc['attribute']) && preg_match("/^[^\s]/i", $field_desc['attribute'])) {
972 $sql .= " ".$this->sanitize($field_desc['attribute']);
973 }
974 if (isset($field_desc['null']) && preg_match("/^[^\s]/i", $field_desc['null'])) {
975 $sql .= " ".$field_desc['null'];
976 }
977 if (isset($field_desc['default']) && preg_match("/^[^\s]/i", $field_desc['default'])) {
978 if (in_array($field_desc['type'], array('tinyint', 'smallint', 'int', 'double'))) {
979 $sql .= " DEFAULT ".((float) $field_desc['default']);
980 } elseif ($field_desc['default'] == 'null' || $field_desc['default'] == 'CURRENT_TIMESTAMP') {
981 $sql .= " DEFAULT ".$this->sanitize($field_desc['default']);
982 } else {
983 $sql .= " DEFAULT '".$this->escape($field_desc['default'])."'";
984 }
985 }
986 if (isset($field_desc['extra']) && preg_match("/^[^\s]/i", $field_desc['extra'])) {
987 $sql .= " ".$this->sanitize($field_desc['extra'], 0, 0, 1);
988 }
989 $sql .= " ".$this->sanitize($field_position, 0, 0, 1);
990
991 dol_syslog(get_class($this)."::DDLAddField ".$sql, LOG_DEBUG);
992 if ($this->query($sql)) {
993 return 1;
994 }
995 return -1;
996 }
997
998 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
1007 public function DDLUpdateField($table, $field_name, $field_desc)
1008 {
1009 // phpcs:enable
1010 $sql = "ALTER TABLE ".$this->sanitize($table);
1011 $sql .= " MODIFY COLUMN ".$this->sanitize($field_name)." ".$this->sanitize($field_desc['type']);
1012 if (in_array($field_desc['type'], array('double', 'tinyint', 'int', 'varchar')) && $field_desc['value']) {
1013 $sql .= "(".$this->sanitize($field_desc['value']).")";
1014 }
1015 if (isset($field_desc['value']) && ($field_desc['null'] == 'not null' || $field_desc['null'] == 'NOT NULL')) {
1016 // 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
1017 if ($field_desc['type'] == 'varchar' || $field_desc['type'] == 'text') {
1018 $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";
1019 $this->query($sqlbis);
1020 } elseif (in_array($field_desc['type'], array('tinyint', 'smallint', 'int', 'double'))) {
1021 $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";
1022 $this->query($sqlbis);
1023 }
1024
1025 $sql .= " NOT NULL";
1026 }
1027
1028 if (isset($field_desc['default']) && $field_desc['default'] != '') {
1029 if (in_array($field_desc['type'], array('tinyint', 'smallint', 'int', 'double'))) {
1030 $sql .= " DEFAULT ".((float) $field_desc['default']);
1031 } elseif ($field_desc['type'] != 'text') {
1032 $sql .= " DEFAULT '".$this->escape($field_desc['default'])."'"; // Default not supported on text fields
1033 }
1034 }
1035
1036 dol_syslog(get_class($this)."::DDLUpdateField ".$sql, LOG_DEBUG);
1037 if (!$this->query($sql)) {
1038 return -1;
1039 } else {
1040 return 1;
1041 }
1042 }
1043
1044 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
1052 public function DDLDropField($table, $field_name)
1053 {
1054 // phpcs:enable
1055 $tmp_field_name = preg_replace('/[^a-z0-9\.\-\_]/i', '', $field_name);
1056
1057 $sql = "ALTER TABLE ".$this->sanitize($table)." DROP COLUMN `".$this->sanitize($tmp_field_name)."`";
1058 if ($this->query($sql)) {
1059 return 1;
1060 }
1061 $this->error = $this->lasterror();
1062 return -1;
1063 }
1064
1065
1066 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
1076 public function DDLCreateUser($dolibarr_main_db_host, $dolibarr_main_db_user, $dolibarr_main_db_pass, $dolibarr_main_db_name)
1077 {
1078 // phpcs:enable
1079 $sql = "CREATE USER '".$this->escape($dolibarr_main_db_user)."' IDENTIFIED BY '".$this->escape($dolibarr_main_db_pass)."'";
1080 dol_syslog(get_class($this)."::DDLCreateUser", LOG_DEBUG); // No sql to avoid password in log
1081 $resql = $this->query($sql);
1082 if (!$resql) {
1083 if ($this->lasterrno != 'DB_ERROR_USER_ALREADY_EXISTS') {
1084 return -1;
1085 } else {
1086 // If user already exists, we continue to set permissions
1087 dol_syslog(get_class($this)."::DDLCreateUser sql=".$sql, LOG_WARNING);
1088 }
1089 }
1090
1091 // Redo with localhost forced (sometimes user is created on %)
1092 $sql = "CREATE USER '".$this->escape($dolibarr_main_db_user)."'@'localhost' IDENTIFIED BY '".$this->escape($dolibarr_main_db_pass)."'";
1093 $resql = $this->query($sql);
1094
1095 $sql = "GRANT ALL PRIVILEGES ON ".$this->escape($dolibarr_main_db_name).".* TO '".$this->escape($dolibarr_main_db_user)."'@'".$this->escape($dolibarr_main_db_host)."'";
1096 dol_syslog(get_class($this)."::DDLCreateUser", LOG_DEBUG); // No sql to avoid password in log
1097 $resql = $this->query($sql);
1098 if (!$resql) {
1099 $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)."'";
1100 return -1;
1101 }
1102
1103 $sql = "FLUSH Privileges";
1104
1105 dol_syslog(get_class($this)."::DDLCreateUser", LOG_DEBUG);
1106 $resql = $this->query($sql);
1107 if (!$resql) {
1108 return -1;
1109 }
1110
1111 return 1;
1112 }
1113
1121 public function getDefaultCharacterSetDatabase()
1122 {
1123 $resql = $this->query("SHOW VARIABLES LIKE 'character_set_database'");
1124 if (!$resql) {
1125 // version Mysql < 4.1.1
1126 return $this->forcecharset;
1127 }
1128 $liste = $this->fetch_array($resql);
1129 $tmpval = $liste['Value'];
1130
1131 return $tmpval;
1132 }
1133
1139 public function getListOfCharacterSet()
1140 {
1141 $resql = $this->query('SHOW CHARSET');
1142 $liste = array();
1143 if ($resql) {
1144 $i = 0;
1145 while ($obj = $this->fetch_object($resql)) {
1146 $liste[$i]['charset'] = $obj->Charset;
1147 $liste[$i]['description'] = $obj->Description;
1148 $i++;
1149 }
1150 $this->free($resql);
1151 } else {
1152 // version Mysql < 4.1.1
1153 return null;
1154 }
1155 return $liste;
1156 }
1157
1164 public function getDefaultCollationDatabase()
1165 {
1166 $resql = $this->query("SHOW VARIABLES LIKE 'collation_database'");
1167 if (!$resql) {
1168 // version Mysql < 4.1.1
1169 return $this->forcecollate;
1170 }
1171 $liste = $this->fetch_array($resql);
1172 $tmpval = $liste['Value'];
1173
1174 return $tmpval;
1175 }
1176
1182 public function getListOfCollation()
1183 {
1184 $resql = $this->query('SHOW COLLATION');
1185 $liste = array();
1186 if ($resql) {
1187 $i = 0;
1188 while ($obj = $this->fetch_object($resql)) {
1189 $liste[$i]['collation'] = $obj->Collation;
1190 $i++;
1191 }
1192 $this->free($resql);
1193 } else {
1194 // version Mysql < 4.1.1
1195 return null;
1196 }
1197 return $liste;
1198 }
1199
1205 public function getPathOfDump()
1206 {
1207 $fullpathofdump = '/pathtomysqldump/mysqldump';
1208
1209 $resql = $this->query("SHOW VARIABLES LIKE 'basedir'");
1210 if ($resql) {
1211 $liste = $this->fetch_array($resql);
1212 $basedir = $liste['Value'];
1213 $fullpathofdump = $basedir.(preg_match('/\/$/', $basedir) ? '' : '/').'bin/mysqldump';
1214 }
1215 return $fullpathofdump;
1216 }
1217
1223 public function getPathOfRestore()
1224 {
1225 $fullpathofimport = '/pathtomysql/mysql';
1226
1227 $resql = $this->query("SHOW VARIABLES LIKE 'basedir'");
1228 if ($resql) {
1229 $liste = $this->fetch_array($resql);
1230 $basedir = $liste['Value'];
1231 $fullpathofimport = $basedir.(preg_match('/\/$/', $basedir) ? '' : '/').'bin/mysql';
1232 }
1233 return $fullpathofimport;
1234 }
1235
1242 public function getServerParametersValues($filter = '')
1243 {
1244 $result = array();
1245
1246 $sql = 'SHOW VARIABLES';
1247 if ($filter) {
1248 $sql .= " LIKE '".$this->escape($filter)."'";
1249 }
1250 $resql = $this->query($sql);
1251 if ($resql) {
1252 while ($obj = $this->fetch_object($resql)) {
1253 $result[$obj->Variable_name] = $obj->Value;
1254 }
1255 }
1256
1257 return $result;
1258 }
1259
1266 public function getServerStatusValues($filter = '')
1267 {
1268 $result = array();
1269
1270 $sql = 'SHOW STATUS';
1271 if ($filter) {
1272 $sql .= " LIKE '".$this->escape($filter)."'";
1273 }
1274 $resql = $this->query($sql);
1275 if ($resql) {
1276 while ($obj = $this->fetch_object($resql)) {
1277 $result[$obj->Variable_name] = $obj->Value;
1278 }
1279 }
1280
1281 return $result;
1282 }
1283}
1284
1288class mysqliDoli extends mysqli
1289{
1301 public function __construct($host, $user, $pass, $name, $port = 0, $socket = "")
1302 {
1303 $flags = 0;
1304 if (PHP_VERSION_ID >= 80100) {
1305 parent::__construct();
1306 } else {
1307 // @phan-suppress-next-line PhanDeprecatedFunctionInternal
1308 parent::init();
1309 }
1310 if (strpos($host, 'ssl://') === 0) {
1311 $host = substr($host, 6);
1312 parent::options(MYSQLI_OPT_SSL_VERIFY_SERVER_CERT, false);
1313 // Suppress false positive @phan-suppress-next-line PhanTypeMismatchArgumentInternalProbablyReal
1314 parent::ssl_set(null, null, "", null, null);
1315 $flags = MYSQLI_CLIENT_SSL;
1316 }
1317 parent::real_connect($host, $user, $pass, $name, $port, $socket, $flags);
1318 }
1319}
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.
Class to make SSL 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:139
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:1991