dolibarr  20.0.0-beta
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 
29 require_once DOL_DOCUMENT_ROOT.'/core/db/DoliDB.class.php';
30 
34 class 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']);
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 
1288 class 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(".$version.") pid cd cd cd description as description
Only used if Module[ID]Desc translation string is not found.
print *****$script_file(".$version.") pid c cd cd cd description as p label as s rowid
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(!defined( 'CSRFCHECK_WITH_TOKEN'))
if(preg_match('/crypted:/i', $dolibarr_main_db_pass)||!empty($dolibarr_main_db_encrypted_pass)) $conf db type
Definition: repair.php:123