Link to home
Start Free TrialLog in
Avatar of Wanda Marston
Wanda MarstonFlag for Canada

asked on

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

Avatar of Terry Woods
Terry Woods
Flag of New Zealand image

Would you share the code that builds the database query?
Avatar of Wanda Marston

ASKER

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

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

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

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

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.
Have you got access to the error log for apache? That may shed some light on the error.
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

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

ASKER CERTIFIED SOLUTION
Avatar of Terry Woods
Terry Woods
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.