PDO syntax or access violation error with pagination script

Question

The code below works but if I type in some combinations such as index.php?page_no (wihtout a page #), or page_no=0 (zero that is, but page_no=1 and everything above works), or if I type in more than 19 numbers (e.g., 22222222222222222222) in the url (after index.php?page_no=) I get the following sort of error(s):

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '5.55555555556E+19,5' at line 1' in

or

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-5,5' at line 1' in

My code is as follows:

Pagination:

<?php
class pager {
    private $db;

    function __construct($DB_con) {
        $this->db = $DB_con;
    }

    public function dataview($query)
    {
        $stmt = $this->db->prepare($query);
        $stmt->bindParam(passport, $_GET['passport'], PDO::PARAM_INT);
        $stmt->execute();
        if ($stmt->rowCount() > 0) {
            while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
                ?>

                // some html here and some echo of columns

                <?php
            }
        }
    }

    public function pagers($query,$records_per_page) {
        $starting_position=0;
        if(isset($_GET["page_no"])) {
            $starting_position=($_GET["page_no"]-1)*$records_per_page;
        }
        $query2=$query." limit $starting_position,$records_per_page";
        return $query2;
    }

    public function pagerslink($query,$records_per_page)
    {
        $self = $_SERVER['PHP_SELF'];
        $stmt = $this->db->prepare($query);
        $stmt->bindParam(passport, $_GET['passport'], PDO::PARAM_INT);
        $stmt->execute();
        $total_no_of_records = $stmt->rowCount();
        if($total_no_of_records > 0)
        {
            ?><tr><td colspan="7"><?php
            $total_no_of_pages=ceil($total_no_of_records/$records_per_page);
            $current_page=1;
            if(isset($_GET["page_no"]))
            {
                $current_page=$_GET["page_no"];
            }
            if($current_page!=1) {
                $previous =$current_page-1;
                echo "<a href='".$self."?page_no=1'>First</a>&nbsp;&nbsp;";
                echo "<a href='".$self."?page_no=".$previous."'>Previous</a>&nbsp;&nbsp;";
            }
            $x="";
            for($i=1;$i<=$total_no_of_pages;$i++) {
                if($i==$current_page) {
                    $x.= "<strong><a href='".$self."?page_no=".$i."' 
style='color:red;text-decoration:none'>".$i."</a></strong>&nbsp;&nbsp;";
                }
                elseif ($i>6 && $i!=$total_no_of_pages) {
                    $x.= ".";
                }
                else {
                    $x.= "<a href='".$self."?page_no=".$i."'>".$i."</a>&nbsp;&nbsp;";
                }
            }
            echo $x;
            if($current_page!=$total_no_of_pages)
            {
                $next=$current_page+1;
                echo "<a href='".$self."?page_no=".$next."'>Next</a>&nbsp;&nbsp;";
                echo
                    "<a href='".$self."page_no=".$total_no_of_pages."'>Last</a>&nbsp;&nbsp;";
            }
        }
    }
}

Index:

<?php
$query = "SELECT * FROM view-i-created ORDER BY passport DESC";
$records_per_page = 5;
$newquery = $pager->pagers($query,$records_per_page);
$pager->dataview($newquery);
$pager->pagerslink($query,$records_per_page);  
?>

Part of my Config file (I added the first line recently, didn't help):

$DB_con->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$DB_con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

I've been working around the clock, 24-7, on this for a number of days now. I didn't even know I had this problem until a couple of weeks ago when I stumbled upon the problem myself.

If anyone reading here can direct me in the right direction I would very much appreciate it. Thanks!

-5 is not valid (you can't go below 0), and 16 characters is too high. Check your input before blindly using it. — aynber, Feb 17 '16 at 15:26
That looks like the typical result of treating a value that is larger than the max integer value the platform can handle as a number, which means it will be converted to float, loose precision, and can result in the exponential notation being used, when you transfer it back into a string context. // Is this an _actual_ problem? Do you have that many records, that passing this high a page number would make sense? I hardly doubt it. So I’d recommend you rather implement a value check for the passed page number, and reject it if it is out of sensible bounds. — CBroe, Feb 17 '16 at 15:28
“when you transfer it back into a string context” – btw., why are you doing that? You are using prepared statements already, so why aren’t you using placeholders for the LIMIT values as well? — CBroe, Feb 17 '16 at 15:30
@aynber I will positively be more thorough for now on, no more blindness for me — Luke NB, Feb 17 '16 at 16:13
@CBroe You are correct, I absolutely do not have so many records that I'd need such a high page number. Thank you for your direction — Luke NB, Feb 17 '16 at 16:20

score 2 · Accepted Answer · answered Feb 17 '16 at 15:30

2

At a glance it looks like two sides of the same issue: An invalid argument for the SQL 'LIMIT' command.

At one end, you supply negative values:

$starting_position=($_GET["page_no"]-1)*$records_per_page;

$query2=$query." limit $starting_position,$records_per_page";

When 'page_no' is 0, then the limit ends up as "LIMIT -5,5", which is invalid. To fix this, use something like this instead:

$limit_bounded = min(max(0, $starting_position), PHP_INT_MAX); // Bounded between 0 and PHP_INT_MAX, which is ~2147000000.
$query2=$query." limit $limit_bounded,$records_per_page";

At the high end, you could either be seeing an integer overflow, or just using too many characters for the upper bound of a limit.

As a security issue, you should escape arguments you use in SQL queries, especially when you are receiving them from $_GET, in order to prevent SQL injections (Someone malicious could request " ' DROP TABLES" or similar as the 'page_no' variable and cause issues for you.)

answered Feb 17 '16 at 15:30

Hiphop03199

729
3
16

1

thank you tremendously. Your answer is extremely effective. After reading your answer I now understand to a greater degree about SQL LIMIT command and invalid/valid limits. Thanks also for reminder about the security issues (SQL injections). I have accepted your answer and additionally I wanted to upvote the answer but since my score is only 3 I don't yet have the ability to do. – Luke NB Feb 17 '16 at 16:03
and regarding the SQL injections, if I used this in pagination - `$stmt = $this->db->prepare($query); $stmt->execute();` and then this in the index - `$query = 'SELECT * FROM view-i-created ORDER BY passport DESC';` would this be safe? – Luke NB Feb 18 '16 at 00:38
Honestly, I don't want to offer you information that might not be entirely inaccurate. In my opinion, using 'prepare()' is a decent enough solution for averagely-secure applications. For an exhaustive explanation though, please check out https://stackoverflow.com/questions/134099/are-pdo-prepared-statements-sufficient-to-prevent-sql-injection – Hiphop03199 Feb 18 '16 at 02:28
1

I'm reviewing the cited thread now. A wealth of information! I am reading more about 1st order injection, 2nd order injection and un-checked dynamic sql. Extensively and exceptionally helpful and useful information regarding PDO prepared statements sufficient and SQL injection. – Luke NB Feb 18 '16 at 03:18

PDO syntax or access violation error with pagination script

1 Answers1