Building a dynamic PHP prepared statement from user entry

Question

I have a database with student records, about 50 or so columns, all of which need to be searchable. I'm familiar with how to build a dynamic search query by appending fields. I can't seem to find any examples on searching a MySQL database using prepared statements that are dynamic in nature. For example, when searching you will only enter the fields you need to search by, so therefore, how do you build a dynamic prepared statement based on user input?

Here is an example of how to do it without prepared statements, however this is prone to SQL injection. I would need to "clean" each variable with a function. However, with a prepared statement this would be much more secure.

function clean($con,$var){
    $var = trim($var);
    $var = strtolower($var);
    $var = mysqli_real_escape_string($con,$var);
    return $var;
}

if(isset($_REQUEST['sub'])){

    if(isset($_REQUEST['student_fname'])){
        $student_fname = null;
        if($_REQUEST['student_fname'] != ''){
            $student_fname = '&& `student_fname` = \''.clean($con,$_REQUEST['student_fname']).'\' ';
        }
    }
    if(isset($_REQUEST['student_lname'])){
        $student_lname = null;
        if($_REQUEST['student_lname'] != ''){
            $student_lname = '&& `student_lname` = \''.clean($con,$_REQUEST['student_lname']).'\' ';
        }
    }

    $sql = "SELECT * FROM `student_records` 
    WHERE 1=1 
    $student_fname
    $student_lname
    ORDER BY class ASC, student_lname ASC";
    echo $sql;
}

note: there would ultimately be many more fields, this example just shows 1 — drooh, May 14 '19 at 16:49
check this https://stackoverflow.com/questions/18527659/php-mysqli-prepared-statement-like — Mohit Kumar, May 14 '19 at 16:49
@mohit that example does not show how to dynamically build a prepared statement..? — drooh, May 14 '19 at 16:52
How are the form field values going to be used in the query? As ANDed values or ORed values? Regardless you will need to go through each of the possible form values that could be entered and build up the query string as you check each possible input. Please [edit] your question and include an example of how your query would be with at least 2 or 3 values. — Dave, May 14 '19 at 16:59

score 6 · Answer 1 · edited Oct 22 '20 at 15:54

As with many things, this would be a LOT easier with PDO. But, just off the top of my head this is how I would approach it. You need to build 3 structures here. The SQL, the list of parameter types ("s" or "i") and the list of parameters themselves. This is all pretty straightforward.

Getting this into the bind_param() function can be a bit tricky if you haven't done it before, but as detailed elsewhere the argument unpacking operator works nicely, once you have all your arguments into an array.

For your POST variables, remember that empty() checks if the array element is present, and also if it's non-empty. This will save you an extra check, with the only caveat being that empty("0") === true.

<?php
if (isset($_REQUEST['sub'])) {
    $types = "";
    $where = [];
    $params = [];

    if (!empty($_REQUEST['student_fname'])) {
        $types .= 's';
        $where[] = 'student_fname = ?';
        $params[] = $_REQUEST['student_fname'];
    }
    if (!empty($_REQUEST['student_lname'])) {
        $types .= 's';
        $where[] = 'student_lname = ?';
        $params[] = $_REQUEST['student_lname'];
    }

    if (count($where)) {
        $where = "AND " . implode(" AND ", $where);
    } else {
        $where = "";
    }

    $sql = "SELECT * FROM student_records
    WHERE 1=1 
    $where
    ORDER BY class ASC, student_lname ASC";

    $stmt = $con->prepare($sql);
    $stmt->bind_param($types, ...$params);
    $stmt->execute();
    while ($res = $stmt->get_result()) {
        // ....
    }
}

Note that this code allows someone to list the entire database; if that isn't your intention, wrap everything in the if (count($where)) statement.

In PDO, things are much easier, no binding needed and parameters are passed as an array already.

<?php
if(isset($_REQUEST['sub'])){
    $where = [];
    $params = [];

    if(!empty($_REQUEST['student_fname'])) {
        $where[] = 'student_fname = ?';
        $params[] = $_REQUEST['student_fname'];
    }
    if(!empty($_REQUEST['student_lname'])){
        $where[] = 'student_lname = ?';
        $params[] = $_REQUEST['student_lname'];
    }

    if (count($where)) {
        $where = "AND " . implode(" AND ", $where);
    } else {
        $where = "";
    }

    $sql = "SELECT * FROM student_records
    WHERE 1=1 
    $where
    ORDER BY class ASC, student_lname ASC";

    $stmt = $con->prepare($sql);
    $stmt->execute($params);
    $data = $stmt->fetchAll(\PDO::FETCH_ASSOC);
}

I believe you need an array of references for call user func. Argument unpacking is much more useful here — Your Common Sense, May 14 '19 at 20:36
That does trigger some long-lost memory (it's been a long time since I've used mysqli for anything other than answering SO questions.) — miken32, May 14 '19 at 20:40

Building a dynamic PHP prepared statement from user entry

1 Answers1

Related