How to save data in MYSQL database

[slotegraafd]

Hi!

So due to this pandemic I've decided to do some programming just for fun in preparation for school in september. I am currently focused on just creating a simple login and registration page. The registration page is supposed to add the users entered data into the MYSQL database and then redirect to the home page. That works just fine. But when I try to login using the information the user registered with it gives me the error that i created that it is incorrect which it is not so I don't think it's actually saving and I'm unsure why...

 

This is the code for my server and creating the errors and what its supposed to do when the button is pressed

<?php 
	session_start();

	// variable declaration
	$username = "";
	$email    = "";
	$errors = array(); 
	$_SESSION['success'] = "";

	// connect to database
	$db = mysqli_connect('localhost', 'root', 'deanna1999', 'registration');

	// REGISTER USER
	if (isset($_POST['registerbtn'])) {
		// receive all input values from the form
		$username = mysqli_real_escape_string($db, $_POST['username']);
		$email = mysqli_real_escape_string($db, $_POST['email']);
		$password_1 = mysqli_real_escape_string($db, $_POST['password_1']);
		$password_2 = mysqli_real_escape_string($db, $_POST['password_2']);

		// form validation: ensure that the form is correctly filled
		if (empty($username)) { array_push($errors, "Username is required"); }
		if (empty($email)) { array_push($errors, "Email is required"); }
		if (empty($password_1)) { array_push($errors, "Password is required"); }

		if ($password_1 != $password_2) {
			array_push($errors, "The two passwords do not match");
		}

		// register user if there are no errors in the form
		if (count($errors) == 0) {
			$password = md5($password_1);//encrypt the password before saving in the database
			$query = "INSERT INTO users (username, email, password) 
					  VALUES('$username', '$email', '$password')";
			mysqli_query($db, $query);

			$_SESSION['username'] = $username;
			$_SESSION['success'] = "You are now logged in";
			header('location: home.php');
		}

	}

	// ... 

	// LOGIN USER
	if (isset($_POST['login'])) {
		$username = mysqli_real_escape_string($db, $_POST['username']);
		$password = mysqli_real_escape_string($db, $_POST['password']);

		if (empty($username)) {
			array_push($errors, "Username is required");
		}
		if (empty($password)) {
			array_push($errors, "Password is required");
		}

		if (count($errors) == 0) {
			$password = md5($password);
			$query = "SELECT * FROM users WHERE username='$username' AND password='$password'";
			$results = mysqli_query($db, $query);

			if (mysqli_num_rows($results) == 1) {
				$_SESSION['username'] = $username;
				$_SESSION['success'] = "You are now logged in";
				header('location: home.php');
			}else {
				array_push($errors, "Wrong username/password combination");
			}
		}
	}

?>

 

[jodunno]

Hi slotegraafd,

I'm just a normal user here so you should wait for pros to help you. However, i would still like to offer my opinion about your posted code: pdo is a safer solution to interacting with a database. I recommend that you switch to pdo: https://phpdelusions.net/pdo

I've never understood error arrays and pushing data into them. a simple binary switch can be used to deal with error scenarios and a variable or array for error messages only:

