Jump to content
Larry Ullman's Book Forums

Script 9.5 mysqli_real_escape_string not working correctly when name has an apostrophe


Recommended Posts

Hello,

Basically, I'm on script 9.5, using mysqli_real_escape_string().  After making the changes to the 9.3 script, I went to enter the name information, but what's weird is this:  I can enter a name if there is no apostrophe or problematic characters, but still can't add to the database if those characters exist.  For example:

I can connect to the database and insert a record using "Sinead Oconnor" -- and when I look in the database, I can confirm that is uploading correctly

I get an error message if I try the same with "Sinead O'Connor" - and can confirm this data is not sent to the database.

Through research and testing, it looks like I am opening/closing the database connections the same way as the written in the book.  What's also weird is that if I go to the book's github, and download the file from the Chapter 9 folder, this same error occurs.  Normally, if I've gotten stuck, I've downloaded the book resources to compare to my code, and see if the book's code will work, and every time, when downloading the books code, it will run the script and I can identify my problem.  This time I'm stuck, because the php script is connecting to the database and updating, but not updating with an apostrophe in the name.

I checked the PHP manual and stack overflow, and it doesn't appear the mysqli_real_escape_string is deprecated, so I just wanted to see if someone else could look at this w/ 'fresh eyes' and maybe spot what I'm not seeing.  My PHP script is below. 

For what it's worth, the PHP version I'm on is PHP Version 7.2.8, and the error message I get is:

***You could not be registered due to a system error. We apologize for any inconvenience.

. mysqli_error($dbc) .

Query: INSERT INTO users (first_name, last_name, email, pass, registration_date) VALUES ('Sinead', 'O'Connor', 'jaz@music.com', SHA2('hello1', 512), NOW() )***

Thank you all in advance!

<?php # Script 9.3 - register.php

$page_title = 'Register';
include('/Applications/MAMP/htdocs/Chapter 9/includes/header.html');

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>jj0j';

        } // 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>\n";
        }
        echo '<p>Please try again.</p><p><br></p></div>';

    } //end of (empty($errors)) IF

mysqli_close($dbc); //Close the db connection.
 
} //end of main submit conditional

?>

 

Link to comment
Share on other sites

This is very weird. You've done good detective work but it doesn't seem like mysqli_real_escape_string() is doing what it's supposed to be doing. I'm kind of guessing here, but mysqli_real_escape_string() requires that the CHARSET is established. I'd start by making your your MySQL connection script does that.

mysqli_set_charset($dbc, 'utf8');

 

Link to comment
Share on other sites

Hey Larry,

Thanks for getting back to me, I really appreciate it.  Also, really digging the book, I'm starting to feel much more confident in the PHP and SQL -- the book and forum is very helpful in learning this, and this knowledge has helped me me a ton in my work, so thank you!

 

Regarding your suggestion, I double checked my script, and it has the CHARSET in it, I'll attach that code below.  One other thought I had is that it might have something to due with a setting in mySQL.  In chapter 7, I can't remember the script, but I got an error, and upon reading the forums, I found out I needed to turn of 'ONLY_FULL_GROUP_BY' in mySQL DB - > Variables, due to a change in the most recent version of mySQL from the publication of the book.  I was specifically looking to see if there was a setting/feature that has been put into place in either mySQL or PHP  that would affect the php function in the last couple years, but I can't seem to find anyone mentioning that being the case.  Do you think there is a setting in mySQL variables that may be overriding the php input of mysqli_real_escape_string()?

 

Thanks again for your help and insight into this matter!

<?php #Script 9.2 - mysqli_connect.php

    define('DB_USER', 'JohnS');
    define('DB_PASSWORD', 'DumbDog2018!');
    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: ' . my_sqli_connect_error() );

    mysqli_set_charset($dbc, 'utf8');

 

Link to comment
Share on other sites

You have a syntax error in your connection script: "my_sqli_connect..."

Working backwords, mysqli_real_escape_string() won't work--won't escape an apostrophe--if it doesn't have access to a database connection with an established CHARSET. To the problem should be either with the database connection or the charset not being set.

