vikbooking / libraries / adapter / database / database.php

database.php in VikBooking Hotel Booking Engine & PMS 1.8.6, at libraries/adapter/database/database.php

922 lines 20.9 KB

Raw Download Zip

1	<?php
2	/**
3	* @package VikWP - Libraries
4	* @subpackage adapter.database
5	* @author E4J s.r.l.
6	* @copyright Copyright (C) 2023 E4J s.r.l. All Rights Reserved.
7	* @license http://www.gnu.org/licenses/gpl-2.0.html GNU/GPL
8	* @link https://vikwp.com
9	*/
10
11	// No direct access
12	defined('ABSPATH') or die('No script kiddies please!');
13
14	/**
15	* This adapter is required to wrap the Wordpress DB
16	* functions using the Joomla DB interface.
17	* This is helpful to improve the portability between Joomla and Wordpress.
18	*
19	* @since 10.0
20	*/
21	class JDatabase
22	{
23	/**
24	* The singleton instance of the database.
25	*
26	* @var JDatabase
27	*/
28	protected static $instance = null;
29
30	/**
31	* The global $wpdb instance.
32	*
33	* @var $wpdb
34	*/
35	protected $db;
36
37	/**
38	* The query set for the execution.
39	*
40	* @var string
41	*/
42	protected $q;
43
44	/**
45	* The last result fetched.
46	*
47	* @var mixed
48	*/
49	protected $result;
50
51	/**
52	* The query offset (start).
53	*
54	* @var integer
55	* @since 10.1.15
56	*/
57	protected $offset;
58
59	/**
60	* The query limit (max number of records).
61	*
62	* @var integer
63	* @since 10.1.15
64	*/
65	protected $limit;
66
67	/**
68	* The common database table prefix.
69	*
70	* @var string
71	* @since 10.1.37
72	*/
73	protected $tablePrefix;
74
75	/**
76	* Returns the global database adapter object, only creating it if it
77	* doesn't already exist.
78	*
79	* @param $wpdb $db The wordpress database handler.
80	*
81	* @return self A new instance of this class.
82	*/
83	public static function getInstance($db)
84	{
85	if (static::$instance === null)
86	{
87	static::$instance = new static($db);
88	}
89
90	return static::$instance;
91	}
92
93	/**
94	* Class constructor.
95	*
96	* @param $wpdb $db The wordpress db handler.
97	*/
98	protected function __construct($db)
99	{
100	$this->db = $db;
101
102	/**
103	* Hook used to suppress/enable database errors.
104	*
105	* @param boolean True to suppress the errors, false otherwise (false by default).
106	*
107	* @since 10.1.13
108	*/
109	$this->db->suppress_errors(apply_filters('vik_db_suppress_errors', false));
110
111	/**
112	* Hook used to show/hide database errors.
113	* In case errors are suppressed, this hook would result useless.
114	*
115	* @param boolean True to show the errors, false otherwise (true by default).
116	*
117	* @since 10.1.13
118	*/
119	$this->db->show_errors(apply_filters('vik_db_show_errors', true));
120	}
121
122	/**
123	* Magic method to proxy the functions in the $wpdb wrapped instance.
124	*
125	* @param string $method The called method.
126	* @param array $args The array of arguments passed to the method.
127	*
128	* @return mixed The value returned by the dispatched method.
129	* Null if the method doesn't exist.
130	*/
131	public function __call($name, $args)
132	{
133	if (method_exists($this->db, $name))
134	{
135	return call_user_func_array(array($this->db, $name), $args);
136	}
137
138	throw new RuntimeException('Call to undefined method ' . __CLASS__ . '::' . $name . '()', 500);
139	}
140
141	/**
142	* This function replaces a string placeholder with the real database prefix.
143	*
144	* @param string $sql The SQL statement to prepare.
145	* @param string $prefix The common table prefix.
146	*
147	* @return string The processed SQL statement.
148	*/
149	public function replacePrefix($sql, $prefix = '#__')
150	{
151	// generate a random placeholder
152	$placeholder = md5($prefix . uniqid());
153
154	// Replace all prefixes between the single/double quotes
155	// with the placeholder generated previously.
156	// This avoids to affect also strings that contains the actual prefix.
157	$sql = preg_replace_callback(
158	// "/('.($prefix).')\|(\".($prefix).\")/",
159	// get all the strings contained between single and double quotes,
160	// even if they don't contain the prefix
161	"/('.?')\|(\".?\")/",
162	function($match) use ($prefix, $placeholder)
163	{
164	// if contained, replace the prefix with the placeholder
165	return str_replace($prefix, $placeholder, $match[0]);
166	},
167	$sql
168	);
169
170	// get the prefix to use
171	$wp_prefix = $this->getPrefix();
172
173	// replace remaining prefixes (e.g. within backticks) with the real db prefix
174	$sql = str_replace($prefix, $wp_prefix, $sql);
175
176	// replace random placeholders with the original escaped prefix
177	$sql = str_replace($placeholder, $prefix, $sql);
178
179	return $sql;
180	}
181
182	/**
183	* Sets the SQL statement string for later execution.
184	*
185	* @param mixed $q The SQL statement to set as string.
186	* @param integer $offset The affected row offset to set.
187	* @param integer $limit The maximum affected rows to set.
188	*
189	* @return self This object to support chaining.
190	*/
191	public function setQuery($q, $offset = 0, $limit = 0)
192	{
193	$this->result = null;
194
195	/**
196	* If we are accessing #__users table, we need to route all
197	* the specified columns that belong to Joomla framework.
198	*
199	* @since 10.1.16
200	*/
201	$q = static::adjustJoomlaQuery2WP($q);
202
203	// save the query with the real db prefix
204	$this->q = $this->replacePrefix($q);
205
206	// register offset and limit (always override previous values)
207	$this->offset = abs((int) $offset);
208	$this->limit = abs((int) $limit);
209
210	return $this;
211	}
212
213	/**
214	* Gets the current query object or a new JDatabaseQuery object.
215	*
216	* @param boolean $new False to return the current query object, True to return a new JDatabaseQuery object.
217	*
218	* @return mixed The JDatabaseQuery object or a SQL plain string.
219	*/
220	public function getQuery($new = false)
221	{
222	if ($new)
223	{
224	JLoader::import('adapter.database.query');
225
226	return new JDatabaseQuery($this);
227	}
228
229	return $this->q;
230	}
231
232	/**
233	* Execute the SQL statement.
234	*
235	* @return boolean True on success, otherwise false.
236	*/
237	public function execute()
238	{
239	$sql = trim((string) $this->q);
240
241	// try to limit the query
242	if ($this->limit > 0 && $this->offset > 0)
243	{
244	$sql .= ' LIMIT ' . $this->offset . ', ' . $this->limit;
245	}
246	elseif ($this->limit > 0)
247	{
248	$sql .= ' LIMIT ' . $this->limit;
249	}
250
251	// if we are executing a SELECT query we need to
252	// load directly all the results fetched
253	if (preg_match("/^(SELECT\|SHOW)/i", $sql))
254	{
255	// result should contain an array
256	$this->result = $this->db->get_results($sql);
257	}
258	// otherwise we can launch a generic query
259	else
260	{
261	// result should contain an integer
262	$this->result = $this->db->query($sql);
263	}
264
265	return (bool) $this->result;
266	}
267
268	/**
269	* Get the number of returned rows for the previous executed SQL statement.
270	* This command is only valid for statements like SELECT or SHOW that return an actual result set.
271	*
272	* @return integer The number of returned rows.
273	*/
274	public function getNumRows()
275	{
276	if (is_array($this->result))
277	{
278	return count($this->result);
279	}
280
281	return 0;
282	}
283
284	/**
285	* Get the number of affected rows by the last INSERT, UPDATE, REPLACE or DELETE
286	* for the previous executed SQL statement.
287	*
288	* @return integer The number of affected rows.
289	*/
290	public function getAffectedRows()
291	{
292	if (is_numeric($this->result))
293	{
294	return $this->result;
295	}
296
297	return 0;
298	}
299
300	/**
301	* Method to get the auto-incremented value from the last INSERT statement.
302	*
303	* @return mixed The value of the auto-increment field from the last inserted row.
304	*/
305	public function insertid()
306	{
307	return $this->db->insert_id;
308	}
309
310	/**
311	* Method to get an array of the result set rows from the database query
312	* where each row is an object.
313	*
314	* @return array The object list.
315	*
316	* @uses execute()
317	*/
318	public function loadObjectList()
319	{
320	if (is_null($this->result))
321	{
322	$this->execute();
323	}
324
325	if (is_array($this->result))
326	{
327	return $this->result;
328	}
329
330	return array();
331	}
332
333	/**
334	* Method to get an array of the result set rows from the database query
335	* where each row is an associative array of ['field_name' => 'row_value'].
336	*
337	* @return array The associative arrays list.
338	*
339	* @uses loadObjectList()
340	*/
341	public function loadAssocList()
342	{
343	$app = array();
344
345	foreach ($this->loadObjectList() as $obj)
346	{
347	$app[] = (array) $obj;
348	}
349
350	return $app;
351	}
352
353	/**
354	* Method to get the first row of the result set from the database query as an object.
355	*
356	* @return mixed The return value or null if the query failed.
357	*
358	* @uses loadObjectList()
359	*/
360	public function loadObject()
361	{
362	$list = $this->loadObjectList();
363
364	if (count($list))
365	{
366	return $list[0];
367	}
368
369	return null;
370	}
371
372	/**
373	* Method to get the first row of the result set from the database query
374	* as an associative array of ['field_name' => 'row_value'].
375	*
376	* @return mixed The return value or null if the query failed.
377	*
378	* @uses loadObject()
379	*/
380	public function loadAssoc()
381	{
382	$obj = $this->loadObject();
383
384	if ($obj !== null)
385	{
386	return (array) $obj;
387	}
388
389	return null;
390	}
391
392	/**
393	* Method to get the first field of the first row of the result set from the database query.
394	*
395	* @return mixed The return value or null if the query failed.
396	*
397	* @uses loadAssoc()
398	*/
399	public function loadResult()
400	{
401	$arr = $this->loadAssoc();
402
403	if (is_array($arr))
404	{
405	$keys = array_keys($arr);
406
407	return $arr[$keys[0]];
408	}
409
410	return null;
411	}
412
413	/**
414	* Method to get the first row of the result set from the database query as an array.
415	*
416	* Columns are indexed numerically so the first column in the result set would be accessible via <var>$row[0]</var>, etc.
417	*
418	* @return mixed The return value or null if the query failed.
419	*
420	* @since 10.1.37
421	*/
422	public function loadRow()
423	{
424	$arr = $this->loadAssoc();
425
426	if (is_array($arr))
427	{
428	return array_values($arr);
429	}
430
431	return null;
432	}
433
434	/**
435	* Method to get an array of values from the <var>$offset</var> field in each row
436	* of the result set from the database query.
437	*
438	* @param integer $offset The row offset to use to build the result array.
439	*
440	* @return array A list containing the columns.
441	*
442	* @uses loadAssocList()
443	*/
444	public function loadColumn($offset = 0)
445	{
446	$column = array();
447
448	foreach ($this->loadAssocList() as $arr)
449	{
450	$keys = array_keys($arr);
451
452	$column[] = $arr[$keys[$offset]];
453	}
454
455	return $column;
456	}
457
458	/**
459	* Quotes and optionally escapes a string to database requirements for use in database queries.
460	*
461	* @param mixed $text A string or an array of strings to quote.
462	* @param boolean $escape True (default) to escape the string, false to leave it unchanged.
463	*
464	* @return mixed The quoted input.
465	*/
466	public function quote($text, $escape = true)
467	{
468	if (is_array($text))
469	{
470	return esc_sql($text);
471	}
472
473	return '\'' . ($escape ? esc_sql((string) $text) : $text) . '\'';
474	}
475
476	/**
477	* Shorten alias for quote() method.
478	*
479	* @see quote()
480	*/
481	public function q($text, $escape = true)
482	{
483	return $this->quote($text, $escape);
484	}
485
486	/**
487	* Wraps an SQL statement identifier name such as column, table or database names
488	* in quotes to prevent injection risks and reserved word conflicts.
489	*
490	* @param mixed $name The identifier name to wrap in quotes, or an array of identifier
491	* names to wrap in quotes. Each type supports dot-notation name.
492	* @param mixed $as The AS query part associated to $name. It can be string or array.
493	*
494	* @return string The quote wrapped name.
495	*
496	* @uses _quoteName()
497	*/
498	public function quoteName($name, $as = null)
499	{
500	// define an empty array
501	$arr = array();
502
503	// fill $arr recursively with quoted names
504	$this->_quoteName($arr, $name, $as);
505
506	// concat the list using a comma separator
507	return implode(', ', $arr);
508	}
509
510	/**
511	* Shorten alias for quoteName() method.
512	*
513	* @see quoteName()
514	*/
515	public function qn($str, $as = null)
516	{
517	return $this->quoteName($str, $as);
518	}
519
520	/**
521	* Recursive method to quote a list of names.
522	*
523	* @param array &$arr A list containing all the quotes names.
524	* @param mixed $name The identifier name to wrap in quotes, or an array of identifier
525	* names to wrap in quotes. Each type supports dot-notation name.
526	* @param mixed $as The AS query part associated to $name. It can be string or array.
527	*
528	* @return void
529	*/
530	protected function _quoteName(array &$arr, $name, $as = null)
531	{
532	// if the name is (still) an array, quote it recursively
533	// until we have a scalar value
534	if (is_array($name))
535	{
536	// iterate the names contained in the list
537	foreach ($name as $i => $inner)
538	{
539	// obtain the AS only if it exists
540	$_as = !is_null($as) && is_array($as) && isset($as[$i]) ? $as[$i] : null;
541
542	$this->_quoteName($arr, $inner, $_as);
543	}
544	}
545	// quote the scalar value
546	else
547	{
548	// explode the name for dot-notation
549	$exp = explode('.', $name);
550
551	$name = "`{$exp[0]}`";
552	if (count($exp) > 1)
553	{
554	$name .= ".`{$exp[1]}`";
555	}
556
557	if (!is_null($as))
558	{
559	$name .= " AS `$as`";
560	}
561
562	$arr[] = $name;
563	}
564	}
565
566	/**
567	* Inserts a row into a table based on an object's properties.
568	*
569	* @param string $table The name of the database table to insert into.
570	* @param object &$object A reference to an object whose public properties match the table fields.
571	* @param string $key The name of the primary key. If provided the object property is updated.
572	*
573	* @return boolean True on success.
574	*/
575	public function insertObject($table, &$object, $key = null)
576	{
577	$data = array();
578
579	foreach (get_object_vars($object) as $k => $v)
580	{
581	// exclude primary key, not null values, arrays, objects and
582	// internal properties (prefixed with an underscore)
583	if ($k != $key && $v !== null && is_scalar($v) && $k[0] != '_')
584	{
585	$data[$k] = $v;
586	}
587	}
588
589	// insert the new record
590	if (!$this->db->insert($this->replacePrefix($table), $data))
591	{
592	return false;
593	}
594
595	// update the primary key if it exists
596	$id = $this->db->insert_id;
597
598	// store affected row
599	$this->result = $id;
600
601	if ($key && $id && is_string($key))
602	{
603	$object->{$key} = $id;
604	}
605
606	return true;
607	}
608
609	/**
610	* Updates a row in a table based on an object's properties.
611	*
612	* @param string $table The name of the database table to update.
613	* @param object &$object A reference to an object whose public properties match the table fields.
614	* @param mixed $key The name (or a list of names) of the primary key.
615	* @param boolean $nulls True to update null fields or false to ignore them.
616	*
617	* @return boolean True on success.
618	*/
619	public function updateObject($table, &$object, $key, $nulls = false)
620	{
621	$set = array();
622	$where = array();
623
624	if (is_string($key))
625	{
626	$key = array($key);
627	}
628
629	if (is_object($key))
630	{
631	$key = (array) $key;
632	}
633
634	foreach (get_object_vars($object) as $k => $v)
635	{
636	// exclude arrays, objects and internal properties (prefixed with an underscore)
637	if (is_array($v) \|\| is_object($v) \|\| $k[0] == '_')
638	{
639	continue;
640	}
641
642	// set the primary key to the WHERE clause instead of a field to update
643	if (in_array($k, $key))
644	{
645	$where[$k] = $v;
646	continue;
647	}
648
649	// update field only if not null or if nulls values are allowed
650	if ($v !== null \|\| $nulls)
651	{
652	$set[$k] = $v;
653	}
654	}
655
656	// we don't have any fields to update
657	if (empty($set))
658	{
659	return true;
660	}
661
662	// update the specified record
663	$affected = $this->db->update($this->replacePrefix($table), $set, $where);
664
665	// store affected rows
666	$this->result = (int) $affected;
667
668	return $affected !== false;
669	}
670
671	/**
672	* Returns the error faced (if any) during the last query execution.
673	*
674	* @return string The error message.
675	*
676	* @since 10.1.58
677	*/
678	public function getLastError()
679	{
680	return $this->db->last_error;
681	}
682
683	/**
684	* Returns a PHP date() function compliant date format for the database driver.
685	*
686	* @return string The format string.
687	*/
688	public function getDateFormat()
689	{
690	return 'Y-m-d H:i:s';
691	}
692
693	/**
694	* Returns the null date in the format of the database driver.
695	*
696	* @return string The null date string.
697	*
698	* @since 10.1.5
699	*/
700	public function getNullDate()
701	{
702	return '0000-00-00 00:00:00';
703	}
704
705	/**
706	* Get the common table prefix for the database driver.
707	*
708	* @return string The common database table prefix.
709	*
710	* @since 10.1.37
711	*/
712	public function getPrefix()
713	{
714	if (is_null($this->tablePrefix))
715	{
716	/**
717	* Hook used to filter the default WP database prefix before it is used.
718	*
719	* @param string The database prefix to use for queries.
720	*
721	* @since 10.1.1
722	*/
723	$this->tablePrefix = apply_filters('vik_get_db_prefix', $this->db->prefix);
724	}
725
726	return $this->tablePrefix;
727	}
728
729	/**
730	* Retrieves field information about a given table.
731	*
732	* @param string $table The name of the database table.
733	* @param boolean $typeOnly True to only return field types.
734	*
735	* @return array An array of fields for the database table.
736	*
737	* @since 10.1.19
738	*/
739	public function getTableColumns($table, $typeOnly = true)
740	{
741	/**
742	* Do not escape the table name to support SQLite too.
743	*
744	* @since 10.1.53
745	*/
746	$q = "SHOW FULL COLUMNS FROM " . $table;
747
748	// set the query to get the table fields statement
749	$this->setQuery($q);
750	$this->execute();
751
752	$fields = $this->loadObjectList();
753
754	$result = [];
755
756	// if we only want the type as the value add just that to the list.
757	if ($typeOnly)
758	{
759	foreach ($fields as $field)
760	{
761	$result[$field->Field] = preg_replace('/[(0-9)]/', '', $field->Type);
762	}
763	}
764	// if we want the whole field data object add that to the list.
765	else
766	{
767	foreach ($fields as $field)
768	{
769	/**
770	* With SQLite the Extra column might not be included.
771	* Simulate the same result by checking whether the column
772	* is equal to `id` and force the "auto_increment" rule.
773	*
774	* @since 10.1.53
775	*/
776	if (!isset($field->Extra))
777	{
778	$field->Extra = $field->Field === 'id' ? 'auto_increment' : '';
779	}
780
781	$result[$field->Field] = $field;
782	}
783	}
784
785	return $result;
786	}
787
788	/**
789	* Method to get an array containing all the database tables.
790	*
791	* @return array An array of all the tables in the database.
792	*
793	* @since 10.1.37
794	*/
795	public function getTableList()
796	{
797	// set the query to get the tables statement
798	$this->setQuery('SHOW TABLES');
799	$this->execute();
800
801	return $this->loadColumn();
802	}
803
804	/**
805	* Shows the table CREATE statement that creates the given tables.
806	*
807	* @param mixed $tables A table name or a list of table names.
808	*
809	* @return array A list of the create SQL for the tables.
810	*
811	* @since 10.1.37
812	*/
813	public function getTableCreate($tables)
814	{
815	$result = [];
816
817	// sanitize input to an array and iterate over the list
818	$tables = (array) $tables;
819
820	foreach ($tables as $table)
821	{
822	// set the query to get the table CREATE statement
823	$this->setQuery('SHOW CREATE TABLE ' . $this->qn($table));
824	$this->execute();
825
826	$row = $this->loadRow();
827
828	// populate the result array based on the create statements
829	$result[$table] = $row[1];
830	}
831
832	return $result;
833	}
834
835	/**
836	* Adjusts a query built for Joomla to WordPress needs.
837	*
838	* @param mixed $query The SQL query string or a query builder.
839	*
840	* @return void
841	*
842	* @since 10.1.16
843	*/
844	public static function adjustJoomlaQuery2WP($query)
845	{
846	// always cast to string
847	$query = (string) $query;
848
849	// check if the query contains `#__users` and an optional alias
850	if (preg_match("/`#__users`(?:\s+AS\s+`([a-z0-9_]+)`)?/i", $query, $match))
851	{
852	$userTable = !empty($match[1]) ? $match[1] : null;
853
854	// check whether an alias should be used
855	$tableAlias = $userTable ? "`{$userTable}`\." : "";
856	$lookup = array();
857
858	// replace all the columns that match the regex
859	$query = preg_replace_callback("/{$tableAlias}`([a-z0-9_]+)`(?:\sAS\s`([a-z0-9_]+)`)?/i", function($match) use ($userTable, $tableAlias, $query, &$lookup)
860	{
861	// get current column and alias
862	$col = $match[1];
863	$alias = isset($match[2]) ? $match[2] : $match[1];
864
865	switch (strtolower($col))
866	{
867	case 'name':
868	$col = 'display_name';
869	break;
870
871	case 'username':
872	$col = 'user_login';
873	break;
874
875	case 'email':
876	$col = 'user_email';
877	break;
878	}
879
880	// rebuild column without using ALIAS
881	$str = ($userTable ? "`{$userTable}`." : "") . "`{$col}`";
882
883	$sign = ($tableAlias ? $tableAlias . '.' : '') . $col;
884
885	// check if lookup doesn't contain this column and the query is a select
886	if (!isset($lookup[$sign]) && preg_match("/^\s*SELECT/i", $query))
887	{
888	// obtain position of current column and position of FROM statement and
889	// make sure the chunk position is displayed before FROM
890	if (preg_match("/{$match[0]}/i", $query, $token, PREG_OFFSET_CAPTURE)
891	&& preg_match("/\sFROM\s/i", $query, $from, PREG_OFFSET_CAPTURE)
892	&& $token[0][1] < $from[0][1])
893	{
894	// add alias for column within SELECT
895	$str .= " AS `{$alias}`";
896	}
897	}
898
899	// mark column as registered within the lookup in order
900	// to avoid adding alias again outside the SELECT
901	$lookup[$sign] = 1;
902
903	return $str;
904	}, $query);
905
906	/**
907	* In case of multi-site, always use the base prefix when
908	* querying the users database table.
909	*
910	* @since 10.1.31
911	*/
912	if (is_multisite())
913	{
914	global $wpdb;
915	$query = preg_replace("/`#__users`/", "`{$wpdb->base_prefix}users`", $query);
916	}
917	}
918
919	return $query;
920	}
921	}
922