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;
}
