Brandrally Brandrally - 4 months ago 19
PHP Question

Best practice for inserting values to MySQLi

I am getting into MySQLi and I am slowly getting the hang of things. I have a piece of code that works for inserting a form values into a DB.

I don't want it to just work though, I really want to do best practice. I believe I have everything covered, though would appreciate at extra set of eyes to help look at something I might of missed, or a better way at constructing the code.

// Check the form is posted
if (isset($_POST["name"])) {

// Let's get things started
$stmt = $db->prepare("INSERT INTO users (name, email, password, active, username, masteradmin, properties) VALUES (?, ?, ?, ?, ?, ?, ?)");

// Form variables
$name = mysqli_real_escape_string($db, $_POST['name']);
$email = mysqli_real_escape_string($db, $_POST['email']);
$password = mysqli_real_escape_string($db, $_POST['password']);
$active = mysqli_real_escape_string($db, $_POST['active']);
$username = mysqli_real_escape_string($db, $_POST['username']);
$masteradmin = mysqli_real_escape_string($db, $_POST['masteradmin']);
$properties = mysqli_real_escape_string($db, $_POST['properties']);

// Bind parameters for markers, where (s = string, i = integer, d = double, b = blob)
$stmt->bind_param('sssssss', $name, $email, $password, $active, $username, $masteradmin, $properties);

// Execute and Go!
$stmt->execute();

// Get the ID of what has been inserted
$helloid = $db->insert_id;

// Wrap things up
$stmt->close();

// Send it on its merry way.
$insertGoTo = "index.php";
header(sprintf("Location: %s", $insertGoTo));

}

Answer

Two quick pieces of feedback

  1. You don't need to use both mysqli_real_escape_string and prepared statements. The latter is sufficient (and superior).

  2. Never store cleartext passwords!! Always hash and salt before storing.

To store, first do:

$hashed_pwd = password_hash($_POST['password'],PASSWORD_DEFAULT);

//now you can store $hashed_pwd in DB

At login, to verify the user's submitted password:

//first select user from DB with matching username

//then verify the cleartext pwd submitted
if(password_verify($cleartext_pwd, $hashed_pwd)){
    //correct password
}else {/*wrong password*/}
Comments