Using dynamic filter from Variable on Prepared Statements [duplicate]

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
– bassxzero, Commented Oct 30, 2017 at 17:06
@bassxzero Also, not using prepared statements properly at all. — GrumpyCrouton
– GrumpyCrouton, Commented Oct 30, 2017 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
– Drake Boein, Commented Oct 30, 2017 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
– Drake Boein, Commented Oct 30, 2017 at 17:17
That's because your entire $filter string is going to be quoted when you bind it like that — Patrick Q
– Patrick Q, Commented Oct 30, 2017 at 17:22

Patrick Q · Accepted Answer · 2020-12-31 17:53:07Z

1

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();

edited Dec 31, 2020 at 17:53

answered Oct 30, 2017 at 18:09

Patrick Q

6,3942 gold badges28 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

odan Over a year ago

Do I have to use mysqli_real_escape_string if I bind parameters? stackoverflow.com/a/2284327/1461181

Patrick Q Over a year ago

@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 Over a year ago

@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 Over a year ago

@mickmackusa I removed the calls to mysqli_real_escape_string()

Collectives™ on Stack Overflow

Using dynamic filter from Variable on Prepared Statements [duplicate]

1 Answer 1

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Linked

Related