Use ready-made PHP statements with MySQLi

Introduction to the use of MySQLi in PHP

Although we often develop our projects using a programming framerwork such as Codeigniter, Laravel or Simfony, many times we need to program some functions natively to adapt to existing code or because the application we are creating or modifying is not programmed using framerwork for various reasons. Many other times we need to program some functions natively for adaptation to the code that already exists or because the application itself that we are creating or modifying is not programmed using framerwork for various reasons that I will not go into now, since it depends on each company, its flow system or even a more custom development that requires greater control of the core.

And while it is true that for many programmers just starting out, prepared statements may seem a bit intimidating or complicated at first, once you get used to working with these methods, you will naturally start using them in all projects where you need them. The aim of this tutorial is to help those who have little or no knowledge of prepared statements to become fully proficient in this subject.

Disclaimer: In reality, though, prepared statements are a fundamental part of the security of your database queries. When it comes to security, you should never be a conformist, no matter how secure you may think your system is.

How does SQL injection work?

Before performing the prepared statement examples, it is important to analyse and understand the use of SQL injections within a computer system, because if we are not able to understand or visualise the flaw, we will not be able to fix or create other security holes in our systems.

In a normal MySQL call, it would do something like:

$name = $_POST['name'];
$mysqli->query("SELECT * FROM myTable WHERE name='$name'"); 

The problem with this, is that if it is based on user input, as in the example, a malicious user could make a OR '1'='1. Making this statement always evaluates as true, since 1=1. In this case, the malicious user now has access to your entire table. Just imagine what could happen if it were a query with DELETE.

This would be the example with the query modified by the attacker:

SELECT * FROM myTable WHERE name='' OR '1'='1' OR '1'='1 

A hacker could do a lot of damage to your website or application if your queries are set up this way. An easy solution to this would be to do:

$name = $mysqli->real_escape_string($_POST['name']);
$mysqli->query("SELECT * FROM myTable WHERE name='$name'"); 

Note the similarity to the first example, I still added inverted commas to the column value. Without quotes, strings remain equally susceptible to SQL injection. . If you are going to use a LIKE clause, you should also perform addcslashes($escaped, '%_')because mysqli::real_escape_stringwill not as indicated here .

This covers the string, as the name of the function indicates, but what about numbers? You could do this using: (int)$mysqli->real_escape_string($_POST['name'])which would certainly work, but that's redundant. If you are converting the variable to an int, you don't need to escape the string. You're already telling it to essentially make sure the value is an integer. Do (int)$_POST['name']would be sufficient. As it is an integer, it is obviously not necessary to add inverted commas to the column either: name of the SQL call.

Actually, if you follow these instructions perfectly, it should be sufficient to use mysqli::real_escape_string for your strings y (int)$var for its integers. Just don't forget to set the default character set. This can be configured in php.ini (it should be the default) as default_charset = "utf-8".and using $mysqli->set_charset('utf8mb4')on every page that uses $mysqli->real_escape_string(). But only for strings or numbers that are values in a WHERE statement or values of one column ; do not use this to names of table / column or SQL keywords as it will cause an error in the code.

Anyway, I still suggest using prepared statements, as they are clearly better suited to protect queries against SQL injections and less prone to errors. Since you don't have to worry about the formatting of the query, you should instead just replace the placeholders with the corresponding values. Of course this does not mean that you should not filter your variables and sanitise your values from malicious code to prevent other attacks such as: XSS injection attacks on our application.

How do MySQLi prepared statements work?

Simply put, this is how MySQLi prepared MySQLi statements work in PHP:

1. Prepare a SQL query with empty values as placeholders (with a question mark for each value).
2. Bind variables to placeholders indicating each variable, together with its type.
3. Execute the request.

The four types of variables allowed:

• i - For integers (int)
• d - For floating or decimal numbers (double)
• s - For text strings (string)
• b - For data (blob)

A prepared statement, as the name implies, is a way to prepare the MySQL call, without storing the variables. You tell it that the variables will eventually go there, but not yet, so the system knows at all times what type of variable it is going to get: whether it is a number, a string or another type of data. The best way to demonstrate this is by example:

$stmt = $mysqli->prepare("SELECT * FROM myTable WHERE name = ? AND age = ?");
$stmt->bind_param("yes", $_POST['name'], $_POST['age']);
$stmt->execute();
//fetching result would go here, but will be covered later
$stmt->close(); 

