Basic login with one database table

Question

I am currently creating a CMS and have a section where a user can create, edit and delete users. The information is generated from a database, where I have made a table with User_ID, User_Name and User_Password. This means I do not want to use the automatically generated database tables VS gives you for their log ins.

With this I am trying to develop a really basic log in but I am having trouble understanding the process.

This is my web.config for the whole application:

<?xml version="1.0"?>
<configuration>
  <connectionStrings>
    <add name="websiteContent" connectionString="uid=AAA;pwd=AAA;Initial Catalog=AAA;Data Source=.\SQLEXPRESS"/>
  </connectionStrings>
  <system.web>
    <compilation debug="true" targetFramework="4.0"/>
    <authentication mode="Forms">
      <forms loginUrl="~/tools/default.aspx" timeout="2880"/>
    </authentication>
  </system.web>
</configuration>

Web.config for login:

<?xml version="1.0"?>
<configuration>

  <location path="default.aspx">
    <system.web>
      <authorization>
        <allow users="*"/>
      </authorization>
    </system.web>
  </location>

  <system.web>
    <authorization>
      <deny users="?"/>
    </authorization>
  </system.web>

</configuration>

This is my log in on the front end:

            <asp:Login ID="Login1" runat="server" CssClass="loginSec" TextLayout="TextOnTop"
                TitleText="" OnAuthenticate="Login1_Authenticate">
                <LabelStyle CssClass="lblLogin" />
                <TextBoxStyle CssClass="txtLogin" />
            </asp:Login>

Log in from the back end:

protected void Login1_Authenticate(object sender, AuthenticateEventArgs e)
{
    string userName = Login1.UserName;
    string passWord = Login1.Password;
    bool rememberUserName = Login1.RememberMeSet;

    using (SqlConnection sqlCon = new SqlConnection(ConfigurationManager.ConnectionStrings["websiteContent"].ConnectionString))
    {
        sqlCon.Open();
        string SQL = "SELECT CMS_Username, CMS_Password FROM CMS_Users WHERE CMS_Username ='" + userName + "' AND CMS_Password ='" + passWord + "'";
        using (SqlCommand sqlComm = new SqlCommand(SQL, sqlCon))
        {
            sqlComm.ExecuteScalar();

            if (sqlComm.ExecuteScalar() != null)
            {
                Response.Redirect("cms.aspx");
            }
            else
            {
                Session["UserAuthentication"] = "";
            }
        }
        sqlCon.Close();
    }
}

What I have done so far has prevented access to the page cms.aspx, but the log in never redirects to the page.

Any insight would be appreciated!!

Answer 1

I have added the settings of Authenticated as required by the docs

Custom authentication schemes should set the Authenticated property to true to indicate that a user has been authenticated.

More research has led me to the neccessity to add this line in your code

FormsAuthentication.SetAuthCookie(Login1.UserName, true);

Also I will try to change your code in such a way that ExecuteScalar returns the count of user with that username and password. In this way ExecuteScalar will never return NULL, but a value that could be zero if no user exists or 1 if user exists (I suppose that you don't have two records with the same user and password)

using (SqlConnection sqlCon = new SqlConnection(ConfigurationManager.ConnectionStrings["websiteContent"].ConnectionString))
{
    sqlCon.Open();
    string SQL = "SELECT COUNT(*) As LoginFound FROM CMS_Users " + 
                 "WHERE CMS_Username =@usr AND CMS_Password = @pwd";
    using (SqlCommand sqlComm = new SqlCommand(SQL, sqlCon))
    {
        sqlComm.Parameters.AddWithValue("@usr", userName);
        sqlComm.Parameters.AddWithValue("@pwd", password);
        int result = (int)sqlComm.ExecuteScalar();
        if (result > 0)
        {
            // In case of success you need to communicate this 
            e.Authenticated = Authenticated;
            FormsAuthentication.SetAuthCookie(Login1.UserName, true);
            Response.Redirect("~/tools/cms.aspx");
        }
        else
        {
            Session["UserAuthentication"] = "";
        }
    }
}

Also, I have removed the string concatenation from your sql command. This is the right way to pass string text to the database. Particularly if the values comes from your user input.

(See Sql Injection )

EDIT Of course the cmd.aspx page should check if the user has been authenticated because otherwise one could type directly the url of the cms.aspx page bypassing the login control.
So in the Page_Load event of cms.aspx add this code

protected void Page_Load(object sender, EventArgs e)
{
    if ( !Request.IsAuthenticated)
    {
        Response.Redirect("~/tools/default.aspx");
    }
}