Myslqi stmt bind param not working properly

Question

I'm new to the new PHP 5 mysqli but I've worked a lot with the old fashioned way.

So here is my code

$query = "SELECT `id`, `catn`, `name`, `name_en`, `image`, `price`, `old_price`, `cat` FROM `products` WHERE `show` = 'Yes'";
        if (!empty($order)) {
            $params[0] = $params[0] . "s";
            $query = $query . " ORDER BY ? ".$way;
            $params[] = $order;
        }
        $stmt = $this->db->prepare($query);

        if (strnatcmp(phpversion(),'5.3') >= 0) //Reference is required for PHP 5.3+
        {
            $refs = array();
            foreach($params as $key => $value)
                $refs[$key] = &$params[$key]; 
        } 

        if ($stmt) {
            call_user_func_array(array($stmt, 'bind_param'), $refs);

            $stmt->execute();
... more code below

The $way var is alway ASC or DESC. But the query is giving me results sorted by the id (like there is no ORDER BY statement). So the query is write before the bind_param so I guess the params are not binding properly ? Can you tell me where the mistake maight be

score 0 · Accepted Answer · answered Nov 06 '13 at 15:51

0

I don't think that you are using bind_param properly since you are excluding the $types argument, which must come first. However, these are used for parameters (e.g. the value for show = ?). You would not use parameters for parts of the query structure such as keywords. You have to concatenate the string to the query itself. If you are worried about security, do the concatenation explicitly, e.g.

if ($way == 'ASC') {
    $query .= "ASC";
}
else {
    $query .= "DESC";
}

bind_param will add quotes around the parameter making the query invalid (ORDER BY 'ASC')

answered Nov 06 '13 at 15:51

Explosion Pills

183,406
48
308
385

The `?` is there to name the field by which I want to sort my results .. is this still incorrect ? I'll try now what you said :) – Bankin Nov 06 '13 at 15:53
@Bankin I understand now. Yes, it's a similar issue. You can't use `?` for MySQL constructs like keywords, tables, columns, etc. – Explosion Pills Nov 06 '13 at 15:57

score 0 · Answer 2 · edited May 23 '17 at 10:32

0

If you compose your ORDER BY clause with a parameter you'll be searching for fixed literal strings, e.g. you'll get this:

ORDER BY 'price' DESC

... rather than this:

ORDER BY price DESC

You cannot use parameters to provide identifiers.

edited May 23 '17 at 10:32

Community

1
1

answered Nov 06 '13 at 15:56

Álvaro González

135,557
38
250
339

Myslqi stmt bind param not working properly

2 Answers2