时间戳

首页 > 解决方案 > 当 SQL 字符串具有“%f”时，Wordpress wpdp->getResults 不执行我的 sql

php - 当 SQL 字符串具有“%f”时，Wordpress wpdp->getResults 不执行我的 sql

问题描述

这是我执行查询时的代码。我在执行之前显示了 SQL 查询，最后一个被执行了。

public static function getProductByUser($user_id, $page, $containName, $sort, $filterOptions)
{
    global $wpdb;
    $limit = 40;
    $offset = ($page - 1) * $limit;
    $querySort = self::buildSortQuery($sort);
    $pidi_db = new DatabaseApi();
    $filterQuery = "";
    if ($filterOptions) {
        $filterQuery = self::buildFilterQuery($filterOptions);
    }
    $sql = "SELECT hp.*, CASE WHEN pf.product_id IS NULL THEN false ELSE true  END as favorite
    FROM {$pidi_db->{UserDomain::$table_name}} ud JOIN {$pidi_db->{self::$table_name}} hp ON ud.domain_name = hp.domain
    LEFT JOIN {$pidi_db->{HotProductFavorite::$table_name}} pf ON pf.product_id = hp.product_id 
    WHERE ud.user_id = %d AND hp.title LIKE %s" . $filterQuery . " GROUP BY hp.product_id " . $querySort . " LIMIT %d OFFSET %d ";


    $query = $wpdb->prepare($sql, $user_id, "%" . $containName . "%", $limit, $offset);

     print_r($sql);
     print_r("\n");

     $wpdb->get_results($query);
     print_r($wpdb->last_query);
     die;

}

当最终的 sql 字符串像这样返回时。查询已执行：

=== Sql after prepare ====
SELECT hp.*, CASE WHEN pf.product_id IS NULL THEN false ELSE true  END as favorite
    FROM wp_pidi_user_domain ud JOIN wp_pidi_hot_product_tracking hp ON ud.domain_name = hp.domain
    LEFT JOIN wp_pidi_hot_product_favorite pf ON pf.product_id = hp.product_id 
    WHERE ud.user_id = %d AND hp.title LIKE %s AND hp.domain LIKE '%asadas%' GROUP BY hp.product_id ORDER BY hp.published_at DESC LIMIT %d OFFSET %d 
 === SQL was executed ===
SELECT hp.*, CASE WHEN pf.product_id IS NULL THEN false ELSE true  END as favorite
    FROM wp_pidi_user_domain ud JOIN wp_pidi_hot_product_tracking hp ON ud.domain_name = hp.domain
    LEFT JOIN wp_pidi_hot_product_favorite pf ON pf.product_id = hp.product_id 
    WHERE ud.user_id = 1 AND hp.title LIKE '%%' AND hp.domain LIKE '%asadas%' GROUP BY hp.product_id ORDER BY hp.published_at DESC LIMIT 40 OFFSET 0

好像 wpdp 执行了我的查询。但是当最终的 SQL 字符串像这样返回时，wpdp 没有执行我的查询，也没有在日志文件中显示任何错误。我认为防止注入功能拒绝了我的查询。

=== sql after prepare ===
SELECT hp.*, CASE WHEN pf.product_id IS NULL THEN false ELSE true  END as favorite
    FROM wp_pidi_user_domain ud JOIN wp_pidi_hot_product_tracking hp ON ud.domain_name = hp.domain
    LEFT JOIN wp_pidi_hot_product_favorite pf ON pf.product_id = hp.product_id 
    WHERE ud.user_id = %d AND hp.title LIKE %s AND hp.title LIKE '%fairy%' GROUP BY hp.product_id ORDER BY hp.published_at DESC LIMIT %d OFFSET %d 
=== lastest sql was executed ===
SELECT * FROM wp_pidi_filter_options WHERE `user_id`=1 AND `screen_id`=1

这是之前执行的 sql，而不是 prepare 之后的 sql。

有人能帮我吗？谢谢！

标签： phpmysqldatabasewordpress

解决方案

我的解决方案： buildFilterQuery() 函数将始终返回这样的字符串，如果您有更好的解决方案，请改进我的答案！：

"AND hp.title LIKE '¥REGEX¥Fairy¥REGEX¥' "

准备好之后：$query = $wpdb->prepare($sql,$arg);我替换了 REGEX 占位符：

   $newFilterQuery = str_replace(self::$REGEX, '%', $query);
   return $wpdb->get_results($newFilterQuery);

推荐阅读