Check if username/password exists in multiple tables

Question

I am working on a login page. I'd like to check if the username & password exists in the database. I have three database tables : Teams,Organizers,Admins with username & password field in each table respectively. I am implementing the login in three-tier architecture.

I believe that I have a problem with my SQL statement below. I tested my sql query with a distinct/valid team username and team password. The COUNT query returns more than one row, which is incorrect.

This are my codes for the data access layer :

 public int getExistingAccount(string username, string password)
    {
        string queryStr = "SELECT COUNT(*) FROM Teams t,Organizers o,Admins a WHERE (t.teamUsername=@username AND t.teamPassword=@password) OR (o.organizerUsername=@username AND o.organizerPassword=@password) OR (a.adminUsername=@username AND a.adminPassword=@password)";

        SqlConnection conn = new SqlConnection(_connStr);
        SqlCommand cmd = new SqlCommand(queryStr, conn);
        cmd.Parameters.AddWithValue("@username", username);
        cmd.Parameters.AddWithValue("@password", password);

        int returnValue = 0;

        conn.Open();
        returnValue = (int)cmd.ExecuteScalar();

        conn.Close();
        return returnValue;

    }

As for the business logic layer codes :

public string getAccount(string username, string password)
    {
        string returnMessage = "";

        if (username.Length == 0)
            returnMessage += "Username cannot empty</br>";

        if (password.Length == 0)
            returnMessage += "Password cannot be empty</br>";

        if (username.Equals(password))
        {
            returnMessage += "Duplicate value. Please try again</br>";
        }

        //Invoke validateInput() method to validate data
        if (returnMessage.Length == 0)
        {
            int noOfRows = 0;

            LogAccounts logInd = new LogAccounts();
            noOfRows = logInd.getExistingAccount(username, password);

            if (noOfRows > 0)
                returnMessage += "Account found";
            else
                returnMessage += "Invalid username/password.";
        }

        return returnMessage;
    }

Answer 1

Try this, select from each table and UNION ALL the results, then count the rows.

select count(*) from
(
SELECT 1 as dummyname FROM Teams t
WHERE (t.teamUsername=@username AND t.teamPassword=@password)
union all
SELECT 1 FROM Organizers o
WHERE (o.organizerUsername=@username AND o.organizerPassword=@password)
UNION ALL
select 1 from Admnis
WHERE (a.adminUsername=@username AND a.adminPassword=@password)
)

Answer 2

I seems you have a really awkward database design, where fetching a single user requires a unnaturally large/long sql query.

In almost every use case you would have a single Users table, and if you need to tie the user to some additional information, you would have a reference to the user table by the UserId. You should read up on foreign keys aswell.

Quick sample:

Users:

- UserId (int or guid) (primary key)

- .... (additional fields removed for brewity)

The other tables would refer to the UserId column, and use that to pull information about the user with a join.

E.g.: SELECT T.*, U.* FROM Teams T INNER JOIN Users U ON U.UserId = T.UserId WHERE U.Username = "AwesomeCoach";

A simple validate query would be something like this:

SELECT COUNT(*) FROM Users WHERE Username = xx AND Password = xx

That would return an integer that specifies how many rows that matched the given username/password combination. It should be either 1 or 0. Put a Unique contraint on the Username column to ensure that there are only one occurence of each Username.

Footnote: I see that you have got an answer that solves the problem you were facing, but I would recommend that you read up on some database design, and try to keep it as simple as possible. Managing multiple users across multiple tables can and will be a hassle as the application grows.

Answer 3

Your design is really bad, you should have all users in one table. After that if you want to take user by id, you should check 3 diff tables. Anyway the problem is in the query you should write it like this:

string queryStr = @"
SELECT 
    COUNT(*) AS TeamsCount,
    (SELECT COUNT(*) Organizers WHERE organizerUsername=@username AND organizerPassword=@password) AS OrgCount,
    (SELECT Count(*) Admins WHERE adminUsername=@username AND adminPassword=@password) AS AdminCount
FROM 
   Teams 
WHERE 
   teamUsername=@username AND 
   teamPassword=@password";

The query should look something like this. After that you need to return this in DataSet. You need:

DataSet dst = new DataSet();
using(SqlAdapter adapter = new SqlAdapter(cmd))
{
    adapter.Fill(dst);
}

In this case you will have dst with 3 columns in it. Check for existing user should be:

if(dst.Tables[0].Rows[0]["TeamsCount"] > 0 || 
     dst.Tables[0].Rows[0]["OrgCount"] > 0 ||
     dst.Tables[0].Rows[0]["AdminCount"] > 0)
{
    //user already exist !
}