[Tutorial] Creating prepared statements with MySQLi
#1
Hey everyone,

This is my first post here but I thought I would share something that I made for my implementation of MyBB from a bit of custom software I have been working on. As many of you may or may not know, the MySQLi extension that is currently implemented in MyBB doesn't support prepared statements. I have built this handy function to solve that issue, for the people who wish to use them.

Step 1:
Start by navigating to your /inc/db_mysqli.php file and opening it in your editing software. Then copy and past the below code into your file anywhere after the opening tag and save the file. This code can most likely be optimized a bit more by people smarter than myself but the jist of the code is you will be able to build a prepared statement and have the function bind your unsanitized inputs directly to the function without worrying about injection vulnerability.

function prepared_select($fromTable, $conditions = NULL, $bindVariable = NULL, $toSelect = "*", $use_prefix = TRUE) {
    if (!is_array($bindVariable)) {
        return FALSE;
    }
    if (empty($conditions)) {
        return FALSE;
    }
    if (!empty($conditions)) {
        $conditions = " " . $conditions;
    }
    $bind_stmt = "";
    $count = 0;
    foreach ($bindVariable as $variable_to_bind) {
        if (is_float($variable_to_bind)) {
            $bind_stmt .= "d";
        } else if (is_int($variable_to_bind)) {
            $bind_stmt .= "i";
        } else if (is_object($variable_to_bind)) {
            $bind_stmt .= "b";
        } else {
            $bind_stmt .= "s";
        }
        $count++;
    }
    $bind_itemset[] = $bind_stmt;
    if ($use_prefix) {
        $query = "SELECT " . $toSelect . " FROM " . $this->table_prefix . $fromTable . $conditions;
    } else {
        $query = "SELECT " . $toSelect . " FROM " . $fromTable . $conditions;
    }
    $this->current_link = &$this->read_link;
    if (($query || $this->last_query_type) && $this->write_link) {
		$this->current_link = &$this->write_link;
		$link_class = $this->write_link;
	} else {
		$this->current_link = &$this->read_link;
		$link_class = $this->read_link;
	}
	if ($this->error_number()) {
		return $this->error($stmt);
	}
    if ($stmt = @mysqli_prepare($link_class, $query)) {
        for ($i = 0; $i < count($bindVariable); $i++) {
            $bind_item = 'bind' . $i;
            $$bind_item = $bindVariable[$i];
            $bind_itemset[] = &$$bind_item;
        }
        call_user_func_array(array($stmt, 'bind_param'), $bind_itemset);
        $stmt->execute();
        $result = $stmt->get_result();
        $count = @mysqli_num_rows($result);
        $result_set = NULL;
        if ($count > 1) {
            $result_set = $result->fetch_all(MYSQLI_ASSOC);
        } else {
            $result_set = $result->fetch_assoc();
        }
        $result->free();
        return $result_set;
    } else {
        return FALSE;
    }
}

Step 2:
Now you can call your fancy new function within your plugin or custom script by running the following code:

$result = $db->prepared_select("sessions", "WHERE uid = ? AND sid = ?", array(0, "b15eefaaf55b11ecd4ed78"));

As you can see, the system will select from the sessions table, where the UID = 0 (a guest integer value) and the SID = b15eefaaf55b11ecd4ed78 (a random string for session id). This will return a standard statement result of associated data that you can now iterate through in a standard while loop like you would with any other mysqli statement, but you know your input is safe from attacks!

This same method can be modified to create a prepared statement for INSERT, UPDATE, REPLACE, or DELETE if you wanted fairly simply. I thought I would share this method in case anyone wanted to security of prepared items, as I have seen posts dating all the way back to 2014 asking for something similar. Of course, you can always use PDO, but this should save some headaches for developers who just want to keep using mysqli prepared statements.

Let me know what you think!
Reply
#2
This user has been denied support. This user has been denied support.
Shouldn't the error handling happen after the query was executed instead of before?
Reply
#3
(2021-06-08, 07:36 AM)frostschutz Wrote: Shouldn't the error handling happen after the query was executed instead of before?

You could handle it after as well, in this particular order the error handler is checking to see if you have an error in your prepared statement before its executed. As I said, this can be optimized further.
Reply


Forum Jump:


Users browsing this thread: 1 Guest(s)