If you've never seen prepared statements before, this may seem a little strange. Basically, what's happening is that you're creating a template for what the SQL statement will be. In this case, we're selecting everything from myTablewhere namee age isequal to ?. The question mark is just a placeholder indicating where the variable goes, they must be presented in order and type within the bind_param.

The method bind_param()is where you attach variables to the dummy values in the prepared template. Notice how there are two letters in quotes before the variables. This tells the database the types of variables. The s specified which name will be a string value, whereas the i identifies "age" as an integer. This is precisely why I didn't add quotes around the question mark for the name, as I would normally do for a string in a SQL call. You probably thought I forgot, but the reality is that there is simply no need to do so (in fact, it won't actually work if you put inverted commas around the question mark ?as it will be treated as a literal string, rather than a dummy placeholder).

It is already telling you that it will be a literal string when you callbind_param()so even if a malicious user tries to insert SQL into your forms, it will still be treated as a string. The method $stmt->execute() is for executing the queryThe last line simply closes the prepared statement.

How to create a MySQLi connection in PHP?

Creating a MySQLi connection is quite simple. I suggest you name a file like:mysqli_connect.php and place this file outside your root directly (html, public_html) so that your credentials are secure. We will also use exception handling, using mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT). This may seem strange, especially if you've never used a bit operator a bit . But all it does is report all errors, while converting them to exceptions, using the class mysqli_sql_exception.

$mysqli = new mysqli("localhost", "username", "password", "databaseName");
if($mysqli->connect_error) {
exit('Error connecting to database'); //Should be a message a typical user could understand in production
}
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli->set_charset("utf8mb4"); 

If you prefer to use set_exception_handler () in place of try/catchyou can do the following to avoid nesting. If you are using this method, you must understand that it will affect all pages where it is included. Therefore, you should either reuse the function again with a custom message for each page or use restore_exception_handler () to return to the integrated function.

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
set_exception_handler(function($e) {
error_log($e->getMessage());
exit('Error connecting to database'); //Should be a message a typical user could understand
});
$mysqli = new mysqli("localhost", "username", "password", "databaseName");
$mysqli->set_charset("utf8mb4"); 

There is a very serious impact of the use mysqli_report()which is that it will report confidential information from your database. You have three options to continue using it, but not for inform your password.

1. You can also use it mysqli_report()strictly in everything, except to create the connection, if you do it with the first method that I showed $mysqli->connect_error(the password is not displayed) and only put in mysqli_report() then new mysqli() .
2. If you call mysqli_report() before create a connection, you must ensure that it is in a try / catch block and specifically prints in its error log. $e->getMessage()no $ewhich still contains their confidential information . Obviously, this applies strictly to the builder.
3. Use set_exception_handler()in the same way as point 2 and use $e->getMessage().

I strongly recommend doing one of these methods. Even if you are diligent and make sure that all your errors are only listed in your error log, I personally don't see why anyone would need to register your password. You'll know what the problem is anyway.

How to insert, update or delete with MySQLi in PHP?

Insert, update and delete have identical syntax, so they will be combined.

Example of a query to insert data:

$stmt = $mysqli->prepare("INSERT INTO myTable (name, age) VALUES (?, ?)");
$stmt->bind_param("yes", $_POST['name'], $_POST['age']);
$stmt->execute();
$stmt->close(); 

Example of a query to update data:

$stmt = $mysqli->prepare("UPDATE myTable SET name = ? WHERE id = ?");
$stmt->bind_param("yes", $_POST['name'], $_SESSION['id']);
$stmt->execute();
$stmt->close(); 

Example of a query to delete data:

$stmt = $mysqli->prepare("UPDATE myTable SET name = ?");
$stmt->bind_param("yes", $_POST['name'], $_POST['age']);
$stmt->execute();
if($stmt->affected_rows === 0) exit('No rows updated');
$stmt->close(); 

In this case, we check if any rows were updated. For reference, here is the use of the mysqli::$affected_rowswhich shows whether rows have been affected or not:

-1 - the query returned an error; redundant if error handling is already in place forexecute()

0 no updated records in UPDATEthere are no rows matching the clause WHERE or no query has been executed.

Greater than 0 returns the number of rows affected; comparable to mysqli_result::$num_rows for SELECT

