I'm using php 5.6.25 and trying to filter the input, but it is only entering the data once into mysqli

Question

I've tried a few ways of filtering and when I do I can only insert into the table once, the id is set to auto increment. This is how variables get defined.

<form action = "php/createaccount.php" method = "post">
First Name <br /> <input type ="text" placeholder="First name" name = "fname"><br />
Last Name <br /> <input type ="text" placeholder="Last name" name = "lname"><br />
User Name <br /> <input type ="text" placeholder="Username" name = "uname"><br />
Password <br /> <input type ="text" placeholder="Password" name = "pword"><br />
School Name <br /> <input type ="text" placeholder="School name" name = "sname"><br />
Email <br /> <input type ="text" placeholder="Email" name = "email"> <br /> 
<input type = "submit" value = "Create Account">
</form>

This is the createaccount.php page

<?php
error_reporting (-1); ini_set ("display_errors", "On");
$con = mysqli_connect("localhost" , "root" , "" , "epicreads");
//check connection
if (mysqli_connect_errno()) {
   echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$sql = $con->prepare("INSERT INTO users (fname, lname, email, uname, sname)
VALUES
(?, ?, ?, ?, ?)");
$sql->bind_param('sssss', $fname, $lname, $email, $uname, $sname);
$sql->execute();

//Password and password security
//Set the cost
$cost = 10;

// Create a random salt
$salt = strtr(base64_encode(mcrypt_create_iv(16, MCRYPT_DEV_URANDOM)), '+', '.');

// Hash the password with the salt
$hash = crypt($pword, $salt);

//Post into table
$pword = "INERT INTO users (pword)
values
('$_POST[pword]')";

//Echo
die('Error: ' . mysqli_error($con));

echo "1 record added";

mysqli_close($con);
?>

this is the updated createaccount.php page UPDATE: With error reporting on it is saying that column 'fname' cannot be null This is my table structure in mysqli

Answer 1

You have a typo here:

//Post into table
$pword = "INERT INTO users (pword)
values
('$_POST[pword]')";

You want to INSERT with an S.

Also:

// Hash the password with the salt
$hash = crypt($pword, $salt);

you're hashing the SQL statement, not the variable.

As recommended in comments you should be using password_hash, it appears you have a cost array value which is typical of password_hash so maybe you copy/pasted it from somewhere where it is used, go back and read the rest of the syntax.

Using your own salts is not advised.

Your issue here:

With error reporting on it is saying that column 'fname' cannot be null

Is because you're trying to enter a empty row; your password SQL, above, is inserting a password value into MySQL but you're probably wanting to UPDATE an existing row rather than INSERT a new row with just a password on it.

So what you should do is research password_hash and edit your password table column to be at least 72 characters long and then insert the password at the same time as you insert the rest of the data into the table.

If you need to edit a row that already exists you should use the UPDATE MySQL function.

You also have your

//Echo  
die('Error: ' . mysqli_error($con));

will run every time the script runs, regardless as if there is an error or if it is picked up. This is not the way to detect Object orientated MySQL errors. See this answer with a good rundown on how to detect MySQL errors in PHP.

There is also no guide as to where your variables come from? I assume you're already working with then before they reach the SQL INSERT but considering you are researching input filtering, I see no evidence of any input filtering on your question code. I hope you're not using global variables, typically posted for varaibles are accessible as $_POST['name'] superglobals once a form has been submitted.

---

<?php
error_reporting (-1);
ini_set ("display_errors", "On");
$con = mysqli_connect("localhost" , "root" , "" , "epicreads");
//check connection
if (mysqli_connect_errno()) {
   echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

/***
New password construct:
//Password and password security
//Set the cost    
***/
$option['cost'] = 10;
$password = password_hash($_POST['pword'], PASSWORD_BCRYPT, $option);


$sql = $con->prepare("INSERT INTO users (fname, lname, email, uname, sname, pword)
VALUES (?, ?, ?, ?, ?,?)");
$sql->bind_param('ssssss', $fname, $lname, $email, $uname, $sname,$password);
$check = $sql->execute();

// execute() can fail for various reasons. 
// And may it be as stupid as someone tripping over the network cable
// 2006 "server gone away" is always an option
if ( $check === false ) {
    //much better than a die statement: 
    error_log('execute() failed: ' . htmlspecialchars($sql->error));
}
else {
    echo "1 record added";
}

$sql->close();