First, fix the syntax error and then try again. If that doesn't work, print out the value of $dbc to confirm that it's an object. If it has a false value, that's a problem.

If you're still not seeing the cause, connect directly to MySQL using the terminal (command line) to confirm your values.

And thanks for the nice words on the book!

Link to comment
Share on other sites

Thanks for the reply, Larry.  I double checked my database in myPHP admin, to make sure it's using UTF8, and it says the collation is 'utf8_general_ci' but that's how all my databases have been set up, so I think that's good to go.

I fixed the mysqli_connect syntax, but the issue is still happening where I can enter a name w/o an apostrophe, but not one including the special character. I had some issue printing the value of $dbc to confirm it's an object:

require('../mysqli_connect.php');

echo "$dbc";

and got the error message: 

Recoverable fatal error: Object of class mysqli could not be converted to string in /Applications/MAMP/htdocs/Chapter 9/register_safe.php on line 110;

I also tried:

require('../mysqli_connect.php');
 
 if($dbc) {
            echo "$dbc";
 } else {
     echo '$dbc not working';
 }

and the else statement didn't print, but rather I got this error again: Recoverable fatal error: Object of class mysqli could not be converted to string in...

I searched google, and it looks like trying to print the value of $dbc isn't working for me, because PHP is looking for mysql_fetch_array() or mysqli_fetch_assoc() to be used to pull data from the database, so maybe I'm misunderstanding how to print the value of $dbc in the browser? 

I can say that the records returned in view_users.php is working correctly for the existing records.

I also checked the values of the database login information on phpmyadmin, and they appear correctly entered into my mysquli_connect.php script.

I appreciate yourhelp on this.  I'll keep working through this and let you know if I can find anything else on my end.

 

Link to comment
Share on other sites

