• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 74
  • Last Modified:

Registration Form will not take anything with an apostrophe.

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
wchirnside
Asked:
wchirnside
  • 7
  • 7
1 Solution
 
Terry WoodsIT GuruCommented:
Would you share the code that builds the database query?
0
 
wchirnsideAuthor Commented:
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
 
Terry WoodsIT GuruCommented:
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!

 
wchirnsideAuthor Commented:
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
 
Terry WoodsIT GuruCommented:
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
 
wchirnsideAuthor Commented:
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
 
Terry WoodsIT GuruCommented:
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
 
wchirnsideAuthor Commented:
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
 
Terry WoodsIT GuruCommented:
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
 
Terry WoodsIT GuruCommented:
Have you got access to the error log for apache? That may shed some light on the error.
0
 
wchirnsideAuthor Commented:
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
 
wchirnsideAuthor Commented:
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
 
Terry WoodsIT GuruCommented:
All the values are being sanitised, so yes, it looks great :-)
0
 
wchirnsideAuthor Commented:
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

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 7
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now