What Does Bind_Param Accomplish

What does bind_param accomplish?

Now, assuming those 4 variables were user-inputted, I don't understand
how this prevents SQL injections. By my understanding, they can still
input whatever they want in there.

The main principle there is using prepared statement which is designed for sending safe query to db server, this can be done by escaping user input which is not part of the real query, and also checking the query without any (where clause) to check the validity of the query before using any parameters.

From this question: PDO sends raw query to MySQL while Mysqli sends prepared query, both produce the same result

$stmt = $mysqli->prepare("SELECT * FROM users WHERE username =?")) {
$stmt->bind_param("i", $user);
$user = "''1''";

server logs:

  130802 23:39:39   175 Connect   ****@localhost on testdb
    175 Prepare   SELECT * FROM users WHERE username =?
    175 Execute   SELECT * FROM users WHERE username =0
    175 Quit

By Using prepared statement, db server will check the query without any parameter, at this stage, errors can be detected before binding any parameter, then, if the query was valid, parameters also will be send to the server for finalizing the query.

From PHP Manual http://php.net/manual/en/mysqli.quickstart.prepared-statements.php:

Escaping and SQL injection

Bound variables will be escaped automatically by the server. The
server inserts their escaped values at the appropriate places into the
statement template before execution. A hint must be provided to the
server for the type of bound variable, to create an appropriate
conversion. See the mysqli_stmt_bind_param() function for more
information.

I also can't find an explanation for the 'sssd' in there. What does it
do? Is that what makes it secure-er?

The answer is here: http://php.net/manual/en/mysqli-stmt.bind-param.php

i
corresponding variable has type integer

d
corresponding variable has type double

s
corresponding variable has type string

b
corresponding variable is a blob and will be sent in packets

Final question: I read on another question that
mysqli_real_escape_string is deprecated, but it doesn't say that in
the manual. How is it deprecated? Can it not escape special characters
anymore for some reason?

Can you give a reference? I think you misunderstood with (mysql_real_escape_string())

What does bind_param() do?

When you prepare an SQL statement, you can insert a placeholder (?) where a column value would go, then use bind_param() to safely substitute that placeholder for the real column's value. This prevents any possibility of an SQL injection.

You can read more about bind_param() here.

Can someone clearly explain why mysqli_prepare()/bind_param() is better than real_escape_string()?

What you are reading, that you need to use mysqli_prepare() and mysqli_bind_param() functions to "properly escape your MySQL queries" is wrong.

It is true that if you use mysqli_prepare() and mysqli_bind_param(), you needn't (and shouldn't) "escape" the values supplied as bind parameters. So, in that sense, there's some truth in what you are reading.

It's only when unsafe variables are included in the SQL text (the actual text of the query) that you need to "properly escape" the variables, usually by wrapping the variables in mysqli_real_escape_string() function calls.

