Wrapping a prepared statement in a function

Question

I've been reading articles about SQL Injection, and decided to modify my code to prevent SQL injection.

For example, I have an input which I insert the value to my database. Initially, my guard against injection was this:

function test_input($data) {
    $data = trim($data);
    $data = stripslashes($data);
    $data = htmlspecialchars($data);
    // $data = addslashes($data);
    $data = mysql_real_escape_string($data);
    return $data;
}

$artist = $_POST["artist"];     // can be anything
$artist = test_input($artist);  // escaped chars are &, quotes, <, >, \n, \r, etc.

if ($mysqli->query("SELECT * FROM `my_table` WHERE `artist` = '$artist'")->num_rows == 0) {
    $mysqli->query("INSERT INTO my_table (artist) VALUES ('$artist')");
    echo "New artist is added.";
} else {
    echo "Artist already exists.";
}

In the articles I've read, it was suggested that one should use prepared statements. I've changed my code and used that:

$artist = $_POST["artist"]; // can be anything

$query = $mysqli->prepare("SELECT * FROM my_table WHERE artist = ?");
$query->bind_param("s", $artist);
$query->execute();
$result = $query->get_result();
$query->close();

if ($result->num_rows == 0) {
    echo "Artist doesn't exist in the DB." . PHP_EOL;
    $query = $mysqli->prepare("INSERT INTO my_table (artist) VALUES (?)");
    $query->bind_param("s", $artist);
    $query->execute();
    if ($query->affected_rows > 0) {
        echo "Artist is added to the DB." . PHP_EOL;
    }
    $query->close();
} else {
    echo "Artist already exists in the DB." . PHP_EOL;
}

While this prevents SQL injection, it doesn't do anything about XSS. So I decided to modify test_input (removed $data = mysql_real_escape_string($data);) and use it to prevent script injection.

function test_input($data) {
    $data = trim($data);
    $data = stripslashes($data);
    $data = htmlspecialchars($data);
    return $data;
}

$artist = $_POST["artist"]; // can be anything
$artist = test_input($artist);

Now, my problem is about using prepared statements. I'll be inserting three items; artist, album, and song. Repeating the same process (prepare, bind, execute, close) over and over again seems redundent to me. I want to create a function and wrap the prepared statement process with it. Something like this:

function p_statement($mysqli, $query_string = "", $type = "", $vars = []) {
    $query = $mysqli->prepare($query_string);
    $query->bind_param($type, $vars);
    $query->execute();
    $result = null;
    preg_match("/^[A-Z]+/", $query_string, $command);
    switch ($command[0]) {
        case "SELECT":
            $result = $query->get_result();
            break;
        case "INSERT":
            $result = $query->affected_rows;
            break;
    }
    $query->close();
    return $result;
}

Though, this presents a problem: $vars array. Since the number of variables that'll be passed to mysqli_stmt::bind_param() will be variable/dynamic, I've used an array in the main function p_statement. I don't know how I should the pass the items in the array to the mysqli_stmt::bind_param(). bind_param expects (type, var1, var2, varn,), and I've got an array.

How can I make this work?

Answer 1

You're looking for implode()

Looking at to manpage and it shows a light about using call_user_func_array. And I edit some of your snippet.

function p_statement($mysqli, $query_string = "", $type = "", $vars = []) {

    $query = $mysqli->prepare($query_string);

    //assign $type to first index of $vars
    array_unshift($vars, $type);

    //Turn all values into reference since call_user_func_array
    //expects arguments of bind_param to be references
    //@see mysqli::bind_param() manpage
    foreach ($vars as $key => $value) {
        $vars[$key] =& $vars[$key];
    }

    call_user_func_array(array($query, 'bind_param'), $vars);
    $query->execute();

    //INSERT, SELECT, UPDATE and DELETE have each 6 chars, you can
    //validate it using substr() below for better and faster performance
    if (strtolower(substr($query_string, 0, 6)) == "select") {
        $result = $query->get_result();
    } else {
        $result = $query->affected_rows;
    }

    $query->close();
    return $result;
}

Answer 2

I've found a way to solve the problem using call_user_func_array.

function p_statement($mysqli, $query_string = "", $type = "", $vars = []) {
    $query = $mysqli->prepare($query_string);
    // create an empty array
    $parameters = array();
    // push the type string into the array by reference
    $parameters[] = & $type;
    // push the items from $vars array into the array by reference
    for ($i = 0; $i < count($vars); $i++) {
        $parameters[] = & $vars[$i];
    }
    // call mysqli_stmt::bind_param with the $parameters array, which contains [type, var1, var2, ...]
    call_user_func_array(array($query, "bind_param"), $parameters);
    $query->execute();
    $result = null;
    preg_match("/^[A-Z]+/", $query_string, $command);
    switch ($command[0]) {
        case "SELECT":
            $result = $query->get_result();
            break;
        case "INSERT":
        case "UPDATE":
        case "DELETE":
            $result = $query->affected_rows;
            break;
    }
    $query->close();
    return $result;
}

$artist = "3 Doors Down";
$year   = 2000;

$artist_select = p_statement($mysqli, "SELECT * FROM albums WHERE artist = ? AND year = ?", "si", [$artist, $year]);

var_dump($artist_select->fetch_all(MYSQLI_ASSOC));

Outputs:

array(1) {
  [0]=>
  array(3) {
    ["album"]=>
    string(15) "The Better Life"
    ["year"]=>
    int(2000)
    ["artist"]=>
    string(12) "3 Doors Down"
  }
}