dolibarr  18.0.0-alpha
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  *
9  * This program is free software; you can redistribute it and/or modify
10  * it under the terms of the GNU General Public License as published by
11  * the Free Software Foundation; either version 3 of the License, or
12  * (at your option) any later version.
13  *
14  * This program is distributed in the hope that it will be useful,
15  * but WITHOUT ANY WARRANTY; without even the implied warranty of
16  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
17  * GNU General Public License for more details.
18  *
19  * You should have received a copy of the GNU General Public License
20  * along with this program. If not, see <https://www.gnu.org/licenses/>.
21  */
22 
28 require_once DOL_DOCUMENT_ROOT.'/core/db/DoliDB.class.php';
29 
33 class DoliDBMysqli extends DoliDB
34 {
36  public $db;
38  public $type = 'mysqli';
39 
41  const LABEL = 'MySQL or MariaDB';
43  const VERSIONMIN = '5.0.3';
44 
46  private $_results;
47 
59  public function __construct($type, $host, $user, $pass, $name = '', $port = 0)
60  {
61  global $conf, $langs;
62 
63  // Note that having "static" property for "$forcecharset" and "$forcecollate" will make error here in strict mode, so they are not static
64  if (!empty($conf->db->character_set)) {
65  $this->forcecharset = $conf->db->character_set;
66  }
67  if (!empty($conf->db->dolibarr_main_db_collation)) {
68  $this->forcecollate = $conf->db->dolibarr_main_db_collation;
69  }
70 
71  $this->database_user = $user;
72  $this->database_host = $host;
73  $this->database_port = $port;
74 
75  $this->transaction_opened = 0;
76 
77  //print "Name DB: $host,$user,$pass,$name<br>";
78 
79  if (!class_exists('mysqli')) {
80  $this->connected = false;
81  $this->ok = false;
82  $this->error = "Mysqli PHP functions for using Mysqli driver are not available in this version of PHP. Try to use another driver.";
83  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);
84  }
85 
86  if (!$host) {
87  $this->connected = false;
88  $this->ok = false;
89  $this->error = $langs->trans("ErrorWrongHostParameter");
90  dol_syslog(get_class($this)."::DoliDBMysqli : Connect error, wrong host parameters", LOG_ERR);
91  }
92 
93  // Try server connection
94  // We do not try to connect to database, only to server. Connect to database is done later in constrcutor
95  $this->db = $this->connect($host, $user, $pass, '', $port);
96 
97  if ($this->db && empty($this->db->connect_errno)) {
98  $this->connected = true;
99  $this->ok = true;
100  } else {
101  $this->connected = false;
102  $this->ok = false;
103  $this->error = empty($this->db) ? 'Failed to connect' : $this->db->connect_error;
104  dol_syslog(get_class($this)."::DoliDBMysqli Connect error: ".$this->error, LOG_ERR);
105  }
106 
107  // If server connection is ok, we try to connect to the database
108  if ($this->connected && $name) {
109  if ($this->select_db($name)) {
110  $this->database_selected = true;
111  $this->database_name = $name;
112  $this->ok = true;
113 
114  // If client is old latin, we force utf8
115  $clientmustbe = empty($conf->db->dolibarr_main_db_character_set) ? 'utf8' : $conf->db->dolibarr_main_db_character_set;
116  if (preg_match('/latin1/', $clientmustbe)) {
117  $clientmustbe = 'utf8';
118  }
119 
120  if ($this->db->character_set_name() != $clientmustbe) {
121  $this->db->set_charset($clientmustbe); // This set charset, but with a bad collation
122 
123  $collation = $conf->db->dolibarr_main_db_collation;
124  if (preg_match('/latin1/', $collation)) {
125  $collation = 'utf8_unicode_ci';
126  }
127 
128  if (!preg_match('/general/', $collation)) {
129  $this->db->query("SET collation_connection = ".$collation);
130  }
131  }
132  } else {
133  $this->database_selected = false;
134  $this->database_name = '';
135  $this->ok = false;
136  $this->error = $this->error();
137  dol_syslog(get_class($this)."::DoliDBMysqli : Select_db error ".$this->error, LOG_ERR);
138  }
139  } else {
140  // Pas de selection de base demandee, ok ou ko
141  $this->database_selected = false;
142 
143  if ($this->connected) {
144  // If client is old latin, we force utf8
145  $clientmustbe = empty($conf->db->dolibarr_main_db_character_set) ? 'utf8' : $conf->db->dolibarr_main_db_character_set;
146  if (preg_match('/latin1/', $clientmustbe)) {
147  $clientmustbe = 'utf8';
148  }
149  if (preg_match('/utf8mb4/', $clientmustbe)) {
150  $clientmustbe = 'utf8';
151  }
152 
153  if ($this->db->character_set_name() != $clientmustbe) {
154  $this->db->set_charset($clientmustbe); // This set utf8_unicode_ci
155 
156  $collation = $conf->db->dolibarr_main_db_collation;
157  if (preg_match('/latin1/', $collation)) {
158  $collation = 'utf8_unicode_ci';
159  }
160  if (preg_match('/utf8mb4/', $collation)) {
161  $collation = 'utf8_unicode_ci';
162  }
163 
164  if (!preg_match('/general/', $collation)) {
165  $this->db->query("SET collation_connection = ".$collation);
166  }
167  }
168  }
169  }
170  }
171 
172 
179  public function hintindex($nameofindex)
180  {
181  return " FORCE INDEX(".preg_replace('/[^a-z0-9_]/', '', $nameofindex).")";
182  }
183 
184 
192  public static function convertSQLFromMysql($line, $type = 'ddl')
193  {
194  return $line;
195  }
196 
197 
198  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
205  public function select_db($database)
206  {
207  // phpcs:enable
208  dol_syslog(get_class($this)."::select_db database=".$database, LOG_DEBUG);
209  $result = false;
210  try {
211  $result = $this->db->select_db($database);
212  } catch (Exception $e) {
213  // Nothing done on error
214  }
215  return $result;
216  }
217 
218 
230  public function connect($host, $login, $passwd, $name, $port = 0)
231  {
232  dol_syslog(get_class($this)."::connect host=$host, port=$port, login=$login, passwd=--hidden--, name=$name", LOG_DEBUG);
233 
234  //mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
235 
236  // Can also be
237  // mysqli::init(); mysql::options(MYSQLI_INIT_COMMAND, 'SET AUTOCOMMIT = 0'); mysqli::options(MYSQLI_OPT_CONNECT_TIMEOUT, 5);
238  // return mysqli::real_connect($host, $user, $pass, $db, $port);
239  $tmp = false;
240  try {
241  $tmp = new mysqli($host, $login, $passwd, $name, $port);
242  } catch (Exception $e) {
243  dol_syslog(get_class($this)."::connect failed", LOG_DEBUG);
244  }
245  return $tmp;
246  }
247 
253  public function getVersion()
254  {
255  return $this->db->server_info;
256  }
257 
263  public function getDriverInfo()
264  {
265  return $this->db->client_info;
266  }
267 
268 
275  public function close()
276  {
277  if ($this->db) {
278  if ($this->transaction_opened > 0) {
279  dol_syslog(get_class($this)."::close Closing a connection with an opened transaction depth=".$this->transaction_opened, LOG_ERR);
280  }
281  $this->connected = false;
282  return $this->db->close();
283  }
284  return false;
285  }
286 
287 
288 
299  public function query($query, $usesavepoint = 0, $type = 'auto', $result_mode = 0)
300  {
301  global $conf, $dolibarr_main_db_readonly;
302 
303  $query = trim($query);
304 
305  if (!in_array($query, array('BEGIN', 'COMMIT', 'ROLLBACK'))) {
306  $SYSLOG_SQL_LIMIT = 10000; // limit log to 10kb per line to limit DOS attacks
307  dol_syslog('sql='.substr($query, 0, $SYSLOG_SQL_LIMIT), LOG_DEBUG);
308  }
309  if (empty($query)) {
310  return false; // Return false = error if empty request
311  }
312 
313  if (!empty($dolibarr_main_db_readonly)) {
314  if (preg_match('/^(INSERT|UPDATE|REPLACE|DELETE|CREATE|ALTER|TRUNCATE|DROP)/i', $query)) {
315  $this->lasterror = 'Application in read-only mode';
316  $this->lasterrno = 'APPREADONLY';
317  $this->lastquery = $query;
318  return false;
319  }
320  }
321 
322  try {
323  if (!$this->database_name) {
324  // Ordre SQL ne necessitant pas de connexion a une base (exemple: CREATE DATABASE)
325  $ret = $this->db->query($query, $result_mode);
326  } else {
327  $ret = $this->db->query($query, $result_mode);
328  }
329  } catch (Exception $e) {
330  dol_syslog(get_class($this)."::query Exception in query instead of returning an error: ".$e->getMessage(), LOG_ERR);
331  $ret = false;
332  }
333 
334  if (!preg_match("/^COMMIT/i", $query) && !preg_match("/^ROLLBACK/i", $query)) {
335  // Si requete utilisateur, on la sauvegarde ainsi que son resultset
336  if (!$ret) {
337  $this->lastqueryerror = $query;
338  $this->lasterror = $this->error();
339  $this->lasterrno = $this->errno();
340 
341  if ($conf->global->SYSLOG_LEVEL < LOG_DEBUG) {
342  dol_syslog(get_class($this)."::query SQL Error query: ".$query, LOG_ERR); // Log of request was not yet done previously
343  }
344  dol_syslog(get_class($this)."::query SQL Error message: ".$this->lasterrno." ".$this->lasterror, LOG_ERR);
345  //var_dump(debug_print_backtrace());
346  }
347  $this->lastquery = $query;
348  $this->_results = $ret;
349  }
350 
351  return $ret;
352  }
353 
354  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
361  public function fetch_object($resultset)
362  {
363  // phpcs:enable
364  // Si le resultset n'est pas fourni, on prend le dernier utilise sur cette connexion
365  if (!is_object($resultset)) {
366  $resultset = $this->_results;
367  }
368  return $resultset->fetch_object();
369  }
370 
371 
372  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
379  public function fetch_array($resultset)
380  {
381  // phpcs:enable
382  // If resultset not provided, we take the last used by connexion
383  if (!is_object($resultset)) {
384  $resultset = $this->_results;
385  }
386  return $resultset->fetch_array();
387  }
388 
389  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
396  public function fetch_row($resultset)
397  {
398  // phpcs:enable
399  // If resultset not provided, we take the last used by connexion
400  if (!is_bool($resultset)) {
401  if (!is_object($resultset)) {
402  $resultset = $this->_results;
403  }
404  return $resultset->fetch_row();
405  } else {
406  // si le curseur est un booleen on retourne la valeur 0
407  return 0;
408  }
409  }
410 
411  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
419  public function num_rows($resultset)
420  {
421  // phpcs:enable
422  // If resultset not provided, we take the last used by connexion
423  if (!is_object($resultset)) {
424  $resultset = $this->_results;
425  }
426  return isset($resultset->num_rows) ? $resultset->num_rows : 0;
427  }
428 
429  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
437  public function affected_rows($resultset)
438  {
439  // phpcs:enable
440  // If resultset not provided, we take the last used by connexion
441  if (!is_object($resultset)) {
442  $resultset = $this->_results;
443  }
444  // mysql necessite un link de base pour cette fonction contrairement
445  // a pqsql qui prend un resultset
446  return $this->db->affected_rows;
447  }
448 
449 
456  public function free($resultset = null)
457  {
458  // If resultset not provided, we take the last used by connexion
459  if (!is_object($resultset)) {
460  $resultset = $this->_results;
461  }
462  // Si resultset en est un, on libere la memoire
463  if (is_object($resultset)) {
464  $resultset->free_result();
465  }
466  }
467 
474  public function escape($stringtoencode)
475  {
476  return $this->db->real_escape_string((string) $stringtoencode);
477  }
478 
485  public function escapeforlike($stringtoencode)
486  {
487  return str_replace(array('_', '\\', '%'), array('\_', '\\\\', '\%'), (string) $stringtoencode);
488  }
489 
495  public function errno()
496  {
497  if (!$this->connected) {
498  // Si il y a eu echec de connexion, $this->db n'est pas valide.
499  return 'DB_ERROR_FAILED_TO_CONNECT';
500  } else {
501  // Constants to convert a MySql error code to a generic Dolibarr error code
502  $errorcode_map = array(
503  1004 => 'DB_ERROR_CANNOT_CREATE',
504  1005 => 'DB_ERROR_CANNOT_CREATE',
505  1006 => 'DB_ERROR_CANNOT_CREATE',
506  1007 => 'DB_ERROR_ALREADY_EXISTS',
507  1008 => 'DB_ERROR_CANNOT_DROP',
508  1022 => 'DB_ERROR_KEY_NAME_ALREADY_EXISTS',
509  1025 => 'DB_ERROR_NO_FOREIGN_KEY_TO_DROP',
510  1044 => 'DB_ERROR_ACCESSDENIED',
511  1046 => 'DB_ERROR_NODBSELECTED',
512  1048 => 'DB_ERROR_CONSTRAINT',
513  1050 => 'DB_ERROR_TABLE_ALREADY_EXISTS',
514  1051 => 'DB_ERROR_NOSUCHTABLE',
515  1054 => 'DB_ERROR_NOSUCHFIELD',
516  1060 => 'DB_ERROR_COLUMN_ALREADY_EXISTS',
517  1061 => 'DB_ERROR_KEY_NAME_ALREADY_EXISTS',
518  1062 => 'DB_ERROR_RECORD_ALREADY_EXISTS',
519  1064 => 'DB_ERROR_SYNTAX',
520  1068 => 'DB_ERROR_PRIMARY_KEY_ALREADY_EXISTS',
521  1075 => 'DB_ERROR_CANT_DROP_PRIMARY_KEY',
522  1091 => 'DB_ERROR_NOSUCHFIELD',
523  1100 => 'DB_ERROR_NOT_LOCKED',
524  1136 => 'DB_ERROR_VALUE_COUNT_ON_ROW',
525  1146 => 'DB_ERROR_NOSUCHTABLE',
526  1215 => 'DB_ERROR_CANNOT_ADD_FOREIGN_KEY_CONSTRAINT',
527  1216 => 'DB_ERROR_NO_PARENT',
528  1217 => 'DB_ERROR_CHILD_EXISTS',
529  1396 => 'DB_ERROR_USER_ALREADY_EXISTS', // When creating a user that already existing
530  1451 => 'DB_ERROR_CHILD_EXISTS',
531  1826 => 'DB_ERROR_KEY_NAME_ALREADY_EXISTS'
532  );
533 
534  if (isset($errorcode_map[$this->db->errno])) {
535  return $errorcode_map[$this->db->errno];
536  }
537  $errno = $this->db->errno;
538  return ($errno ? 'DB_ERROR_'.$errno : '0');
539  }
540  }
541 
547  public function error()
548  {
549  if (!$this->connected) {
550  // Si il y a eu echec de connexion, $this->db n'est pas valide pour mysqli_error.
551  return 'Not connected. Check setup parameters in conf/conf.php file and your mysql client and server versions';
552  } else {
553  return $this->db->error;
554  }
555  }
556 
557  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
565  public function last_insert_id($tab, $fieldid = 'rowid')
566  {
567  // phpcs:enable
568  return $this->db->insert_id;
569  }
570 
579  public function encrypt($fieldorvalue, $withQuotes = 1)
580  {
581  global $conf;
582 
583  // Type of encryption (2: AES (recommended), 1: DES , 0: no encryption)
584  $cryptType = (!empty($conf->db->dolibarr_main_db_encryption) ? $conf->db->dolibarr_main_db_encryption : 0);
585 
586  //Encryption key
587  $cryptKey = (!empty($conf->db->dolibarr_main_db_cryptkey) ? $conf->db->dolibarr_main_db_cryptkey : '');
588 
589  $escapedstringwithquotes = ($withQuotes ? "'" : "").$this->escape($fieldorvalue).($withQuotes ? "'" : "");
590 
591  if ($cryptType && !empty($cryptKey)) {
592  if ($cryptType == 2) {
593  $escapedstringwithquotes = "AES_ENCRYPT(".$escapedstringwithquotes.", '".$this->escape($cryptKey)."')";
594  } elseif ($cryptType == 1) {
595  $escapedstringwithquotes = "DES_ENCRYPT(".$escapedstringwithquotes.", '".$this->escape($cryptKey)."')";
596  }
597  }
598 
599  return $escapedstringwithquotes;
600  }
601 
608  public function decrypt($value)
609  {
610  global $conf;
611 
612  // Type of encryption (2: AES (recommended), 1: DES , 0: no encryption)
613  $cryptType = (!empty($conf->db->dolibarr_main_db_encryption) ? $conf->db->dolibarr_main_db_encryption : 0);
614 
615  //Encryption key
616  $cryptKey = (!empty($conf->db->dolibarr_main_db_cryptkey) ? $conf->db->dolibarr_main_db_cryptkey : '');
617 
618  $return = $value;
619 
620  if ($cryptType && !empty($cryptKey)) {
621  if ($cryptType == 2) {
622  $return = 'AES_DECRYPT('.$value.',\''.$cryptKey.'\')';
623  } elseif ($cryptType == 1) {
624  $return = 'DES_DECRYPT('.$value.',\''.$cryptKey.'\')';
625  }
626  }
627 
628  return $return;
629  }
630 
631 
632  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
638  public function DDLGetConnectId()
639  {
640  // phpcs:enable
641  $resql = $this->query('SELECT CONNECTION_ID()');
642  if ($resql) {
643  $row = $this->fetch_row($resql);
644  return $row[0];
645  } else {
646  return '?';
647  }
648  }
649 
650  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
662  public function DDLCreateDb($database, $charset = '', $collation = '', $owner = '')
663  {
664  // phpcs:enable
665  if (empty($charset)) {
666  $charset = $this->forcecharset;
667  }
668  if (empty($collation)) {
669  $collation = $this->forcecollate;
670  }
671 
672  // ALTER DATABASE dolibarr_db DEFAULT CHARACTER SET latin DEFAULT COLLATE latin1_swedish_ci
673  $sql = "CREATE DATABASE `".$this->escape($database)."`";
674  $sql .= " DEFAULT CHARACTER SET `".$this->escape($charset)."` DEFAULT COLLATE `".$this->escape($collation)."`";
675 
676  dol_syslog($sql, LOG_DEBUG);
677  $ret = $this->query($sql);
678  if (!$ret) {
679  // We try again for compatibility with Mysql < 4.1.1
680  $sql = "CREATE DATABASE `".$this->escape($database)."`";
681  dol_syslog($sql, LOG_DEBUG);
682  $ret = $this->query($sql);
683  }
684  return $ret;
685  }
686 
687  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
695  public function DDLListTables($database, $table = '')
696  {
697  // phpcs:enable
698  $listtables = array();
699 
700  $like = '';
701  if ($table) {
702  $tmptable = preg_replace('/[^a-z0-9\.\-\_%]/i', '', $table);
703 
704  $like = "LIKE '".$this->escape($tmptable)."'";
705  }
706  $tmpdatabase = preg_replace('/[^a-z0-9\.\-\_]/i', '', $database);
707 
708  $sql = "SHOW TABLES FROM `".$tmpdatabase."` ".$like.";";
709  //print $sql;
710  $result = $this->query($sql);
711  if ($result) {
712  while ($row = $this->fetch_row($result)) {
713  $listtables[] = $row[0];
714  }
715  }
716  return $listtables;
717  }
718 
719  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
726  public function DDLInfoTable($table)
727  {
728  // phpcs:enable
729  $infotables = array();
730 
731  $tmptable = preg_replace('/[^a-z0-9\.\-\_]/i', '', $table);
732 
733  $sql = "SHOW FULL COLUMNS FROM ".$tmptable.";";
734 
735  dol_syslog($sql, LOG_DEBUG);
736  $result = $this->query($sql);
737  if ($result) {
738  while ($row = $this->fetch_row($result)) {
739  $infotables[] = $row;
740  }
741  }
742  return $infotables;
743  }
744 
745  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
758  public function DDLCreateTable($table, $fields, $primary_key, $type, $unique_keys = null, $fulltext_keys = null, $keys = null)
759  {
760  // phpcs:enable
761  // FIXME: $fulltext_keys parameter is unused
762 
763  $pk = '';
764  $sqluq = $sqlk = array();
765 
766  // cles recherchees dans le tableau des descriptions (fields) : type,value,attribute,null,default,extra
767  // ex. : $fields['rowid'] = array('type'=>'int','value'=>'11','null'=>'not null','extra'=> 'auto_increment');
768  $sql = "CREATE TABLE ".$table."(";
769  $i = 0;
770  $sqlfields = array();
771  foreach ($fields as $field_name => $field_desc) {
772  $sqlfields[$i] = $field_name." ";
773  $sqlfields[$i] .= $field_desc['type'];
774  if (preg_match("/^[^\s]/i", $field_desc['value'])) {
775  $sqlfields[$i] .= "(".$field_desc['value'].")";
776  }
777  if (preg_match("/^[^\s]/i", $field_desc['attribute'])) {
778  $sqlfields[$i] .= " ".$field_desc['attribute'];
779  }
780  if (preg_match("/^[^\s]/i", $field_desc['default'])) {
781  if ((preg_match("/null/i", $field_desc['default'])) || (preg_match("/CURRENT_TIMESTAMP/i", $field_desc['default']))) {
782  $sqlfields[$i] .= " default ".$field_desc['default'];
783  } else {
784  $sqlfields[$i] .= " default '".$this->escape($field_desc['default'])."'";
785  }
786  }
787  if (preg_match("/^[^\s]/i", $field_desc['null'])) {
788  $sqlfields[$i] .= " ".$field_desc['null'];
789  }
790  if (preg_match("/^[^\s]/i", $field_desc['extra'])) {
791  $sqlfields[$i] .= " ".$field_desc['extra'];
792  }
793  $i++;
794  }
795  if ($primary_key != "") {
796  $pk = "primary key(".$primary_key.")";
797  }
798 
799  if (is_array($unique_keys)) {
800  $i = 0;
801  foreach ($unique_keys as $key => $value) {
802  $sqluq[$i] = "UNIQUE KEY '".$key."' ('".$this->escape($value)."')";
803  $i++;
804  }
805  }
806  if (is_array($keys)) {
807  $i = 0;
808  foreach ($keys as $key => $value) {
809  $sqlk[$i] = "KEY ".$key." (".$value.")";
810  $i++;
811  }
812  }
813  $sql .= implode(',', $sqlfields);
814  if ($primary_key != "") {
815  $sql .= ",".$pk;
816  }
817  if ($unique_keys != "") {
818  $sql .= ",".implode(',', $sqluq);
819  }
820  if (is_array($keys)) {
821  $sql .= ",".implode(',', $sqlk);
822  }
823  $sql .= ") engine=".$type;
824 
825  if (!$this->query($sql)) {
826  return -1;
827  } else {
828  return 1;
829  }
830  }
831 
832  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
839  public function DDLDropTable($table)
840  {
841  // phpcs:enable
842  $tmptable = preg_replace('/[^a-z0-9\.\-\_]/i', '', $table);
843 
844  $sql = "DROP TABLE ".$tmptable;
845 
846  if (!$this->query($sql)) {
847  return -1;
848  } else {
849  return 1;
850  }
851  }
852 
853  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
861  public function DDLDescTable($table, $field = "")
862  {
863  // phpcs:enable
864  $sql = "DESC ".$table." ".$field;
865 
866  dol_syslog(get_class($this)."::DDLDescTable ".$sql, LOG_DEBUG);
867  $this->_results = $this->query($sql);
868  return $this->_results;
869  }
870 
871  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
881  public function DDLAddField($table, $field_name, $field_desc, $field_position = "")
882  {
883  // phpcs:enable
884  // cles recherchees dans le tableau des descriptions (field_desc) : type,value,attribute,null,default,extra
885  // ex. : $field_desc = array('type'=>'int','value'=>'11','null'=>'not null','extra'=> 'auto_increment');
886  $sql = "ALTER TABLE ".$table." ADD ".$field_name." ";
887  $sql .= $field_desc['type'];
888  if (preg_match("/^[^\s]/i", $field_desc['value'])) {
889  if (!in_array($field_desc['type'], array('date', 'datetime')) && $field_desc['value']) {
890  $sql .= "(".$field_desc['value'].")";
891  }
892  }
893  if (isset($field_desc['attribute']) && preg_match("/^[^\s]/i", $field_desc['attribute'])) {
894  $sql .= " ".$field_desc['attribute'];
895  }
896  if (isset($field_desc['null']) && preg_match("/^[^\s]/i", $field_desc['null'])) {
897  $sql .= " ".$field_desc['null'];
898  }
899  if (isset($field_desc['default']) && preg_match("/^[^\s]/i", $field_desc['default'])) {
900  if (preg_match("/null/i", $field_desc['default'])) {
901  $sql .= " default ".$field_desc['default'];
902  } else {
903  $sql .= " default '".$this->escape($field_desc['default'])."'";
904  }
905  }
906  if (isset($field_desc['extra']) && preg_match("/^[^\s]/i", $field_desc['extra'])) {
907  $sql .= " ".$field_desc['extra'];
908  }
909  $sql .= " ".$field_position;
910 
911  dol_syslog(get_class($this)."::DDLAddField ".$sql, LOG_DEBUG);
912  if ($this->query($sql)) {
913  return 1;
914  }
915  return -1;
916  }
917 
918  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
927  public function DDLUpdateField($table, $field_name, $field_desc)
928  {
929  // phpcs:enable
930  $sql = "ALTER TABLE ".$table;
931  $sql .= " MODIFY COLUMN ".$field_name." ".$field_desc['type'];
932  if (in_array($field_desc['type'], array('double', 'tinyint', 'int', 'varchar')) && $field_desc['value']) {
933  $sql .= "(".$field_desc['value'].")";
934  }
935  if ($field_desc['null'] == 'not null' || $field_desc['null'] == 'NOT NULL') {
936  // 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
937  if ($field_desc['type'] == 'varchar' || $field_desc['type'] == 'text') {
938  $sqlbis = "UPDATE ".$table." SET ".$field_name." = '".$this->escape($field_desc['default'] ? $field_desc['default'] : '')."' WHERE ".$field_name." IS NULL";
939  $this->query($sqlbis);
940  } elseif ($field_desc['type'] == 'tinyint' || $field_desc['type'] == 'int') {
941  $sqlbis = "UPDATE ".$table." SET ".$field_name." = ".((int) $this->escape($field_desc['default'] ? $field_desc['default'] : 0))." WHERE ".$field_name." IS NULL";
942  $this->query($sqlbis);
943  }
944 
945  $sql .= " NOT NULL";
946  }
947 
948  if ($field_desc['default'] != '') {
949  if ($field_desc['type'] == 'double' || $field_desc['type'] == 'tinyint' || $field_desc['type'] == 'int') {
950  $sql .= " DEFAULT ".$this->escape($field_desc['default']);
951  } elseif ($field_desc['type'] != 'text') {
952  $sql .= " DEFAULT '".$this->escape($field_desc['default'])."'"; // Default not supported on text fields
953  }
954  }
955 
956  dol_syslog(get_class($this)."::DDLUpdateField ".$sql, LOG_DEBUG);
957  if (!$this->query($sql)) {
958  return -1;
959  } else {
960  return 1;
961  }
962  }
963 
964  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
972  public function DDLDropField($table, $field_name)
973  {
974  // phpcs:enable
975  $tmp_field_name = preg_replace('/[^a-z0-9\.\-\_]/i', '', $field_name);
976 
977  $sql = "ALTER TABLE ".$table." DROP COLUMN `".$tmp_field_name."`";
978  if ($this->query($sql)) {
979  return 1;
980  }
981  $this->error = $this->lasterror();
982  return -1;
983  }
984 
985 
986  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
996  public function DDLCreateUser($dolibarr_main_db_host, $dolibarr_main_db_user, $dolibarr_main_db_pass, $dolibarr_main_db_name)
997  {
998  // phpcs:enable
999  $sql = "CREATE USER '".$this->escape($dolibarr_main_db_user)."' IDENTIFIED BY '".$this->escape($dolibarr_main_db_pass)."'";
1000  dol_syslog(get_class($this)."::DDLCreateUser", LOG_DEBUG); // No sql to avoid password in log
1001  $resql = $this->query($sql);
1002  if (!$resql) {
1003  if ($this->lasterrno != 'DB_ERROR_USER_ALREADY_EXISTS') {
1004  return -1;
1005  } else {
1006  // If user already exists, we continue to set permissions
1007  dol_syslog(get_class($this)."::DDLCreateUser sql=".$sql, LOG_WARNING);
1008  }
1009  }
1010 
1011  // Redo with localhost forced (sometimes user is created on %)
1012  $sql = "CREATE USER '".$this->escape($dolibarr_main_db_user)."'@'localhost' IDENTIFIED BY '".$this->escape($dolibarr_main_db_pass)."'";
1013  $resql = $this->query($sql);
1014 
1015  $sql = "GRANT ALL PRIVILEGES ON ".$this->escape($dolibarr_main_db_name).".* TO '".$this->escape($dolibarr_main_db_user)."'@'".$this->escape($dolibarr_main_db_host)."'";
1016  dol_syslog(get_class($this)."::DDLCreateUser", LOG_DEBUG); // No sql to avoid password in log
1017  $resql = $this->query($sql);
1018  if (!$resql) {
1019  $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)."'";
1020  return -1;
1021  }
1022 
1023  $sql = "FLUSH Privileges";
1024 
1025  dol_syslog(get_class($this)."::DDLCreateUser", LOG_DEBUG);
1026  $resql = $this->query($sql);
1027  if (!$resql) {
1028  return -1;
1029  }
1030 
1031  return 1;
1032  }
1033 
1041  public function getDefaultCharacterSetDatabase()
1042  {
1043  $resql = $this->query('SHOW VARIABLES LIKE \'character_set_database\'');
1044  if (!$resql) {
1045  // version Mysql < 4.1.1
1046  return $this->forcecharset;
1047  }
1048  $liste = $this->fetch_array($resql);
1049  $tmpval = $liste['Value'];
1050 
1051  return $tmpval;
1052  }
1053 
1059  public function getListOfCharacterSet()
1060  {
1061  $resql = $this->query('SHOW CHARSET');
1062  $liste = array();
1063  if ($resql) {
1064  $i = 0;
1065  while ($obj = $this->fetch_object($resql)) {
1066  $liste[$i]['charset'] = $obj->Charset;
1067  $liste[$i]['description'] = $obj->Description;
1068  $i++;
1069  }
1070  $this->free($resql);
1071  } else {
1072  // version Mysql < 4.1.1
1073  return null;
1074  }
1075  return $liste;
1076  }
1077 
1085  {
1086  $resql = $this->query('SHOW VARIABLES LIKE \'collation_database\'');
1087  if (!$resql) {
1088  // version Mysql < 4.1.1
1089  return $this->forcecollate;
1090  }
1091  $liste = $this->fetch_array($resql);
1092  $tmpval = $liste['Value'];
1093 
1094  return $tmpval;
1095  }
1096 
1102  public function getListOfCollation()
1103  {
1104  $resql = $this->query('SHOW COLLATION');
1105  $liste = array();
1106  if ($resql) {
1107  $i = 0;
1108  while ($obj = $this->fetch_object($resql)) {
1109  $liste[$i]['collation'] = $obj->Collation;
1110  $i++;
1111  }
1112  $this->free($resql);
1113  } else {
1114  // version Mysql < 4.1.1
1115  return null;
1116  }
1117  return $liste;
1118  }
1119 
1125  public function getPathOfDump()
1126  {
1127  $fullpathofdump = '/pathtomysqldump/mysqldump';
1128 
1129  $resql = $this->query('SHOW VARIABLES LIKE \'basedir\'');
1130  if ($resql) {
1131  $liste = $this->fetch_array($resql);
1132  $basedir = $liste['Value'];
1133  $fullpathofdump = $basedir.(preg_match('/\/$/', $basedir) ? '' : '/').'bin/mysqldump';
1134  }
1135  return $fullpathofdump;
1136  }
1137 
1143  public function getPathOfRestore()
1144  {
1145  $fullpathofimport = '/pathtomysql/mysql';
1146 
1147  $resql = $this->query('SHOW VARIABLES LIKE \'basedir\'');
1148  if ($resql) {
1149  $liste = $this->fetch_array($resql);
1150  $basedir = $liste['Value'];
1151  $fullpathofimport = $basedir.(preg_match('/\/$/', $basedir) ? '' : '/').'bin/mysql';
1152  }
1153  return $fullpathofimport;
1154  }
1155 
1162  public function getServerParametersValues($filter = '')
1163  {
1164  $result = array();
1165 
1166  $sql = 'SHOW VARIABLES';
1167  if ($filter) {
1168  $sql .= " LIKE '".$this->escape($filter)."'";
1169  }
1170  $resql = $this->query($sql);
1171  if ($resql) {
1172  while ($obj = $this->fetch_object($resql)) {
1173  $result[$obj->Variable_name] = $obj->Value;
1174  }
1175  }
1176 
1177  return $result;
1178  }
1179 
1186  public function getServerStatusValues($filter = '')
1187  {
1188  $result = array();
1189 
1190  $sql = 'SHOW STATUS';
1191  if ($filter) {
1192  $sql .= " LIKE '".$this->escape($filter)."'";
1193  }
1194  $resql = $this->query($sql);
1195  if ($resql) {
1196  while ($obj = $this->fetch_object($resql)) {
1197  $result[$obj->Variable_name] = $obj->Value;
1198  }
1199  }
1200 
1201  return $result;
1202  }
1203 }
DoliDBMysqli\connect
connect($host, $login, $passwd, $name, $port=0)
Connect to server.
Definition: mysqli.class.php:230
DoliDBMysqli\getDriverInfo
getDriverInfo()
Return version of database client driver.
Definition: mysqli.class.php:263
DoliDBMysqli\fetch_object
fetch_object($resultset)
Returns the current line (as an object) for the resultset cursor.
Definition: mysqli.class.php:361
DoliDBMysqli\convertSQLFromMysql
static convertSQLFromMysql($line, $type='ddl')
Convert a SQL request in Mysql syntax to native syntax.
Definition: mysqli.class.php:192
db
$conf db
API class for accounts.
Definition: inc.php:41
DoliDB\lasterror
lasterror()
Return last error label.
Definition: DoliDB.class.php:329
DoliDB
Class to manage Dolibarr database access.
Definition: DoliDB.class.php:30
DoliDBMysqli\close
close()
Close database connexion.
Definition: mysqli.class.php:275
$sql
if(isModEnabled('facture') &&!empty($user->rights->facture->lire)) if((isModEnabled('fournisseur') &&empty($conf->global->MAIN_USE_NEW_SUPPLIERMOD) && $user->hasRight("fournisseur", "facture", "lire"))||(isModEnabled('supplier_invoice') && $user->hasRight("supplier_invoice", "lire"))) if(isModEnabled('don') &&!empty($user->rights->don->lire)) if(isModEnabled('tax') &&!empty($user->rights->tax->charges->lire)) if(isModEnabled('facture') &&isModEnabled('commande') && $user->hasRight("commande", "lire") &&empty($conf->global->WORKFLOW_DISABLE_CREATE_INVOICE_FROM_ORDER)) $sql
Social contributions to pay.
Definition: index.php:745
DoliDBMysqli\num_rows
num_rows($resultset)
Return number of lines for result of a SELECT.
Definition: mysqli.class.php:419
DoliDBMysqli\decrypt
decrypt($value)
Decrypt sensitive data in database.
Definition: mysqli.class.php:608
DoliDBMysqli\fetch_array
fetch_array($resultset)
Return datas as an array.
Definition: mysqli.class.php:379
DoliDBMysqli\getDefaultCollationDatabase
getDefaultCollationDatabase()
Return collation used in current database.
Definition: mysqli.class.php:1084
DoliDBMysqli\getVersion
getVersion()
Return version of database server.
Definition: mysqli.class.php:253
rowid
print *****$script_file(".$version.") pid c cd cd cd description as p label as s rowid
Definition: email_expire_services_to_representatives.php:79
DoliDB\lastquery
lastquery()
Return last request executed with query()
Definition: DoliDB.class.php:274
DoliDBMysqli\__construct
__construct($type, $host, $user, $pass, $name='', $port=0)
Constructor.
Definition: mysqli.class.php:59
Exception
DoliDBMysqli\affected_rows
affected_rows($resultset)
Return the number of lines in the result of a request INSERT, DELETE or UPDATE.
Definition: mysqli.class.php:437
DoliDBMysqli\free
free($resultset=null)
Libere le dernier resultset utilise sur cette connexion.
Definition: mysqli.class.php:456
DoliDBMysqli\getPathOfRestore
getPathOfRestore()
Return full path of restore program.
Definition: mysqli.class.php:1143
DoliDBMysqli\getListOfCollation
getListOfCollation()
Return list of available collation that can be used for database.
Definition: mysqli.class.php:1102
DoliDBMysqli\$type
$type
Database type.
Definition: mysqli.class.php:38
DoliDBMysqli\getServerStatusValues
getServerStatusValues($filter='')
Return value of server status (current indicators on memory, cache...)
Definition: mysqli.class.php:1186
DoliDB\lasterrno
lasterrno()
Return last error code.
Definition: DoliDB.class.php:151
dol_syslog
dol_syslog($message, $level=LOG_INFO, $ident=0, $suffixinfilename='', $restricttologhandler='', $logcontext=null)
Write log message into outputs.
Definition: functions.lib.php:1639
DoliDBMysqli\escape
escape($stringtoencode)
Escape a string to insert data.
Definition: mysqli.class.php:474
DoliDBMysqli
Class to manage Dolibarr database access for a MySQL database using the MySQLi extension.
Definition: mysqli.class.php:33
DoliDBMysqli\select_db
select_db($database)
Select a database.
Definition: mysqli.class.php:205
DoliDBMysqli\getListOfCharacterSet
getListOfCharacterSet()
Return list of available charset that can be used to store data in database.
Definition: mysqli.class.php:1059
DoliDBMysqli\getPathOfDump
getPathOfDump()
Return full path of dump program.
Definition: mysqli.class.php:1125
DoliDBMysqli\escapeforlike
escapeforlike($stringtoencode)
Escape a string to insert data into a like.
Definition: mysqli.class.php:485
DoliDBMysqli\query
query($query, $usesavepoint=0, $type='auto', $result_mode=0)
Execute a SQL request and return the resultset.
Definition: mysqli.class.php:299
DoliDBMysqli\fetch_row
fetch_row($resultset)
Return datas as an array.
Definition: mysqli.class.php:396
DoliDB\lastqueryerror
lastqueryerror()
Return last query in error.
Definition: DoliDB.class.php:360
DoliDBMysqli\encrypt
encrypt($fieldorvalue, $withQuotes=1)
Encrypt sensitive data in database Warning: This function includes the escape and add the SQL simple ...
Definition: mysqli.class.php:579
DoliDBMysqli\LABEL
const LABEL
Database label.
Definition: mysqli.class.php:41
DoliDBMysqli\getServerParametersValues
getServerParametersValues($filter='')
Return value of server parameters.
Definition: mysqli.class.php:1162
DoliDBMysqli\VERSIONMIN
const VERSIONMIN
Version min database.
Definition: mysqli.class.php:43
DoliDBMysqli\error
error()
Return description of last error.
Definition: mysqli.class.php:547
DoliDBMysqli\hintindex
hintindex($nameofindex)
Return SQL string to force an index.
Definition: mysqli.class.php:179
DoliDBMysqli\last_insert_id
last_insert_id($tab, $fieldid='rowid')
Get last ID after an insert INSERT.
Definition: mysqli.class.php:565
type
if(preg_match('/crypted:/i', $dolibarr_main_db_pass)||!empty($dolibarr_main_db_encrypted_pass)) $conf db type
Definition: repair.php:119
DoliDBMysqli\errno
errno()
Return generic error code of last operation.
Definition: mysqli.class.php:495