(We note that it's possible to make of use of prepared statements and still include un-escaped variables in the SQL text, rather than passing the variable values as bind_parameters. That does sort of defeats the purpose of using prepared statements, but the point is, either way, you can write code that is vulnerable.

MySQL now supports "server side" prepared statements (if the option is enabled in the connection), and that's a performance optimization (in some cases) of repeated executions of identical SQL text. (This has been long supported in other databases, such as Oracle, where making use of prepared statements has been a familiar pattern for, like, since forever.)

Q: Did they implement [prepared statements] so that people wouldn't forget to escape values before sending them in a query?

A: Based on the number of examples of code vulnerable to SQL Injection when not using prepared statements, despite the documentation regarding mysql_real_escape_string() function, you'd think that certainly would be sufficient reason.

I think one big benefit is that when we're reading code, we can see a SQL statement as a single string literal, rather than a concatenation of a bunch of variables, with quotes and dots and calls to mysql_real_escape_string, which isn't too bad with a simple query, but with a more complex query, it is just overly cumbersome. The use of the ? placeholder makes for a more understandable SQL statement,... true, I need to look at other lines of code to figure out what value is getting stuffed there. (I think the Oracle style named parameters :fee, :fi, :fo, :fum is preferable to the positional ?, ?, ?, ? notation.) But having STATIC SQL text is what is really the benefit.

Q: Or is it somehow faster?

As I mentioned before, the use of server side prepared statements can be and advantage in terms of performance. It's not always the case that it's faster, but for repeated execution of the same statement, where the only difference is literal values (as in repeated inserts), it can provide a performance boost.

Q: Or should I use this method when I intend to use the same query repeatedly (since a mysqli_stmt can be reused) and use the traditional method in other cases?

That's up to you. My preference is for using STATIC SQL text. But this really comes from a long history of using Oracle, and using the same pattern with MySQL fits naturally. (Albeit, from Perl using the DBI interface, and Java using JDBC and MyBATIS, or other ORMs (Hibernate, Glassfish JPA, et al.)

Following the same pattern just feels natural in PHP; the introduction of mysqli_ and PDO are a welcome relief from the arcane (and abused) mysql_ interface.

Good code can be written following either pattern. But I challenge you to think ahead, about more complex SQL statements, and whether the choice to use mysqli_real_escape_string() and concatenating together a dynamic string to be executed, rather than using static SQL text and bind parameters, might make reading, and deciphering, the actual SQL being executed more complicated for the soul that finds themselves maintaining code they didn't write.

I think studies have shown that code is read ten times more than it is written, which is why we strive to produce readable, understandable code, even if that means more lines of code. (When each statement is doing a single identifiable thing, that's usually easier for me to understand than reading a jumble of concatenated function calls in one complicated statement.

Trying to understand bind_param (can't edit variables or else nothing will save)

The first argument ("ssi" in your case) is a format string that tells bind_param what types of arguments are being bound to it. "s" stands for "string" and "i" stands from "integer". Here you have a series made up of two strings (the first and last name) followed by an integer (the id).

If you want to remove the last name, you should remove it from the set clause, remove its bound variable and remove the corresponding "s" from the format string:

if ($stmt = $mysqli->prepare("UPDATE players SET firstname = ? WHERE id = ?"))
# Updating lastname was removed here -------------------------^
{
    $stmt->bind_param("si", $firstname, $id);
    # s removed --------^
    # $lastname remoed ----------------^

    $stmt->execute();
    $stmt->close();
}

Bind Param with array of parameters

call_user_func_array
"Call a callback with an array of parameters"

call_user_func_array(array($stmt, "bind_param"), array_merge(array($type), $params));

should do the job

UPDATE: you have also to change your params array:

$params = array(&$firstName, &$lastName, &$address, &$postcode, &$email, &$password);

as mysqli_stmt::bind_param expects the second and the following parameters by reference.

EDIT: Your query seems to be wrong. Maybe you have less fields than you have variables there. Do:

"INSERT INTO Users (field1, field2, field3, field4, field5, field6) VALUES (?, ?, ?, ?, ?, ?)"

where you replace the name of the fields by the correct names

Dynamically bind params in $bind_param(); Mysqli

Here is an example that could help ( prepare() function is a class method ).

function prepare( $query, $bind = array() )
{   
    if ( !$stmt = $this->mysqli->prepare( $query ) ) 
        throw new Exception( 'Query failed: ' . $query . PHP_EOL . $this->mysqli->error );  

    // if $bind is not an empty array shift the type element off the beginning and call stmt->bind_param() with variables to bind passed as reference
    if ( $type = array_shift( $bind ) )
        call_user_func_array( 
            array( $stmt, 'bind_param' ), 
            array_merge( array( $type ), array_map( function( &$item ) { return $item; }, $bind ) ) 
        );

    if ( !$stmt->execute() ) 
        throw new Exception( 'Execute failed: ' . PHP_EOL . $stmt->error );

    // choose what to return here ( 'affected_rows', 'insert_id', 'mysqli_result', 'stmt', 'array' ) 

}

Example of usage:

$db->prepare( "SELECT * FROM user WHERE user_name = ? OR user_email = ?", [ 'ss', $user_name, $user_name ] );

Am I Successfully Safeguarding Against SQL Injection?

Yes, since you are using the prepared statement you are safe from sql injection.

This part of the code protects you.

// Bind the variables to the parameter as strings. 
$stmt->bind_param("sss", $cleanverse, $book, $reference);

The other best practices you might incorporate in your code might be:

To check the maximum string length that you are expecting from the user and to truncate it, if they exceed that.
To disallow any html input, if you are not expecting them by using something like strip_tags or html purifier libraries.
You can also look into how to implement CSRF.

What Does Bind_Param Accomplish