Solved

Registration Form will not take anything with an apostrophe.

Posted on 2016-09-25
14
44 Views
Last Modified: 2016-09-26
I have a registration form that doesn't seem to take apostrophe's in any of the fields. I have checked with my hosting company to make sure that I am using the same version of MySql and they have assured me that we are compatible - Version 5.5.44.

	// Check for a first name:
	if (preg_match ('/^[A-Z \'.-]{2,20}$/i', stripslashes($_POST['first_name']))) {
		$fn = mysqli_real_escape_string ($connect, $_POST['first_name']); 
	} else {
		$reg_errors['first_name'] = 'Please enter your first name or NA!';
	}
	
		// Check for a middle name:
	if (preg_match ('/^[A-Z \'.-]{1,20}$/i', stripslashes($_POST['mid_initial']))) {
		$mi = mysqli_real_escape_string ($connect, $_POST['mid_initial']); 
	} else {
		$reg_errors['mid_initial'] = 'Please enter your middle name or initial or NA!';
	}
	
	// Check for a last name:
	if (preg_match ('/^[A-Z \'.-]{2,40}$/i', stripslashes($_POST['last_name']))) {
		$ln = mysqli_real_escape_string ($connect, $_POST['last_name']);
	} else {
		$reg_errors['last_name'] = 'Please enter your last name or NA!';
	}

Open in new window


Part of the error message that I receive.
[connect] => mysqli Object
(
[affected_rows] => -1
[client_info] => 5.5.34
[client_version] => 50534
[connect_errno] => 0
[connect_error] =>
[errno] => 1064
[error] => You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Meara', 'Agree', SUBDATE(NOW(), INTERVAL 1 DAY) )' at line 1
[field_count] => 2
[host_info] => tiredofbeingrippedoff.netfirmsmysql.com via TCP/IP
[info] =>
[insert_id] => 0
[server_info] => 5.6.32-78.1-log
[server_version] => 50632
[stat] => Uptime: 139478 Threads: 18 Questions: 61622130 Slow queries: 652 Opens: 1816721 Flush tables: 1 Open tables: 2000 Queries per second avg: 441.805
[sqlstate] => 00000
[protocol_version] => 10
[thread_id] => 1329834
[warning_count] => 0
)

Open in new window

0
Comment
Question by:wchirnside
  • 7
  • 7
14 Comments
 
LVL 35

Expert Comment

by:Terry Woods
ID: 41814971
Would you share the code that builds the database query?
0
 

Author Comment

by:wchirnside
ID: 41814994
Hoping that this is what you are looking for -

			$q = "INSERT INTO users (username, email, pass, first_name, mid_initial, last_name, agree, date_expires) VALUES ('" . stripslashes($u) . "', '$e', '"  .  get_password_hash($p) .  "', '" . stripslashes($fn) . "', '" . stripslashes($mi) . "', '" . stripslashes($ln) . "', '$agree', SUBDATE(NOW(), INTERVAL 1 DAY) )"; 
			

Open in new window

0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 41815008
Where you've already used mysqli_real_escape _string on the value, you shouldn't use stripslashes, as mysqli_real_escape _string has added backslashes to escape the ' character. You've stripped the backslash out by doing that.
0
 

Author Comment

by:wchirnside
ID: 41815017
Okay I think I have understood what you are saying. So you are saying that there would NOW be no backslash so I would think that the wording would go through BUT would have a slash in front of it - rather than just the name - O'Meara, for instance. So now you are saying that it also is not accepting the apostrophe as well as having no backslash. Just want to understand as this is old code and I believe it has worked up until just a few days ago.
0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 41815093
MySQL requires single quotes to be backslashed in order to include them in a query.

eg
insert into user (name) values ('O\'Meara');

Open in new window

which gets inserted into the database as O'Meara

The mysqli_real_escape_string ensures that dangerous characters such as single quotes (and backslashes too that are actually in the data, and anything else that needs it) are escaped with backslashes. Once that function has been run on the data, the data is ready to be used in a query. You definitely don't want to strip the slashes out again using stripslashes.
0
 

Author Comment

by:wchirnside
ID: 41815152
Okay I changed the MySql query but I seem to get the same error message.


			$q = "INSERT INTO users (username, email, pass, first_name, mid_initial, last_name, agree, date_expires) VALUES ('$u') , ('$e'), '" .  get_password_hash($p) .  "', ('$fn'), ('$mi'), ('$ln'), ('$agree'), SUBDATE(NOW(), INTERVAL 1 DAY) )";

Open in new window



