如何使用带动态列名的prepare（）？

Question

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

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

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

Answer 1

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

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?