Built-in Forms Authentication - against a database

Jan 05 2006

Built-in Forms Authentication - against a database

This is an enhancement of these article(s)

Built-in Forms Authentication - description

Built-in Forms Authentication - sample with DOWNLOAD

Lets enhance the solutions from those articles by not storing the users and passwords in web.config but instead in a database.

Database table A_USER (using SQL Server but that doesn't matter of course )

Do put in some users in there and then modify btnLogin_Click to read from the database:



    private void btnLogin_Click(object sender, System.EventArgs e) 
    { 
        bool fOK = false; 
        OleDbConnection oConn = new OleDbConnection("Provider=sqloledb;Data Source=localhost;Initial catalog=kbmentor;User Id=sa;Password=stefan;"); 
        OleDbCommand oCommand = oConn.CreateCommand(); 
        OleDbDataReader oReader; 
        oCommand.CommandText = "select pwd from a_user where userid='" + txtID.Text + "'"; 
        oConn.Open(); 
        oReader = oCommand.ExecuteReader(CommandBehavior.CloseConnection); 
        if ( oReader.Read() ) 
            { 
                if ( oReader["pwd"].ToString() == txtPwd.Text ) 
                    fOK = true; } 
                oReader.Close(); 
            if ( fOK ) 
            { //Det gick bra 
                FormsAuthentication.RedirectFromLoginPage(txtID.Text,false); 
            } 
            else 
            { 
                LabelError.Text = "Error logging in"; 
            } 
    }

I've chosen to use OleDbConnection here in this sample but don't take that as a guideline - against SQL Server SqlClient is of course preferred - however just wanted to make it easy for you if you want to use the sample code against an Access database.

Now, lets look at web.config. Actually no change needed, however you might want to delete the lines



<CREDENTIALS passwordformat="Clear"></CREDENTIALS><USER name="kalle" password="kallepwd"></USER><USER name="pelle" password="pellepwd"></USER>AAAAAENDSOURCECODE

cause it is not needed anymore and might cause confusion.

Also notable, why didn't I just:



oCommand.CommandText = "select * from a_user where userid='" + txtID.Text + "' and pwd='" + txtPwd.Text + "'";

That would cause a SQL injection security risk, actually all text variables coming from a client form and being sent to the database is a risk, however by comparing the password in our code instead of directly in the SQL string it mimimizes the risk of someone getting in without the password.

However, always use parameterized queries against the database when dealing with texts, I didn't cause I wanted this example to be as clear as possible.

ASPCode.net

Free asp.net and asp scripts

Built-in Forms Authentication - against a database