When I originally asked this question, it was similar to: Check to see if an email is already in the database using prepared statements This helped me to solve my situation.
After looking at both our codes, I noticed that I was validating user input whereas the other post didn't mention it.
So, is it still good to validate user input when using prepared statements?
My original post: I have been trying this for several days now and am having problems with the code. I am trying tp learn php and mysql/MariaDB and am working on a multi user login system that allows for administrators and users.
So far I have the following working:
My problem that I am running into is that I want to add a check to prevent the duplication of user names (newly registering users or admin created users can't create a new user if name already taken). I have tried inserting a check to see but it still adds the user even if that username already exists.
I was wondering if someone could look at the code I have and see where I am going wrong.
Here is my administrator create user code:
<?php include('../functions.php') ?>
if (!isAdmin()) {
$_SESSION['msg'] = "You must log in first";
header('location: ../login.php');
}
<!DOCTYPE html>
<html>
<head>
<title>Registration system PHP and MySQL - Create user</title>
<link rel="stylesheet" type="text/css" href="../style.css">
<style>
.header {
background: #003366;
}
button[name=register_btn] {
background: #003366;
}
</style>
</head>
<body>
<div class="header">
<h2>Admin - create user</h2>
</div>
<form method="post" action="create_user.php">
<?php echo display_error(); ?>
<div class="input-group">
<label>Username</label>
<input type="text" name="username" value="<?php echo $username; ?>">
</div>
<div class="input-group">
<label>Email</label>
<input type="email" name="email" value="<?php echo $email; ?>">
</div>
<div class="input-group">
<label>User type</label>
<select name="user_type" id="user_type" >
<option value=""></option>
<option value="admin">Admin</option>
<option value="user">User</option>
</select>
</div>
<div class="input-group">
<label>Password</label>
<input type="password" name="password_1">
</div>
<div class="input-group">
<label>Confirm password</label>
<input type="password" name="password_2">
</div>
<div class="input-group">
<button type="submit" class="btn" name="register_btn"> + Create user</button>
</div>
</form>
</body>
</html>
**Here is Register page:**
<?php include('functions.php') ?>
<!DOCTYPE html>
<html>
<head>
<title>Registration system PHP and MySQL</title>
<link rel="stylesheet" href="style.css">
</head>
<body>
<div class="header">
<h2>Register</h2>
</div>
<form method="post" action="register.php">
<?php echo display_error(); ?>
<div class="input-group">
<label for="username" class="col-md-3 control-label">User Name*</label>
<div class="col-md-9">
<input type="username" class="form-control" name="username" placeholder="User Name" required>
</div>
</div>
<div class="input-group">
<label for="email" class="col-md-3 control-label">Email*</label>
<div class="col-md-9">
<input type="email" class="form-control" name="email" placeholder="Email" required>
</div>
</div>
<div class="input-group">
<label for="password" class="col-md-3 control-label">Password</label>
<div class="col-md-9">
<input type="password" class="form-control" name="password_1" placeholder="Password" required>
</div>
</div>
<div class="input-group">
<label for="password" class="col-md-3 control-label">Confirm password</label>
<div class="col-md-9">
<input type="password" class="form-control" name="password_2" placeholder="Password" required>
</div>
</div>
<div class="input-group">
<button type="submit" class="btn" name="register_btn">Register</button>
</div>
<p>
Already a member? <a href="login.php">Sign in</a>
</p>
</form>
</body>
</html>
Here are all my functions:
<?php
session_start();
// connect to database
$db = mysqli_connect('removed variables to connect to database this works');
// variable declaration
$username = "";
$email = "";
$errors = array();
// call the register() function if register_btn is clicked
if (isset($_POST['register_btn'])) {
register();
}
function register(){
// call these variables with the global keyword to make them available in function
global $db, $errors, $username, $email;
// receive all input values from the form. Call the e() function
// defined below to escape form values
$username = e($_POST['username']);
$email = e($_POST['email']);
$password_1 = e($_POST['password_1']);
$password_2 = e($_POST['password_2']);
$sql= "SELECT * FROM users WHERE username = '$username'";
$result=mysqli_query($sql);
if(mysqli_num_rows($result)!=0)
{
echo"name already exists";
}
// 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 = hash('sha256', $password_1);//encrypt the password before saving in the database
// excecute insert query
if (isset($_POST['user_type'])) {
$user_type = e($_POST['user_type']);
$query = "INSERT INTO users (username, email, user_type, password)
VALUES('$username', '$email', '$user_type', '$password')";
mysqli_query($db, $query);
$_SESSION['success'] = "New user successfully created!!";
header('location: home.php');
}else{
$query = "INSERT INTO users (username, email, user_type, password)
VALUES('$username', '$email', 'user', '$password')";
mysqli_query($db, $query);
// get id of the created user
$logged_in_user_id = mysqli_insert_id($db);
$_SESSION['user'] = getUserById($logged_in_user_id); // put logged in user in session
$_SESSION['success'] = "You are now logged in";
header('location: index.php');
}
}
}
// ge
// return user array from their id
function getUserById($id){
global $db;
$query = "SELECT * FROM users WHERE id=" . $id;
$result = mysqli_query($db, $query);
$user = mysqli_fetch_assoc($result);
return $user;
}
// escape string
function e($val){
global $db;
return mysqli_real_escape_string($db, trim($val));
}
function display_error() {
global $errors;
if (count($errors) > 0){
echo '<div class="error">';
foreach ($errors as $error){
echo $error .'<br>';
}
echo '</div>';
}
}
function isLoggedIn()
{
if (isset($_SESSION['user'])) {
return true;
}else{
return false;
}
}
// log user out if logout button clicked
if (isset($_GET['logout'])) {
session_destroy();
unset($_SESSION['user']);
header("location: login.php");
}
// call the login() function if register_btn is clicked
if (isset($_POST['login_btn'])) {
login();
}
// LOGIN USER
function login(){
global $db, $username, $errors;
// grap form values
$username = e($_POST['username']);
$password = e($_POST['password']);
// make sure form is filled properly
if (empty($username)) {
array_push($errors, "Username is required");
}
if (empty($password)) {
array_push($errors, "Password is required");
}
// attempt login if no errors on form
if (count($errors) == 0) {
$password = hash(sha256, $password);
$query = "SELECT * FROM users WHERE username='$username' AND password='$password' LIMIT 1";
$results = mysqli_query($db, $query);
if (mysqli_num_rows($results) == 1) { // user found
// check if user is admin or user
$logged_in_user = mysqli_fetch_assoc($results);
if ($logged_in_user['user_type'] == 'admin') {
$_SESSION['user'] = $logged_in_user;
$_SESSION['success'] = "You are now logged in";
header('location: admin/home.php');
}else{
$_SESSION['user'] = $logged_in_user;
$_SESSION['success'] = "You are now logged in";
header('location: index.php');
}
}else {
array_push($errors, "Wrong username/password combination");
}
}
}
function isAdmin()
{
if (isset($_SESSION['user']) && $_SESSION['user']['user_type'] == 'admin' ) {
return true;
}else{
return false;
}
}
The function I have been adding the code to is the register function As you can see, I selected all from my users table and assigned it to a variable (the username variable was assigned just above it from the Post Action. I then did a mysqli query on that variable and assigned this to another variable. Then I tried to say if the number of rows did not equal zero, then the username existed.
I am not sure what I am doing wrong. If any can provide some insight, please let me know. I know this code is probably not the best. I am trying this out in my local environment to learn. Any advice would be greatly appreciated.
Thanks in advance.
The propblem is in the mysqli_query function, this function needs at least 2 parameters, the first one is the link to your database, so your code should be something like:
$connection = mysqli_connect("localhost","db_user","db_password","db_name");
/* your code */
$result=mysqli_query($connection, $sql);