Improve company productivity with a Business Account.Sign Up

x
?
Solved

Registration Form will not take anything with an apostrophe.

Posted on 2016-09-25
14
Medium Priority
?
76 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.
1
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 

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.
1
 

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
 

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.
1
 
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 2000 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.
2

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This article discusses how to implement server side field validation and display customized error messages to the client.
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
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…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

580 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