如何在动态列名中使用prepare()? [英] How to use prepare() with dynamic column names?

问题描述

我有一个函数，该函数将sql表的列名字符串作为参数，返回1个字符串结果:

I have a function that takes an sql table column name string as a parameter, returns 1 string result:

function myFunction($column_name) {
    return $wpdb->get_var($wpdb->prepare("SELECT %s FROM myTable WHERE user_id=%s", $column_name, $current_user->user_login));
}

但是，由于prepare的性质，我无法将变量用于列名(和表名).

However, this code does NOT work, since with the nature of prepare, I can't use a variable for column names (and table names).

这可行，但我认为这会带来安全问题:

This works, but I think it poses a security issue:

return $wpdb->get_var('SELECT ' . $column_name . ' FROM myTable WHERE user_id=' . $current_user->user_login); 

要在我的prepare语句中使用动态列名，我需要做什么?

What do I need to do in order to to use dynamic column names in my prepare statement?

推荐答案

您可以改为使用已批准"值的列表，这样就不会真正在查询中使用用户数据.像这样:

You could use a list of "approved" values instead, that way you're not really using user data inside a query. Something like this:

$Approved = array ('firstname', 'lastname', 'birthdate') ;
$Location = array_search($ColumnName, $Approved) // Returns approved column location as int
if($Location !== FALSE) {
    // Use the value from Approved using $Location as a key
    $Query = $wpdb->Prepare('SELECT ' . $Approved[$Location] . ' FROM myTable WHERE user_id=:userid');
    $Query->Execute(array(
        :userid => $current_user->user_login
    ));

    return $Query;
} else {
    return false;
}

也许仅获取所有(SELECT *或SELECT a，b，c，d)用户数据并将其保存到会话中以供以后使用可能会更容易?

Maybe it might be easier to just get all (SELECT * or SELECT a,b,c,d) of the user data and save it to session to use later?

这篇关于如何在动态列名中使用prepare()?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！