Prepared Statement with Optional Parameters

Question

I'm setting up a REST service using Slim Framework and PDO. I've a controller with optional parameters for GET request, in particular, user can set fields to be selected and results limit value:

/*
  GET /products

  {fields} -
  {limit} -
 */

$app->get(
        '/products(/)', function () use ($app, $dbHelper) {
    $app->response()->header("Content-Type", "application/json");
    $params = $app->request->get();
    echo json_encode($dbHelper->getProducts($params));
}
);

Writing down "getProducts" method I need to check for that parameters and to build a prepared statement for the query:

function getProduct($id, $params) {

if(isset($params['fields'])) {
    $fields = $params['fields'];
} else {
    $fields = "id, name, price";
}

if(isset($params['limit'])) {
    $limit = $params['limit']);
} else {
    $limit = 30;
}

try {
    $sqlProduct = "SELECT :fields";
    $sqlProduct.= " FROM products";
    $sqlProduct.= " LIMIT :limit";

    $stmt = $this->db->prepare($sqlProduct);
    $stmt->bindValue(':fields', $fields, PDO::PARAM_STR); // is this a String?
    $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);

    $stmt->execute();

    $products = $stmt->fetchAll(PDO::FETCH_ASSOC);

    foreach($products as $product) {
        array_push($this->response, $product);
    }

} catch (PDOException $pdoe) {
    echo "PDOException: " . pdoe.getMessage();
}

return $this->response;
}

I'm PDO newbie, the code above is not working correctly. What is the correct way to parametrize the query?

Shuld I use isset function to check for params?

What about data type check (e.g. how can I be sure that "limit" is a number)?

I know nothing about Slim but [prepared statements don't work that way](http://stackoverflow.com/questions/182287/can-php-pdo-statements-accept-the-table-name-as-parameter). Do you really want to accept arbitrary column names from the outside? It doesn't seem to be even useful; your database design is not going to change dynamically, is it? — Álvaro González, Jan 20 '14 at 15:33
I want the user to be able to select specific fields, 'cause products table have a lot of fields potentially useful, but not always togheter (besides you can have a lighter JSON). As a reference, I've read FB graph API documentation, and often you can pass "fields" as argument of the request. — Jumpa, Jan 20 '14 at 15:37
Please read the linked question in my comment, yours is actually a duplicate of it and current accepted answer provides an alternative. — Álvaro González, Jan 20 '14 at 15:56
No, you simply have to write code to generate SQL automatically from a list of allowed column names. — Álvaro González, Jan 20 '14 at 16:07

Prepared Statement with Optional Parameters

0 Answers0