How to use prepare() with dynamic column names?

1 Views

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

Read More

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?

Post Views: 1

Related posts

2 comments

  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?

  2. As of Mar 29, 2023:

    As of milestone 6.2, which was released Mar 29, you can now use %i instead of %s as a placeholder inside $wpdb-prepare() statements for table and column names. Here’s a dev-note about it with examples.

Comments are closed.