I am having an issue with mysqli_real_escape_string() function. For reference, I'm working locally on MAMP (PHP version 7.2.8 & SQL version 5.7.23). The issue I'm having is this:
if I submit some user data via an HTML form field -- Dan Bannon -- the submittal into the SQL database is successful.
If I try to submit the user data the same way, but it includes a special character -- Dan O'Bannon -- I receive an error.
I've checked forums to see if others have this same problem, but I'm not seeing anything exactly like it. I'm sure the function is working to some extent, as it will at least allow a normal input to be entered and stored in the database. The problem is that it's not working with an apostrophe in a name.
Right now, it's possible there is an issue with setting the CHARSET in the database, maybe?
I've checked the char and collation, and I'm using UTF8 in both the PHP and the database. I've had another person take a look at this, but maybe I/we are missing something and perhaps somebody else has seen this error before and can point me in the right direction.
I'll include my code below, I've checked it a bunch of times and can't really see a syntax error, and I'm just wondering if anyone out there has seen this, where mysqli_real_escape_string() will submit information w/o special characters to the DB, but will not submit names/data w/ apostrophe's in the text field.
Code to process user inputs:
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
require('../mysqli_connect.php');
$errors = []; //initialize an error array.
if (empty($_POST['first_name'])) {
$errors[] = 'You forgot to enter your first name. ';
} else {
$fn = mysqli_real_escape_string($dbc, trim($_POST['first_name']));
}
if (empty($_POST['last_name'])) {
$errors[] = 'You Forgot to enter your last name. ';
} else {
$ln = mysqli_real_escape_string($dbc, trim($_POST['last_name']));
}
if (empty($_POST['email'])) {
$errors[] = 'You forgot to enter your email address. ';
} else {
$e = mysqli_real_escape_string($dbc, trim($_POST['email']));
}
if (!empty($_POST['pass1'])) {
if ($_POST['pass1'] != $_POST['pass2']) {
$errors[] = 'Your password did not match the confirmed password. ';
} else {
$p = mysqli_real_escape_string($dbc, trim($_POST['pass1']));
}
} else {
$errors[] = 'You forgot to enter your password. ';
}
if (empty($errors)) { //If everything is okay
//register user into database
$q = "INSERT INTO users (first_name, last_name, email, pass, registration_date) VALUES ('$fn', '$ln', '$e', SHA2('$p', 512), NOW() )";
$r = @mysqli_query($dbc, $q);
if ($r) {
echo '<div style= "margin: 100px"><h1>Thank You!</h1>
<p>You are now registered. In chapter 12 you will actually be able to log in!</p><p><br></p></div>';
} else {
echo '<div style="margin: 100px;"><h1>System Error</h1>
<p class="error"> You could not be registered due to a system error. We apologize for any inconvenience.</p>';
//debug message
echo '<p>' . mysqli_error($dbc) . '<br><br>Query: ' . $q . '</p></div>';
} // end of ($r) IF
mysqli_close($dbc); //closes db connection
//inlcuding footer and quit the script
include('/Applications/MAMP/htdocs/Chapter 9/includes/footer.html');
exit();
} else { //reports the errors
echo '<div style="margin: 100px;"><h1>Error!</h1>
<p class="error">The following error(s) occurred:<br>';
foreach ($errors as $msg) { //Print each error.
echo " - $msg<br>
";
}
echo '<p>Please try again.</p><p><br></p></div>';
} //end of (empty($errors)) IF
Here's my sqli database connection (called w/ require('../mysqli_connect.php');) as well:
define('DB_USER', 'JohnS');
define('DB_PASSWORD', 'password12');
define('DB_HOST', 'localhost');
define('DB_NAME', 'book_db');
$dbc = @mysqli_connect (DB_HOST, DB_USER, DB_PASSWORD, DB_NAME) OR die('Could not connect to MySQL: ' . mysqli_connect_error() );
mysqli_set_charset($dbc, 'utf8');
I truly appreciate any help or pointers you may be able to provide, thanks all in advance!
question from:
https://stackoverflow.com/questions/65874582/mysqli-real-escape-string-and-or-mysqli-set-charset-not-working-properly 
