CODEWITHSUNDEEP
c#asp.net
Technology Lover
Technology Lover

Reputation: 143

Checking the existence of the user in the database?

I developed an intranet web-based application and it is working well. This system is only dedicated to one department in my company. Any user from outside this department will get an Error page when he tries to browse the system. The system will check the user information by passing his network id to the Active Directory and get his information from there. For doing that, I create a class called Security. And then I put the following in my Master Page:

if (Security.isMember(netID))
        {
            ................
        }
        else
            Response.Redirect("Error.aspx");

code-behind:

public static bool isMember(string userid)
    {
        if (Org.Code == "Org. Code") 
            return true;
        else
            return false;
    }

Since the user is gonna be added to the database after his first browsing to the website, I want to modify the previous method in the Security class to check if the user existed in the database or not. If not, the system should use the Security class to check if the user belong to that department or not. I don't know who to put a code for checking the existence of the user in the database inside the previous method. Could you please help me in this?

My Initial Code for Checking the Existence of the User in the Database:

if (!String.IsNullOrEmpty(userid))
        {
            string username = userid;
            string connString = "Data Source=localhost\\sqlexpress;Initial Catalog=Test;Integrated Security=True";
            string cmdText2 = "SELECT Count(*) FROM employee WHERE Username = '" + username + "'";
            using (SqlConnection conn = new SqlConnection(connString))
            {
                conn.Open();
                // Open DB connection.
                using (SqlCommand cmd = new SqlCommand(cmdText2, conn))
                {
                    cmd.ExecuteScalar();
                }
            }
        }

I modified by method in the Security class to be like this:

public static bool isMember(string userid)
    {
        if (!String.IsNullOrEmpty(userid))
        {
            string username = userid;
            string connString = "Data Source=localhost\\sqlexpress;Initial Catalog=Test;Integrated Security=True";
            string cmdText2 = "SELECT Count(*) FROM employee WHERE Username = '" + username + "'";
            using (SqlConnection conn = new SqlConnection(connString))
            {
                conn.Open();
                // Open DB connection.
                using (SqlCommand cmd = new SqlCommand(cmdText2, conn))
                {
                    cmd.ExecuteScalar();
                }
            }

            return true;
        }
        else if (Org.Code == code) 
            return true;
        else
            return false;
    }

But by doing this, the system was open to everybody even if they are outside the department. So how to fix this?

Upvotes: 1

Views: 285

Answers (1)

Karel Frajták
Karel Frajták

Reputation: 4489

You have to check the result of the executed command:

var count = (int) cmd.ExecuteScalar();
return count == 1; // return true if there's only one employee with given name

Also prevent SQL injection attack and modify 

string cmdText2 = "SELECT Count(*) FROM employee WHERE Username = '" + username + "'";

to 

string cmdText2 = "SELECT Count(*) FROM employee WHERE Username = @UserName";

and set the parameter value in code

cmd.Parameters.Add("@UserName", SqlDbType.VarChar);
cmd.Parameters["@UserName"].Value = userName;

Upvotes: 1

Related Questions