My INSERT just will not work

QueenMaebee · October 13, 2021, 6:30am

I’ve been trying for the past day to setup this register system and the INSERT just wont work.

<?php

$con = mysqli_connect('localhost', 'root', 'root', 'creators');
if(mysqli_connect_errno())
{
	echo("1"); //error code #1 = connection failed
	exit();
};

$username = $_POST["username"];
$password = $_POST["password"];
$email = $_POST["email"];
$discord = $_POST["discord"];

$namecheckquery = "SELECT username FROM userprofiles WHERE username='" . $username . "';";
$namecheck = mysqli_query($con, $namecheckquery) or die("2: Namecheck query failed"); //error code #2 - namecheck query failed

if(mysqli_num_rows($namecheck) > 0){
	echo("3: Name Already Exists");
	exit();
};

$emailcheckquery = "SELECT email FROM userprofiles WHERE email='" . $email . "';";
$emailcheck = mysqli_query($con, $emailcheckquery) or die("5: emailCheck Query Failed"); //error code #2 - namecheck query failed
if(mysqli_num_rows($emailcheck) > 0){
	echo("6: Name Already Exists");
	exit();
};

$salt = "\$\5rounds=50000\$" . "stickyhoney" . $username . "\$";
$hash = crypt($password, $salt);
mysqli_query($con, "INSERT INTO userprofiles (username, hash, salt, email, discord) VALUES ('$username' , '$hash' , '$salt' , '$email' , '$discord')") or die("4: Insert Player Query Failed"); //this line has the issues and I dont know why

echo("0");

?>

phdr · October 13, 2021, 7:03am

I’m going to guess that you are getting your cryptic 4: Insert Player Query Failed message? If so, you need error handling that will display the actual error information when you are learning, developing, and debugging code/query(ies) and will log the actual error information on a live/public server. To do this, use exceptions for database statement errors and in most cases simply let php catch and handle the exception, where php 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.) The exception to this is when inserting/updating user submitted data. In this case, your code would catch the exception, test if the error number is for a duplicate index error (your username and email columns should each be defined as a unique index), and then set up a message telling the user what was wrong with the data that they submitted. For all other error numbers, just re-throw the exception and let php handle it. You would then remove all the existing error handling logic since it will no longer get executed upon an error.

As to the posted code -

Don’t use the root user for applications. Create a database user that only has the permission needed for you application.
Switch to the much simpler and more consistent PDO extension.
Your post method form processing code should detect if a post method form was submitted before accessing any of the form data.
Don’t copy variables to over variables for nothing.
Don’t put external, unknown, dynamic values directly into an sql query statement. Use a prepared query instead.
Don’t create verbose variable names for nothing.
Validate all independent input data at one time, by storing user/validation error messages in an array, using the field name as the array index. You would then test/display the content of this array when you re-display the form. This will allow the user to correct as many errors as possible at one time, rather than resubmitting the data for each error.
Don’t roll your own password hashing. Use php’s password_hash() and password_verify().
I suspect, but didn’t check, that the current problem is using a reserved word as a column name. If the above error handling indicates a sql syntax error due to a column name, rename the offending column(s) to something else.