I do have an update on this topic, maybe it can help to shed some more light.  I double checked my SQL in phpMyAdim -- and my tables have all been collated in utf8_general_ci, but the server collation (in phpMyAdmin) was set to: utf9mb4_unicode_ci.  I changed the server collation to match utf8_general_ci, but it looks like the error is still persistent. I also changed the mysqli_set_charset() in mysqli_connnect.php to ...($dbc, 'utf8_general_ci) to see if that would have any affect, but it looks like that's not the case.  

I also accessed the mySQL from the terminal and could find/edit all entries there as well.  I can also upload a name like 'o'shanuassay' from within phpMyAdmin or from the terminal, and that name will display correctly on view_users.php.  A couple other tests that I did was to to test other special characters, like quotation marks and a hash tag, and those characters will upload to the database from the html form just fine.  I believe this is to be expected though, as I gather the mysqli_real_escape_string() is designed primarily to escape those apostrophes, in this instance. I also put the CHARSET function in the register.php file, like so, to see if it wasn't being executed in the mysqli_connect file:

 

$page_title = 'Register';
include('/Applications/MAMP/htdocs/Chapter 9/includes/header.html');

if ($_SERVER['REQUEST_METHOD'] == 'POST') {

    require('../mysqli_connect.php');

    mysqli_set_charset($dbc, 'utf8');

    $errors = []; //initialize an error array.

but even in putting that mysqli_set_charset() in register.php doesn't seem to change the process one way or the other.

Another thought I had, is because the error code I'm getting comes from this section:

 $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

I'm wondering why this if/else function would essentially return $r as true when there is no apostrophe in a given input field, but then false/else if that apostrophe exists.

On another note, I published this problem on another forum, and the users there basically told me to not use this mysqli_real_escape_string() function, but instead to use a Prepared Statement as it negates the need to escape in this instance.  It looks like in Chapter 13 there's a section about the benefits of using this method instead.  I'd hate to leave this unresolved, as I'd like to know what is going wrong to avoid it in the future,  but do you think it'd be wise to just rewrite the code as a prepared statement as suggested?  

Again, I appreciate your time and input on this; Thank You!

Link to comment
Share on other sites

Sorry for the delay; this is a super random issue that I've never seen before. I'm not finding anything relevant in Google searches, either. 

Yes, you can use prepared statements, which don't use mysqli_real_escape_string() at all. That's totally fine, if not a better end result.

If you want to continue debugging this, change your code to this, try it, and let me know what the result is:

} else {
    echo '<p>Submitted last name: ' . $_POST['last_name'] . '</p>';
    echo '<p>Established charset: ' . mysqli_get_charset($dbc) . '</p>';
    $ln = mysqli_real_escape_string($dbc, trim($_POST['last_name']));
    echo '<p>Processed last name: ' . $ln . '</p>';
}

 

Link to comment
Share on other sites

No worries -- I'm cool to keep debugging it.

Based on your prior response, I put this code on the page:

if($dbc) {
        echo "$dbc";

    } else {
        
    echo '<p>Submitted last name: ' . $_POST['last_name'] . '</p>';
    echo '<p>Established charset: ' . mysqli_get_charset($dbc) . '</p>';
    $ln = mysqli_real_escape_string($dbc, trim($_POST['last_name']));
    echo '<p>Processed last name: ' . $ln . '</p>';
}

And when I do so, I see this printed:

Quote

 

Submitted last name:

Established charset:

Processed last name:

 

So it prints the HTML elements, but not the information called by PHP.

For reference, here's what my HTML code looks like on register.php, for the form inputs -- maybe there is an error here?

<div style="margin: 100px;"><h1>Register</h1>
<form action="register.php" method="post">
    <p>First Name: <input type="text" name="first_name" size="15" maxlength="20" value="<?php
    if (isset($_POST['first_name'])) echo $_POST['first_name'];?>"></p>
    
    <p>Last Name: <input type="text" name="last_name" size="15" maxlength="40" value="<?php
    if (isset($_POST['last_name'])) echo $_POST['last_name'];?>"></p>
    
    <p>E-Mail: <input type="text" name="email" size="15" maxlength="60" value="<?php
    if (isset($_POST['email'])) echo $_POST['email'];?>"></p>

    <p>Password: <input type="text" name="pass1" size="10" maxlength="20" value="<?php
    if (isset($_POST['pass1'])) echo $_POST['pass1'];?>"></p>

    <p>Confirm Password: <input type="text" name="pass2" size="10" maxlength="20" value="<?php
    if (isset($_POST['pass2'])) echo $_POST['pass2'];?>"></p>

    <p><input type="submit" value="Register"></p>
</form>

</div>

Thanks again for your help and insight!

Link to comment
Share on other sites

Sorry, I should have been more clear. The original else clause for handling the last name is this:
 

} else {
    $ln = mysqli_real_escape_string($dbc, trim($_POST['last_name']));
}

Replace that with what I previously posted and then fill out the form--using an apostrophe in the last name value--and submit the form to see the results. But that mysqli_get_charset() returns nothing is informative already. 

The problem isn't going to be with your HTML form. The values are getting to PHP fine, they're just not being escaped by mysqli_real_escape_string(). 

Link to comment
Share on other sites

Hey, I'm going to test that new code you sent later today, but I've been busy on my end. Either way, I was looking into making this a prepared statement, and think I got that aspect figured out, but I noticed something that I think eluded me before.  Essentially, we have the register.php for 9.3, originally made without the mysqli_real_escape_string, and at the bottom of which, we have HTML code, that creates the input, using form action="register.php". 

As I've been working on this, I noticed there was a typo that was displaying at the bottom of my screen, 'jj0j' via HTML. I found that same typo in the php file, when changing 9.3 to 9.5, so I deleted the typo, added the mysqil_real_esacpes, and saved the file (9.5) as register_safe.php.  I then changed my header.html section to look like this:

body>
<nav class="navbar navbar-default navbar-fixed-top">
	<div class="container">
		<div class="navbar-header"><a class="navbar-brand" href="index.php">Your Website</a></div>
		<div id="navbar" class="collapse navbar-collapse">
			<ul class="nav navbar-nav">
				<li class="active"><a href="index.php">Home</a></li>
				<li><a href="register_safe.php">Register</a></li>
				<li><a href="view_users.php">View Users</a></li>
				<li><a href="password.php">Change Password</a></li>
			</ul>
		</div>
	</div>
