imagify / inc / classes / class-imagify-db.php

class-imagify-db.php in Imagify Image Optimization: Optimize Images | Compress & Convert to WebP/AVIF 1.7, at inc/classes/class-imagify-db.php

432 lines 13.0 KB

Raw Download Zip

1	<?php
2	defined( 'ABSPATH' ) \|\| die( 'Cheatin\' uh?' );
3
4	/**
5	* Imagify DB class. It reunites tools to work with the DB.
6	*
7	* @since 1.6.13
8	* @author Grégory Viguier
9	*/
10	class Imagify_DB {
11
12	/**
13	* Class version.
14	*
15	* @var string
16	*/
17	const VERSION = '1.0.1';
18
19	/**
20	* Some hosts limit the number of JOINs in SQL queries, but we need them.
21	*
22	* @since 1.6.13
23	* @access public
24	* @author Grégory Viguier
25	*/
26	public static function unlimit_joins() {
27	global $wpdb;
28	static $done = false;
29
30	if ( $done ) {
31	return;
32	}
33
34	$done = true;
35	$query = 'SET SQL_BIG_SELECTS=1';
36
37	/**
38	* Filter the SQL query allowing to remove the limit on JOINs.
39	*
40	* @since 1.6.13
41	* @author Grégory Viguier
42	*
43	* @param string\|bool $query The query. False to prevent any query.
44	*/
45	$query = apply_filters( 'imagify_db_unlimit_joins_query', $query );
46
47	if ( $query && is_string( $query ) ) {
48	$wpdb->query( $query ); // WPCS: unprepared SQL ok.
49	}
50	}
51
52	/**
53	* Change an array of values into a comma separated list, ready to be used in a `IN ()` clause.
54	*
55	* @since 1.6.13
56	* @access public
57	* @author Grégory Viguier
58	*
59	* @param array $values An array of values.
60	* @return string A comma separated list of values.
61	*/
62	public static function prepare_values_list( $values ) {
63	$values = esc_sql( (array) $values );
64	$values = array_map( array( __CLASS__, 'quote_string' ), $values );
65	return implode( ',', $values );
66	}
67
68	/**
69	* Wrap a value in quotes, unless it's an integer.
70	*
71	* @since 1.6.13
72	* @access public
73	* @author Grégory Viguier
74	*
75	* @param int\|string $value A value.
76	* @return int\|string
77	*/
78	public static function quote_string( $value ) {
79	return is_numeric( $value ) ? $value : "'" . addcslashes( $value, "'" ) . "'";
80	}
81
82	/**
83	* First half of escaping for LIKE special characters % and _ before preparing for MySQL.
84	* Use this only before wpdb::prepare() or esc_sql(). Reversing the order is very bad for security.
85	*
86	* Example Prepared Statement:
87	* $wild = '%';
88	* $find = 'only 43% of planets';
89	* $like = $wild . $wpdb->esc_like( $find ) . $wild;
90	* $sql = $wpdb->prepare( "SELECT * FROM $wpdb->posts WHERE post_content LIKE %s", $like );
91	*
92	* Example Escape Chain:
93	* $sql = esc_sql( $wpdb->esc_like( $input ) );
94	*
95	* @since 1.7
96	* @access public
97	* @author Grégory Viguier
98	*
99	* @param string $text The raw text to be escaped. The input typed by the user should have no extra or deleted slashes.
100	* @return string Text in the form of a LIKE phrase. The output is not SQL safe. Call $wpdb::prepare() or real_escape next.
101	*/
102	public static function esc_like( $text ) {
103	global $wpdb;
104
105	if ( method_exists( $wpdb, 'esc_like' ) ) {
106	// Introduced in WP 4.0.0.
107	return $wpdb->esc_like( $text );
108	}
109
110	return addcslashes( $text, '_%\\' );
111	}
112
113	/**
114	* Get Imagify mime types, ready to be used in a `IN ()` clause.
115	*
116	* @since 1.6.13
117	* @access public
118	* @author Grégory Viguier
119	*
120	* @return string A comma separated list of mime types.
121	*/
122	public static function get_mime_types() {
123	static $mime_types;
124
125	if ( ! isset( $mime_types ) ) {
126	$mime_types = self::prepare_values_list( imagify_get_mime_types() );
127	}
128
129	return $mime_types;
130	}
131
132	/**
133	* Get post statuses related to attachments, ready to be used in a `IN ()` clause.
134	*
135	* @since 1.7
136	* @access public
137	* @author Grégory Viguier
138	*
139	* @return string A comma separated list of post statuses.
140	*/
141	public static function get_post_statuses() {
142	static $statuses;
143
144	if ( ! isset( $statuses ) ) {
145	$statuses = self::prepare_values_list( imagify_get_post_statuses() );
146	}
147
148	return $statuses;
149	}
150
151	/**
152	* Get the SQL JOIN clause to use to get only attachments that have the required WP metadata.
153	* It returns an empty string if the database has no attachments without the required metadada.
154	* It also triggers Imagify_DB::unlimit_joins().
155	*
156	* @since 1.7
157	* @access public
158	* @author Grégory Viguier
159	*
160	* @param string $id_field An ID field to match the metadata ID against in the JOIN clause.
161	* Default is the posts table `ID` field, using the `p` alias: `p.ID`.
162	* In case of "false" value or PEBKAC, fallback to the same field without alias.
163	* @param bool $matching Set to false to get a query to fetch metas NOT matching the file extensions.
164	* @param bool $test Test if the site has attachments without required metadata before returning the query. False to bypass the test and get the query anyway.
165	* @return string
166	*/
167	public static function get_required_wp_metadata_join_clause( $id_field = 'p.ID', $matching = true, $test = true ) {
168	global $wpdb;
169
170	if ( $test && ! imagify_has_attachments_without_required_metadata() ) {
171	return '';
172	}
173
174	self::unlimit_joins();
175	$clause = '';
176
177	if ( ! $id_field \|\| ! is_string( $id_field ) ) {
178	$id_field = "$wpdb->posts.ID";
179	}
180
181	$join = $matching ? 'INNER' : 'LEFT';
182
183	foreach ( self::get_required_wp_metadata_aliases() as $meta_name => $alias ) {
184	$clause .= "
185	$join JOIN $wpdb->postmeta AS $alias
186	ON ( $id_field = $alias.post_id AND $alias.meta_key = '$meta_name' )";
187	}
188
189	return $clause;
190	}
191
192	/**
193	* Get the SQL part to be used in a WHERE clause, to get only attachments that have (in)valid '_wp_attached_file' and '_wp_attachment_metadata' metadatas.
194	* It returns an empty string if the database has no attachments without the required metadada.
195	*
196	* @since 1.7
197	* @access public
198	* @author Grégory Viguier
199	*
200	* @param string $aliases The aliases to use for the meta values.
201	* @param bool $matching Set to false to get a query to fetch invalid metas.
202	* @param bool $test Test if the site has attachments without required metadata before returning the query. False to bypass the test and get the query anyway.
203	* @return string A query.
204	*/
205	public static function get_required_wp_metadata_where_clause( $aliases = array(), $matching = true, $test = true ) {
206	static $query = array();
207
208	if ( $test && ! imagify_has_attachments_without_required_metadata() ) {
209	return '';
210	}
211
212	if ( is_string( $aliases ) ) {
213	$aliases = array(
214	'_wp_attached_file' => $aliases,
215	);
216	}
217
218	$aliases = imagify_merge_intersect( $aliases, self::get_required_wp_metadata_aliases() );
219	$key = implode( '\|', $aliases ) . '\|' . (int) $matching;
220
221	if ( isset( $query[ $key ] ) ) {
222	return $query[ $key ];
223	}
224
225	$alias_1 = $aliases['_wp_attached_file'];
226	$alias_2 = $aliases['_wp_attachment_metadata'];
227	$extensions = self::get_extensions_where_clause( $alias_1, $matching, $test );
228
229	if ( $matching ) {
230	$query[ $key ] = "AND $alias_1.meta_value NOT LIKE '%://%' AND $alias_1.meta_value NOT LIKE '_:\\\\\%' $extensions";
231	} else {
232	$query[ $key ] = "AND ( $alias_2.meta_value IS NULL OR $alias_1.meta_value IS NULL OR $alias_1.meta_value LIKE '%://%' OR $alias_1.meta_value LIKE '_:\\\\\%' $extensions )";
233	}
234
235	return $query[ $key ];
236	}
237
238	/**
239	* Get the SQL part to be used in a WHERE clause, to get only attachments that have a valid file extensions.
240	* It returns an empty string if the database has no attachments without the required metadada.
241	*
242	* @since 1.7
243	* @access public
244	* @author Grégory Viguier
245	*
246	* @param string $alias The alias to use for the meta value.
247	* @param bool $matching Set to false to get a query to fetch metas NOT matching the file extensions.
248	* @param bool $test Test if the site has attachments without required metadata before returning the query. False to bypass the test and get the query anyway.
249	* @return string A query.
250	*/
251	public static function get_extensions_where_clause( $alias = false, $matching = true, $test = true ) {
252	static $extensions;
253	static $query = array();
254
255	if ( $test && ! imagify_has_attachments_without_required_metadata() ) {
256	return '';
257	}
258
259	if ( ! isset( $extensions ) ) {
260	$extensions = array_keys( imagify_get_mime_types() );
261	$extensions = implode( '\|', $extensions );
262	$extensions = explode( '\|', $extensions );
263	}
264
265	if ( ! $alias ) {
266	$alias = self::get_required_wp_metadata_aliases();
267	$alias = $alias['_wp_attached_file'];
268	}
269
270	$key = $alias . '\|' . (int) $matching;
271
272	if ( isset( $query[ $key ] ) ) {
273	return $query[ $key ];
274	}
275
276	if ( $matching ) {
277	$query[ $key ] = "AND ( LOWER( $alias.meta_value ) LIKE '%." . implode( "' OR LOWER( $alias.meta_value ) LIKE '%.", $extensions ) . "' )";
278	} else {
279	$query[ $key ] = "OR ( LOWER( $alias.meta_value ) NOT LIKE '%." . implode( "' AND LOWER( $alias.meta_value ) NOT LIKE '%.", $extensions ) . "' )";
280	}
281
282	return $query[ $key ];
283	}
284
285	/**
286	* Get the aliases used for the metas in self::get_required_wp_metadata_join_clause(), self::get_required_wp_metadata_where_clause(), and self::get_extensions_where_clause().
287	*
288	* @since 1.7
289	* @access public
290	* @author Grégory Viguier
291	*
292	* @return array An array with the meta name as key and its alias as value.
293	*/
294	public static function get_required_wp_metadata_aliases() {
295	return array(
296	'_wp_attached_file' => 'imrwpmt1',
297	'_wp_attachment_metadata' => 'imrwpmt2',
298	);
299	}
300
301	/**
302	* Combine two arrays with some specific keys.
303	* We use this function to combine the result of 2 SQL queries.
304	*
305	* @since 1.6.13
306	* @access public
307	* @author Grégory Viguier
308	*
309	* @param array $keys An array of keys.
310	* @param array $values An array of arrays like array( 'id' => id, 'value' => value ).
311	* @param int $keep_keys_order Set to true to return an array ordered like $keys instead of $values.
312	* @return array The combined arrays.
313	*/
314	public static function combine_query_results( $keys, $values, $keep_keys_order = false ) {
315	if ( ! $keys \|\| ! $values ) {
316	return array();
317	}
318
319	$result = array();
320	$keys = array_flip( $keys );
321
322	foreach ( $values as $v ) {
323	if ( isset( $keys[ $v['id'] ] ) ) {
324	$result[ $v['id'] ] = $v['value'];
325	}
326	}
327
328	if ( $keep_keys_order ) {
329	$keys = array_intersect_key( $keys, $result );
330	return array_replace( $keys, $result );
331	}
332
333	return $result;
334	}
335
336	/**
337	* A helper to retrieve all values from one or several post metas, given a list of post IDs.
338	* The $wpdb cache is flushed to save memory.
339	*
340	* @since 1.6.13
341	* @access public
342	* @author Grégory Viguier
343	*
344	* @param array $metas An array of meta names like:
345	* array(
346	* 'key1' => 'meta_name_1',
347	* 'key2' => 'meta_name_2',
348	* 'key3' => 'meta_name_3',
349	* )
350	* If a key contains 'data', the results will be unserialized.
351	* @param array $ids An array of post IDs.
352	* @return array An array of arrays of results like:
353	* array(
354	* 'key1' => array( post_id_1 => 'result_1', post_id_2 => 'result_2', post_id_3 => 'result_3' ),
355	* 'key2' => array( post_id_1 => 'result_4', post_id_3 => 'result_5' ),
356	* 'key3' => array( post_id_1 => 'result_6', post_id_2 => 'result_7' ),
357	* )
358	*/
359	public static function get_metas( $metas, $ids ) {
360	global $wpdb;
361
362	if ( ! $ids ) {
363	return array_fill_keys( array_keys( $metas ), array() );
364	}
365
366	$sql_ids = implode( ',', $ids );
367
368	foreach ( $metas as $result_name => $meta_name ) {
369	$metas[ $result_name ] = $wpdb->get_results( // WPCS: unprepared SQL ok.
370	"SELECT pm.post_id as id, pm.meta_value as value
371	FROM $wpdb->postmeta as pm
372	WHERE pm.meta_key = '$meta_name'
373	AND pm.post_id IN ( $sql_ids )
374	ORDER BY pm.post_id DESC",
375	ARRAY_A
376	);
377
378	$wpdb->flush();
379	$metas[ $result_name ] = self::combine_query_results( $ids, $metas[ $result_name ], true );
380
381	if ( strpos( $result_name, 'data' ) !== false ) {
382	$metas[ $result_name ] = array_map( 'maybe_unserialize', $metas[ $result_name ] );
383	}
384	}
385
386	return $metas;
387	}
388
389	/**
390	* Create/Upgrade the table in the database.
391	*
392	* @since 1.7
393	* @access public
394	* @author Grégory Viguier
395	*
396	* @param string $table_name The (prefixed) table name.
397	* @param string $schema_query Query representing the table schema.
398	* @return bool True on success. False otherwise.
399	*/
400	public static function create_table( $table_name, $schema_query ) {
401	global $wpdb;
402
403	require_once ABSPATH . 'wp-admin/includes/upgrade.php';
404
405	$schema_query = trim( $schema_query );
406	$charset_collate = $wpdb->get_charset_collate();
407
408	dbDelta( "CREATE TABLE $table_name ($schema_query) $charset_collate;" );
409
410	return empty( $wpdb->last_error ) && self::table_exists( $table_name );
411	}
412
413	/**
414	* Tell if the given table exists.
415	*
416	* @since 1.7
417	* @access public
418	* @author Grégory Viguier
419	*
420	* @param string $table_name Full name of the table (with DB prefix).
421	* @return bool
422	*/
423	public static function table_exists( $table_name ) {
424	global $wpdb;
425
426	$escaped_table = self::esc_like( $table_name );
427	$result = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $escaped_table ) );
428
429	return $result === $table_name;
430	}
431	}
432