CodeSnip:Forms Authentication Against a Database
page 1 of 1
Published: 09 Dec 2004
Unedited - Community Contributed
I've seen a number of posts to the peer support lists and personally received a few requests for a code sample that performs Forms Authentication against a database.
by Web Team at ORCS Web
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 7944/ 4

I've seen a number of posts to the peer support lists and personally received a few requests for a code sample that performs Forms Authentication against a database.

I took code from an earlier article that authenticated against an XML file (Forms Authentication Against An XML File) and converted the code to use the ExecuteScalar method of the command object to query a database to validate the username and password.

I used SQL Server as the data source when writing this code, but the database can be any type supported by ASP.Net. The sample database I created has a single table named tUsers and has just two columns - username and password - both varchar(50) data types.

There are plenty of articles online explaining the integration of ASP.Net and databases, so I'm not going to spend time re-explaining that. The code below should be fully functional though and it should be a good starting place for you to integrate your own Forms Authentication against a database.

<%@ Page Language="VB" Trace="false" EnableViewState="False" %>
<%@ Import Namespace="System.Web.Security" %>
<%@ Import Namespace="System.Data"%>
<%@ Import Namespace="System.Data.SqlClient" %>

<script language="VB" runat=server>
Sub Login(Src as Object, E as EventArgs) 
  Dim sUsername as string = txtUser.Value.Replace("'","''")
  Dim sPassword as string = txtPwd.Value.Replace("'","''")

  if (CheckLoginFromDb(sUsername, sPassword)) <> "no match" then
    FormsAuthentication.RedirectFromLoginPage(sUsername, false)
    lblStatus.InnerHtml = "Invalid login"
  end if
end sub

Function CheckLoginFromDb(sUsername as string, sPassword as string) as string
  Dim sSQL          as String
  Dim sRetUsername  as String

  sSQL = "SELECT username FROM tUsers WHERE "
  sSQL += "username = '" & sUsername & "' "
  sSQL += "AND password = '" & sPassword & "'"

  Dim sConnStr As String = CStr(ConfigurationSettings.AppSettings("MyConn"))
  Dim oConn As SqlConnection = New SqlConnection(sConnStr)
  Dim oCommand as SQLCommand = New SQLCommand(sSQL, oConn)
  oCommand.CommandType = CommandType.Text

    ' Open the connection and execute the Command 
    sRetUsername = oCommand.ExecuteScalar() 
  Catch e As Exception
    throw e
    ' Close the Connection 
    If oConn.State = ConnectionState.Open then 
    End If 
  End Try

  If trim(sRetUsername) = "" then
    return "no match"
    return sRetUsername
  end if
End Function

<form method=post runat=server>
  Username: <INPUT type=text name=txtUser id=txtUser runat=server/><BR>
  Password: <INPUT type=password name=txtPwd id=txtPwd runat=server/><BR>
<INPUT type=submit OnServerClick="Login" runat=server/>

<SPAN id="lblStatus" runat=server/>

By Brad Kingsley, founder and president of ORCS Web, Inc. - a company that provides managed hosting services for clients who develop and deploy their applications on Microsoft Windows platforms.

User Comments

Title: Help   
Name: Jo
Date: 2009-10-22 4:23:14 PM
Hello..thanks a lot for these coding !! but since am new to programming am getting a bit stuck to launch it...i would to know to set up a form authentication against a database i've created step by step...Could you please help me...
Title: Reply to Sagar   
Name: Brad Kingsley
Date: 2004-12-28 6:40:24 AM
Sagar - I'm not sure if you read the first article in this series. If not, it may answer your question:
Title: More to know about it   
Name: sagar
Date: 2004-12-27 11:39:58 PM
If the user Directly place the URL of a page for which he is not authenticated, how to deny them and also if user is placed the username and password , how to make them authenticated for some of the forms (which are not allow to them)
Title: No I understand your comment   
Name: Brad Kingsley
Date: 2004-12-11 8:54:54 AM
Now I understand what you mean. Yes, that is a
concern. A quick (but a bit sloppy) fix would be
to Replace() " AND ", " OR ", " SELECT ",
" DELETE ", and a few other SQL keywords.

The cleaner solution would be to not build the SQL
dynamically as you mentioned.

I personally suggest that everyone writing database-
related code place all of their SQL into stored
procedures and pass parameters to the procedure for
processing. There could be concerns that way too if
the developer isn't careful, but it's nice to isolate
the SQL for easy changes when needed, without touching
the web code (and performance is generally better with
SPs vs. inline code).

Thanks for the comments - hopefully everyone scrolls
down to read these. I feel the above still gives a good
quick and easy sample for logical understanding of the
process -- developers note though that the above isn't
intended as production code.

Thanks again,

Title: I believe you misunderstood me   
Name: Ed
Date: 2004-12-11 8:32:50 AM

I'm sorry for not clarifying my answer. By NULL, I meant a null value. Placing an extra ' character will give you an empty value.
If they enter the following:

"' or 1=1 --" (Obviously, without the double quotes)

This makes your sql statement read as follows:

SELECT username FROM tUsers WHERE username = '' or 1=1 --AND password = '

Since the "--" comments out the rest of the sql statement, they are in, because 1 does equal 1. This is a classic SQL injection attack. You code allows for this type of attack to occur.

When I said it's a disaster waiting to happen, I meant that a malicious user could use this injection against you, and try to add code to the code to delete your database:

"' or 1=1 GO DELETE * FROM USERS GO --"

Even though your user table is named tUsers, you get the idea. The idea is NEVER create a dynamic SQL statement based on user input.
Title: disaster in waiting?   
Name: Brad Kingsley
Date: 2004-12-11 5:57:49 AM
"If the user puts in say: NULL or 1=1 -- They are in."

AFAIK, neither username='NULL' nor password='NULL' would give any matches. I'm also not sure how username='1=1' would do any good. You'll need to post again and leave more information to clarify for me.

"And that's the least of your troubles with this code."

This is just a sample to give a starting point, but myself and the readers certainly welcome any input you have. What troubles do you see with this example?


Title: Programmer   
Name: Ed
Date: 2004-12-10 9:14:56 PM
This is a disaster in waiting. If the user puts in say:

NULL or 1=1 --

They are in. And that's the least of your troubles with this code.

I understand that it is listed as a place to start, but you might want to teach them the correct way to write code, and not just write code.
Title: Yes, I know SQLParameters   
Name: Brad Kingsley
Date: 2004-12-10 5:42:58 AM
This wasn't an article to discuss the different ways to pull SQL data, but rather to give a quick functional view of using SQL as the data source for Forms Authentication.

I'm not sure what you mean by "delete this poorly".

Title: do you not know SqlParameters?   
Name: Michael Stegmann
Date: 2004-12-10 3:35:38 AM
sSQL = "SELECT username FROM tUsers WHERE "
sSQL += "username = @username "
sSQL += "AND password = @passwort "

oCommand.Parameter.Add "@username, sUsername
oCommand.Parameter.Add "@username, sPasswort

an, of cource, delete this poorly ...Replace("'", "''")

Product Spotlight
Product Spotlight 

Community Advice: ASP | SQL | XML | Regular Expressions | Windows

©Copyright 1998-2024  |  Page Processed at 2024-04-18 6:47:39 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search