How to prepare a statement with a dynamic number of parameters?

Question

This code works but it seems insecure because of concatenating GET parameters into the query. I'm concatenating because I need a dynamic number of parameters in the WHERE clause that can be of different types (IN, normal comparison condition).

How can I prepare a secure statement from a dynamic number of different type WHERE conditions?

class myclass
{
    public function index($where_clause = 1)
    {
        // db connection (using pdo)
        $stm = $this->dbh->query("SELECT COUNT(amount) paid_qs FROM qanda $where_clause");
        $ret = $stm->fetch(PDO::FETCH_ASSOC);
        // do stuff
    }
    public function gen_where_clause()
    {
        $where_clause = '';
        if (isset($_GET['c']) || isset($_GET['t']))
        {
            $where_clause = 'WHERE ';
            if (isset($_GET['c']))
            {
                $where_clause .= 'cat = ' . $_GET['c'];
            }
            if (isset($_GET['t']))
            {
                if (isset($_GET['c']))
                {
                    $where_clause .= $where_clause . ' AND '
                    }
                $where_clause .= 'tag IN(' . $_GET['t'] . ')';
            }
        }
        return $this->index($where_clause);
    }
}

Answer 1

I'll address this question on three fronts: actual correctness of the code, a solution, and better practices.

---

The Code

This code actually does not work, as mentioned there are very basic syntax error that even prevents it from actually being run at all. I'll assume this is a simplification error, however even the concatenation is wrong: the statement is duplicated each time (.= and the string itself. either of these will work, both will destroy the query)

$where_clause .= $where_clause . ' AND '

---

Dynamic Number Of Parameters

The problem of having a dynamic number of parameters is interesting, and depending on the needs can be fairly convoluted, however in this case, a fairly simple param concatenation will allow you to achieve a dynamic number of parameters, as suggested by AbraCadaver .

More exactly, when a condition is added to the statement, separately add the sql to the statement, and the values to an array:

$sql .= 'cat = :cat';
$values[':cat'] = $_GET['c'];

You can then prepare the statement and execute it with the correct parameters.

$stmt = $pdo->prepare($sql);
$stmt->execute($values);

---

Better Practices

As mentioned, the code presented in this question possibly is not functional at all, so let me highlight a few basic OOP principles that would dramatically enhance this snippet.

• Dependency Injection

The db connection should be injected through the constructor, not recreated every time you execute a query (as it will, if you connect in the index method). Notice that $pdo is a private property. It should not be public, accessible by other objects. If these objects need a database connection, inject the same pdo instance in their constructor as well.

class myclass
{
    private $pdo;
    public function __construct(PDO $pdo) { $this->pdo = $pdo; }
}

• The flow

One of these methods should be private, called by the other (public one) that would receive in arguments everything that is needed to run the functions. In this case, there does not seem to be any arguments involved, everything comes from $_GET.

We can adapt index so that it accepts both the sql and the values for the query, but these three lines could easily be transferred to the other method.

private function index($sql, $values)
{
    $stmt = $this->pdo->prepare($sql);
    $stmt->execute($values);
    return $stmt->fetchAll();
}

Then the public gen_where_clause (I believe that is wrongly named... it really generates the values, not the clauses) that can be safely used, that will generate a dynamic number of parameters, protecting you from sql injection.

public function gen_where_clause()
{
    $sql = "SELECT COUNT(amount) AS paid_qs FROM qanda ";
    $values = [];
    if (isset($_GET['c']) || isset($_GET['t']))
    {
        $sql .= ' WHERE ';
        if (isset($_GET['c']))
        {
            $sql .= ' cat = :cat ';
            $values[':cat'] = $_GET['c'];
        }
        // etc.
    }
    return $this->index($sql, $values);
}

• Filtering inputs

Escaping values is not needed, for sql injection protection that is, when using parameterized queries. However, sanitizing your inputs is always a correct idea. Sanitize it outside of the function, then pass it as an argument to the "search" function, decoupling the function from the superglobal $_GET. Defining the arguments for filtering is out of the rather large scope of this post, consult the documentation.

// global code
// create $pdo normally
$instance = new myclass($pdo);
$inputs = filter_input_array(INPUT_GET, $args);
$results = $instance->gen_search_clause($inputs);