pagination working in mysl_deprecated but not working on PDO

[mutago]

The pagination code below works well in mysql_deprecated as follows

<?php

require('database.php');
									if (isset($_GET['chmsc']))
									{
									$chmsc=$_GET['chmsc'];
                                                                    $chmsc=strip_tags($chmsc);
                                                                 $chmsc=htmlentities($chmsc, ENT_QUOTES, "UTF-8");
									}
									else
										$chmsc=1;
									?>


<?php

$per_page=strip_tags(2);
									
				$total_pages=htmlentities(ceil($rows/$per_page), ENT_QUOTES, "UTF-8");
				echo"page $chmsc of $total_pages<br>";
										
										if($chmsc!=1)
										{
			echo "<a href='home.php?chmsc=1'>First </a>","  ";
			$previous=htmlentities($chmsc-1, ENT_QUOTES, "UTF-8");
			echo "<a href='home.php?chmsc=$previous'> Previous</a>", "  ";
										}
										if (($chmsc!=1) && ($chmsc!=$total_pages))
										echo "||";
										if($chmsc!=$total_pages)
										{
		$next=htmlentities($chmsc+ 1, ENT_QUOTES, "UTF-8");
		echo "<a href='home.php?chmsc=$next'>Next </a>","  ";
		echo "<a href='home.php?chmsc=$total_pages'> Last</a>";
										}
										echo "<br/><br/>";
			$x=($chmsc-1)*$per_page;
					


$query  = "SELECT *,UNIX_TIMESTAMP() - date_created AS TimeSpent FROM post WHERE (username = '$user') ORDER BY post_id DESC limit $x, $per_page";
$result = mysql_query($query);

