dolibarr  17.0.4
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';
40  const LABEL = 'MySQL or MariaDB';
42  const VERSIONMIN = '5.0.3';
44  private $_results;
45 
57  public function __construct($type, $host, $user, $pass, $name = '', $port = 0)
58  {
59  global $conf, $langs;
60 
61  // Note that having "static" property for "$forcecharset" and "$forcecollate" will make error here in strict mode, so they are not static
62  if (!empty($conf->db->character_set)) {
63  $this->forcecharset = $conf->db->character_set;
64  }
65  if (!empty($conf->db->dolibarr_main_db_collation)) {
66  $this->forcecollate = $conf->db->dolibarr_main_db_collation;
67  }
68 
69  $this->database_user = $user;
70  $this->database_host = $host;
71  $this->database_port = $port;
72 
73  $this->transaction_opened = 0;
74 
75  //print "Name DB: $host,$user,$pass,$name<br>";
76 
77  if (!class_exists('mysqli')) {
78  $this->connected = false;
79  $this->ok = false;
80  $this->error = "Mysqli PHP functions for using Mysqli driver are not available in this version of PHP. Try to use another driver.";
81  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);
82  }
83 
84  if (!$host) {
85  $this->connected = false;
86  $this->ok = false;
87  $this->error = $langs->trans("ErrorWrongHostParameter");
88  dol_syslog(get_class($this)."::DoliDBMysqli : Connect error, wrong host parameters", LOG_ERR);
89  }
90 
91  // Try server connection
92  // We do not try to connect to database, only to server. Connect to database is done later in constrcutor
93  $this->db = $this->connect($host, $user, $pass, '', $port);
94 
95  if ($this->db && empty($this->db->connect_errno)) {
96  $this->connected = true;
97  $this->ok = true;
98  } else {
99  $this->connected = false;
100  $this->ok = false;
101  $this->error = empty($this->db) ? 'Failed to connect' : $this->db->connect_error;
102  dol_syslog(get_class($this)."::DoliDBMysqli Connect error: ".$this->error, LOG_ERR);
103  }
104 
105  // If server connection is ok, we try to connect to the database
106  if ($this->connected && $name) {
107  if ($this->select_db($name)) {
108  $this->database_selected = true;
109  $this->database_name = $name;
110  $this->ok = true;
111 
112  // If client is old latin, we force utf8
113  $clientmustbe = empty($conf->db->dolibarr_main_db_character_set) ? 'utf8' : $conf->db->dolibarr_main_db_character_set;
114  if (preg_match('/latin1/', $clientmustbe)) {
115  $clientmustbe = 'utf8';
116  }
117 
118  if ($this->db->character_set_name() != $clientmustbe) {
119  $this->db->set_charset($clientmustbe); // This set charset, but with a bad collation
120 
121  $collation = $conf->db->dolibarr_main_db_collation;
122  if (preg_match('/latin1/', $collation)) {
123  $collation = 'utf8_unicode_ci';
124  }
125 
126  if (!preg_match('/general/', $collation)) {
127  $this->db->query("SET collation_connection = ".$collation);
128  }
129  }
130  } else {
131  $this->database_selected = false;
132  $this->database_name = '';
133  $this->ok = false;
134  $this->error = $this->error();
135  dol_syslog(get_class($this)."::DoliDBMysqli : Select_db error ".$this->error, LOG_ERR);
136  }
137  } else {
138  // Pas de selection de base demandee, ok ou ko
139  $this->database_selected = false;
140 
141  if ($this->connected) {
142  // If client is old latin, we force utf8
143  $clientmustbe = empty($conf->db->dolibarr_main_db_character_set) ? 'utf8' : $conf->db->dolibarr_main_db_character_set;
144  if (preg_match('/latin1/', $clientmustbe)) {
145  $clientmustbe = 'utf8';
146  }
147  if (preg_match('/utf8mb4/', $clientmustbe)) {
148  $clientmustbe = 'utf8';
149  }
150 
151  if ($this->db->character_set_name() != $clientmustbe) {
152  $this->db->set_charset($clientmustbe); // This set utf8_unicode_ci
153 
154  $collation = $conf->db->dolibarr_main_db_collation;
155  if (preg_match('/latin1/', $collation)) {
156  $collation = 'utf8_unicode_ci';
157  }
158  if (preg_match('/utf8mb4/', $collation)) {
159  $collation = 'utf8_unicode_ci';
160  }
161 
162  if (!preg_match('/general/', $collation)) {
163  $this->db->query("SET collation_connection = ".$collation);
164  }
165  }
166  }
167  }
168  }
169 
170 
177  public function hintindex($nameofindex)
178  {
179  return " FORCE INDEX(".preg_replace('/[^a-z0-9_]/', '', $nameofindex).")";
180  }
181 
182 
190  public static function convertSQLFromMysql($line, $type = 'ddl')
191  {
192  return $line;
193  }
194 
195 
196  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
203  public function select_db($database)
204  {
205  // phpcs:enable
206  dol_syslog(get_class($this)."::select_db database=".$database, LOG_DEBUG);
207  $result = false;
208  try {
209  $result = $this->db->select_db($database);
210  } catch (Exception $e) {
211  // Nothing done on error
212  }
213  return $result;
214  }
215 
216 
228  public function connect($host, $login, $passwd, $name, $port = 0)
229  {
230  dol_syslog(get_class($this)."::connect host=$host, port=$port, login=$login, passwd=--hidden--, name=$name", LOG_DEBUG);
231 
232  //mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
233 
234  // Can also be
235  // mysqli::init(); mysql::options(MYSQLI_INIT_COMMAND, 'SET AUTOCOMMIT = 0'); mysqli::options(MYSQLI_OPT_CONNECT_TIMEOUT, 5);
236  // return mysqli::real_connect($host, $user, $pass, $db, $port);
237  $tmp = false;
238  try {
239  $tmp = new mysqli($host, $login, $passwd, $name, $port);
240  } catch (Exception $e) {
241  dol_syslog(get_class($this)."::connect failed", LOG_DEBUG);
242  }
243  return $tmp;
244  }
245 
251  public function getVersion()
252  {
253  return $this->db->server_info;
254  }
255 
261  public function getDriverInfo()
262  {
263  return $this->db->client_info;
264  }
265 
266 
273  public function close()
274  {
275  if ($this->db) {
276  if ($this->transaction_opened > 0) {
277  dol_syslog(get_class($this)."::close Closing a connection with an opened transaction depth=".$this->transaction_opened, LOG_ERR);
278  }
279  $this->connected = false;
280  return $this->db->close();
281  }
282  return false;
283  }
284 
285 
286 
297  public function query($query, $usesavepoint = 0, $type = 'auto', $result_mode = 0)
298  {
299  global $conf, $dolibarr_main_db_readonly;
300 
301  $query = trim($query);
302 
303  if (!in_array($query, array('BEGIN', 'COMMIT', 'ROLLBACK'))) {
304  $SYSLOG_SQL_LIMIT = 10000; // limit log to 10kb per line to limit DOS attacks
305  dol_syslog('sql='.substr($query, 0, $SYSLOG_SQL_LIMIT), LOG_DEBUG);
306  }
307  if (empty($query)) {
308  return false; // Return false = error if empty request
309  }
310 
311  if (!empty($dolibarr_main_db_readonly)) {
312  if (preg_match('/^(INSERT|UPDATE|REPLACE|DELETE|CREATE|ALTER|TRUNCATE|DROP)/i', $query)) {
313  $this->lasterror = 'Application in read-only mode';
314  $this->lasterrno = 'APPREADONLY';
315  $this->lastquery = $query;
316  return false;
317  }
318  }
319 
320  try {
321  if (!$this->database_name) {
322  // Ordre SQL ne necessitant pas de connexion a une base (exemple: CREATE DATABASE)
323  $ret = $this->db->query($query, $result_mode);
324  } else {
325  $ret = $this->db->query($query, $result_mode);
326  }
327  } catch (Exception $e) {
328  dol_syslog(get_class($this)."::query Exception in query instead of returning an error: ".$e->getMessage(), LOG_ERR);
329  $ret = false;
330  }
331 
332  if (!preg_match("/^COMMIT/i", $query) && !preg_match("/^ROLLBACK/i", $query)) {
333  // Si requete utilisateur, on la sauvegarde ainsi que son resultset
334  if (!$ret) {
335  $this->lastqueryerror = $query;
336  $this->lasterror = $this->error();
337  $this->lasterrno = $this->errno();
338 
339  if (empty($conf->global->SYSLOG_LEVEL) || $conf->global->SYSLOG_LEVEL < LOG_DEBUG) {
340  dol_syslog(get_class($this)."::query SQL Error query: ".$query, LOG_ERR); // Log of request was not yet done previously
341  }
342  dol_syslog(get_class($this)."::query SQL Error message: ".$this->lasterrno." ".$this->lasterror, LOG_ERR);
343  //var_dump(debug_print_backtrace());
344  }
345  $this->lastquery = $query;
346  $this->_results = $ret;
347  }
348 
349  return $ret;
350  }
351 
352  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
359  public function fetch_object($resultset)
360  {
361  // phpcs:enable
362  // Si le resultset n'est pas fourni, on prend le dernier utilise sur cette connexion
363  if (!is_object($resultset)) {
364  $resultset = $this->_results;
365  }
366  return $resultset->fetch_object();
367  }
368 
369 
370  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
377  public function fetch_array($resultset)
378  {
379  // phpcs:enable
380  // If resultset not provided, we take the last used by connexion
381  if (!is_object($resultset)) {
382  $resultset = $this->_results;
383  }
384  return $resultset->fetch_array();
385  }
386 
387  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
394  public function fetch_row($resultset)
395  {
396  // phpcs:enable
397  // If resultset not provided, we take the last used by connexion
398  if (!is_bool($resultset)) {
399  if (!is_object($resultset)) {
400  $resultset = $this->_results;
401  }
402  return $resultset->fetch_row();
403  } else {
404  // si le curseur est un booleen on retourne la valeur 0
405  return 0;
406  }
407  }
408 
409  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
417  public function num_rows($resultset)
418  {
419  // phpcs:enable
420  // If resultset not provided, we take the last used by connexion
421  if (!is_object($resultset)) {
422  $resultset = $this->_results;
423  }
424  return isset($resultset->num_rows) ? $resultset->num_rows : 0;
425  }
426 
427  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
435  public function affected_rows($resultset)
436  {
437  // phpcs:enable
438  // If resultset not provided, we take the last used by connexion
439  if (!is_object($resultset)) {
440  $resultset = $this->_results;
441  }
442  // mysql necessite un link de base pour cette fonction contrairement
443  // a pqsql qui prend un resultset
444  return $this->db->affected_rows;
445  }
446 
447 
454  public function free($resultset = null)
455  {
456  // If resultset not provided, we take the last used by connexion
457  if (!is_object($resultset)) {
458  $resultset = $this->_results;
459  }
460  // Si resultset en est un, on libere la memoire
461  if (is_object($resultset)) {
462  $resultset->free_result();
463  }
464  }
465 
472  public function escape($stringtoencode)
473  {
474  return $this->db->real_escape_string((string) $stringtoencode);
475  }
476 
484  public function escapeunderscore($stringtoencode)
485  {
486  return str_replace('_', '\_', (string) $stringtoencode);
487  }
488 
495  public function escapeforlike($stringtoencode)
496  {
497  return str_replace(array('\\', '_', '%'), array('\\\\', '\_', '\%'), (string) $stringtoencode);
498  }
499 
505  public function errno()
506  {
507  if (!$this->connected) {
508  // Si il y a eu echec de connexion, $this->db n'est pas valide.
509  return 'DB_ERROR_FAILED_TO_CONNECT';
510  } else {
511  // Constants to convert a MySql error code to a generic Dolibarr error code
512  $errorcode_map = array(
513  1004 => 'DB_ERROR_CANNOT_CREATE',
514  1005 => 'DB_ERROR_CANNOT_CREATE',
515  1006 => 'DB_ERROR_CANNOT_CREATE',
516  1007 => 'DB_ERROR_ALREADY_EXISTS',
517  1008 => 'DB_ERROR_CANNOT_DROP',
518  1022 => 'DB_ERROR_KEY_NAME_ALREADY_EXISTS',
519  1025 => 'DB_ERROR_NO_FOREIGN_KEY_TO_DROP',
520  1044 => 'DB_ERROR_ACCESSDENIED',
521  1046 => 'DB_ERROR_NODBSELECTED',
522  1048 => 'DB_ERROR_CONSTRAINT',
523  1050 => 'DB_ERROR_TABLE_ALREADY_EXISTS',
524  1051 => 'DB_ERROR_NOSUCHTABLE',
525  1054 => 'DB_ERROR_NOSUCHFIELD',
526  1060 => 'DB_ERROR_COLUMN_ALREADY_EXISTS',
527  1061 => 'DB_ERROR_KEY_NAME_ALREADY_EXISTS',
528  1062 => 'DB_ERROR_RECORD_ALREADY_EXISTS',
529  1064 => 'DB_ERROR_SYNTAX',
530  1068 => 'DB_ERROR_PRIMARY_KEY_ALREADY_EXISTS',
531  1075 => 'DB_ERROR_CANT_DROP_PRIMARY_KEY',
532  1091 => 'DB_ERROR_NOSUCHFIELD',
533  1100 => 'DB_ERROR_NOT_LOCKED',
534  1136 => 'DB_ERROR_VALUE_COUNT_ON_ROW',
535  1146 => 'DB_ERROR_NOSUCHTABLE',
536  1215 => 'DB_ERROR_CANNOT_ADD_FOREIGN_KEY_CONSTRAINT',
537  1216 => 'DB_ERROR_NO_PARENT',
538  1217 => 'DB_ERROR_CHILD_EXISTS',
539  1396 => 'DB_ERROR_USER_ALREADY_EXISTS', // When creating a user that already existing
540  1451 => 'DB_ERROR_CHILD_EXISTS',
541  1826 => 'DB_ERROR_KEY_NAME_ALREADY_EXISTS'
542  );
543 
544  if (isset($errorcode_map[$this->db->errno])) {
545  return $errorcode_map[$this->db->errno];
546  }
547  $errno = $this->db->errno;
548  return ($errno ? 'DB_ERROR_'.$errno : '0');
549  }
550  }
551 
557  public function error()
558  {
559  if (!$this->connected) {
560  // Si il y a eu echec de connexion, $this->db n'est pas valide pour mysqli_error.
561  return 'Not connected. Check setup parameters in conf/conf.php file and your mysql client and server versions';
562  } else {
563  return $this->db->error;
564  }
565  }
566 
567  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
575  public function last_insert_id($tab, $fieldid = 'rowid')
576  {
577  // phpcs:enable
578  return $this->db->insert_id;
579  }
580 
589  public function encrypt($fieldorvalue, $withQuotes = 1)
590  {
591  global $conf;
592 
593  // Type of encryption (2: AES (recommended), 1: DES , 0: no encryption)
594  $cryptType = (!empty($conf->db->dolibarr_main_db_encryption) ? $conf->db->dolibarr_main_db_encryption : 0);
595 
596  //Encryption key
597  $cryptKey = (!empty($conf->db->dolibarr_main_db_cryptkey) ? $conf->db->dolibarr_main_db_cryptkey : '');
598 
599  $escapedstringwithquotes = ($withQuotes ? "'" : "").$this->escape($fieldorvalue).($withQuotes ? "'" : "");
600 
601  if ($cryptType && !empty($cryptKey)) {
602  if ($cryptType == 2) {
603  $escapedstringwithquotes = "AES_ENCRYPT(".$escapedstringwithquotes.", '".$this->escape($cryptKey)."')";
604  } elseif ($cryptType == 1) {
605  $escapedstringwithquotes = "DES_ENCRYPT(".$escapedstringwithquotes.", '".$this->escape($cryptKey)."')";
606  }
607  }
608 
609  return $escapedstringwithquotes;
610  }
611 
618  public function decrypt($value)
619  {
620  global $conf;
621 
622  // Type of encryption (2: AES (recommended), 1: DES , 0: no encryption)
623  $cryptType = (!empty($conf->db->dolibarr_main_db_encryption) ? $conf->db->dolibarr_main_db_encryption : 0);
624 
625  //Encryption key
626  $cryptKey = (!empty($conf->db->dolibarr_main_db_cryptkey) ? $conf->db->dolibarr_main_db_cryptkey : '');
627 
628  $return = $value;
629 
630  if ($cryptType && !empty($cryptKey)) {
631  if ($cryptType == 2) {
632  $return = 'AES_DECRYPT('.$value.',\''.$cryptKey.'\')';
633  } elseif ($cryptType == 1) {
634  $return = 'DES_DECRYPT('.$value.',\''.$cryptKey.'\')';
635  }
636  }
637 
638  return $return;
639  }
640 
641 
642  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
648  public function DDLGetConnectId()
649  {
650  // phpcs:enable
651  $resql = $this->query('SELECT CONNECTION_ID()');
652  if ($resql) {
653  $row = $this->fetch_row($resql);
654  return $row[0];
655  } else {
656  return '?';
657  }
658  }
659 
660  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
672  public function DDLCreateDb($database, $charset = '', $collation = '', $owner = '')
673  {
674  // phpcs:enable
675  if (empty($charset)) {
676  $charset = $this->forcecharset;
677  }
678  if (empty($collation)) {
679  $collation = $this->forcecollate;
680  }
681 
682  // ALTER DATABASE dolibarr_db DEFAULT CHARACTER SET latin DEFAULT COLLATE latin1_swedish_ci
683  $sql = "CREATE DATABASE `".$this->escape($database)."`";
684  $sql .= " DEFAULT CHARACTER SET `".$this->escape($charset)."` DEFAULT COLLATE `".$this->escape($collation)."`";
685 
686  dol_syslog($sql, LOG_DEBUG);
687  $ret = $this->query($sql);
688  if (!$ret) {
689  // We try again for compatibility with Mysql < 4.1.1
690  $sql = "CREATE DATABASE `".$this->escape($database)."`";
691  dol_syslog($sql, LOG_DEBUG);
692  $ret = $this->query($sql);
693  }
694  return $ret;
695  }
696 
697  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
705  public function DDLListTables($database, $table = '')
706  {
707  // phpcs:enable
708  $listtables = array();
709 
710  $like = '';
711  if ($table) {
712  $tmptable = preg_replace('/[^a-z0-9\.\-\_%]/i', '', $table);
713 
714  $like = "LIKE '".$this->escape($tmptable)."'";
715  }
716  $tmpdatabase = preg_replace('/[^a-z0-9\.\-\_]/i', '', $database);
717 
718  $sql = "SHOW TABLES FROM `".$tmpdatabase."` ".$like.";";
719  //print $sql;
720  $result = $this->query($sql);
721  if ($result) {
722  while ($row = $this->fetch_row($result)) {
723  $listtables[] = $row[0];
724  }
725  }
726  return $listtables;
727  }
728 
729  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
736  public function DDLInfoTable($table)
737  {
738  // phpcs:enable
739  $infotables = array();
740 
741  $tmptable = preg_replace('/[^a-z0-9\.\-\_]/i', '', $table);
742 
743  $sql = "SHOW FULL COLUMNS FROM ".$tmptable.";";
744 
745  dol_syslog($sql, LOG_DEBUG);
746  $result = $this->query($sql);
747  if ($result) {
748  while ($row = $this->fetch_row($result)) {
749  $infotables[] = $row;
750  }
751  }
752  return $infotables;
753  }
754 
755  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
768  public function DDLCreateTable($table, $fields, $primary_key, $type, $unique_keys = null, $fulltext_keys = null, $keys = null)
769  {
770  // phpcs:enable
771  // FIXME: $fulltext_keys parameter is unused
772 
773  $pk = '';
774  $sqluq = $sqlk = array();
775 
776  // cles recherchees dans le tableau des descriptions (fields) : type,value,attribute,null,default,extra
777  // ex. : $fields['rowid'] = array('type'=>'int','value'=>'11','null'=>'not null','extra'=> 'auto_increment');
778  $sql = "CREATE TABLE ".$table."(";
779  $i = 0;
780  $sqlfields = array();
781  foreach ($fields as $field_name => $field_desc) {
782  $sqlfields[$i] = $field_name." ";
783  $sqlfields[$i] .= $field_desc['type'];
784  if (preg_match("/^[^\s]/i", $field_desc['value'])) {
785  $sqlfields[$i] .= "(".$field_desc['value'].")";
786  }
787  if (preg_match("/^[^\s]/i", $field_desc['attribute'])) {
788  $sqlfields[$i] .= " ".$field_desc['attribute'];
789  }
790  if (preg_match("/^[^\s]/i", $field_desc['default'])) {
791  if ((preg_match("/null/i", $field_desc['default'])) || (preg_match("/CURRENT_TIMESTAMP/i", $field_desc['default']))) {
792  $sqlfields[$i] .= " default ".$field_desc['default'];
793  } else {
794  $sqlfields[$i] .= " default '".$this->escape($field_desc['default'])."'";
795  }
796  }
797  if (preg_match("/^[^\s]/i", $field_desc['null'])) {
798  $sqlfields[$i] .= " ".$field_desc['null'];
799  }
800  if (preg_match("/^[^\s]/i", $field_desc['extra'])) {
801  $sqlfields[$i] .= " ".$field_desc['extra'];
802  }
803  $i++;
804  }
805  if ($primary_key != "") {
806  $pk = "primary key(".$primary_key.")";
807  }
808 
809  if (is_array($unique_keys)) {
810  $i = 0;
811  foreach ($unique_keys as $key => $value) {
812  $sqluq[$i] = "UNIQUE KEY '".$key."' ('".$this->escape($value)."')";
813  $i++;
814  }
815  }
816  if (is_array($keys)) {
817  $i = 0;
818  foreach ($keys as $key => $value) {
819  $sqlk[$i] = "KEY ".$key." (".$value.")";
820  $i++;
821  }
822  }
823  $sql .= implode(',', $sqlfields);
824  if ($primary_key != "") {
825  $sql .= ",".$pk;
826  }
827  if ($unique_keys != "") {
828  $sql .= ",".implode(',', $sqluq);
829  }
830  if (is_array($keys)) {
831  $sql .= ",".implode(',', $sqlk);
832  }
833  $sql .= ") engine=".$type;
834 
835  if (!$this->query($sql)) {
836  return -1;
837  } else {
838  return 1;
839  }
840  }
841 
842  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
849  public function DDLDropTable($table)
850  {
851  // phpcs:enable
852  $tmptable = preg_replace('/[^a-z0-9\.\-\_]/i', '', $table);
853 
854  $sql = "DROP TABLE ".$tmptable;
855 
856  if (!$this->query($sql)) {
857  return -1;
858  } else {
859  return 1;
860  }
861  }
862 
863  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
871  public function DDLDescTable($table, $field = "")
872  {
873  // phpcs:enable
874  $sql = "DESC ".$table." ".$field;
875 
876  dol_syslog(get_class($this)."::DDLDescTable ".$sql, LOG_DEBUG);
877  $this->_results = $this->query($sql);
878  return $this->_results;
879  }
880 
881  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
891  public function DDLAddField($table, $field_name, $field_desc, $field_position = "")
892  {
893  // phpcs:enable
894  // cles recherchees dans le tableau des descriptions (field_desc) : type,value,attribute,null,default,extra
895  // ex. : $field_desc = array('type'=>'int','value'=>'11','null'=>'not null','extra'=> 'auto_increment');
896  $sql = "ALTER TABLE ".$table." ADD ".$field_name." ";
897  $sql .= $field_desc['type'];
898  if (preg_match("/^[^\s]/i", $field_desc['value'])) {
899  if (!in_array($field_desc['type'], array('date', 'datetime')) && $field_desc['value']) {
900  $sql .= "(".$field_desc['value'].")";
901  }
902  }
903  if (isset($field_desc['attribute']) && preg_match("/^[^\s]/i", $field_desc['attribute'])) {
904  $sql .= " ".$field_desc['attribute'];
905  }
906  if (isset($field_desc['null']) && preg_match("/^[^\s]/i", $field_desc['null'])) {
907  $sql .= " ".$field_desc['null'];
908  }
909  if (isset($field_desc['default']) && preg_match("/^[^\s]/i", $field_desc['default'])) {
910  if (preg_match("/null/i", $field_desc['default'])) {
911  $sql .= " default ".$field_desc['default'];
912  } else {
913  $sql .= " default '".$this->escape($field_desc['default'])."'";
914  }
915  }
916  if (isset($field_desc['extra']) && preg_match("/^[^\s]/i", $field_desc['extra'])) {
917  $sql .= " ".$field_desc['extra'];
918  }
919  $sql .= " ".$field_position;
920 
921  dol_syslog(get_class($this)."::DDLAddField ".$sql, LOG_DEBUG);
922  if ($this->query($sql)) {
923  return 1;
924  }
925  return -1;
926  }
927 
928  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
937  public function DDLUpdateField($table, $field_name, $field_desc)
938  {
939  // phpcs:enable
940  $sql = "ALTER TABLE ".$table;
941  $sql .= " MODIFY COLUMN ".$field_name." ".$field_desc['type'];
942  if (in_array($field_desc['type'], array('double', 'tinyint', 'int', 'varchar')) && $field_desc['value']) {
943  $sql .= "(".$field_desc['value'].")";
944  }
945  if ($field_desc['null'] == 'not null' || $field_desc['null'] == 'NOT NULL') {
946  // 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
947  if ($field_desc['type'] == 'varchar' || $field_desc['type'] == 'text') {
948  $sqlbis = "UPDATE ".$table." SET ".$field_name." = '".$this->escape($field_desc['default'] ? $field_desc['default'] : '')."' WHERE ".$field_name." IS NULL";
949  $this->query($sqlbis);
950  } elseif ($field_desc['type'] == 'tinyint' || $field_desc['type'] == 'int') {
951  $sqlbis = "UPDATE ".$table." SET ".$field_name." = ".((int) $this->escape($field_desc['default'] ? $field_desc['default'] : 0))." WHERE ".$field_name." IS NULL";
952  $this->query($sqlbis);
953  }
954 
955  $sql .= " NOT NULL";
956  }
957 
958  if ($field_desc['default'] != '') {
959  if ($field_desc['type'] == 'double' || $field_desc['type'] == 'tinyint' || $field_desc['type'] == 'int') {
960  $sql .= " DEFAULT ".$this->escape($field_desc['default']);
961  } elseif ($field_desc['type'] != 'text') {
962  $sql .= " DEFAULT '".$this->escape($field_desc['default'])."'"; // Default not supported on text fields
963  }
964  }
965 
966  dol_syslog(get_class($this)."::DDLUpdateField ".$sql, LOG_DEBUG);
967  if (!$this->query($sql)) {
968  return -1;
969  } else {
970  return 1;
971  }
972  }
973 
974  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
982  public function DDLDropField($table, $field_name)
983  {
984  // phpcs:enable
985  $tmp_field_name = preg_replace('/[^a-z0-9\.\-\_]/i', '', $field_name);
986 
987  $sql = "ALTER TABLE ".$table." DROP COLUMN `".$tmp_field_name."`";
988  if ($this->query($sql)) {
989  return 1;
990  }
991  $this->error = $this->lasterror();
992  return -1;
993  }
994 
995 
996  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
1006  public function DDLCreateUser($dolibarr_main_db_host, $dolibarr_main_db_user, $dolibarr_main_db_pass, $dolibarr_main_db_name)
1007  {
1008  // phpcs:enable
1009  $sql = "CREATE USER '".$this->escape($dolibarr_main_db_user)."' IDENTIFIED BY '".$this->escape($dolibarr_main_db_pass)."'";
1010  dol_syslog(get_class($this)."::DDLCreateUser", LOG_DEBUG); // No sql to avoid password in log
1011  $resql = $this->query($sql);
1012  if (!$resql) {
1013  if ($this->lasterrno != 'DB_ERROR_USER_ALREADY_EXISTS') {
1014  return -1;
1015  } else {
1016  // If user already exists, we continue to set permissions
1017  dol_syslog(get_class($this)."::DDLCreateUser sql=".$sql, LOG_WARNING);
1018  }
1019  }
1020 
1021  // Redo with localhost forced (sometimes user is created on %)
1022  $sql = "CREATE USER '".$this->escape($dolibarr_main_db_user)."'@'localhost' IDENTIFIED BY '".$this->escape($dolibarr_main_db_pass)."'";
1023  $resql = $this->query($sql);
1024 
1025  $sql = "GRANT ALL PRIVILEGES ON ".$this->escape($dolibarr_main_db_name).".* TO '".$this->escape($dolibarr_main_db_user)."'@'".$this->escape($dolibarr_main_db_host)."'";
1026  dol_syslog(get_class($this)."::DDLCreateUser", LOG_DEBUG); // No sql to avoid password in log
1027  $resql = $this->query($sql);
1028  if (!$resql) {
1029  $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)."'";
1030  return -1;
1031  }
1032 
1033  $sql = "FLUSH Privileges";
1034 
1035  dol_syslog(get_class($this)."::DDLCreateUser", LOG_DEBUG);
1036  $resql = $this->query($sql);
1037  if (!$resql) {
1038  return -1;
1039  }
1040 
1041  return 1;
1042  }
1043 
1051  public function getDefaultCharacterSetDatabase()
1052  {
1053  $resql = $this->query('SHOW VARIABLES LIKE \'character_set_database\'');
1054  if (!$resql) {
1055  // version Mysql < 4.1.1
1056  return $this->forcecharset;
1057  }
1058  $liste = $this->fetch_array($resql);
1059  $tmpval = $liste['Value'];
1060 
1061  return $tmpval;
1062  }
1063 
1069  public function getListOfCharacterSet()
1070  {
1071  $resql = $this->query('SHOW CHARSET');
1072  $liste = array();
1073  if ($resql) {
1074  $i = 0;
1075  while ($obj = $this->fetch_object($resql)) {
1076  $liste[$i]['charset'] = $obj->Charset;
1077  $liste[$i]['description'] = $obj->Description;
1078  $i++;
1079  }
1080  $this->free($resql);
1081  } else {
1082  // version Mysql < 4.1.1
1083  return null;
1084  }
1085  return $liste;
1086  }
1087 
1095  {
1096  $resql = $this->query('SHOW VARIABLES LIKE \'collation_database\'');
1097  if (!$resql) {
1098  // version Mysql < 4.1.1
1099  return $this->forcecollate;
1100  }
1101  $liste = $this->fetch_array($resql);
1102  $tmpval = $liste['Value'];
1103 
1104  return $tmpval;
1105  }
1106 
1112  public function getListOfCollation()
1113  {
1114  $resql = $this->query('SHOW COLLATION');
1115  $liste = array();
1116  if ($resql) {
1117  $i = 0;
1118  while ($obj = $this->fetch_object($resql)) {
1119  $liste[$i]['collation'] = $obj->Collation;
1120  $i++;
1121  }
1122  $this->free($resql);
1123  } else {
1124  // version Mysql < 4.1.1
1125  return null;
1126  }
1127  return $liste;
1128  }
1129 
1135  public function getPathOfDump()
1136  {
1137  $fullpathofdump = '/pathtomysqldump/mysqldump';
1138 
1139  $resql = $this->query('SHOW VARIABLES LIKE \'basedir\'');
1140  if ($resql) {
1141  $liste = $this->fetch_array($resql);
1142  $basedir = $liste['Value'];
1143  $fullpathofdump = $basedir.(preg_match('/\/$/', $basedir) ? '' : '/').'bin/mysqldump';
1144  }
1145  return $fullpathofdump;
1146  }
1147 
1153  public function getPathOfRestore()
1154  {
1155  $fullpathofimport = '/pathtomysql/mysql';
1156 
1157  $resql = $this->query('SHOW VARIABLES LIKE \'basedir\'');
1158  if ($resql) {
1159  $liste = $this->fetch_array($resql);
1160  $basedir = $liste['Value'];
1161  $fullpathofimport = $basedir.(preg_match('/\/$/', $basedir) ? '' : '/').'bin/mysql';
1162  }
1163  return $fullpathofimport;
1164  }
1165 
1172  public function getServerParametersValues($filter = '')
1173  {
1174  $result = array();
1175 
1176  $sql = 'SHOW VARIABLES';
1177  if ($filter) {
1178  $sql .= " LIKE '".$this->escape($filter)."'";
1179  }
1180  $resql = $this->query($sql);
1181  if ($resql) {
1182  while ($obj = $this->fetch_object($resql)) {
1183  $result[$obj->Variable_name] = $obj->Value;
1184  }
1185  }
1186 
1187  return $result;
1188  }
1189 
1196  public function getServerStatusValues($filter = '')
1197  {
1198  $result = array();
1199 
1200  $sql = 'SHOW STATUS';
1201  if ($filter) {
1202  $sql .= " LIKE '".$this->escape($filter)."'";
1203  }
1204  $resql = $this->query($sql);
1205  if ($resql) {
1206  while ($obj = $this->fetch_object($resql)) {
1207  $result[$obj->Variable_name] = $obj->Value;
1208  }
1209  }
1210 
1211  return $result;
1212  }
1213 }
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 connexion.
escapeforlike($stringtoencode)
Escape a string to insert data into a like.
getServerStatusValues($filter='')
Return value of server status (current indicators on memory, cache...)
num_rows($resultset)
Return number of lines for result of a SELECT.
getServerParametersValues($filter='')
Return value of server parameters.
const VERSIONMIN
Version min database.
$type
Database type.
error()
Return description of last error.
static convertSQLFromMysql($line, $type='ddl')
Convert a SQL request in Mysql syntax to native syntax.
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 ...
affected_rows($resultset)
Return the number of lines in the result of a request INSERT, DELETE or UPDATE.
getDefaultCollationDatabase()
Return collation used in current database.
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.
getPathOfRestore()
Return full path of restore program.
getPathOfDump()
Return full path of dump program.
escapeunderscore($stringtoencode)
Escape a string to insert data.
connect($host, $login, $passwd, $name, $port=0)
Connect to server.
errno()
Return generic error code of last operation.
getListOfCollation()
Return list of available collation that can be used for database.
getDriverInfo()
Return version of database client driver.
getListOfCharacterSet()
Return list of available charset that can be used to store data in database.
close()
Close database connexion.
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)) $resql
Social contributions to pay.
Definition: index.php:745
print *****$script_file(".$version.") pid c cd cd cd description as p label as s rowid
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:119
$conf db
API class for accounts.
Definition: inc.php:41