[affected_rows] => -1
[client_info] => 5.5.34
[client_version] => 50534
[connect_errno] => 0
[connect_error] =>
[errno] => 1064
[error] => You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''?I? ???g???@???i?~4??J~?v7', ('O\'Day'), ('O\'Toole'), ('O\'Meara'), 'Agr' at line 1
[field_count] => 2
[host_info] => tiredofbeingrippedoff.netfirmsmysql.com via TCP/IP
[info] =>
[insert_id] => 0
[server_info] => 5.6.32-78.1-log
[server_version] => 50632
[stat] => Uptime: 156191 Threads: 24 Questions: 68871823 Slow queries: 662 Opens: 2001586 Flush tables: 1 Open tables: 2000 Queries per second avg: 440.946
[sqlstate] => 00000
[protocol_version] => 10
[thread_id] => 1476606
[warning_count] => 0

Open in new window

0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 41815167
The brackets aren't correct. Try:
			$q = "INSERT INTO users (username, email, pass, first_name, mid_initial, last_name, agree, date_expires) VALUES ('$u' , '$e', '" .  get_password_hash($p) .  "', '$fn', '$mi', '$ln', '$agree', SUBDATE(NOW(), INTERVAL 1 DAY) )";

Open in new window

0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:wchirnside
ID: 41815168
Now it seems that my server is not accepting the page at all.

Internal Server Error

The server encountered an internal error or misconfiguration and was unable to complete your request.

Please contact the server administrator, and inform them of the time the error occurred, and anything you might have done that may have caused the error.

More information about this error may be available in the server error log.

Open in new window

0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 41815170
Note also that you've only shown me code where mysqli_real_escape_string is used on the values for first name, middle name and last name.

You also need to ensure that the username and email are sanitised, and any other value that is obtained from the user.
0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 41815172
Have you got access to the error log for apache? That may shed some light on the error.
0
 

Author Comment

