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.

Answer 3

You can return 0 to many recordssets from a SQL stored procedure, as well as your output parameter.

In tsql a recordset is similar to a table or datarow.

So, taking your example procedure, you can do something like this:

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) 
 begin
   if exists (select [password], username  
                from users  
                where [password]=@password  
                  and username=@username) 
     set @ret =1 
   else  
     begin
         set @ret=2
     end  
   -- The select below is just an example - modify it to your needs
   select * from users 
   where username = @username
 end
 else  
   set @ret=3 

Now you have a recordset with userdetails where they exist as well as your int output value.

Three caveats with this answer though:

• The sql I've provided can be improved depending on usage and actual data, it is just intended to show the select into the record set
• The way you access the records sets and the output parameters will depend on your data access technology.
• Storing passwords in the database the way you are is probably a bad idea.

Answer 4

• Don't store the password in the DB - store a salted hash (also note that default case-insensitive collation will mean that neither username nor password are case-sensitively compared - usually passwords are case-sensitive) You can use the T-SQL hashbytes function with appropriate salted data and appropriate choice of hash function
• Do not indicate any difference to the user if a username is valid but password is invalid or vice versa, this information can also be used to assist in attacks
• Have the client code check for the existence of the row in the result set and act appropriately, no row indicates no login to proceed - you cannot get the output parameters until you've consumed all the rows IIRC anyway

Here's the code I would recommend (I left off the output parameter and assume the password is not stored but the hash is - and that the password is simply salted with the username to create the hash (not always the best choice, especially if you allow usernames to change and don't re-get the password properly to re-store a new hash, plus user names are usually not case-sensitive)):

create procedure [dbo].[sp_users_login] (
  @username varchar(30),
  @password varchar(30)
)
as
    select username -- , addition users.columns 
    from users 
    where pwhash = HashBytes('SHA1', UPPER(@username) + @password) 
        and username = @username)

You should also look at these questions:

Is it okay to store salts with hashes?

Crypto, hashes and password questions, total noob?

Salt Generation and open source software

Secure hash and salt for PHP passwords