How to get combined rows?

A common problem with $mysqli->affectedRowsis that it makes it impossible to know why it returned a zero in an UPDATE. This is due to the fact that it prints the number of modified rows, so it is ambiguous if you update your values with the same data.

One amazing feature that is unique to MySQLi and does not exist in PDO is the ability to get more information about a query. Technically, you can do this in PDO, but it can only be done on the connection, so you don't have a choice.

$stmt = $mysqli->prepare("UPDATE myTable SET name = ?");
$stmt->bind_param("yes", $_POST['name'], $_POST['age']);
$stmt->execute();
$stmt->close();
echo $mysqli->info; 

This will print:

Rows matched: 1 Changed: 0 Warnings: 0

I consider this to be a rather unwise implementation, as it is extremely inelegant to use it as it is. Fortunately, we can change that by turning it into an associative array. All credit goes to this useful commentary in the PHP documentation. While the use of mysqli->info for UPDATES is by far its most common case, it can also be used to other types of consultations .

preg_match_all('/(S[^:]+): (d+)/', $mysqli->info, $matches);
$infoArr = array_combine($matches[1], $matches[2]);
var_export($infoArr); 

This will now generate a matrix.

[Rows matched' => '1', 'Changed' => '0', 'Warnings' => '0']

How to get the primary key inserted in MySQLi with PHP?

$stmt = $mysqli->prepare("INSERT INTO myTable (name, age) VALUES (?, ?)");
$stmt->bind_param("yes", $_POST['name'], $_POST['age']);
$stmt->execute();
echo $mysqli->insert_id;
$stmt->close(); 

How to check if the entry is duplicated?

This is useful if you create a unique constraint on a table, so no duplicates are allowed. You can even do this for multiple columns, so it will have to be that exact permutation. If exception handling were disabled, you would check the error code with $mysqli->errno. With exception handling turned on, you can choose between that or the generic exception method $e->getCode(). Note that this differs from PDOException, which will print the SQLSTATE, rather than the error code.

Here is a list of error messages . The error code for a duplicate row entry from an update or insert is 1062 and SQLSTATE is 23000 . To specifically check SQLSTATE, you must use $mysqli->sqlstate.

try {
$stmt = $mysqli->prepare("INSERT INTO myTable (name, age) VALUES (?, ?)");
$stmt->bind_param("yes", $_POST['name'], $_POST['age']);
$stmt->execute();
$stmt->close();
} catch(Exception $e) {
if($mysqli->errno === 1062) echo 'Duplicate entry';
} 

This is how I would establish a single restriction:

ALTER TABLE myTable ADD CONSTRAINT unique_person UNIQUE (name, age) 

How to select data with MySQLi in PHP?

All selection statements in prepared queries will start in roughly the same way. However, there is a key difference in storing and retrieving the results. The two methods that exist are get_result()y bind_result().

get_result ()

This is the more versatile of the two, as it can be used for any scenario. It should be noted that this requires mysqlnd, which has been included in PHP since 5.3 and has been the default native driver since 5.4, as indicated here . I doubt that many people are using versions earlier than that, so generally you should haveget_result()no problems.

This essentially exposes the regular, unprepared mysqli_result API. That is, once you do: $result = get_result()you can use it in exactly the same way as you would use it with $result = $mysqli->query().

You can now use the following methods to retrieve one row at a time or all at once. These are just some of the most common ones, but you can take a look at the whole mysqli_result class for all its methods.

One row

• $result->fetch_assoc() - Obtain an associative matrix
• $result->fetch_row() - Obtain a numerical matrix
• $result->fetch_object() - Obtain a matrix of objects

All

• $result->fetch_all(MYSQLI_ASSOC) - Obtain an associative matrix
• $result->fetch_all(MYSQLI_NUM) - Obtain a numerical matrix

$stmt = $mysqli->prepare("SELECT * FROM myTable WHERE name = ?");
$stmt->bind_param("s", $_POST['name']);
$stmt->execute();
$result = $stmt->get_result();
if($result->num_rows === 0) exit('No rows');
while($row = $result->fetch_assoc()) {
$ids[] = $row['id'];
$names[] = $row['name'];
$ages[] = $row['age'];
}
var_export($ages);
$stmt->close(); 

Exit:

[22, 18, 19, 27, 36, 7]

bind_result ()

You may be asking yourself, why use it? bind_result()? Personally, I consider it to be far inferior to get_result()in all scenarios, except when searching for a single row in separate variables. In addition, before get_result()existed and mysqlnd was integrated into PHP, this was his only option, which is why he might be using it in a lot of legacy code.

The most annoying part about using bind_result()is that it must link each column you select and then loop through the values. Obviously, this is not ideal for a large number of values or to use *. It's especially annoying to use the star selector in bind_result()The database does not even know what these values are without looking in the database.

Also, this makes your code extremely unmaintainable with table changes. Usually, this won't matter, as you shouldn't be using the wildcard in production mode anyway (but you know you are).

$stmt = $mysqli->prepare("SELECT id, name, age FROM myTable WHERE name = ?");
$stmt->bind_param("s", $_POST['name']);
$stmt->execute();
$stmt->store_result();
if($stmt->num_rows === 0) exit('No rows');
$stmt->bind_result($idRow, $nameRow, $ageRow);
while($stmt->fetch()) {
$ids[] = $idRow;
$names[] = $nameRow;
$ages[] = $ageRow;
}
var_export($ids);
$stmt->close(); 

Exit:

[106, 221, 3, 55, 583, 72]

How to retrieve an associative array with MySQLi?

I think this is typically the most common use case. I will also use chaining in the following, although that is obviously not necessary.

$stmt = $mysqli->prepare("SELECT id, name, age FROM myTable WHERE name = ?");
$stmt->bind_param("s", $_POST['name']);
$stmt->execute();
$arr = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
if(!$arr) exit('No rows');
var_export($arr);
$stmt->close(); 

If you need to modify the result set, then you should probably use a while loop with fetch_assoc()and search each row one at a time.

$arr = [];
$stmt = $mysqli->prepare("SELECT id, name, age FROM myTable WHERE name = ?");
$stmt->bind_param("s", $_POST['name']);
$stmt->execute();
$result = $stmt->get_result();
while($row = $result->fetch_assoc()) {
$arr[] = $row;
}
if(!$arr) exit('No rows');
var_export($arr);
$stmt->close(); 

Exit:

[
['id' => 27, 'name' => 'Jessica', 'age' => 27],
['id' => 432, 'name' => 'Jimmy', 'age' => 19]
]

Actually, you can also do this by using bind_result()although it was clearly not designed to do so. Here are a smart solution Although I personally feel it is something that is great to know is possible, but realistically it should not be used.

Obtain a numerical matrix

This follows the same format as an associative array. To get the whole array in one command, without looping, you would use mysqli_result->fetch_all(MYSQLI_NUM). If you need to get the results in a loop, you should use mysqli_result->fetch_row().

$stmt = $mysqli->prepare("SELECT location, favorite_color, age FROM myTable WHERE name = ?");
$stmt->bind_param("s", $_POST['name']);
$stmt->execute();
$arr = $stmt->get_result()->fetch_all(MYSQLI_NUM);
if(!$arr) exit('No rows');
var_export($arr);
$stmt->close(); 

And, of course, the adaptation of the while loop.

$arr = [];
$stmt = $mysqli->prepare("SELECT location, favorite_color, age FROM myTable WHERE name = ?");
$stmt->bind_param("s", $_POST['name']);
$stmt->execute();
$result = $stmt->get_result();
while($row = $result->fetch_row()) {
$arr[] = $row;
}
if(!$arr) exit('No rows');
var_export($arr);
$stmt->close(); 

Exit:

[
[Boston', 'green', 28],
[Seattle', 'blue', 49],
['Atlanta', 'pink', 24].
]

Get a single row

Personally, I find it easier to use bind_result()when I know, in fact, that I will only search one row, as I can access the variables in a cleaner way.

$stmt = $mysqli->prepare("SELECT id, name, age FROM myTable WHERE name = ?");
$stmt->bind_param("s", $_POST['name']);
$stmt->execute();
$stmt->store_result();
if($stmt->num_rows === 0) exit('No rows');
$stmt->bind_result($id, $name, $age);
$stmt->fetch();
echo $name; //Output: 'Ryan'; //Output: 'Ryan'; //Output: 'Ryan'; //Output: 'Ryan'.
$stmt->close(); 

Now you can simply use the variables bind_result(), $nameas it knows that they will only contain a value, not an array.

Here is the version with get_result():

$stmt = $mysqli->prepare("SELECT id, name, age FROM myTable WHERE name = ?");
$stmt->bind_param("s", $_POST['name']);
$stmt->execute();
$arr = $stmt->get_result()->fetch_assoc();
if(!$arr) exit('No rows');
var_export($arr);
$stmt->close(); 

I would then use the variable as $arr['id']for example.

Exit:

['id' => 36, 'name' => 'Kevin', 'age' => 39]

Obtain a matrix of objects

This is very similar to looking up an associative array. The only main difference is that you will access it as a $arr[0]->age. Also, in case you didn't know, objects are passed by value, whereas arrays are passed by reference.

$arr = []
$stmt = $mysqli->prepare("SELECT id, name, age FROM myTable WHERE id = ?");
$stmt->bind_param("s", $_SESSION['id']);
$stmt->execute();
$result = $stmt->get_result();
while($row = $result->fetch_object()) {
$arr[] = $row;
}
if(!$arr) exit('No rows');
var_export($arr);
$stmt->close(); 

Exit:

[
stdClass Object ['id' => 27, 'name' => 'Jessica', 'age' => 27],
stdClass Object ['id' => 432, 'name' => 'Jimmy', 'age' => 19]
]

You can even add property values to an existing class as well. However, it should be noted that there is a potential problem, according to this comment in the PHP docs. The problem is that if you have a default value in your constructor with a duplicate variable name, it will first look up the object and then will set the value of the constructor, thus overwriting the result obtained. Strangely enough, there was a "mistake". from PHP 5.6.21 to 7.0.6 where this would not happen. Although this violates OOP principles, some people would like this feature, although it was a bug in certain versions. Something like PDO::FETCH_PROPS_LATEin PDO must be implemented in MySQLi to give you a choice.

class myClass {}
$arr = [];
$stmt = $mysqli->prepare("SELECT id, name, age FROM myTable WHERE id = ?");
$stmt->bind_param("s", $_SESSION['id']);
$stmt->execute();
$result = $stmt->get_result();
while($row = $result->fetch_object('myClass')) {
$arr[] = $row;
}
if(!$arr) exit('No rows');
var_export($arr);
$stmt->close(); 

As the comment says, this is how you would do it correctly. All you need is a simple if condition to check if the variable is equal to the value in the constructor; if it isn't, just don't set it in the constructor. This is essentially the same as using PDO::FETCH_PROPS_LATEin PDO.

class myClass {
private $id;
public function __construct($id = 0) {
if($this->id === 0) $this->id = $id;
}
}
$arr = [];
$stmt = $mysqli->prepare("SELECT id, name, age FROM myTable WHERE id = ?");
$stmt->bind_param("s", $_SESSION['id']);
$stmt->execute();
$result = $stmt->get_result();
while($row = $result->fetch_object('myClass')) {
$arr[] = $row;
}
if(!$arr) exit('No rows');
var_export($arr);
$stmt->close(); 

Another unexpected, but potentially useful, behaviour of using fetch_object('myClass')is that it can modify private variables. I'm really not sure how I feel about this, as it seems to violate encapsulation principles and I don't feel confident about it.

Conclusion

bind_result () is best used to retrieve a single row without too many columns, or *extremely inelegant for associative arrays.

get_result () is preferred for almost all use cases.

Extras:

You probably think you could do something like:

$stmt = $mysqli->prepare("SELECT id, name, age FROM myTable WHERE Name LIKE %?%"); 

But this is not allowed. The ?placeholder must be either the full string or the integer literal value. This is how you would do it correctly.

$search = "%{$_POST['search']}%";
$stmt = $mysqli->prepare("SELECT id, name, age FROM myTable WHERE name LIKE ?");
$stmt->bind_param("s", $search);
$stmt->execute();
$arr = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
if(!$arr) exit('No rows');
var_export($arr);
$stmt->close(); 

Use of WHERE IN in prepared queries:

This is definitely something I would like to see improved in MySQLi. For now, in WHERE INit is possible to use MySQLi prepared statements, but it is a bit time consuming.

Side note : The following two examples use the splat operator to unpack arguments, which requires PHP 5.6+. If you are using a version lower than that, you can replace it with call_user_func_array().

$inArr = [12, 23, 44];
$clause = implode(',', array_fill(0, count($inArr), '?')); //create 3 question marks
$types = str_repeat('i', count($inArr)); //create 3 ints for bind_param
$stmt = $mysqli->prepare("SELECT id, name FROM myTable WHERE id IN ($clause)");
$stmt->bind_param($types, ...$inArr);
$stmt->execute();
$resArr = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
if(!$resArr) exit('No rows');
var_export($resArr);
$stmt->close(); 

With other placeholders

The first example showed how to use the WHERE IN What if you want to use other placeholders in different locations?

$inArr = [12, 23, 44];
$clause = implode(',', array_fill(0, count($inArr), '?')); //create 3 question marks
$types = str_repeat('i', count($inArr)); //create 3 ints for bind_param
$types .= 'i'; //add 1 more int type
$fullArr = array_merge($inArr, [26]); //merge WHERE IN array with other value(s)
$stmt = $mysqli->prepare("SELECT id, name FROM myTable WHERE id IN ($clause) AND age > ?");
$stmt->bind_param($types, ...$fullArr); //4 placeholders to bind
$stmt->execute();
$resArr = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
if(!$resArr) exit('No rows');
var_export($resArr);
$stmt->close(); 

Multiple statements prepared in transactions:

This may seem strange because it would even justify its own section, as you can literally use prepared statements one after the other. While this will certainly work, it does not guarantee that your queries will be atomic. This means that if you were to run ten queries, and one failed, the other nine would succeed. If you want your SQL queries to run only if they all succeed, then you should use transactions.

try {
$mysqli->autocommit(FALSE); //turn on transactions
$stmt1 = $mysqli->prepare("INSERT INTO myTable (name, age) VALUES (?, ?)");
$stmt2 = $mysqli->prepare("UPDATE myTable SET name = ? WHERE id = ?");
$stmt1->bind_param("yes", $_POST['name'], $_POST['age']);
$stmt2->bind_param("yes", $_POST['name'], $_SESSION['id']);
$stmt1->execute();
$stmt2->execute();
$stmt1->close();
$stmt2->close();
$mysqli->autocommit(TRUE); //turn off transactions + commit queued queries
} catch(Exception $e) {
$mysqli->rollback(); //remove all queries from queue if error (undo)
throw $e;
} 

Reusing the same template, with different values

try {
$mysqli->autocommit(FALSE); //turn on transactions
$stmt = $mysqli->prepare("INSERT INTO myTable (name, age) VALUES (?, ?)");
$stmt->bind_param("yes", $name, $age);
$name = 'John';
$age = 21;
$stmt->execute();
$name = 'Rick';
$age = 24;
$stmt->execute();
$stmt->close();
$mysqli->autocommit(TRUE); //turn off transactions + commit queued queries
} catch(Exception $e) {
$mysqli->rollback(); //remove all queries from queue if error (undo)
throw $e;
} 

Error handling with MiSQLi

Fatal error: Uncaught Error: Call to a member function bind_param() on boolean

Anyone who has used MySQLi prepared statements has seen this message at some point, but what does it mean? Almost nothing at all. So how do you fix this, you might ask? For starters, don't forget to turn on exception handling, instead of error handling. mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT)when creating a connection to the database.

Exception handling

All mysqli functions return false in case of error or failure, so you can easily check the correctness of each function and report errors with $mysqli->error. However, this is very tedious and there is a more elegant way to do it if you enable internal reporting. I recommend doing it this way, as it is much more portable from development to production.

This can also be used in production, as long as you have an error log configured for all errors; this must be set in php.ini. Never report bugs directly on your site while in production. You will kick yourself for such a silly mistake.

In addition, the placement of mysqli_report() before create a new connection, it will also show your connection password; otherwise, it will just report everything afterwards, such as your queries.

Here is what your php.ini file should look like in production: do both display_errors = Offy log_errors = On. Also, note that each page should really only use one block. try/catch The only way to ensure that a single global query can be used, rather than adjusting each query individually, is through a single global query. The only exception to this is with transactions, which would be nested, but would throw their own exception, so that the overall try/catchcan "catch" it.

try {
$stmt = $mysqli->prepare("DELETE FROM myTable WHERE id = ?");
$stmt->bind_param("i", $_SESSION['id']);
$stmt->execute();
$stmt->close();
$stmt = $mysqli->prepare("SELECT id, name, age FROM myTable WHERE name = ?");
$stmt->bind_param("s", $_POST['name']);
$stmt->execute();
$arr = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
$stmt->close();
try {
$mysqli->autocommit(FALSE); //turn on transactions
$stmt = $mysqli->prepare("INSERT INTO myTable (name, age) VALUES (?, ?)");
$stmt->bind_param("yes", $name, $age);
$name = 'John';
$age = 21;
$stmt->execute();
$name = 'Rick';
$age = 24;
$stmt->execute();
$stmt->close();
$mysqli->autocommit(TRUE); //turn off transactions + commit queued queries
} catch(Exception $e) {
$mysqli->rollback(); //remove all queries from queue if error (undo)
throw $e;
}
} catch (Exception $e) {
error_log($e);
exit('Error message for user to understand');
} 

Custom exception handling

As indicated above, you may alternatively use set_exception_handler()on each page (or a global redirect). This removes the key nesting layer. If you are using transactions, you should still use a try catch with that, but then throw your own exception.

set_exception_handler(function($e) {
error_log($e);
exit('Error deleting');
});
$stmt = $mysqli->prepare("DELETE FROM myTable WHERE id = ?");
$stmt->execute();
$stmt->close(); 

It is to be expected that all MySQLi errors will be converted to exceptions with mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT). Oddly enough, I noticed that I was still getting a warning error when I bind_param()had too many or too few variables or types bound. The message sent is as follows:

Warning: mysqli_stmt::bind_param(): Number of variables doesn't match number of parameters in prepared statement

One solution to this is to use a global error handler to trigger an exception. An example of this could be:

set_error_handler(function($errno, $errstr, $errfile, $errline) {
throw new Exception("$errstr on line $errline in file $errfile");
}); 

This only happened on runtime warnings, but I turned all errors into exceptions. I don't see any problem with doing this, but there are some people who are strongly against it.

Some extras:

Do I need $stmt->close()?

Great question par: $mysqli->close()y $stmt->close(),becauseessentially have the same effect. The former closes the MySQLi connection, while the latter closes the prepared statement. TLDR; in general, both are not necessary in most cases, as both will be closed once the execution of the script is complete. There is also a function to simply free the memory associated with the MySQLi result and the prepared statement, respectively: $result->free()y $stmt->free(). I myself will probably never use it, but if you are interested, here is the of the result y the parameterised query . The following should also be borne in mind: both the $stmt->close()and the end of the script execution will free the memory anyway.

Final verdict: I usually just do it $mysqli->close()y $stmt->close()although it can be argued that this is a bit superfluous. If you plan to use the same variable $stmtagain for other prepared statements, you should close it or use a different variable name, such as $stmt2. Finally, I have never found the need to simply release them, without closing them.

Classes: mysqli vs. mysqli_stmt vs. mysqli_result

One thing you may have noticed along the way is that there are certain methods that exist in two of the classes, as an alias almost. Personally, I think it would be better to have only one version, like in PDO, to avoid confusion.

• mysqli::$affected_rowso mysqli_stmt::$affected_rows- It belongs to mysqli_stmt. It works the same with either one, but it will be an error if it is called after the declaration is closed with any of the methods
• mysqli_result::$num_rowso mysqli_stmt::$num_rows- $result->num_rowscan only be used with get_result()while $stmt->num_rowswhich can only be used with bind_result().
• mysqli::$insert_ido mysqli_stmt::$insert_id- It belongs to mysqli. Better to use $mysqli->insert_idas it will continue to function even after $stmt->close()is used. There is also a note on the 2011 PHP documents stating that $stmt->insert_idwill only get the first query executed. I tried this on my current version of 7.1 and this does not seem to be the case. The recommended one to use is the mysqli class version anyway.

So, does using prepared statements mean I am safe from attackers?

While you are safe from SQL injection, you still need to validate and sanitise your user-entered data. You can use a function such as filter_var () to validate the data before insert them into the database and htmlspecialchars () to disinfect after retrieve them in their consultations.

=====================

P.S, if you liked the tutorial you can leave a thank you comment, if anything has changed since then, feel free to leave me a comment with the new procedures.
Do you have a question or a problem that you don't know how to solve? Leave me a comment anyway, I will get back to you as soon as possible.