$db->query with "LIMIT ? OFFSET ?" not working.

[larry.giroux@qcdatabase.com]

This query will not work if I try to bind the limit and the offset. I works fine if I type in 'LIMIT 20 OFFSET 0'.

Code:

$q = 'SELECT *, DATE_FORMAT(l.date, "%m/%d/%Y") as `date`
FROM db_listings l 
WHERE l.omit=0 
AND l.closed=0 
ORDER BY l.id DESC 
LIMIT ? OFFSET ?;';

This line binds the variables.

Code:

$db->query($q,array($arr['lim'],$arr['off']));

I had to go into the query method of the userspice DB class and add a line...

Code:

public function query($sql, $params = array()){
    $this->_pdo->setAttribute( PDO::ATTR_EMULATE_PREPARES, false ); /* ADDED LINE */

Now it works fine. I cannot figure out why it won't work otherwise. I am using uniform server with php 7.1.1 and MySQL 5.6.35. I am new to PDO. I never had issues like this with mysqli. Is this something I am doing wrong?

[mudmin]

@Brandin might have better info on this, but I don't normally bind limits and offsets. I tend to do something like this
$limit = 7;
$offset = 2;
$db->query("SELECT *, DATE_FORMAT(l.date, "%m/%d/%Y") as `date`
FROM db_listings l
WHERE l.omit=0
AND l.closed=0
ORDER BY l.id DESC LIMIT $limit OFFSET $offset")->results();

Due to the fact that there isn't really a sql injection vector (that I know of) in PDO for Limits and offsets. But Brandin is your guy when it comes to SQL.

[Brandin]

Anytime we've had to do limit and offsets you cannot bind the variables. Its probably something we can resolve with the solution you provided, but currently the DB class we have built does not support the binding of those.

Brandin.

[larry.giroux@qcdatabase.com]

Anytime we've had to do limit and offsets you cannot bind the variables. Its probably something we can resolve with the solution you provided, but currently the DB class we have built does not support the binding of those.

Brandin.

Thanks for the quick response. I don't know how that will affect security, I just copied that line of php from stack. This stuff is a little over my head but I am now reading up on it. Love userspice BTW, it is working out great for a project I had to put together fast.

[mudmin]

Yeah. The security/binding part is kind of difficult to explain. I just try to follow best practices. Glad you're enjoying userspice. Seriousily, don't hesitate to ask if you need any help.