Now that we have the table built as well as the stored procedure used to return the data, let us look at security.
You will get to a point where you need a page to add links but you certainly don't want people to come in and add links to thir porn sites, spam, phishing scams, etc. so you need to protect the page.
Although I won't discuss the code for the page here, let's look at the stored procedure that will validate our users.
First, we need a table. There are only two critical elements to a user table. User name and password. Everything else, first name, last name, email addy, etc. is cosmetic so I won't worry about that here.
Here is my table definition:
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'U' AND name = 'site_users')
BEGIN
DROP Table site_users
END
GO
CREATE TABLE site_users
(
username varchar(20),
passwd varchar(254)
)
GO
insert into site_users(username,passwd) values ('admin','admin')
GO
Now, the insert at the bottom is nothing more than the first account, the default user account. We set a user name and password of admin. Don't worry about the password because later on when we get into the add/modify records, we will implement security to encrypt/decrypt the password but for now, let's leave it as is.
Once we have the table, we can then write a stored procedure to verify that the user name and password passed to it is actually valid.
Notice, I said "verify" and not "return". We will NOT be returning data in this stored procedure but instead, we will return a 0 if the user is valid or a 1 if the credentials passed are not valid.
Here is the code:
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'ValidateUser')
BEGIN
DROP Procedure dbo.ValidateUser
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Ron Hagerman
-- Create date: 1/26/2008
-- Description: Looks for a user name and password
-- returns true if found, false if not.
-- =============================================
CREATE PROCEDURE dbo.ValidateUser
@sUser varchar(36) =NULL,
@sPasswd varchar(3000) =NULL,
@retVal bit output
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
if (Select count(*) from site_users where username = @sUser and passwd = @sPasswd) > 0
select @retVal = 0
else
select @retVal = 1
END
GO
So what is happening here? Well, we have an if/then statement. If one or more rows are returned from our query, set our output parameter to 0. If the query returns less than 1 row, set the output parameter to 1.
We pass in the user name and the password and return @retVal. Nothing more complicated than that.
The question that begs to be answered is, why do it this way? Why not just add a data table to our web page and say select * from site_users where .... and validate that way? Or even have a stored procedure that returns the data?
The answer is security mostly. When you return data, you return a valid user name and password. If your page has issues, the data could show in the error page if you don't configure the web.config properly. If someone finds a way to execute your stored procedure, they would return critical information about users. This way, we return a 0 or a 1 and nothing more gets passed out of the database. You can also add a function to your code that looks for that instead of constantly looking for a combination of values.