sql query, returning table or table row?

Question

In my web application I write a query to check the userid and password. If userid is failed I write the query that it display userid is wrong if password is wrong then it display password is wrong. The query I write just returns int, but I want to return table or data row can you give me a solution?

This is my query:

user table consist username ,emailid,mobile,country etc.,

create procedure [dbo].[sp_users_login] (
  @username varchar(30),
  @password varchar(30),
  @ret int output)
as
 if exists (select username from users where username=@username)
   if exists (select [password], username 
                from users 
                where [password]=@password 
                  and username=@username)
     set @ret =1
   else 
     set @ret=2 
 else 
   set @ret=3

My query will return only an int, but I need the user details as well. Such as: user, emailid, etc. I want total details of particular user - is it possible in this query?

Answer 1

I was thinking of using HAVING to force the results into a single row, and subqueries to check for things, but this isn't necessary.

Try using aggregates in your SELECT clause, so that you get exactly one row back. Then you can use CASE to get the info you want.

SELECT 
  CASE 
    WHEN COUNT(*) = 0 THEN 'No username' 
    WHEN MAX(password) = @password THEN 'Logged in' 
    ELSE 'Bad password'
  END as LoginStatus,
  MAX(emailaddress) as Email
FROM dbo.Users u
WHERE username = @username
;

Repeat the MAX(emailaddress) for all the other fields you need. If username is unique (and you should put a constraint in place to make sure it is), then this will be fine. If there's no matching user, these rows will come back blank. But if they just got the password wrong, these fields will be returned, so check your LoginStatus to see whether you should be paying attention to it or not.

Answer 2

select [password], username, email, ...
from users 
where username=@username;

You should just select the field of interest using the user name. You can't possibly have two different users with the same user name and different password. What if one decides to change its password and the new one happens to match the other user's password?? What if one user enters the other user's password due to a typo (say they are close) and all of the sudden he logged in into another user account? How are you going to identify users to start with?

Also you should return the same error 'Incorrect user name and password' irelevant if the user missed the user name and password or just the password. Returning different error messages is information disclosure, you're just doing hackers a service by disclosing that a user they tried at random exists or not.