PEEL Shopping
Open source ecommerce : PEEL Shopping
database.php
Go to the documentation of this file.
1 <?php
2 // This file should be in UTF8 without BOM - Accents examples: éèê
3 // +----------------------------------------------------------------------+
4 // | Copyright (c) 2004-2015 Advisto SAS, service PEEL - contact@peel.fr |
5 // +----------------------------------------------------------------------+
6 // | This file is part of PEEL Shopping 8.0.0, which is subject to an |
7 // | opensource GPL license: you are allowed to customize the code |
8 // | for your own needs, but must keep your changes under GPL |
9 // | More information: https://www.peel.fr/lire/licence-gpl-70.html |
10 // +----------------------------------------------------------------------+
11 // | Author: Advisto SAS, RCS 479 205 452, France, https://www.peel.fr/ |
12 // +----------------------------------------------------------------------+
13 // $Id: database.php 46935 2015-09-18 08:49:48Z gboussin $
14 if (!defined('IN_PEEL')) {
15  die();
16 }
17 
18 // If you want to use a database server different from MySQL,
19 // you can change the following functions which are called from everywhere in PEEL
31 function db_connect(&$database_object, $database_name = null, $serveur_mysql = null, $utilisateur_mysql = null, $mot_de_passe_mysql = null, $continue_if_error = false)
32 {
33  // Connexion à la BDD
34  if(empty($serveur_mysql)) {
35  $serveur_mysql = $GLOBALS['serveur_mysql'];
36  }
37  if(empty($serveur_mysql) || $serveur_mysql=='votre_serveur_mysql') {
38  return null;
39  }
40  if(empty($utilisateur_mysql)) {
41  $utilisateur_mysql = $GLOBALS['utilisateur_mysql'];
42  }
43  if(empty($mot_de_passe_mysql)) {
44  $mot_de_passe_mysql = $GLOBALS['mot_de_passe_mysql'];
45  }
46  if(empty($database_name) && $database_name!==false) {
47  $database_name = $GLOBALS['nom_de_la_base'];
48  }
49  if($GLOBALS['site_parameters']['mysql_extension'] == 'mysqli') {
50  $port = @ini_get("mysqli.default_port");
51  if(empty($port)) {
52  // Port par défaut
53  $port = 3306;
54  }
55  $socket = @ini_get("mysqli.default_socket");
56  if($socket === false) {
57  // Socket par défaut
58  $socket = null;
59  }
60  // Gestion des connexions du type server:socket ou server:port
61  $server_infos = explode(':',$serveur_mysql);
62  if(isset($server_infos[1])) {
63  if(is_numeric($server_infos[1])){
64  $port = $server_infos[1];
65  } else {
66  $socket = $server_infos[1];
67  }
68  }
69  if(isset($GLOBALS['site_parameters']['use_database_permanent_connection']) && ($GLOBALS['site_parameters']['use_database_permanent_connection'] === true || ($GLOBALS['site_parameters']['use_database_permanent_connection'] == 'local' && (strpos($GLOBALS['wwwroot'], '://localhost')!==false || strpos($GLOBALS['wwwroot'], '://127.0.0.1')!==false)))) {
70  // L'utilisation de pconnect est souvent plus rapide, mais peut créer des problèmes divers
71  // Pour le travail en local sur un PC winbows, l'amélioration de performance peut être très grande
72  $database_object = new mysqli('p:'.$server_infos[0], $utilisateur_mysql, $mot_de_passe_mysql, '', $port, $socket);
73  } else {
74  $database_object = new mysqli($server_infos[0], $utilisateur_mysql, $mot_de_passe_mysql, '', $port, $socket);
75  }
76  if (mysqli_connect_error()) {
77  $error_no = mysqli_connect_errno();
78  $error_text = mysqli_connect_error();
79  }
80  } else {
81  $database_object = mysql_connect($serveur_mysql, $utilisateur_mysql, $mot_de_passe_mysql);
82  }
83  if(!empty($error_no) && !$continue_if_error) {
84  $sujet_du_mail = 'MySQL connection problem (' . mysqli_connect_errno() . '): '.mysqli_connect_error();
85  $contenu_du_mail = "The page " . $_SERVER['REQUEST_URI'] . " had an error while trying to connect to MySQL on " . $serveur_mysql . " - the user is " . $utilisateur_mysql . ". Please check if MySQL is currently launched and if the connection parameters are valid.";
86  $contenu_du_mail .= "\n\nLa page " . $_SERVER['REQUEST_URI'] . " a provoqué une erreur lors de sa tentative de connexion à MySQL situé sur le serveur " . $serveur_mysql . " - l'utilisateur est " . $utilisateur_mysql . ". Il faudrait vérifier si le serveur MySQL est actuellement lancé et si les paramètres de connexion sont valides.";
87  if (!empty($GLOBALS['support'])) {
88  send_email($GLOBALS['support'], $sujet_du_mail, $contenu_du_mail, null, null, null, '', null);
89  }
90  if (!empty($GLOBALS['site_parameters']['display_warning_if_connection_problem'])) {
91  echo $sujet_du_mail;
92  }
93  trigger_error($serveur_mysql. ' - ' .$sujet_du_mail, E_USER_NOTICE);
94  die();
95  }
96  if(!empty($database_name)) {
97  $GLOBALS['selection_de_la_base'] = select_db($database_name, $database_object, $continue_if_error);
98  }
99  return $database_object;
100 }
101 
110 function select_db($database_name, &$database_object, $continue_if_error = false)
111 {
112  if($GLOBALS['site_parameters']['mysql_extension'] == 'mysqli') {
113  $GLOBALS['selection_de_la_base'] = $database_object->select_db($database_name);
114  } else {
115  $GLOBALS['selection_de_la_base'] = mysql_select_db($database_name, $database_object);
116  }
117  if (!$GLOBALS['selection_de_la_base'] && !$continue_if_error) {
118  if(is_object($database_object) && !empty($database_object->error)) {
119  $contenu_display = $database_object->error;
120  } else {
121  $contenu_display = 'MySQL database selection problem: ' . $database_name;
122  }
123  $sujet_du_mail = "Database selection problem";
124  $contenu_du_mail = "The page " . $_SERVER['REQUEST_URI'] . " had an error while trying to connect to MySQL database - " . $contenu_display;
125  if (!empty($GLOBALS['support'])) {
126  send_email($GLOBALS['support'], $sujet_du_mail, $contenu_du_mail, null, null, null, '', null);
127  }
128  if (!empty($display_warning_if_database_object_problem)) {
129  echo $contenu_display;
130  }
131  trigger_error($contenu_display, E_USER_ERROR);
132  // Le script s'arrête sur une fatal error
133  }
134  // Définition des paramètres de connexion à MySQL
135  if (GENERAL_ENCODING == 'utf-8') {
136  query("SET NAMES 'utf8'", false, $database_object);
137  } elseif (GENERAL_ENCODING == 'iso-8859-1') {
138  query("SET NAMES 'iso-8859-1'", false, $database_object);
139  } else {
140  // Please check if you need to convert GENERAL_ENCODING encoding name to mysql name
141  query("SET NAMES '" . GENERAL_ENCODING . "'", false, $database_object);
142  }
143  return $GLOBALS['selection_de_la_base'];
144 }
145 
158 function query($query, $die_if_error = false, $database_object = null, $silent_if_error = false, $security_sql_filter = true)
159 {
160  if(empty($query)) {
161  return false;
162  }
163  if (defined('IN_PEEL_ADMIN') && a_priv('demo') && ((strpos(strtolower($query), 'insert ') !== false && strpos(strtolower($query), 'into ') !== false) || strpos(strtolower($query), 'update ') !== false || strpos(strtolower($query), 'delete ') !== false || strpos(strtolower($query), 'alter ') !== false)) {
164  // L'utilisateur ayant le profil "demo" ne peut pas faire de modification des données
165  return false;
166  }
167  if ($security_sql_filter && (strpos(strtolower($query), 'information_schema') !== false || strpos(strtolower($query), 'loadfile') !== false || strpos(strtolower($query), 'union all') !== false) || strpos(strtolower($query), 'benchmark(') !== false) {
168  // On empêche l'exécution de requêtes contenant certains mots clé
169  return false;
170  }
171  if(empty($database_object)) {
172  $database_object = &$GLOBALS['database_object'];
173  }
174  if (defined('PEEL_DEBUG') && PEEL_DEBUG) {
175  $start_time = microtime_float();
176  }
177  $i = 0;
178  while (empty($query_values)) {
179  if ($i > 0) {
180  // Si on veut réessayer la requête, on regarde si c'est adapté de réinitialiser la connexion
181  if (empty($error_number) || in_array($error_number, array(111, 126, 127, 141, 144, 145, 1034, 1053, 1137, 1152, 1154, 1156, 1184, 1205, 1317, 2003, 2006, 2013))) {
182  // Liste des erreurs : http://dev.mysql.com/doc/mysql/fr/Error-messages.html
183  // par ailleurs : 2013 : Lost connection to MySQL server during query
184  // 2006 MySQL server has gone away
185  if(!empty($database_object)) {
186  // On se reconnecte après une petite pause pour laisser au serveur la possibilité de gérer un problème
187  sleep(1);
188  }
189  // On force une reconnexion
190  db_connect($database_object);
191  } elseif($error_number == 1364 && String::strpos($query, 'sql_mode') === false) {
192  // Si problème "Field doesn't have a default values" on passe en mode compatibilité définitivement pour les prochaines pages vues
193  set_configuration_variable(array('technical_code' => 'mysql_sql_mode_force', 'string' => 'MYSQL40', 'site_id' => 0, 'origin' => 'auto'), true);
194  // Pour le reste de la génération de page, on passe en mode compatibilité
195  query("SET @@session.sql_mode='MYSQL40");
196  break;
197  } else {
198  // Si l'erreur n'est pas reconnue, on s'arrête là
199  break;
200  }
201  }
202  unset($error_number);
203  unset($error_name);
204  if(!empty($database_object)) {
205  if($GLOBALS['site_parameters']['mysql_extension'] == 'mysqli') {
206  if ($silent_if_error) {
207  $query_values = @$database_object->query($query);
208  } else {
209  $query_values = $database_object->query($query);
210  }
211  } else {
212  if ($silent_if_error) {
213  $query_values = @mysql_query($query, $database_object);
214  } else {
215  $query_values = mysql_query($query, $database_object);
216  }
217  }
218  }
219  if (empty($query_values) && !empty($database_object)) {
220  // Si problème dans la requête, on récupère les codes d'erreur
221  if($GLOBALS['site_parameters']['mysql_extension'] == 'mysqli') {
222  $error_number = $database_object->errno;
223  $error_name = $database_object->error;
224  } else {
225  $error_number = mysql_errno($database_object);
226  $error_name = mysql_error($database_object);
227  }
228  }
229  $i++;
230  if ($i >= 2) {
231  break;
232  }
233  }
234  if (defined('PEEL_DEBUG') && PEEL_DEBUG) {
235  $end_time = microtime_float();
236  $GLOBALS['peel_debug'][] = array('sql' => $query, 'duration' => $end_time - $start_time, 'start' => $start_time - $GLOBALS['script_start_time']);
237  }
238  if (!empty($query_values)) {
239  return $query_values;
240  } else {
241  if (!$silent_if_error || in_array($error_number, array(1118))) {
242  // Si l'erreur est 1118 (Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535.) qui peut arriver lors d'un ALTER TABLE ADD alors on affiche quand même l'erreur pour meilleure gestion par l'administrateur
243  $error_message = vb($GLOBALS['STR_SQL_ERROR']) . ' ' . vb($error_number) . ' - ' . vb($error_name) . " - " . vb($GLOBALS['STR_PAGE']) . ' ' . vb($_SERVER['REQUEST_URI']) . ' - IP ' . vb($_SERVER['REMOTE_ADDR']) . ' - ' . $query . ' - Error number ';
244  if (empty($GLOBALS['display_errors']) && a_priv('admin*', false)) {
245  // Erreurs pas visibles => on rend quand même visible si on est loggué en administrateur
246  echo '[admin info : ' . $error_message . ']<br />';
247  }
248  trigger_error($error_message , E_USER_NOTICE);
249  }
250  if ($die_if_error) {
251  die();
252  } else {
253  return false;
254  }
255  }
256 }
257 
264 function fetch_row($query_result)
265 {
266  if (!empty($query_result)) {
267  if($GLOBALS['site_parameters']['mysql_extension'] == 'mysqli') {
268  return $query_result->fetch_row();
269  } else {
270  return mysql_fetch_row($query_result);
271  }
272  } else {
273  return null;
274  }
275 }
276 
283 function fetch_assoc($query_result)
284 {
285  if (!empty($query_result)) {
286  if($GLOBALS['site_parameters']['mysql_extension'] == 'mysqli') {
287  return $query_result->fetch_assoc();
288  } else {
289  return mysql_fetch_assoc($query_result);
290  }
291  } else {
292  return null;
293  }
294 }
295 
302 function fetch_object($query_result)
303 {
304  if (!empty($query_result)) {
305  if($GLOBALS['site_parameters']['mysql_extension'] == 'mysqli') {
306  return $query_result->fetch_object();
307  } else {
308  return mysql_fetch_object($query_result);
309  }
310  } else {
311  return null;
312  }
313 }
314 
321 function num_rows($query_result)
322 {
323  if (!empty($query_result)) {
324  if($GLOBALS['site_parameters']['mysql_extension'] == 'mysqli') {
325  return $query_result->num_rows;
326  } else {
327  return mysql_num_rows($query_result);
328  }
329  } else {
330  return null;
331  }
332 }
333 
339 function insert_id($database_object = null)
340 {
341  if (empty($database_object)) {
342  $database_object = &$GLOBALS['database_object'];
343  }
344  if($GLOBALS['site_parameters']['mysql_extension'] == 'mysqli') {
345  return $database_object->insert_id;
346  } else {
347  return mysql_insert_id();
348  }
349 }
350 
356 function affected_rows($database_object = null)
357 {
358  if(empty($database_object)) {
359  $database_object = &$GLOBALS['database_object'];
360  }
361  if($GLOBALS['site_parameters']['mysql_extension'] == 'mysqli') {
362  return $database_object->affected_rows;
363  } else {
364  return mysql_affected_rows();
365  }
366 }
367 
374 function real_escape_string($value)
375 {
376  if (is_array($value)) {
377  foreach($value as $this_key => $this_value) {
378  $value[$this_key] = real_escape_string($this_value);
379  }
380  } elseif(!empty($GLOBALS['database_object'])) {
381  if($GLOBALS['site_parameters']['mysql_extension'] == 'mysqli') {
382  $value = $GLOBALS['database_object']->real_escape_string($value);
383  } else {
384  $value = mysql_real_escape_string($value);
385  }
386  } else {
387  $value = null;
388  }
389  return $value;
390 }
391 
400 function nohtml_real_escape_string($value, $allowed_tags = null)
401 {
402  if (is_array($value)) {
403  foreach($value as $this_key => $this_value) {
404  $value[$this_key] = nohtml_real_escape_string($this_value, $allowed_tags);
405  }
406  } elseif(!empty($GLOBALS['database_object'])) {
407  if($GLOBALS['site_parameters']['mysql_extension'] == 'mysqli') {
408  $value = $GLOBALS['database_object']->real_escape_string(@String::strip_tags($value, $allowed_tags));
409  } else {
410  $value = mysql_real_escape_string(@String::strip_tags($value, $allowed_tags));
411  }
412  } else {
413  $value = null;
414  }
415  return $value;
416 }
417 
424 function word_real_escape_string($value)
425 {
426  if (is_array($value)) {
427  foreach($value as $this_key => $this_value) {
428  $value[$this_key] = word_real_escape_string($this_value);
429  }
430  } elseif(!empty($GLOBALS['database_object'])) {
431  $value = String::substr($value, 0, min(String::strpos(str_replace(array('+', ',', ';', '(', ')', '!', '=', '`', '|', '&'), ' ', $value) . ' ', ' '), 60));
432  if($GLOBALS['site_parameters']['mysql_extension'] == 'mysqli') {
433  $value = $GLOBALS['database_object']->real_escape_string($value);
434  } else {
435  $value = mysql_real_escape_string($value);
436  }
437  } else {
438  $value = null;
439  }
440  return $value;
441 }
442 
449 function create_sql_from_array($array)
450 {
451  $sql = "";
452  $i = 0;
453 
454  foreach($array as $key => $value) {
455  if ($i > 0) {
456  $sql .= ', ';
457  }
458  $sql .= "`" . word_real_escape_string($key) . "`= '" . real_escape_string($value) . "'";
459  $i++;
460  }
461 
462  return $sql;
463 }
464 
473 function get_table_fields($table_name, $database_object = null, $silent_if_error = false)
474 {
475  $sql = "SHOW COLUMNS FROM `" . word_real_escape_string($table_name) . "`";
476  $query = query($sql, false, $database_object, $silent_if_error);
477  while ($result = fetch_assoc($query)) {
478  $fields[] = $result;
479  }
480  if (empty($fields)) {
481  return null;
482  } else {
483  return $fields;
484  }
485 }
486 
495 function get_table_field_names($table_name, $link_identifier = null, $silent_if_error = false)
496 {
497  $fields = get_table_fields($table_name, $link_identifier, $silent_if_error);
498  if (empty($fields)) {
499  return null;
500  } else {
501  foreach($fields as $this_field) {
502  $results[] = $this_field['Field'];
503  }
504  return $results;
505  }
506 }
507 
516 function get_table_field_types($table_name, $link_identifier = null, $silent_if_error = false)
517 {
518  $fields = get_table_fields($table_name, $link_identifier, $silent_if_error);
519  if (empty($fields)) {
520  return null;
521  } else {
522  foreach($fields as $this_field) {
523  $results[$this_field['Field']] = $this_field['Type'];
524  }
525  return $results;
526  }
527 }
528 
537 function get_table_index($table_name, $link_identifier = null, $silent_if_error = false)
538 {
539  $sql = "SHOW INDEX FROM `" . word_real_escape_string($table_name) . "`";
540  $query = query($sql, false, $link_identifier, $silent_if_error);
541  while ($result = fetch_assoc($query)) {
542  $fields[] = $result;
543  }
544  if (empty($fields)) {
545  return array();
546  } else {
547  foreach($fields as $this_field) {
548  $results[] = $this_field['Column_name'];
549  }
550  return $results;
551  }
552 }
553 
561 function &listTables($name_part = null)
562 {
563  // Récupère la liste des tables contenues dans la base courante
564  static $tables_list;
565  if (!isset($tables_list[$name_part])) {
566  $sql = "SHOW TABLES FROM `".word_real_escape_string($GLOBALS['nom_de_la_base']) . "`";
567  $result = query($sql);
568  while ($table_name = fetch_row($result)) {
569  if (empty($name_part) || String::strpos($table_name[0], $name_part) !== false) {
570  $tables_list[$name_part][$table_name[0]] = $table_name[0];
571  }
572  }
573  }
574  return $tables_list[$name_part];
575 }
576 
584 function &list_dbs($name_part = null)
585 {
586  // Récupère la liste des tables contenues dans la base courante
587  static $databases_list;
588  if (!isset($databases_list[$name_part])) {
589  $sql = "SHOW DATABASES";
590  $result = query($sql);
591  while ($table_name = fetch_row($result)) {
592  if ((empty($name_part) || String::strpos($table_name[0], $name_part) !== false) && $table_name[0] != "information_schema" && $table_name[0] != "mysql") {
593  $databases_list[$name_part][$table_name[0]] = $table_name[0];
594  }
595  }
596  }
597  return $databases_list[$name_part];
598 }
599 
605 function db_close($database_object = null)
606 {
607  if(empty($database_object)) {
608  $database_object = &$GLOBALS['database_object'];
609  }
610  if(!empty($database_object)) {
611  if($GLOBALS['site_parameters']['mysql_extension'] == 'mysqli') {
612  $database_object->close();
613  } else {
614  mysql_close($GLOBALS['database_object']);
615  }
616  }
617 }
618 
static strip_tags($string, $allowed_tags=null)
String::strip_tags()
Definition: String.php:548
fetch_row($query_result)
fetch_row()
Definition: database.php:264
$result
get_table_index($table_name, $link_identifier=null, $silent_if_error=false)
get_table_index()
Definition: database.php:537
static strpos($haystack, $needle, $offset=0)
Returns the numeric position of the first occurrence of needle in the haystack string.
Definition: String.php:54
$error_message
Definition: choixbase.php:18
& list_dbs($name_part=null)
Renvoie un tableau avec la liste des bases de données accessibles.
Definition: database.php:584
affected_rows($database_object=null)
affected_rows()
Definition: database.php:356
word_real_escape_string($value)
Applique real_escape_string dans le cas où on n'insère qu'un seul mot, de moins de 30 caractères...
Definition: database.php:424
select_db($database_name, &$database_object, $continue_if_error=false)
select_db()
Definition: database.php:110
insert_id($database_object=null)
insert_id()
Definition: database.php:339
& listTables($name_part=null)
Renvoie un tableau avec la liste des tables de la base courante.
Definition: database.php:561
nohtml_real_escape_string($value, $allowed_tags=null)
Protège les données pour insertion dans MySQL ET supprime les tags HTML pour protéger de toute sorte ...
Definition: database.php:400
if(!defined('IN_PEEL')) db_connect(&$database_object, $database_name=null, $serveur_mysql=null, $utilisateur_mysql=null, $mot_de_passe_mysql=null, $continue_if_error=false)
db_connect()
Definition: database.php:31
microtime_float()
microtime_float()
Definition: fonctions.php:2776
real_escape_string($value)
real_escape_string()
Definition: database.php:374
query($query, $die_if_error=false, $database_object=null, $silent_if_error=false, $security_sql_filter=true)
The query() function is meant to be called anywhere you want to make a query.
Definition: database.php:158
vb(&$var, $default=null)
Variable blanche if $var n'est pas défini, retourne $default, sinon retourne $var.
Definition: format.php:97
get_table_field_types($table_name, $link_identifier=null, $silent_if_error=false)
get_table_field_types()
Definition: database.php:516
if(strlen($date2)== '10') if($type== 'users-by-age'&&a_priv('admin_users', true)) elseif($type== 'forums-count'&&a_priv('admin_content', true)) elseif($type== 'forums-categories'&&a_priv('admin_content', true)) elseif($type== 'users-count'&&a_priv('admin_users', true)) elseif($type== 'product-categories'&&a_priv('admin_products', true)) elseif($type== 'users-by-sex'&&a_priv('admin_users', true)) elseif($type== 'users-by-country'&&a_priv('admin_users', true)) elseif($type== 'sales'&&a_priv('admin_sales', true))
Definition: chart-data.php:160
num_rows($query_result)
num_rows()
Definition: database.php:321
if(!defined('IN_PEEL')) send_email($to, $mail_subject= '', $mail_content= '', $template_technical_code=null, $template_tags=null, $format=null, $sender=null, $html_add_structure=true, $html_correct_conformity=false, $html_convert_url_to_links=true, $reply_to=null, $attached_files_infos_array=null, $lang=null, $additional_infos_array=array(), $attachment_not_sent_by_email=false)
Envoi d'un email à un utilisateur.
Definition: emails.php:38
get_table_field_names($table_name, $link_identifier=null, $silent_if_error=false)
get_table_field_names()
Definition: database.php:495
fetch_object($query_result)
fetch_object()
Definition: database.php:302
fetch_assoc($query_result)
fetch_assoc()
Definition: database.php:283
create_sql_from_array($array)
create_sql_from_array()
Definition: database.php:449
$GLOBALS['page_columns_count']
a_priv($requested_priv, $demo_allowed=false, $site_configuration_modification=false, $user_id=null)
Renvoie true si l'utilisateur de la session a le privilège $requested_priv ou un droit supérieur Des ...
Definition: user.php:63
set_configuration_variable($frm, $update_if_technical_code_exists=false, $allow_create=true)
Ajoute la zone HTML dans la table peel_configuration.
Definition: fonctions.php:4199
db_close($database_object=null)
db_close()
Definition: database.php:605
static substr($string, $start, $length=null)
Returns the portion of string specified by the start and length parameters.
Definition: String.php:112
get_table_fields($table_name, $database_object=null, $silent_if_error=false)
get_table_fields()
Definition: database.php:473

This documentation for Open ecommerce PEEL Shopping and PEEL.fr has been generated by Doxygen on Thu Oct 15 2015 14:30:09 - Peel ecommerce is a product of Agence web Advisto SAS. All rights reserved.