How do I check if the username exists?

Question

I have been trying to figure this out for hours but haven't succeeded. I'm making a registration page in PHP for a MSSQL Database. I figured out how to insert the username in the database, however I'm not sure how to check if it exists. Here's my code:

<?php
$server = "----";
$user = "----";
$pass = "----";
$db = "----";

$link = mssql_connect($server, $user, $pass);
$selected = mssql_select_db ($db, $link);
?>

--------------------------------   

<html>
<head>
</head>
<body>
<center>
<form action="register.php" method="post">
<div style="border: 1px solid black; width:320px; font-family:arial;">
<center>
<table cellspacing="5">
<tr>
<td>Account:</td><td><input type="text" name="username" value="" /></td>
</tr>
<tr>
<td>Password:</td><td><input type="text" name="password" value="" /></td>
</tr>
<tr>
<td colspan="2"><center><input type="submit" value="submit" /></center></td>
</tr>
</table>
</center>
</div>
</form>
</center>
</body>
</html>

-------------------------------- 

<?php
require_once('config.php');
$username=$_POST['username'];
$password=$_POST['password'];
$query = "INSERT INTO Accounts (AccountName, Password) VALUES ('$username', '$password')"; 
//if username exists {}
//else {
$result = mssql_query($query);
// }
?>

The method where you select and check number of rows is insufficient and insecure. There's a small lag between PHP script and any database. Imagine the scenario - you and I both create accounts. Script checks the username for you and I, and we both get "username is ok". After that, we both get the same username in the db - not what you want. These scenarios are handled by adding unique constraint on `AccountName` and inserting the data. If insert fails due to unique constraint failing - you know username is taken. — N.B., May 07 '14 at 09:40

score 0 · Answer 1 · answered May 07 '14 at 02:10

do like following. your password might be in encrypted format so you have to check accordingly

$sql="SELECT * FROM Accounts WHERE AccountName='$username' AND Password='$password'";
$result = mssql_query($sql);
if (mssql_num_rows($query)) {
  echo 'user exists';
}else{
  echo 'user does not exist';
}

score 0 · Accepted Answer · edited May 23 '17 at 12:25

Consult the following using the mssql_num_rows() function.

$query_check = "SELECT * FROM Accounts WHERE AccountName='$username' AND Password='$password'";
$results = mssql_query($query_check);

if(mssql_num_rows($results) > 0){
  echo "Exists.";
}

else{
  echo "Sorry.";
}

Plus I suggest you change:

<input type="text" name="password" value="" />

to

<input type="password" name="password" value="" />

as you will be exposing passwords in plain viewable text.

You're presently open to SQL injection.

You should be using bound parameters.

Read the following articles:

Passwords

I noticed that you may be storing passwords in plain text. This is not recommended.

Consider using one of the following:

CRYPT_BLOWFISH
crypt()
bcrypt()
scrypt()
On OPENWALL
PBKDF2
PBKDF2 on PHP.net
PHP 5.5's password_hash() function.
Compatibility pack (if PHP < 5.5) https://github.com/ircmaxell/password_compat/

Other links:

PBKDF2 For PHP

To tell you the truth, I don't know how MSSQL does to prevent injection and I've been Googling ever since I saw your message. From finding this on SO http://stackoverflow.com/a/16203965/ led me to http://stackoverflow.com/q/574805/ and aside using PDO which is apparently not 100% stable for MSSQL environment, I really don't know what to suggest to the OP. @AndrewMedico If you have any suggestions, I'm all ears. — Funk Forty Niner, May 07 '14 at 03:21
Maybe I should write like you did in some of the answers I found that you've given, something to the effect of "You should be using bound parameters..." ought to be enough. @AndrewMedico [`Link`](http://stackoverflow.com/a/552171/) and that's just one of them. Plus, [`another`](http://stackoverflow.com/a/23373560/) — Funk Forty Niner, May 07 '14 at 03:32

score 0 · Answer 3 · edited Oct 09 '16 at 10:22

use @parameter to pass the value to the stored procedure
to find the username already exits

Write a stored procedure

create stored procedure abc
    @username varchar(20),
    @pass varchar(20)

    if exists(select usename 
              from tablename 
              where username = @username and password = @pass)
    begin
        select 
            'alreadyexists' as result
    end
    else
    begin
        select usename  
        from tablename 
        where username = @username and password = @pass
    end
end

How do I check if the username exists?

3 Answers3