phpmysqliprepared-statement

How to make a fully dynamic prepared statement using mysqli API?

I need to change this query to use a prepared statement. Is it possible?

The query:

$sql = "SELECT id, title, content, priority, date, delivery FROM tasks " . $op . " " . $title . " " . $content . " " . $priority . " " . $date . " " . $delivery . " ORDER BY " . $orderField . " " . $order . " " . $pagination . "";

Before the query, there's code to check the POST variables and change the content of variables in the query.

//For $op makes an INNER JOIN with or without IN clause depending on the content of a $_POST variable
$op = "INNER JOIN ... WHERE opID  IN ('"$.opID."')";
//Or
$op = "INNER JOIN ... ";

//For $title (depends of $op):
$title = "WHERE title LIKE'%".$_POST["title"]."%'";
//Or
$title = "AND title LIKE'%".$_POST["title"]."%'";

//For $content:
$content = "AND content LIKE '%".$_POST["content"]."%'";

//For $priority just a switch:
$priority = "AND priority = DEPENDING_CASE";

//For $date and $delivery another switch 
$d = date("Y-m-d", strtotime($_POST["date"]));
$date = "AND date >= '$d' 00:00:00 AND date <= '$d' 23:59:59";
//Or $date = "AND date >= '$d' 00:00:00";
//Or $date = "AND date <= '$d' 23:59:59";

//For $orderField
$orderField = $_POST["column"];

//For $order
$order= $_POST["order"];

//For $pagination 
$pagination = "LIMIT ".$offset.",". $recordsPerPage;

How I could do this query using prepared statement?

A full example of query would be like this (depending of $_POST checks):

SELECT id, title, content, priority, date, delivery FROM tasks INNER JOIN op ON task.op = op.opId WHERE op IN (4851,8965,78562) AND title LIKE '%PHT%' AND content LIKE '%%' AND priority = '2' ORDER BY date DESC LIMIT 0, 10
Solution

  • An excellent question. And thank you for moving to prepared statements. It seems that after all those years of struggle, the idea finally is starting to take over.

    Disclaimer: there will be links to my own site because I am helping people with PHP for 20+ years and got an obsession with writing articles about most common issues.

    Yes, it's perfectly possible. Check out my article, How to create a search filter for mysqli for the fully functional example.

    For the WHERE part, all you need is to create two separate arrays - one containing query conditions with placeholders and one containing actual values for these placeholders, i.e:

    WHERE clause

    $conditions = [];
$parameters = [];

if (!empty($_GET["content"])) {
    $conditions[] = 'content LIKE ?';
    $parameters[] = '%'.$_GET['content ']."%";
}

    and so on, for all search conditions.

    Then you could implode all the conditions using AND string as a glue, and get a first-class WHERE clause:

    if ($conditions)
{
    $where .= " WHERE ".implode(" AND ", $conditions);
}

    The routine is the same for all search conditions, but it will be a bit different for the IN() clause.

    IN() clause

    is a bit different as you will need more placeholders and more values to be added:

    if (!empty($_GET["opID"])) {
    $in  = str_repeat('?,', count($array) - 1) . '?';
    $conditions[] = "opID IN ($in)";
    $parameters = array_merge($parameters, $_GET["opID"]);
}

    this code will add as many ? placeholders to the IN() clause as many elements in the $_GET["opID"] and will add all those values to the $parameters array. The explanation can be found in the adjacent article in the same section on my site.

    After you are done with WHERE clause, you can move to the rest of your query

    ORDER BY clause

    You cannot parameterize the order by clause, because field names and SQL keywords cannot be represented by a placeholder. So you will need another approach called whitelisting. First define a white list with allowed values

    $sortColumns = ["title","content","priority"]; // add your own

    and then check input values against them

    if (!in_array($_GET["column"], $sortColumns)) { 
    throw new RuntimeException("Invalid order by value"); 
}

    The actual action may vary but in any case it should make sure that no invalud value makes it in the query.

    LIMIT clause

    LIMIT values are perfectly parameterized so you can just add them to the $parameters array:

    $limit = "LIMIT ?, ?";
$parameters[] = $offset;
$parameters[] = $recordsPerPage;

    The final assembly

    In the end, your query will be something like this

    $sql = "SELECT id, title, content, priority, date, delivery 
        FROM tasks INNER JOIN ... $where ORDER BY `$orderField` $order $limit";

    And it can be executed using the following code

    $stmt = $mysqli->prepare($sql);
$stmt->bind_param(str_repeat("s", count($parameters)), ...$parameters);
$stmt->execute();
$data = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);

    where $data is a conventional array contains all the rows returned by the query.

    Note that I am using $_GET array because GET method is preferred (and even prescribed by HTTP standard) for search functionality.