$error = 0; if (empty(bla_bla)) { $errors = 1; $message = 'bla_bla contains no usable data'; }
if($errors) { //code to handle errors }
//else continue or no else if header relocation exit is used if $errors

if one of the required fields is empty or erroneous then just cut out completely and stop evaluating the rest of the data.

you should use password_verify to check the password. MAJOR security error here. Also, hashing passwords as a student testing login scripts is not necessary but it is absolutely necessary on live site. encryption is not a protection mechnism. Use hashes. skip for now but never forget to hash the passwords (which also requires a rehash if php changed something as the default encryption method.)

you use a header relocate without an exit:
header('location: home.php');

change this to:
header('location: home.php'); exit;

to stop evaluation of the rest of the script.

i wouldn't escape input. I recommend that you validate input then compare login values. In any event, just use htmlentities with ENT_QUOTES or html special chars before outputting post data or using it in anyway.

you have the following code:

f (mysqli_num_rows($results) == 1) 

you need to verify that the usernames match and that the passwords match:

if ($username === $resultfromdb && password_verify()) { } else {}

password verify works like so:

if (hash_equals($usernameFromDB, $username) && password_verify($password, $passwordFromDB)) { } else { }

Start with pdo then try again. I'm sure that pro members will help you further.

Good luck and i hope that you switch to pdo for security purposes. Learn proper coding early to save many headaches and problems.

[slotegraafd]

22 minutes ago, jodunno said:

Hi slotegraafd,

I'm just a normal user here so you should wait for pros to help you. However, i would still like to offer my opinion about your posted code: pdo is a safer solution to interacting with a database. I recommend that you switch to pdo: https://phpdelusions.net/pdo

I've never understood error arrays and pushing data into them. a simple binary switch can be used to deal with error scenarios and a variable or array for error messages only:

$error = 0; if (empty(bla_bla)) { $errors = 1; $message = 'bla_bla contains no usable data'; }
if($errors) { //code to handle errors }
//else continue or no else if header relocation exit is used if $errors

if one of the required fields is empty or erroneous then just cut out completely and stop evaluating the rest of the data.

you should use password_verify to check the password. MAJOR security error here. Also, hashing passwords as a student testing login scripts is not necessary but it is absolutely necessary on live site. encryption is not a protection mechnism. Use hashes. skip for now but never forget to hash the passwords (which also requires a rehash if php changed something as the default encryption method.)

you use a header relocate without an exit:
header('location: home.php');

change this to:
header('location: home.php'); exit;

to stop evaluation of the rest of the script.

i wouldn't escape input. I recommend that you validate input then compare login values. In any event, just use htmlentities with ENT_QUOTES or html special chars before outputting post data or using it in anyway.

you have the following code:

f (mysqli_num_rows($results) == 1) 

you need to verify that the usernames match and that the passwords match:

if ($username === $resultfromdb && password_verify()) { } else {}

password verify works like so:

if (hash_equals($usernameFromDB, $username) && password_verify($password, $passwordFromDB)) { } else { }

Start with pdo then try again. I'm sure that pro members will help you further.

Good luck and i hope that you switch to pdo for security purposes. Learn proper coding early to save many headaches and problems.

Hi, that's all very helpful but that doesnt really have anything to do with the problem I am having. All I need to know is why the information is not being stored into the database after the user registers and why it wont let me log in with the registered data

[mac_gyver]

1 hour ago, slotegraafd said:

I don't think it's actually saving

you can examine the data in the database using a tool like phpmyadmin.

next, you should have php's error_reporting set to E_ALL and display_errors set to ON, preferably in the php.ini on your system, so that php will help you by reporting and displaying all the errors that it detects. while you are making changes to the php.ini, set output_buffering to OFF, so that any messages from your code or non-fatal php error messages will be seen and not discarded at the header() redirects. you should also have error handling for all the statements that can fail. for database statements, just use exceptions for errors and in most cases let php catch and handle the exception, where it will use its error related settings to control what happens with the actual error information (database statement errors will 'automatically' get displayed/logged the same as php errors.) if you need, someone can post how to enable exceptions for errors for the mysqli database extension or if you switch to the much simpler PDO database extension.

[slotegraafd]

27 minutes ago, mac_gyver said:

you can examine the data in the database using a tool like phpmyadmin.

next, you should have php's error_reporting set to E_ALL and display_errors set to ON, preferably in the php.ini on your system, so that php will help you by reporting and displaying all the errors that it detects. while you are making changes to the php.ini, set output_buffering to OFF, so that any messages from your code or non-fatal php error messages will be seen and not discarded at the header() redirects. you should also have error handling for all the statements that can fail. for database statements, just use exceptions for errors and in most cases let php catch and handle the exception, where it will use its error related settings to control what happens with the actual error information (database statement errors will 'automatically' get displayed/logged the same as php errors.) if you need, someone can post how to enable exceptions for errors for the mysqli database extension or if you switch to the much simpler PDO database extension.

I was going to try using phpmyadmin but i dont really know how to download it, or use it

[mac_gyver]

most of the all-in-one development systems (XAMPP...) include phpmyadmin. which/how did you obtain - your development system?

[slotegraafd]

1 minute ago, mac_gyver said:

most of the all-in-one development systems (XAMPP...) include phpmyadmin. which/how did you obtain - your development system?

I'm using XAMPP if thats what you're asking

[mac_gyver]

in the XAMPP control panel, the MySQL Admin tab opens phpmyadmin.

[slotegraafd]

oh I see, so i click on that but now its saying access denied and it cant connect

[mac_gyver]

according to your php script, you have changed the database root password to be 'deanna1999'. you will need to change phpmyadmin's configuration password setting to be the same.

[slotegraafd]

whenever I try to start the mysql thing in xampp it says it shut down unexpectedly there is something wrong with the port, and the password is the password I used for the database that I have using MYSQL workbench

[Barand]

If you have Workbench why are you even bothering with phpMyAdmin?

[slotegraafd]

1 hour ago, Barand said:

If you have Workbench why are you even bothering with phpMyAdmin?

Sigh I feel like a broken record. Let me start over. All I am trying to do is figure out why the data that the user registers wont be saved in the database

[Barand]

As you aren't checking for MySQL errors, put this line just before you connect with mysqli_connect()

mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT); 

That will check for errors for you.

[slotegraafd]

No errors showed up... Still having the same problem

 

[gizmola]

1 hour ago, slotegraafd said:

Sigh I feel like a broken record. Let me start over. All I am trying to do is figure out why the data that the user registers wont be saved in the database

With all due respect to you, you are an admitted novice.  Many of the people who have replied to you have developed systems with php and mysql professionally for years if not decades.  People are asking you to verify some things for a reason.  When I'm diagnosing something, I may be running through a mental checklist that includes a vast number of variables you aren't aware of, having coded for a living.

• You think that it might not be saving the data (but you aren't sure)
• It could be saving the data, but just not reading it back
• You need error reporting turned on to see if there are hidden runtime errors or warnings that will pinpoint a problem

Many people here will help you with your problem, but I will not for one reason only. 

I don't need you to change to PDO, although I agree it's a far nicer API to work with than mysqli. 

But I absolutely will not help anyone who is not using bound parameters and prepared statements.  It's dangerous obsolete coding.  Your code (including storing the passwords as md5 hashes without even a salt!!! harkens back to a time 10+ years in the past.  Whether this is a hobby or not, there is no reason to write obsolete code when you can just as easily write modern code.

It would take at most 10 minutes to read about the technique and add the code you would need to utilize that parameters.  I can't be bothered to help someone debug something that is teaching them an improper practice any more than an electrician would teach someone how to work on wiring in their home, and not insist they turn off the circuit breaker and verify it was off with a multimeter.

I'm not saying that you are the type of person who is stubborn and can't or won't try and learn, but in the past when people start to react the way you did as illustrated by your quoted comment, it's someone who is stubborn and easily offended.  That does not lead to learning and a valuable expenditure of my time or the time of the other volunteers who answer questions.

[gizmola]

Here's one obvious potential issue with your code.

 

$query = "SELECT * FROM users WHERE username='$username' AND password='$password'";
			$results = mysqli_query($db, $query);

			if (mysqli_num_rows($results) == 1) {

This is fine so long as there is one and only one row in the db with that username and password.  We don't know if that's actually true in your case.  Typically you might have a unique constraint/index on username to prevent this, but if you don't have that, you might have unknowingly entered 2+ rows with the same username and password.

This test is going to fail in that case, at the moment you have 2 rows.  

Obviously you don't want this to be possible, but without knowing how your user table is set up, we can't be sure.  

[slotegraafd]

35 minutes ago, gizmola said:

With all due respect to you, you are an admitted novice.  Many of the people who have replied to you have developed systems with php and mysql professionally for years if not decades.  People are asking you to verify some things for a reason.  When I'm diagnosing something, I may be running through a mental checklist that includes a vast number of variables you aren't aware of, having coded for a living.

• You think that it might not be saving the data (but you aren't sure)
• It could be saving the data, but just not reading it back
• You need error reporting turned on to see if there are hidden runtime errors or warnings that will pinpoint a problem

Many people here will help you with your problem, but I will not for one reason only. 

I don't need you to change to PDO, although I agree it's a far nicer API to work with than mysqli. 

But I absolutely will not help anyone who is not using bound parameters and prepared statements.  It's dangerous obsolete coding.  Your code (including storing the passwords as md5 hashes without even a salt!!! harkens back to a time 10+ years in the past.  Whether this is a hobby or not, there is no reason to write obsolete code when you can just as easily write modern code.

It would take at most 10 minutes to read about the technique and add the code you would need to utilize that parameters.  I can't be bothered to help someone debug something that is teaching them an improper practice any more than an electrician would teach someone how to work on wiring in their home, and not insist they turn off the circuit breaker and verify it was off with a multimeter.

I'm not saying that you are the type of person who is stubborn and can't or won't try and learn, but in the past when people start to react the way you did as illustrated by your quoted comment, it's someone who is stubborn and easily offended.  That does not lead to learning and a valuable expenditure of my time or the time of the other volunteers who answer questions.

First things first dont friggen attack me okay? I am NEW TO PROGRAMMING i know nothing! Hense the reason why the program that I am creating is so stupid and doesnt work and all the other insults you gave me. I wasnt against the idea of using the error reporting because I did and IT SHOWED ME NOTHING. I was simply asking for other suggestions. So if you arent gonna be helpful then leave me the hell alone. And yes I do have a lot of damn attitude.

[slotegraafd]

29 minutes ago, gizmola said:

Here's one obvious potential issue with your code.

 

$query = "SELECT * FROM users WHERE username='$username' AND password='$password'";
			$results = mysqli_query($db, $query);

			if (mysqli_num_rows($results) == 1) {

This is fine so long as there is one and only one row in the db with that username and password.  We don't know if that's actually true in your case.  Typically you might have a unique constraint/index on username to prevent this, but if you don't have that, you might have unknowingly entered 2+ rows with the same username and password.

This test is going to fail in that case, at the moment you have 2 rows.  

Obviously you don't want this to be possible, but without knowing how your user table is set up, we can't be sure.  

It only has one row of data I entered a test one just to well test. cuz its only supposed to be checking for one because I don't want users with duplicate usernames

[gizmola]

19 hours ago, slotegraafd said:

First things first dont friggen attack me okay? I am NEW TO PROGRAMMING i know nothing! Hense the reason why the program that I am creating is so stupid and doesnt work and all the other insults you gave me. I wasnt against the idea of using the error reporting because I did and IT SHOWED ME NOTHING. I was simply asking for other suggestions. So if you arent gonna be helpful then leave me the hell alone. And yes I do have a lot of damn attitude.

I didn't attack you.  I took a lot of time to explain to you why others have requested information or changes you seem resistant to.  This is not how to engage people to help you (for free no less).  If I want to attack someone in a forum reply, I can do a pretty good job of that, but that is not why I'm here.  I've actually answered easily 10k questions from beginners or intermediate developers like yourself.

Did you carefully read my reply?  I took a good amount of time explaining the urgency and importance of writing modern safe mysqli code.  Please read this, modify your code and provide an update.  In the process it might fix whatever issue you have, but if it doesn't, people will most likely continue to aid you, if you show the effort you've made to learn and implement their suggestions.  

Last but not least, are you using a tutorial to base your code on?  If so, let us know which one you are following.  Based on the mysql code, that could be contributing to your problems.

[gizmola]

19 hours ago, slotegraafd said:

It only has one row of data I entered a test one just to well test. cuz its only supposed to be checking for one because I don't want users with duplicate usernames

Good to know that you have determined that you only have one row in there.  That is progress, if you are sure.   

That code does not insure you only have one row for a given username.  That should be enforced at the database level by adding a unique index to the users.username table.  You can do that with phpMyAdmin/workbench etc. or learn to write the SQL statement that does it.  The tools simply write the DDL for you and execute it, but SQL DDL is not difficult to read or understand.

Here's the actual statement:

CREATE UNIQUE INDEX users_username_idx ON users (username) 

If this can not be run due to a duplicate index error, then you would be covered.  If however you get an error because there are already multiple duplicate usernames in the table, it will not be possible to create the index, and you would need to clean that up before you can create the index.

Back to your statement: it only checks if there is one and only one row in the database.  If there is an error in your current code (and you have stated that there is) it's possible that more than one identical row exists.  In that case, your code will generate an incorrect result.  Even though there is actually > 1 user rows with that username and pw, your code treats that situation as if there are none.  

[slotegraafd]

46 minutes ago, gizmola said:

Good to know that you have determined that you only have one row in there.  That is progress, if you are sure.   

That code does not insure you only have one row for a given username.  That should be enforced at the database level by adding a unique index to the users.username table.  You can do that with phpMyAdmin/workbench etc. or learn to write the SQL statement that does it.  The tools simply write the DDL for you and execute it, but SQL DDL is not difficult to read or understand.

Here's the actual statement:

CREATE UNIQUE INDEX users_username_idx ON users (username) 

If this can not be run due to a duplicate index error, then you would be covered.  If however you get an error because there are already multiple duplicate usernames in the table, it will not be possible to create the index, and you would need to clean that up before you can create the index.

Back to your statement: it only checks if there is one and only one row in the database.  If there is an error in your current code (and you have stated that there is) it's possible that more than one identical row exists.  In that case, your code will generate an incorrect result.  Even though there is actually > 1 user rows with that username and pw, your code treats that situation as if there are none.  

Didn't you say you werent going to help me?

[gizmola]

36 minutes ago, slotegraafd said:

Didn't you say you werent going to help me?

I'm not going to help you debug your php/mysql code if it's going to use php interpolation of variables into SQL strings.  It's up to you if you are going to adopt modern practices.

[jodunno]

On 5/12/2020 at 6:01 PM, slotegraafd said:

Hi, that's all very helpful but that doesnt really have anything to do with the problem I am having. All I need to know is why the information is not being stored into the database after the user registers and why it wont let me log in with the registered data

Hi again,

I have 20+ years experience in programming. Do whatever you want but consider the following:

When i first tried xampp with phymyadmin i couldn't login to my site either. I had to switch to the console. I rebuilt my database through the console and i logged in just fine. I think that phpmyadmin is difficult to use somehow. I filled in all of the data but it wasn' working. I find that the console makes it easier to build a database. Try to verify that your database is working using the console. If you need help with that, then let us know. Honestly, i have no problems since i switched to the console.

Good luck.

 

[slotegraafd]

On 5/14/2020 at 4:16 PM, jodunno said:

Hi again,

I have 20+ years experience in programming. Do whatever you want but consider the following:

When i first tried xampp with phymyadmin i couldn't login to my site either. I had to switch to the console. I rebuilt my database through the console and i logged in just fine. I think that phpmyadmin is difficult to use somehow. I filled in all of the data but it wasn' working. I find that the console makes it easier to build a database. Try to verify that your database is working using the console. If you need help with that, then let us know. Honestly, i have no problems since i switched to the console.

Good luck.

 

Oh well, I am currently using MYSQL im not even using phpmyadmin and i wouldnt know how to use the pdo you suggested im more familiar with mysql