while($row = mysql_fetch_assoc($result)){	
// continue			

?>

When i tried to move it to PDO for sql injection protection it does not fetch any records from database below is the code

<?php
	require('database.php');

									if (isset($_GET['chmsc']))
									{
									$chmsc=$_GET['chmsc'];
                                                                    $chmsc=strip_tags($chmsc);
                                                                 $chmsc=htmlentities($chmsc, ENT_QUOTES, "UTF-8");
									}
									else
										$chmsc=1;
									?>

<?php

$per_page=strip_tags(2);
									
				$total_pages=htmlentities(ceil($rows/$per_page), ENT_QUOTES, "UTF-8");
				echo"page $chmsc of $total_pages<br>";
										
										if($chmsc!=1)
										{
			echo "<a href='home.php?chmsc=1'>First </a>","  ";
			$previous=htmlentities($chmsc-1, ENT_QUOTES, "UTF-8");
			echo "<a href='home.php?chmsc=$previous'> Previous</a>", "  ";
										}
										if (($chmsc!=1) && ($chmsc!=$total_pages))
										echo "||";
										if($chmsc!=$total_pages)
										{
		$next=htmlentities($chmsc+ 1, ENT_QUOTES, "UTF-8");
		echo "<a href='home.php?chmsc=$next'>Next </a>","  ";
		echo "<a href='home.php?chmsc=$total_pages'> Last</a>";
										}
										echo "<br/><br/>";
			$x=($chmsc-1)*$per_page;
					



$result = $db->prepare('SELECT *,UNIX_TIMESTAMP() - date_created AS TimeSpent FROM post WHERE username = :username ORDER BY post_id DESC limit $x, $per_page');
		$result->execute(array(
			':username' => $user
		));

// continue

?>

I discovered that the problem is in the sql query statement

$result = $db->prepare('SELECT *,UNIX_TIMESTAMP() - date_created AS TimeSpent FROM post WHERE username = :username ORDER BY post_id DESC limit $x, $per_page');

if i used query statement like this

$result = $db->prepare('SELECT *,UNIX_TIMESTAMP() - date_created AS TimeSpent FROM post WHERE username = :username ORDER BY post_id DESC ');

it will query the records but it will not pagenate it. when i added the variables
$x, $per_page
it will fetch empty page. any help please

[mutago]

i have tried this but the query still displays empty page

$result = $db->prepare('SELECT *,UNIX_TIMESTAMP() - date_created AS TimeSpent FROM post WHERE username = :username ORDER BY post_id DESC LIMIT :X, :per_page');
        $result->execute(array(
            ':username1' =>$user,
                        ':x' => $x,
                         ':per_page' => $per_page
                    ));

The problem is from the query, any help

[traq]

First off, if you are getting "blank pages" when there is an error, that means you have disabled error reporting. During development, you need to make sure error reporting is enabled. Likewise, you need to check the errors coming from MySQL to see what is going wrong. There is no point in guessing (or in asking us to guess).

error_reporting(-1);
ini_set('display_errors',1); 

The relevant method in PDO is PDO::errorInfo().

Easiest solution:

$result = $db->prepare('SELECT *,UNIX_TIMESTAMP() - date_created AS TimeSpent FROM post WHERE username = :username ORDER BY post_id DESC limit $x, $per_page'); 

Your query is in single quotes, so the variables are not being interpolated. If you use double-quotes, this should work just fine. You need to be absolutely sure that $x and $per_page are integers—for example, by using $x = intval( $x ) before putting it into the SQL statement. Otherwise, you will create an SQL Injection vulnerability.

Harder solution:
Ask if you can't get the above to work. As an explanation, PDO has a "feature" (of questionable merit) called "emulated prepared statements." This means that, when enabled, PDO will only pretend to prepare your statement instead of actually passing it to the DB to be prepared. PDO has troubles sometimes correctly emulating non-string values.

It's a complex fix. I don't really follow it too well myself. This is another reason I prefer MySQLi.

[mutago]

Traq you are crazily good. Thank you

I have one more question. To prevent sql injection vulnerabilty, you suggested i used intval(). i hope below is the correct way of using the functions before passing it to sql queries

$x=intval(($chmsc-1)*$per_page);

[\\.\]

Best way of guarding against injection IMHO is to operate a whitelist and should you find that your $_GET or $_POST arrays have more fields than they should, that is a good sign that someone is possibly pushing payloads in the expectation of them being executed.

Basically you first line of defense will be to limit your exposure to the outside world.

$whitelist = array(
"start",
"end",
"id",
"string"
); 

under the old school methods would represent via the array keys the fields I would allow from a particular source, for instance if these were to a POST variable then I would have used

$clean_POST = array();
foreach($whitelist as $key) 
    $clean_POST[$key] = sanitizing_functions( $_POST[$key] ); 

where the sanitizing_functions() function would be what you wanted to do to your data stream to clean it, you then know that anything you use in that $clean_POST array is clean, you will find that many scripts made by people will clean the $_POST array and push the cleaned data straight back in to itself, this is for obvious reason bad practice.

With a change in the way in which databases are more protected against injection methods, you can still use the whitelist as a way of shoring up defenses, for example if you checked the number of elements in a $_POST array against an accepted white list and that certain fields were present / set and you found that you had a couple of additional fields that you wouldn't normally have or be expecting or a certain field was not present that should be present, you should treat that users IP as a phishing trip or trawler for weaknesses in scripts.

So don't rule out entirely that your visitors are honorable and trust worthy for a single moment, to let ones guard down is not only silly but also dangerous.

[mutago]

ok but as long as html is not allowed in the form inputs i always like using strip_tags to prevent html injections or further sanitize variables using htmlspecialchars. what do u have to say

thank you

[traq]

To prevent sql injection vulnerabilty, you suggested i used intval(). i hope below is the correct way of using the functions before passing it to sql queries

$x=intval(($chmsc-1)*$per_page); 

Sorry I missed your post! In the case above, you're doing math on the variable, so you can be assure that it's been converted into a number by the time you're done. Therefore, you don't need to do anything more to it:

$x = ($chmsc-1)*$per_page; 

Best way of guarding against injection IMHO is to operate a whitelist and should you find that your $_GET or $_POST arrays have more fields than they should, that is a good sign that someone is possibly pushing payloads in the expectation of them being executed.

Basically you first line of defense will be to limit your exposure to the outside world.

$whitelist = array(
"start",
"end",
"id",
"string"
); 

under the old school methods would represent via the array keys the fields I would allow from a particular source, for instance if these were to a POST variable then I would have used

$clean_POST = array();
foreach($whitelist as $key) 
    $clean_POST[$key] = sanitizing_functions( $_POST[$key] ); 

where the sanitizing_functions() function would be what you wanted to do to your data stream to clean it, you then know that anything you use in that $clean_POST array is clean, you will find that many scripts made by people will clean the $_POST array and push the cleaned data straight back in to itself, this is for obvious reason bad practice.

With a change in the way in which databases are more protected against injection methods, you can still use the whitelist as a way of shoring up defenses, for example if you checked the number of elements in a $_POST array against an accepted white list and that certain fields were present / set and you found that you had a couple of additional fields that you wouldn't normally have or be expecting or a certain field was not present that should be present, you should treat that users IP as a phishing trip or trawler for weaknesses in scripts.

So don't rule out entirely that your visitors are honorable and trust worthy for a single moment, to let ones guard down is not only silly but also dangerous.

While checking the number of fields submitted, using whitelists, and so forth are very good practice, they are not suitable for preventing SQL injection.

Likewise, applying a "sanitizing" function to all user input immediately and indiscriminately is not a good solution either (in fact, it's a bad solution: you'll find many examples of functions that do this online; avoid them! They can lead to a false sense of security (assuming everything is "clean" now), or even open up new vulnerabilities. There is no reason to "roll your own solution" when a proper solution already exists. You won't do a better job). What's important is that you apply the right solution to the right data at the right time. strip_tags, for example, is useless in guarding against SQL injection, just as mysqli_real_escape_string is useless in guarding against XSS attacks.

In my example, the risk of SQL injection exists because we are building an SQL query using variables that ultimately came from the user. As @\\.\ noted, Never Trust User Input. Because the desired values are numbers, our solution is straightforward: make sure they're numbers. If the values needed to be strings, the solution would be to use a prepared statement instead of interpolating the variables. (Prepared statements are always the correct answer, BTW; because they allow the database to know exactly which parts are data and which are commands. The fact that it doesn't work in this specific case is unfortunate.)

[\\.\]

The point I was making is that you are doing yourself a favor by only accepting inputs that you are expecting to get from a data stream / push.

If you have a data stream being pushed and you have fields which are username, password and you are missing submit, then you can challenge this by either ignoring the push and not do anything or return a page that is the index page or error page, etc.

If I produce a forward facing page that accepts a user input, I like to dope the page and also flavor it with a bit of salt too, none of that doping and salting can be replicated off the originating server but the server can easily reconstruct the salt and dismiss the form if its out of time / date or contains data in fields that should be empty.

A simple tool on the frontline to reject a form or data push can be a big benefit to the server and its security.

While I agree that some sanitizing methods are worthless, some function in PHP are worth using because its not just about injection its about not allowing the PHP script to be broken which would allow malicious code to be run.

[mutago]

thank u so much