</nav>

I then started to hit the snag, and I think I became 'blind' to this, but that typo I deleted from 9.5 'jj0j' started to show again in the error message, when I would not be able to insert a name w/ an apostrophe. On recently testing, what I'm noticing is when I click on the Register button on the header, I'm taken to register_safe.php. Same w/ View users. But, when I go to actually submit, I can submit any name w/o an apostrophe fine, but get the error w/ the apostrophe. I've noticed that my URL changes though upon hitting the submit button, so it'll start at:

http://localhost:8888/Chapter 9/register_safe.php

But when I click the submit button w/ o'hampton in the last name field, the URL changes back to:

http://localhost:8888/Chapter 9/register.php

I further tested this by adding test to the appropriate HTML, so the error would say '...this message is a test of new theory' but the standard default message from register.php only prints, along with the typo 'jj0J'.

So then, I looked at the HTML form again, and noticed I did not update the <Form Action"=register.php"> to <Form Action="register_safe.php">. I changed this, so the from HTML now reads:

 

<div style="margin: 100px;"><h1>Register</h1>
<form action="register_safe.php" method="post">
    <p>First Name: <input type="text" name="first_name" size="15" maxlength="20" value="<?php
    if (isset($_POST['first_name'])) echo $_POST['first_name'];?>"></p>
    
    <p>Last Name: <input type="text" name="last_name" size="15" maxlength="40" value="<?php
    if (isset($_POST['last_name'])) echo $_POST['last_name'];?>"></p>
    
    <p>E-Mail: <input type="text" name="email" size="15" maxlength="60" value="<?php
    if (isset($_POST['email'])) echo $_POST['email'];?>"></p>

    <p>Password: <input type="text" name="pass1" size="10" maxlength="20" value="<?php
    if (isset($_POST['pass1'])) echo $_POST['pass1'];?>"></p>

    <p>Confirm Password: <input type="text" name="pass2" size="10" maxlength="20" value="<?php
    if (isset($_POST['pass2'])) echo $_POST['pass2'];?>"></p>

    <p><input type="submit" value="Register"></p>
</form>

</div>

However, after reloading the site, it looks like when I go to enter that name, the URL is still reverting to register.php, rather than running the form action from register_safe.php. It's like I have an incorrect link somewhere, where the form is wanting to use the code and error message from 9.3 register.php instead of using the form action of 9.5 to use register_safe.php.

I know this is a different direction than you posted before, and I'll still run that test and let you know what come up -- but I wouldn't be surprised if there really isn't an issue with mysqli_real_escape_string, but rather an issue with the form action or page linking continuing to reference the code from 9.3 on submittal of the form's button, rather than running w/ and displaying the error code from 9.5.

Let me know your thoughts on this hypothesis, thanks again for all your help, I'll post when I get a chance to run that code from your last post.

Link to comment
Share on other sites

Ah, okay, so it could definitely be possible that you're just not running the code you think you're running. Specifically: you're still submitting the form to the older version of register.php, which does not mysqli_real_escape_string(). That would solve this mystery. 

Link to comment
Share on other sites

On 2/5/2021 at 8:39 AM, Larry said:

Ah, okay, so it could definitely be possible that you're just not running the code you think you're running. Specifically: you're still submitting the form to the older version of register.php, which does not mysqli_real_escape_string(). That would solve this mystery. 

Lol, wow that was it.  Literally just not double checking my form action in the HTML section was causing the script to be ran on the old, register.php. At least, I'll now know to double check that aspect, and hopefully won't make this mistake again. 

I really appreciate your help in figuring out this issue, even though it was a mistake in the HTML form action on my end, at least we were able to get it working. 

Thanks Again Larry!

Link to comment
Share on other sites

 Share

×
×
  • Create New...