PDO quote in my request

Question

I have a query:

SELECT * FROM products WHERE 1=1 ORDER BY :order_by :order_dir LIMIT :limit OFFSET :offset

My params array:

Array
(
    [order_by] => price
    [order_dir] => ASC
    [limit] => 50
    [offset] => 0
)

And I have a function for bindValues:

    public function query($sql, $params=array(), $limit = null, $offset = null){

            try { 

                if (!is_null($offset) && !is_null($limit)) {
                    $sql .= ' LIMIT :limit OFFSET :offset';
                    $params['limit']        =  (int)$limit;
                    $params['offset']       =   (int)$offset;
                }

                $stmt = $this->database->prepare($sql); 

                foreach($params as $key => $value) {
                        if(is_int($value)) {
                            $param = PDO::PARAM_INT;
                        } elseif(is_bool($value)) {
                            $param = PDO::PARAM_BOOL;
                        } elseif(is_null($value)) {
                            $param = PDO::PARAM_NULL;
                        } elseif(is_string($value)) {
                            $param = PDO::PARAM_STR;
                        } else {
                            $param = FALSE;
                        }

                        if($param) $stmt->bindValue(":$key", $value, $param);
                }

                $stmt->execute();
                return $stmt;

            } catch(PDOException $e){
                throw new Exception($e->getMessage());
            }
   }

I'm trying to get products:

$result->products   = $db->query($sql, $arr, 50, 0)->fetchAll();

My raw query I think looks like:

SELECT * FROM products WHERE 1=1 ORDER BY "price" "ASC" LIMIT 50 OFFSET 0

The query above is wrong. It should be:

SELECT * FROM products WHERE 1=1 ORDER BY price ASC LIMIT 50 OFFSET 0

How can I solve this problem?

`order by case when :order_dir='ASC' then :order_by end ASC, case when :order_dir='DESC' then :order_by end DESC ...` — Lukasz Szozda, Oct 15 '15 at 20:01

score 1 · Answer 1 · answered Oct 15 '15 at 20:03

You can use CASE for this like:

SELECT * 
FROM products 
WHERE 1=1 
ORDER BY 
   CASE WHEN :order_dir='ASC' THEN price END ASC,
   CASE WHEN :order_dir='DESC' THEN price END DESC
LIMIT :limit 
OFFSET :offset

it will become:

ORDER BY
   price ASC,
   NULL DESC

or:

ORDER BY
   NULL ASC,
   price DESC

score 0 · Answer 2 · edited May 23 '17 at 11:58

0

Unfortunately you can't replace column names with PDO parameters. You have to check the values manually. This is a great SO post answering your question.

edited May 23 '17 at 11:58

Community

1
1

answered Oct 15 '15 at 20:03

Akshendra Pratap

2,002
1
12
25

PDO quote in my request

2 Answers2