PDO: Invalid parameter number: mixed named and positional parameters

Question

I have come across this warning I've not seen before:

Warning: PDOStatement::execute() [pdostatement.execute]: SQLSTATE[HY093]: Invalid parameter number: mixed named and positional parameters in...

Referring to the following PDO query (have simplified the function for ease of reading):

$offset = 0;
$limit = 12;
function retrieve_search_posts($searchfield, $offset, $limit){


        $where = array();

        $words = preg_split('/[\s]+/',$searchfield);

        array_unshift($words, '');
        unset($words[0]);

        $where_string = implode(" OR ", array_fill(0,count($words), "`post_title` LIKE ?"));

        $query = "
                                SELECT  p.post_id, post_year, post_desc, post_title, post_date, img_file_name, p.cat_id
                                FROM    mjbox_posts p
                                JOIN    mjbox_images i
                                ON      i.post_id = p.post_id
                                        AND i.cat_id = p.cat_id
                                        AND i.img_is_thumb = 1
                                        AND post_active = 1
                                WHERE $where_string
                                ORDER BY post_date
                                LIMIT :offset, :limit
                                DESC";
        $stmt = $dbh->prepare($query);

        foreach($words AS $index => $word){
            $stmt->bindValue($index, "%".$word."%", PDO::PARAM_STR);
        }
        $stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
        $stmt->bindParam(':limit', $limit, PDO::PARAM_INT);
        $stmt->execute();

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

        return $searcharray;
    }

The function and PDO query works fine without the offset and limit variables included in the query. So what might be causing this warning?

Thanks

Wouldn't it be the fact that you're mixing named parameters (`:offset`, `:limit`) with positional parameters (`LIKE ?`) as the warning states? — Wiseguy, Apr 08 '13 at 20:24
@MarcB Maybe I am missing something, but where do you see an sql injection hole? — jeroen, Apr 08 '13 at 20:36
the hole is here "WHERE $where_string." $where_string is devised from $searchfield, which I am assuming the user passes in. You should be using a bindParam for this. — CountMurphy, Apr 08 '13 at 20:44
@CountMurphy If you check how that string is built, you'll see it comes from an array containing just `post_title LIKE ?` strings — jeroen, Apr 08 '13 at 20:45
@jeron, looks like you're right. dont know how I missed that. I really shouldn't only skim the code before commenting... — CountMurphy, Apr 08 '13 at 20:47
As jeroen says, I assumed it is okay because I bind the parameter that is used in the `$where_string` variable that is in the query!? — crmepham, Apr 08 '13 at 20:48
@crm: you build your own imploded `$where_string`, then embed it directly in to the sql. you're NOT using bound parameters there. it's direct sql injection. even if the original stuff going into $where_string isn't coming from (say) $_POST, you can STILL inject yourself. **ANY** data going into an sql query can be used for injection, unless you use proper escaping, or placeholders/prepared statements. — Marc B, Apr 08 '13 at 21:02
@MarcB The OP is building `$where_string` concatenating hard-coded `post_title LIKE ?` strings with `OR` strings and then binding his variables to the placeholders. Looks fine to me. — jeroen, Apr 08 '13 at 21:09

score 19 · Accepted Answer · answered Apr 08 '13 at 20:36

Change

LIMIT :offset, :limit

to

LIMIT ?, ?

and

$stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
$stmt->bindParam(':limit', $limit, PDO::PARAM_INT);

to:

$stmt->bindValue($index+1, $offset, PDO::PARAM_INT);
$stmt->bindValue($index+2, $limit, PDO::PARAM_INT);

score 13 · Answer 2 · answered Apr 08 '13 at 20:26

13

in your where_string you use ? that is a positional parameter and in your limit and offset you use : that is a named parameter that is causing the warning don't mix them

answered Apr 08 '13 at 20:26

Miguelo

1,078
6
13

Thanks, do you know how I could use a named parameter in the where_String instead of the positional one or vice versa? – crmepham Apr 08 '13 at 20:36
@crm Either replace the `?` instances with named parameters like `:pattern1`, `:pattern2`, or the named parameters with `?`, and they'll have indexes of count+1 and count+2, where count is the number of patterns being checked. – IMSoP Apr 08 '13 at 20:47

PDO: Invalid parameter number: mixed named and positional parameters

2 Answers2

Linked

Related