Adding An Offset To Prepared SQL Statement PDO

Question

I am converting a sql query to prepared statement to protect it from sql injection.

I am trying to use the information from this question

I do not get any errors but no data is received. I even console.log(html);

Originally I was using this (which works well.)

<?php

$db_host = "localhost";
$db_user = "";
$db_pass = "";
$db_name = "";

try
{
     $DB_con = new PDO("mysql:host={$db_host};dbname={$db_name}",$db_user,$db_pass);
     $DB_con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOException $exception)
{
     echo $exception->getMessage();
}
?>

$limit  = (intval($_GET['limit']) != 0) ? $_GET['limit'] : 5;
$offset = (intval($_GET['offset']) != 0) ? $_GET['offset'] : 0;

$sql = "SELECT * FROM wuno_inventory WHERE 1 ORDER BY id ASC LIMIT $limit OFFSET $offset";
try {
    $stmt = $DB_con->prepare($sql);
    $stmt->execute();
    $results = $stmt->fetchAll();
}
catch (Exception $ex) {
    echo $ex->getMessage();
}
if (count($results) > 0) {
    foreach ($results as $res) {
        echo '<tr class="invent">';
        echo '<td>' . $res['wuno_product'] . '</td>';
        echo '<td>' . $res['wuno_alternates'] . '</td>';
        echo '<td>' . $res['wuno_description'] . '</td>';
        echo '<td>' . $res['wuno_onhand'] . '</td>';
        echo '<td>' . $res['wuno_condition'] . '</td>';
        echo '</tr>';
    }
}
?> 

And now that I am trying to make secure as suggested in the answer I referenced above I am doing this,

$limit  = (intval($_GET['limit']) != 0) ? $_GET['limit'] : 5;
$offset = (intval($_GET['offset']) != 0) ? $_GET['offset'] : 0;

$stmt = $DB_con->prepare("SELECT * FROM wuno_inventory WHERE 1 ORDER BY id ASC LIMIT :limit, :offset");
$stmt->bindValue(':limit', (int) trim($_GET['limit']), PDO::PARAM_INT);
$stmt->bindValue(':offset', (int) trim($_GET['offset']), PDO::PARAM_INT);
try {
    $stmt->execute();
    $results = $stmt->fetchAll();
}
catch (Exception $ex) {
    echo $ex->getMessage();
}

No data is received when I change the query to this. How can I bind limit and offset to remove it from the statement and make my query more secure.

I have also tried this,

 $stmt->bindParam(':limit', (int) trim($_GET['limit']), PDO::PARAM_INT);
 $stmt->bindParam(':offset', (int) trim($_GET['offset']),PDO::PARAM_INT);




<script type="text/javascript">
jQuery(document).ready(function($) {
var busy = true;
var limit = 5;
var offset = 0;
var itemID = $("#itemID").val();
var assetPath = "<?php echo $assetPath ?>";
var searchPath = "<?php echo $searchPath ?>"; 

function displayRecords(lim, off) {
  jQuery.ajax({
          type: "GET",
          async: false,
          url: assetPath,
          data: "limit=" + lim + "&offset=" + off,
          cache: false,
          beforeSend: function() {
            $("#loader_message").html("").hide();
            $('#loader_image').show();
          },
          success: function(html) {
console.log(html);
            $("#productResults").append(html);
            $('#loader_image').hide();
            if (html === null) {
             $("#loader_message").html('<button data-atr="nodata" class="btn btn-default" type="button">No more records.</button>').show();
            } else {
console.log(html);
             $("#loader_message").html('Loading... <img src="../wp-content/uploads/2016/02/loading.gif" alt="Loading" alt="Loading">').show();
            }
            window.busy = false;
          }
        });
}

(function($) {
$(document).ready(function() {
if (busy === true) {
  displayRecords(limit, offset);
  busy = false;
}
});
})( jQuery );



(function($) {
$(document).ready(function() {
$(window).scroll(function() {
          if ($(window).scrollTop() + $(window).height() > $("#productResults").height() && !busy) {
            offset = limit + offset;
         displayRecords(limit, offset);

          }
});
});
})( jQuery );
});
</script>

HTML

<table id="prods" class="display table center-table" width="100%" >
                <thead>  
                        <tr>  
                            <th>Product #</th>  
                            <th>Alternate #</th>  
                            <th>Description</th>  
                            <th>On Hand</th>  
                        <th>Condition</th>
                        </tr>  
                    </thead>  

                <tbody id="productResults"> 

                </tbody>

            </table>

Answer 1

Do all your variable manipulation prior to the bindValue calls. (Trim is probably unnecessary as you're casting to an int.)

try
{
     $DB_con = new PDO("mysql:host={$db_host};dbname={$db_name}",$db_user,$db_pass);
     $DB_con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOException $exception)
{
     echo $exception->getMessage();
}

$limit  = (intval($_GET['limit']) != 0) ? (int) $_GET['limit'] : 5;
$offset = (intval($_GET['offset']) != 0) ? (int) $_GET['offset'] : 0;


try {
    $stmt = $DB_con->prepare("SELECT * FROM wuno_inventory LIMIT :limit OFFSET :offset");
    $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
    $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
    $stmt->execute();
    $results = $stmt->fetchAll();
}
catch (Exception $ex) {
    echo $ex->getMessage();
}
if (count($results) > 0) {
    foreach ($results as $res) {
        echo '<tr class="invent">';
        echo '<td>' . $res['wuno_product'] . '</td>';
        echo '<td>' . $res['wuno_alternates'] . '</td>';
        echo '<td>' . $res['wuno_description'] . '</td>';
        echo '<td>' . $res['wuno_onhand'] . '</td>';
        echo '<td>' . $res['wuno_condition'] . '</td>';
        echo '</tr>';
    }
}