Solved

Registration Form will not take anything with an apostrophe.

Posted on 2016-09-25
14
60 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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

If you are experiencing a similar issue, please ask a related question

These days socially coordinated efforts have turned into a critical requirement for enterprises.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo‚Ķ
The viewer will learn how to dynamically set the form action using jQuery.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

840 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