Using dynamic filter from Variable on Prepared Statements

Question

I have problem with MYSQLI query prepared statements, I would like to filter out code based on post value.

Here is my code

    $arrfilter = array();
    if(!empty($_POST['website'])){
        $website = $conn->real_escape_string($_POST['website']);
        array_push($arrfilter,"website='$website'");
    }
    if(!empty($_POST['gamename'])){
        $gamename = $conn->real_escape_string($_POST['gamename']);
        array_push($arrfilter,"gamename='$gamename'");
    }
    if(!empty($_POST['action'])){
        $action = $conn->real_escape_string($_POST['action']);
        array_push($arrfilter,"action='$action'");
    }
    if(count($arrfilter) > 0){
        $filter = implode(' and ',$arrfilter);
    }
    $fdate= "%$date%";
    $sql = "SELECT url,referrer,ip,user_agent,action,date FROM cpa_track WHERE date LIKE ? and ? ORDER BY date DESC";
        $process = $conn->prepare($sql);
        $process->bind_param('ss',$fdate,$filter);
        $process->execute() or die("Error: ".$conn->e

rror);
    $process->store_result();

My achievement is to use $filter as dynamic filter on Mysqli prepared statements. How can I do this as I received this

Fatal error: Call to a member function bind_param() on a non-object

`SELECT url,referrer,ip,user_agent,action,date FROM cpa_track WHERE date LIKE ? %$date% ORDER BY date DESC` is not valid sql — bassxzero, Oct 30 '17 at 17:06
@bassxzero Also, not using prepared statements properly at all. — GrumpyCrouton, Oct 30 '17 at 17:07
@bassxzero. I have put %$date% as bind_param anyway. I just need to fill out $filter value in the conditional. — Drake Boein, Oct 30 '17 at 17:08
how about this $sql = "SELECT url,referrer,ip,user_agent,action,date FROM cpa_track WHERE date LIKE ? and ? ORDER BY date DESC"; $process = $conn->prepare($sql); $process->bind_param('ss',$fdate,$filter); I just got an empty data. — Drake Boein, Oct 30 '17 at 17:17
That's because your entire `$filter` string is going to be quoted when you bind it like that — Patrick Q, Oct 30 '17 at 17:22
@PatrickQ is there anything I can do to achieve this. My achievement is to use filter based on the post input. If it is not empty, then the filter would be included on $sql. — Drake Boein, Oct 30 '17 at 17:23
You could use the bind_param method like this: `$stmt->bind_param($types, ...$params);` Source: https://stackoverflow.com/a/36457865/1461181 — odan, Oct 30 '17 at 21:09

Patrick Q · Answer 1 · 2020-12-31T17:53:07.010

I put this together using the code at the end of this comment on the manual page for bind_param(). I use PDO, not mysqli, so I haven't actually tested this. The logic seems to be sound though. The concept is that you incrementally build the parameters that will be sent to bind_params() and then use reflection to apply the constructed values. Just FYI, I find doing this far less complicated with PDO thanks to the ability to use named parameters and call bindValue() multiple times.

$conditions = "";
$types = "s";
$values = array($fdate);

if(!empty($_POST['website'])){
    $conditions .= " AND website = ?";
    $types .= "s";
    $values[] = $_POST['website'];
}
if(!empty($_POST['gamename'])){
    $conditions .= " AND gamename = ?";
    $types .= "s";
    $values[] = $_POST['gamename'];
}
if(!empty($_POST['action'])){
    $conditions .= " AND action = ?";
    $types .= "s";
    $values[] = $_POST['action'];
}

$bindArray = array($types);

foreach($values as $value)
{
    $bindArray[] = $value;
}

$fdate= "%$date%";
$sql = "SELECT url,referrer,ip,user_agent,action,date FROM cpa_track WHERE date LIKE ? $conditions ORDER BY date DESC";
$process = $conn->prepare($sql);

$refClass = new ReflectionClass('mysqli_stmt'); 
$refMethod = $refClass->getMethod("bind_param");
$refMethod->invokeArgs($process,$bindArray);

$process->execute() or die("Error: ".$conn->error);
$process->store_result();

Do I have to use `mysqli_real_escape_string` if I bind parameters? https://stackoverflow.com/a/2284327/1461181 — odan, Oct 30 '17 at 20:53
@DanielO. No. I was just copying what OP had. I wouldn't do both. I _would_ suggest maybe doing some sort of requirements-based sanitization/validation of the inputs, but from a technical standpoint, the binding is all you need. — Patrick Q, Oct 30 '17 at 20:59
@mickmackusa What specifically would you like to see? Considering this only has 1 upvote, wasn't accepted as an answer, and is 3+ years old, I can't imagine there's a whole lot of people clamoring to use this as a dupe target. — Patrick Q, Dec 28 '20 at 13:23
@mickmackusa I removed the calls to `mysqli_real_escape_string()` — Patrick Q, Dec 31 '20 at 17:53

Using dynamic filter from Variable on Prepared Statements

1 Answers1