CODEWITHSUNDEEP
phpwordpressprepared-statementwordpress-database
Karim
Karim

Reputation: 21

How to use $wpdb->prepare for dynamic value

I build SQL with a dynamic value from an array. How can I use placeholder or that dynamic condition?

I'm calling that function like this:

get_all_results("TABLE_NAME", ["column" => "VALUE"])

    public static function get_all_results(string $table_name, array $where = []): array
    {
        global $wpdb;

        /**
         * Prepare condition
         * 
         * @var string
         */
        $condition = "";
        $index = 0;
        foreach ($where as $key => $value) {
            if ($index > 0) {
                $condition .= "AND ";
            }

            $condition .= "`$key` = '$value' ";
            $index += 1;
        }

        if (!empty($condition)) {
            $condition = " WHERE $condition ";
        }

        $results = $wpdb->get_results($wpdb->prepare("SELECT * FROM %i {$condition} ", $table_name));
        return $results;
    }

How can I use $wpdb->prepare with my function or what should I do in this case ?

This line is the main problem for me, "Plugin Check" - plugin giving me error for this.

        $results = $wpdb->get_results($wpdb->prepare("SELECT * FROM %i {$condition} ", $table_name));

WordPress.DB.PreparedSQL.InterpolatedNotPrepared Line 238 of file includes/classes/DB.php.

Use placeholders and $wpdb->prepare(); found interpolated variable $condition at "SELECT * FROM %i WHERE $condition".

SELECT * FROM %i WHERE $condition

Upvotes: 1

Views: 659

Answers (2)

mickmackusa
mickmackusa

Reputation: 47991

For a basic query builder function that receives an associative array of where conditions joined by AND, here is a modern implementation with %i for identifier placeholders.

public static function get_all_results(string $table_name, array $where = []): array
{
    global $wpdb;

    $sql = 'SELECT * FROM %i';
    $clauses = [];
    $params = [$table_name];
    foreach ($where as $key => $value) {
        $clauses[] = '%i = %s';
        array_push($params, $key, $value);
    }

    if ($clauses) {
        $sql .= ' WHERE ' . implode(' AND ', $clauses);
    }

    return $wpdb->get_results($wpdb->prepare($sql, $params));
}

This might be enough for the most basic scenarios but it won't be long before you realize the need for:

  • conditions that need a non-= comparison
  • OR between conditions
  • nested conditional logic
  • $where keys which are not column names
  • $where values which should not be wrapped in quotes (as a string)
  • ... and much, much more.

Here are some topically related (non-Wordpress) posts:

Upvotes: 0

TSCAmerica.com
TSCAmerica.com

Reputation: 5397

I will use placeholders for each value in the $where array, in the below code that includes dynamically constructing the SQL query with placeholders for each condition, instead of directly inserting the condition string into the query. The actual values of these placeholders are then safely passed through $wpdb->prepare(), which effectively prevents SQL injection vulnerabilities, hope this helps

public static function get_all_results(string $table_name, array $where = []): array
{
    global $wpdb;

    //Prepare a condition
    $condition = "";
    $values = [];
    foreach ($where as $key => $value) {
        if (!empty($condition)) {
            $condition .= " AND ";
        }

        $condition .= $wpdb->prepare("`$key` = %s", $value);
    }

    if (!empty($condition)) {
        $condition = " WHERE $condition ";
    }

    $query = "SELECT * FROM `$table_name` $condition";

    $results = $wpdb->get_results($query);
    return $results;
}

Upvotes: 1

Related Questions