PDO Prepared Statement with % and Limit

Question

I am really new to PDO, but I need to use it to avoid SQL injections.

Here is my SQL Query with the variables I've got via the POST Request and I want to do a PDO with that SQL Query (this version works):

if($refid == "") $refid="%";

$lastOrders = "SELECT * FROM Orders WHERE REFID LIKE'$refid' 
               ORDER BY dateAdded DESC LIMIT 0,$limiter";
$ps_orders = $db->query($lastOrders);
$data = $ps_orders->fetchAll();

My problem(s): The given $refid is either a number which I can find in the database, or it isn't specified by the POST Request (so the value is $refid="") and in that case I want to set the WHERE part to this: WHERE REFID LIKE '%', so I can see in that case all results for every "RefID". Is there a way to do it that way, or do I really need to create 2 different SQL Queries for both cases ?

My try (doesn't work can't figure out what the exactly problem is):

if($refid == "") $refid="%";

$sql = "SELECT * FROM Orders WHERE REFID LIKE :refid
        ORDER BY dateAdded DESC LIMIT :min,:max";
$ps_orders = $db->prepare($sql);
$ps_orders->bindParam(':refid', $refid, PDO::PARAM_STR);
$ps_orders->bindParam(':min', 0, PDO::PARAM_INT);
$ps_orders->bindParam(':max', (int)$limiter, PDO::PARAM_INT);
$ps_orders->execute();
$data = $ps_orders->fetchAll();

As I described the $refid is set by a POST Request: $refid = $_POST['refid']; — kentor, Aug 26 '14 at 22:01

score 1 · Accepted Answer · answered Aug 26 '14 at 21:34

bindParam requires a variable reference so it won't work with as

bindParam(":min", 0, PDO::PARAM_INT)

You could create one like $zero = 0, but this seems unnecessary so you can just use bindValue instead. Same for (int)$limiter which becomes a value rather than a variable.

PDO Prepared Statement with % and Limit

1 Answers1