PHP check if value exists in Database

Question

I am using MySQL and I want to check if the value of the users input $_POST['username'] already exists in my database (in the field username). I have tried this code:

$usernameExists = "SELECT * FROM users WHERE username = " . $_POST['username'];

if ($usernameExists) {
    echo "Exists"
}

I put this code after the if (!empty...) statement;

but nothing happened. If you need my full code, it is available here, but I assume the rest of it won't be helpful:

<?php

session_start();

if (isset($_SESSION['user_id'])) { // user is already logged in
    header("Location: index.php");
}

require('database.php');

$message = '';
$emailMessage = '';
$usernameMessage = '';
$passwordMessage = '';
$confirmMessage = '';

if (!empty($_POST['email']) && !empty($_POST['username']) && !empty($_POST['password']) && !empty($_POST['confirmPassword'])) { // user submitted form; enter user

    if (!filter_var($_POST['email'], FILTER_VALIDATE_EMAIL)) {
        $emailMessage = 'Invalid email.';

    } elseif (strlen($_POST['username']) < 4 || strlen($_POST['username']) > 250) {
        $usernameMessage = 'Username has to be between 4 and 250 characters.';

    } elseif (!preg_match("/^[a-zA-z0-9]*$/", $_POST['username'])) {
        $usernameMessage = 'Username can only contain numbers and letters.';

    } elseif (strlen($_POST['password']) < 6 || strlen($_POST['password']) > 250) {
        $passwordMessage = 'Password has to be between 6 and 250 characters.';

    } elseif ($_POST['confirmPassword'] !== $_POST['password']) {
        $confirmMessage = 'Passwords don\'t match THONK';

    } else {
        $sql = "INSERT INTO users (email, username, password) VALUES (:email, :username, :password)";
        $stmt = $conn->prepare($sql);

        $stmt->bindParam(':email', $_POST['email']);
        $stmt->bindParam(':username', $_POST['username']);

        $password = password_hash($_POST['password'], PASSWORD_BCRYPT);
        $stmt->bindParam(':password', $password);

        if ($stmt->execute()) {
            $message = 'Successfully created new user: ' . $_POST['username'];
        } else {
            $message = 'There was an error lol';
        }
    }
}
?>

`username = " . $_POST['username']` is invalid SQL and opening you to SQL injections. Unless your usernames are all integers. Your current query with my name would be `SELECT * FROM users WHERE username = chris85`. Parameterize that query as you have elsewhere. — chris85, Jun 21 '17 at 20:11
You are using prepared statements in your `INSERT` statement. Do the same for your `SELECT` statement and you should be set. — M. Eriksson, Jun 21 '17 at 20:12
Also do you ever send `$usernameExists` to the DB? If not that is the first problem. — chris85, Jun 21 '17 at 20:14
@MagnusEriksson For the moment I'm not worrying about prepared statements. I just want to get it to show up, but with the statement I put in the OP it didn't work. Once I get get that to echo if that username already exists, I will worry about making it secure ;) — , Jun 21 '17 at 20:15
That line of code assigns a string to a variable. That's all it does. It doesn't matter that the string looks like a SQL statement... it's just a string. (If you were to send that string to the database to be executed as a SQL statement, it appears to be vulnerable to SQL Injection. Supply potentially unsafe values as *bind values* to a *prepared statement.) — spencer7593, Jun 21 '17 at 20:16
@chris85 I think that's my problem. But I'm not sure what bit of the code will do that. — , Jun 21 '17 at 20:16
If you parameterize the query, it will work. If you don't, you need to wrap the username in single quotes (strings _must_ be quoted or the query will fail). However, this will leave you _wide open to sql injections_ and why you don't care about that is beyond me. You should _always_ think about writing secure code from the start. That will minimize the risk of forgetting/missing to fix that later. — M. Eriksson, Jun 21 '17 at 20:21
Did someone else write the large code block? That is using the correct approach. — chris85, Jun 21 '17 at 20:29
@MuhammadUsman The OP seems to be using PDO, not the procedural version of mysqli. Even if the OP _did_ use that, your example is invalid. That function takes two arguments, the first being the database link. — M. Eriksson, Jun 21 '17 at 20:34

score 3 · Accepted Answer · answered Jun 21 '17 at 20:25

Query the database using a prepared statement. Like this:

 $usernameExists = 0;
 $sql = 'SELECT username FROM users WHERE username = :username';
 $stmt = $conn->prepare($sql);
 $stmt->bindValue(':username',$_POST['username']);
 $stmt->execute();

 if($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    // row(s) returned
    $usernameExists = 1;
 } else {
    // no row returned
    $usernameExists = 0;
 }
 $stmt->closeCursor();

Then you can do this:

if ($usernameExists) {
   echo "Exists"
}

PHP check if value exists in Database

1 Answers1