x

Help to write a stored procedure

Hi,

I am sathish, I am trying to write a stored procedure to check the login details. Find the query below and help me please..I marked (??) where i have doubt..

alter procedure logindetail  
@username varchar(20),  
@password varchar(20)  
 as  
begin  
if(@username is not null and @password is not null)  
begin  
select username, password from login  /*It will check the username & password it is ok login */
where username = @username  
and password = @password  
select 88888,' Mr.' + upper(@username) +' logged in successful '  
--return 2  
end  
else if (@username <> and @password <> ) /* here it want to through an error msg if username & password is not matching with the table*/ ???????????
begin
select 99999,'  Mr.' + upper(SYSTEM_USER) +'    Please Enter the correct username & password'  
return 1
end

else if(@username is null and @password is null)  /*It will check the username & password it is not ok return error */ 
begin  
select 88888,'  Mr.' + upper(SYSTEM_USER) +'    Please Enter the correct username & password'  
return 0  
end  
end  

Thanks in advance

Regards Sathish
more ▼

asked Apr 28, 2011 at 11:56 PM in Default

sathishkumar gravatar image

sathishkumar
234 22 24 26

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

If you want to check login credentials against a table in SQL Server, I sugest you do just this:

alter procedure logindetail  
@username varchar(20),  
@password varchar(20)  
 as  
select username, password from login 
where username = @username  
and password = @password  
and @username is not null and @password is not null

If that stored procedure returns a row, your application will know the provided credentials are correct. If no rows are returned, the login credentials are wrong.

If you get no rows returned, you should have your application handle the error messages. I did provide a null-check in the WHERE clause, but your application should make sure no NULL parameters are sent.
more ▼

answered Apr 29, 2011 at 12:09 AM

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
16.6k 17 20 33

(comments are locked)
10|1200 characters needed characters left

Hi, your inner queries in the stored proc should look like this:

if(@username is null or @password is null)  /*It will check the username & password it is not ok return error */ 
begin  
    select 88888,'  Mr.' + upper(SYSTEM_USER) +'    Please Enter the correct username & password'  
    return 0
end
else
begin
    if (exists(select username, password from login  where username = @username and password = @password ))  /*It will check the username & password it is ok login */
    begin
        select 88888,' Mr.' + upper(@username) +' logged in successful '  
        return 2  
    end
    else
    begin
        select 99999,'  Mr.' + upper(SYSTEM_USER) +'    Please Enter the correct username & password'  
        return 1    
    end    
end
more ▼

answered Apr 29, 2011 at 12:09 AM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

(comments are locked)
10|1200 characters needed characters left

I would like to point out a few concerns with what you are wanting to do.

  1. You are auditing logins yourself - SQL Server has built-in login auditing, it can be turned on to only log failed logins, or both failed and successful logins.
  2. Your check seems to run against a "valid logins" table. The comparison of password and username doesn't seem to be using hashing of the password - I hope this is done before passing the details into the server and not just comparing plaintext against the table. This would be a major security hole in your system. If someone can read the stored passwords, they can spoof the system and do bad things without detection.
  3. Your error messages all expect the end user to be male (they all start with 'Mr.'). You may be wanting to only cater for males, but this does reduce your possible user group by approx. 50% of the worlds population!

You can still continue with your implementation by placing the select statement to check if the username and password is valid into the ELSE condition:

ELSE
BEGIN
  IF NOT EXISTS (SELECT * FROM Login WHERE username=@username and password = @password) -- No valid login found
    SELECT 99999,'  Mr.' + upper(SYSTEM_USER) +' Please Enter the correct username & password'  
END
more ▼

answered Apr 29, 2011 at 12:12 AM

WilliamD gravatar image

WilliamD
25.9k 17 19 41

(comments are locked)
10|1200 characters needed characters left

Hi Magnus,Pavel Pawlowski,WilliamD

Thanks for your reply

more ▼

answered Apr 29, 2011 at 01:31 AM

sathishkumar gravatar image

sathishkumar
234 22 24 26

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x415

asked: Apr 28, 2011 at 11:56 PM

Seen: 1424 times

Last Updated: Apr 29, 2011 at 12:00 AM