Use this only before wpdb::prepare() or esc_sql(). Reversing the order is very bad for security.
Example Prepared Statement:
$wild = '%';
$find = 'only 43% of planets';
$like = $wild . $wpdb->esc_like( $find ) . $wild;
$sql = $wpdb->prepare( "SELECT * FROM $wpdb->posts WHERE post_content LIKE %s", $like );
Example Escape Chain:
$sql = esc_sql( $wpdb->esc_like( $input ) );
function posts_where($where, &$wp_query) { global $wpdb; if ($title = $wp_query->get('like_title')) { $where .= " AND " . $wpdb->posts . ".post_title LIKE '%" . esc_sql(wpdb::esc_like($title)) . "%'"; } return $where; }
/** * Used internally to generate an SQL string for searching across multiple columns. * * @since 4.6.0 * @access protected * * @param string $string Search string. * @param array $columns Columns to search. * * @return string Search SQL. */ protected function get_search_sql($string, $columns) { $like = '%' . $this->db->esc_like($string) . '%'; $searches = array(); foreach ($columns as $column) { $searches[] = $this->db->prepare("{$column} LIKE %s", $like); } return '(' . implode(' OR ', $searches) . ')'; }
/** * @param array $args * @param string $output_type * @return int|mixed */ public function find($args, $output_type = OBJECT) { $args = wp_parse_args($args, array('select' => '*', 'offset' => 0, 'limit' => 1, 'orderby' => 'id', 'order' => 'DESC', 'email' => '', 'method' => '', 'datetime_after' => '', 'datetime_before' => '', 'include_errors' => true)); $where = array(); $params = array(); // build general select from query $query = sprintf("SELECT %s FROM `%s`", $args['select'], $this->table_name); // add email to WHERE clause if ('' !== $args['email']) { $where[] = 'email LIKE %s'; $params[] = '%%' . $this->db->esc_like($args['email']) . '%%'; } // add method to WHERE clause if ('' !== $args['method']) { $where[] = 'method = %s'; $params[] = $args['method']; } // add datetime to WHERE clause if ('' !== $args['datetime_after']) { $where[] = 'datetime >= %s'; $params[] = $args['datetime_after']; } if ('' !== $args['datetime_before']) { $where[] = 'datetime <= %s'; $params[] = $args['datetime_before']; } if (!$args['include_errors']) { $where[] = 'success = %d'; $params[] = 1; } // add where parameters if (count($where) > 0) { $query .= ' WHERE ' . implode(' AND ', $where); } // prepare parameters if (!empty($params)) { $query = $this->db->prepare($query, $params); } // return result count if ($args['select'] === 'COUNT(*)') { return (int) $this->db->get_var($query); } // return single row if ($args['limit'] === 1) { $query .= ' LIMIT 1'; return $this->db->get_row($query); } // perform rest of query $args['limit'] = absint($args['limit']); $args['offset'] = absint($args['offset']); $args['orderby'] = preg_replace("/[^a-zA-Z]/", "", $args['orderby']); $args['order'] = preg_replace("/[^a-zA-Z]/", "", $args['order']); // add ORDER BY, OFFSET and LIMIT to SQL $query .= sprintf(' ORDER BY `%s` %s LIMIT %d, %d', $args['orderby'], $args['order'], $args['offset'], $args['limit']); return $this->db->get_results($query, $output_type); }
/** * @param array $args * * @return int|mixed */ public function find($args = array()) { $args = wp_parse_args($args, array('select' => '*', 'email' => '', 'method' => '', 'type' => '', 'limit' => 1, 'offset' => 0, 'orderby' => 'id', 'order' => 'DESC')); $where = array(); $params = array(); // build general select from query $query = sprintf("SELECT %s FROM `%s`", $args['select'], $this->table_name); // add email to WHERE clause if ('' !== $args['email']) { $where[] = 'email LIKE %s'; $params[] = '%%' . $this->db->esc_like($args['email']) . '%%'; } // add method to WHERE clause if ('' !== $args['method']) { $where[] = 'method = %s'; $params[] = $args['method']; } // add where parameters if (count($where) > 0) { $query .= ' WHERE ' . implode(' AND ', $where); } // prepare parameters if (!empty($params)) { $query = $this->db->prepare($query, $params); } // return result count if ($args['select'] === 'COUNT(*)') { $query .= ' LIMIT 1'; return (int) $this->db->get_var($query); } // return single row if ($args['limit'] === 1) { $query .= ' LIMIT 1'; return $this->db->get_row($query); } // perform rest of query $args['limit'] = absint($args['limit']); $args['offset'] = absint($args['offset']); $order_by = sanitize_key($args['orderby']) . ' ' . strtoupper(sanitize_key($args['order'])); // add ORDER BY, OFFSET and LIMIT to SQL $query .= sprintf(' ORDER BY %s LIMIT %d, %d', $order_by, $args['offset'], $args['limit']); return $this->db->get_results($query); }
/** * Used internally to generate an SQL string for searching across multiple columns * * @access protected * @since 3.1.0 * * @param string $string * @param array $cols * @param bool $wild Whether to allow wildcard searches. Default is false for Network Admin, true for single site. * Single site allows leading and trailing wildcards, Network Admin only trailing. * @return string */ protected function get_search_sql($string, $cols, $wild = false) { $searches = array(); $leading_wild = 'leading' == $wild || 'both' == $wild ? '%' : ''; $trailing_wild = 'trailing' == $wild || 'both' == $wild ? '%' : ''; $like = $leading_wild . $this->db->esc_like($string) . $trailing_wild; foreach ($cols as $col) { if ('ID' == $col) { $searches[] = $this->db->prepare("{$col} = %s", $string); } else { $searches[] = $this->db->prepare("{$col} LIKE %s", $like); } } return ' AND (' . implode(' OR ', $searches) . ')'; }
/** * Generate SQL for the ORDER BY condition based on passed search terms. * * @param array $q Query variables. * @return string ORDER BY clause. */ protected function parse_search_order(&$q) { if ($q['search_terms_count'] > 1) { $num_terms = count($q['search_orderby_title']); // If the search terms contain negative queries, don't bother ordering by sentence matches. $like = ''; if (!preg_match('/(?:\\s|^)\\-/', $q['s'])) { $like = '%' . $this->db->esc_like($q['s']) . '%'; } $search_orderby = ''; // sentence match in 'post_title' if ($like) { $search_orderby .= $this->db->prepare("WHEN {$this->db->posts}.post_title LIKE %s THEN 1 ", $like); } // sanity limit, sort as sentence when more than 6 terms // (few searches are longer than 6 terms and most titles are not) if ($num_terms < 7) { // all words in title $search_orderby .= 'WHEN ' . implode(' AND ', $q['search_orderby_title']) . ' THEN 2 '; // any word in title, not needed when $num_terms == 1 if ($num_terms > 1) { $search_orderby .= 'WHEN ' . implode(' OR ', $q['search_orderby_title']) . ' THEN 3 '; } } // Sentence match in 'post_content' and 'post_excerpt'. if ($like) { $search_orderby .= $this->db->prepare("WHEN {$this->db->posts}.post_excerpt LIKE %s THEN 4 ", $like); $search_orderby .= $this->db->prepare("WHEN {$this->db->posts}.post_content LIKE %s THEN 5 ", $like); } if ($search_orderby) { $search_orderby = '(CASE ' . $search_orderby . 'ELSE 6 END)'; } } else { // single word or sentence search $search_orderby = reset($q['search_orderby_title']) . ' DESC'; } return $search_orderby; }
/** * Takes an image URL (or attachment ID) and returns a URL to a version of that same image that is equal in dimensions to the passed $width and $height parameters * The image will be resized on-the-fly, saved, and returned if an image of that same size doesn't already exist in the media library * * @param string|int $image The image URL or attachment ID whose resized version the function should return * @param int $width The desired width of the returned image * @param int $height The desired height of the returned image * @param boolean $crop Should the image be cropped to the desired dimensions (Defaults to false in which case the image is scaled down, rather than cropped) * @return string */ public static function thumbIt($image, $width, $height, $crop = true) { global $wpdb; if (is_int($image)) { $attachment_id = $image > 0 ? $image : false; } else { $img_url = esc_url($image); $upload_dir = wp_upload_dir(); $base_url = $upload_dir['baseurl']; if (substr($img_url, 0, strlen($base_url)) !== $base_url) { return $image; } $result = $wpdb->get_var($wpdb->prepare("SELECT post_id FROM {$wpdb->postmeta} WHERE meta_key = '_wp_attachment_metadata' AND meta_value LIKE %s LIMIT 1;", '%' . wpdb::esc_like(str_replace(trailingslashit($base_url), '', $img_url)) . '%')); $attachment_id = absint($result) > 0 ? absint($result) : false; } if ($attachment_id === false) { return $image; } $image = wp_get_attachment_url($attachment_id); $attachment_meta = wp_get_attachment_metadata($attachment_id); if ($attachment_meta === false) { return $image; } $width = absint($width); $height = absint($height); $needs_resize = true; foreach ($attachment_meta['sizes'] as $size) { if ($width === $size['width'] && $height === $size['height']) { $image = str_replace(basename($image), $size['file'], $image); $needs_resize = false; break; } } if ($needs_resize) { $attached_file = get_attached_file($attachment_id); $resized = image_make_intermediate_size($attached_file, $width, $height, (bool) $crop); if (!is_wp_error($resized) && $resized !== false) { $key = sprintf('resized-%dx%d', $width, $height); $attachment_meta['sizes'][$key] = $resized; $image = str_replace(basename($image), $resized['file'], $image); wp_update_attachment_metadata($attachment_id, $attachment_meta); $backup_sizes = get_post_meta($attachment_id, '_wp_attachment_backup_sizes', true); if (!is_array($backup_sizes)) { $backup_sizes = array(); } $backup_sizes[$key] = $resized; update_post_meta($attachment_id, '_wp_attachment_backup_sizes', $backup_sizes); } } return $image; }
/** * Used internally to generate an SQL string for searching across multiple columns. * * @since 4.6.0 * @access protected * * @param string $string Search string. * @param array $columns Columns to search. * @return string Search SQL. */ protected function get_search_sql($string, $columns) { if (false !== strpos($string, '*')) { $like = '%' . implode('%', array_map(array($this->db, 'esc_like'), explode('*', $string))) . '%'; } else { $like = '%' . $this->db->esc_like($string) . '%'; } $searches = array(); foreach ($columns as $column) { $searches[] = $this->db->prepare("{$column} LIKE %s", $like); } return '(' . implode(' OR ', $searches) . ')'; }
function se_lookup() { global $wpdb; $search = wpdb::esc_like($_REQUEST['q']); $query = 'SELECT ID,post_title FROM ' . $wpdb->posts . ' WHERE post_title LIKE \'' . $search . '%\' AND post_status = \'publish\' ORDER BY post_title ASC'; //$post_array = []; foreach ($wpdb->get_results($query) as $row) { $post_title = $row->post_title; //$id = $row->ID; //$meta = get_post_meta($id, 'YOUR_METANAME', TRUE); echo $post_title . "~"; } die; }
/** * Used internally to generate a SQL string related to the 'search' parameter. * * @since 4.6.0 * @access protected * * @param string $string * @return string */ protected function get_search_sql( $string ) { $like = '%' . $this->db->esc_like( $string ) . '%'; return $this->db->prepare( '((t.name LIKE %s) OR (t.slug LIKE %s))', $like, $like ); }
/** * Generate SQL JOIN and WHERE clauses for a first-order query clause. * * "First-order" means that it's an array with a 'key' or 'value'. * * @since 4.1.0 * @access public * * @param array $clause Query clause, passed by reference. * @param array $parent_query Parent query array. * @param string $clause_key Optional. The array key used to name the clause in the original `$meta_query` * parameters. If not provided, a key will be generated automatically. * @return array { * Array containing JOIN and WHERE SQL clauses to append to a first-order query. * * @type string $join SQL fragment to append to the main JOIN clause. * @type string $where SQL fragment to append to the main WHERE clause. * } */ public function get_sql_for_clause(&$clause, $parent_query, $clause_key = '') { $sql_chunks = array('where' => array(), 'join' => array()); if (isset($clause['compare'])) { $clause['compare'] = strtoupper($clause['compare']); } else { $clause['compare'] = isset($clause['value']) && is_array($clause['value']) ? 'IN' : '='; } if (!in_array($clause['compare'], array('=', '!=', '>', '>=', '<', '<=', 'LIKE', 'NOT LIKE', 'IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN', 'EXISTS', 'NOT EXISTS', 'REGEXP', 'NOT REGEXP', 'RLIKE'))) { $clause['compare'] = '='; } $meta_compare = $clause['compare']; // First build the JOIN clause, if one is required. $join = ''; // We prefer to avoid joins if possible. Look for an existing join compatible with this clause. $alias = $this->find_compatible_table_alias($clause, $parent_query); if (false === $alias) { $i = count($this->table_aliases); $alias = $i ? 'mt' . $i : $this->meta_table; // JOIN clauses for NOT EXISTS have their own syntax. if ('NOT EXISTS' === $meta_compare) { $join .= " LEFT JOIN {$this->meta_table}"; $join .= $i ? " AS {$alias}" : ''; $join .= $this->db->prepare(" ON ({$this->primary_table}.{$this->primary_id_column} = {$alias}.{$this->meta_id_column} AND {$alias}.meta_key = %s )", $clause['key']); // All other JOIN clauses. } else { $join .= " INNER JOIN {$this->meta_table}"; $join .= $i ? " AS {$alias}" : ''; $join .= " ON ( {$this->primary_table}.{$this->primary_id_column} = {$alias}.{$this->meta_id_column} )"; } $this->table_aliases[] = $alias; $sql_chunks['join'][] = $join; } // Save the alias to this clause, for future siblings to find. $clause['alias'] = $alias; // Determine the data type. $_meta_type = isset($clause['type']) ? $clause['type'] : ''; $meta_type = $this->get_cast_for_type($_meta_type); $clause['cast'] = $meta_type; // Fallback for clause keys is the table alias. Key must be a string. if (is_int($clause_key) || !$clause_key) { $clause_key = $clause['alias']; } // Ensure unique clause keys, so none are overwritten. $iterator = 1; $clause_key_base = $clause_key; while (isset($this->clauses[$clause_key])) { $clause_key = $clause_key_base . '-' . $iterator; $iterator++; } // Store the clause in our flat array. $this->clauses[$clause_key] =& $clause; // Next, build the WHERE clause. // meta_key. if (array_key_exists('key', $clause)) { if ('NOT EXISTS' === $meta_compare) { $sql_chunks['where'][] = $alias . '.' . $this->meta_id_column . ' IS NULL'; } else { $sql_chunks['where'][] = $this->db->prepare("{$alias}.meta_key = %s", trim($clause['key'])); } } // meta_value. if (array_key_exists('value', $clause)) { $meta_value = $clause['value']; if (in_array($meta_compare, array('IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN'))) { if (!is_array($meta_value)) { $meta_value = preg_split('/[,\\s]+/', $meta_value); } } else { $meta_value = trim($meta_value); } switch ($meta_compare) { case 'IN': case 'NOT IN': $meta_compare_string = '(' . substr(str_repeat(',%s', count($meta_value)), 1) . ')'; $where = $this->db->prepare($meta_compare_string, $meta_value); break; case 'BETWEEN': case 'NOT BETWEEN': $meta_value = array_slice($meta_value, 0, 2); $where = $this->db->prepare('%s AND %s', $meta_value); break; case 'LIKE': case 'NOT LIKE': $meta_value = '%' . $this->db->esc_like($meta_value) . '%'; $where = $this->db->prepare('%s', $meta_value); break; // EXISTS with a value is interpreted as '='. // EXISTS with a value is interpreted as '='. case 'EXISTS': $meta_compare = '='; $where = $this->db->prepare('%s', $meta_value); break; // 'value' is ignored for NOT EXISTS. // 'value' is ignored for NOT EXISTS. case 'NOT EXISTS': $where = ''; break; default: $where = $this->db->prepare('%s', $meta_value); break; } if ($where) { if ('CHAR' === $meta_type) { $sql_chunks['where'][] = "{$alias}.meta_value {$meta_compare} {$where}"; } else { $sql_chunks['where'][] = "CAST({$alias}.meta_value AS {$meta_type}) {$meta_compare} {$where}"; } } } /* * Multiple WHERE clauses (for meta_key and meta_value) should * be joined in parentheses. */ if (1 < count($sql_chunks['where'])) { $sql_chunks['where'] = array('( ' . implode(' AND ', $sql_chunks['where']) . ' )'); } return $sql_chunks; }