by:wchirnside
ID: 41815186
Following is the most recent code that I have in the Registration page. I will check into the server log.

    // Check for an agreement:
	if (isset($_POST['agree']) && ($_POST['agree'] == TRUE)) {
	$agree = 'Agree';
	} else {
   $reg_errors['agree'] = '<blink><h3a>Please agree to the user Terms and Conditions</h3a></blink><br />';
   }
		
	// Check for a first name:
	if (preg_match ('/^[A-Z \'.-]{2,20}$/i', stripslashes($_POST['first_name']))) {
		$fn = mysqli_real_escape_string ($connect, $_POST['first_name']); 
	} else {
		$reg_errors['first_name'] = 'Please enter your first name or NA!';
	}
	
		// Check for a middle name:
	if (preg_match ('/^[A-Z \'.-]{1,20}$/i', stripslashes($_POST['mid_initial']))) {
		$mi = mysqli_real_escape_string ($connect, $_POST['mid_initial']); 
	} else {
		$reg_errors['mid_initial'] = 'Please enter your middle name or initial or NA!';
	}
	
	// Check for a last name:
	if (preg_match ('/^[A-Z \'.-]{2,40}$/i', stripslashes($_POST['last_name']))) {
		$ln = mysqli_real_escape_string ($connect, $_POST['last_name']);
	} else {
		$reg_errors['last_name'] = 'Please enter your last name or NA!';
	}

	// Check for a username:
	if (preg_match ('/^[A-Z0-9 \'.-]{2,30}$/i', stripslashes($_POST['username']))) {
		$u = mysqli_real_escape_string ($connect, $_POST['username']);
	} else {
		$reg_errors['username'] = 'Please enter a desired name!';
	}
	
	// Check for an email address:
	if (filter_var($_POST['email'], FILTER_VALIDATE_EMAIL)) {
		$e = mysqli_real_escape_string ($connect, $_POST['email']);
	} else {
		$reg_errors['email'] = 'Please enter a valid email address!';
	}

	// Check for a password and match against the confirmed password:
	if (preg_match ('/^(\w*(?=\w*\d)(?=\w*[a-z])(?=\w*[A-Z])\w*){6,20}$/', $_POST['pass1']) ) {
		if ($_POST['pass1'] == $_POST['pass2']) {
			$p = mysqli_real_escape_string ($connect, $_POST['pass1']);
		} else {
			$reg_errors['pass2'] = 'Your password did not match the confirmed password!';
		}
	} else {
		$reg_errors['pass1'] = 'Please enter a valid password!';
	}
	
	if (empty($reg_errors)) { // If everything's OK...

		// Make sure the email address and username are available:
		$q = "SELECT email, username FROM users WHERE email='$e' OR username='$u'";
		$r = mysqli_query ($connect, $q);
		
		// Get the number of rows returned:
		$rows = mysqli_num_rows($r);
		
		if ($rows == 0) { // No problems!
			
			// Add the user to the database...
			
			// Temporary: set expiration to a month!
			// Change after adding PayPal!
			//$q = "INSERT INTO users (username, email, pass, first_name, last_name, date_expires) VALUES ('$u', '$e', '"  .  get_password_hash($p) .  "', '$fn', '$ln', ADDDATE(NOW(), INTERVAL 1 MONTH) )";
			
			
			// New query:
			// Sets expiration to yesterday:
			$q = "INSERT INTO users (username, email, pass, first_name, mid_initial, last_name, agree, date_expires) VALUES ('$u', '$e', '"  .  get_password_hash($p) .  "', '$fn', '$mi', '$ln', '$agree', SUBDATE(NOW(), INTERVAL 1 DAY) )"

Open in new window

0
 

Author Comment

by:wchirnside
ID: 41815207
I was able to get into the log and apparently I left out the semi colon at the end of the MySql query.

SO I go the page back up and entered the information into the form and it went through and the information is in the database.

So I believe that this is the code I sent recently and this is now correct. Does the following code reflect what you were telling me in the above dialog?

// For storing registration errors:
$reg_errors = array();

// Check for a form submission:
if ($_SERVER['REQUEST_METHOD'] == 'POST') {

    // Check for an agreement:
	if (isset($_POST['agree']) && ($_POST['agree'] == TRUE)) {
	$agree = 'Agree';
	} else {
   $reg_errors['agree'] = '<blink><h3a>Please agree to the user Terms and Conditions</h3a></blink><br />';
   }
		
	// Check for a first name:
	if (preg_match ('/^[A-Z \'.-]{2,20}$/i', stripslashes($_POST['first_name']))) {
		$fn = mysqli_real_escape_string ($connect, $_POST['first_name']); 
	} else {
		$reg_errors['first_name'] = 'Please enter your first name or NA!';
	}
	
		// Check for a middle name:
	if (preg_match ('/^[A-Z \'.-]{1,20}$/i', stripslashes($_POST['mid_initial']))) {
		$mi = mysqli_real_escape_string ($connect, $_POST['mid_initial']); 
	} else {
		$reg_errors['mid_initial'] = 'Please enter your middle name or initial or NA!';
	}
	
	// Check for a last name:
	if (preg_match ('/^[A-Z \'.-]{2,40}$/i', stripslashes($_POST['last_name']))) {
		$ln = mysqli_real_escape_string ($connect, $_POST['last_name']);
	} else {
		$reg_errors['last_name'] = 'Please enter your last name or NA!';
	}

	// Check for a username:
	if (preg_match ('/^[A-Z0-9 \'.-]{2,30}$/i', stripslashes($_POST['username']))) {
		$u = mysqli_real_escape_string ($connect, $_POST['username']);
	} else {
		$reg_errors['username'] = 'Please enter a desired name!';
	}
	
	// Check for an email address:
	if (filter_var($_POST['email'], FILTER_VALIDATE_EMAIL)) {
		$e = mysqli_real_escape_string ($connect, $_POST['email']);
	} else {
		$reg_errors['email'] = 'Please enter a valid email address!';
	}

	// Check for a password and match against the confirmed password:
	if (preg_match ('/^(\w*(?=\w*\d)(?=\w*[a-z])(?=\w*[A-Z])\w*){6,20}$/', $_POST['pass1']) ) {
		if ($_POST['pass1'] == $_POST['pass2']) {
			$p = mysqli_real_escape_string ($connect, $_POST['pass1']);
		} else {
			$reg_errors['pass2'] = 'Your password did not match the confirmed password!';
		}
	} else {
		$reg_errors['pass1'] = 'Please enter a valid password!';
	}
	
	if (empty($reg_errors)) { // If everything's OK...

		// Make sure the email address and username are available:
		$q = "SELECT email, username FROM users WHERE email='$e' OR username='$u'";
		$r = mysqli_query ($connect, $q);
		
		// Get the number of rows returned:
		$rows = mysqli_num_rows($r);
		
		if ($rows == 0) { // No problems!
			
			// Add the user to the database...
			
			// Temporary: set expiration to a month!
			// Change after adding PayPal!
			//$q = "INSERT INTO users (username, email, pass, first_name, last_name, date_expires) VALUES ('$u', '$e', '"  .  get_password_hash($p) .  "', '$fn', '$ln', ADDDATE(NOW(), INTERVAL 1 MONTH) )";
			
			
			// New query:
			// Sets expiration to yesterday:
			$q = "INSERT INTO users (username, email, pass, first_name, mid_initial, last_name, agree, date_expires) VALUES ('$u', '$e', '"  .  get_password_hash($p) .  "', '$fn', '$mi', '$ln', '$agree', SUBDATE(NOW(), INTERVAL 1 DAY) )";
				

Open in new window

0
 
LVL 35

Accepted Solution

by:
Terry Woods earned 500 total points
ID: 41815242
All the values are being sanitised, so yes, it looks great :-)
0
 

Author Closing Comment

by:wchirnside
ID: 41815922
Thank you for your quick and direct to the point answer. Most of us do not have time to read articles for example, or try 20 different types of things to make something work. Now I feel like I can rely on EE again for any help.
1

Featured Post

Easy Project Management (No User Manual Required)

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction HTML checkboxes provide the perfect way for a web developer to receive client input when the client's options might be none, one or many.  But the PHP code for processing the checkboxes can be confusing at first.  What if a checkbox is…
Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now