The following warnings occurred:
Warning [2] Undefined variable $unreadreports - Line: 26 - File: global.php(961) : eval()'d code PHP 8.2.25 (Linux)
File Line Function
/global.php(961) : eval()'d code 26 errorHandler->error
/global.php 961 eval
/printthread.php 16 require_once



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

+- UserSpice (https://userspice.com/forums)
+-- Forum: Support Center (https://userspice.com/forums/forumdisplay.php?fid=23)
+--- Forum: UserSpice 4.3 and Below (https://userspice.com/forums/forumdisplay.php?fid=26)
+--- Thread: $db->query with "LIMIT ? OFFSET ?" not working. (/showthread.php?tid=1193)

$db->query with "LIMIT ? OFFSET ?" not working. - larry.giroux@qcdatabase.com - 10-28-2018

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?

RE: $db->query with "LIMIT ? OFFSET ?" not working. - mudmin - 10-28-2018

@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.

RE: $db->query with "LIMIT ? OFFSET ?" not working. - Brandin - 10-28-2018

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.

RE: $db->query with "LIMIT ? OFFSET ?" not working. - larry.giroux@qcdatabase.com - 10-28-2018

(10-28-2018, 05:42 PM)Brandin Wrote: 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.

RE: $db->query with "LIMIT ? OFFSET ?" not working. - mudmin - 10-29-2018

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.