Wrapping a prepared statement in a function
<p>I've been reading articles about SQL Injection, and decided to modify my code to prevent SQL injection.</p>

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

<pre><code>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 &amp;, quotes, &lt;, &gt;, \n, \r, etc.

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

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

<pre><code>$artist = $_POST["artist"]; // can be anything

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

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

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

<pre><code>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);
</code></pre>

<p>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:</p>

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

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

<p>How can I make this work?</p>