Setting Roles in Login page using a column in sql

Question

So I'm building a page named "Login" where the person with one account already in database can well do the login and entry in the "main page"

I have 2 Different Rolls: -Admin - Can create others accoutns, edit, delete etc...

-Normal user - Can't create another accounts, and it is just enable to read, not edit, delete etc...

I did a column in sql with the following name "function" where it keeps the roles as numbers...

1-admin

2-Normal User

But I'm not getting how I should use it, I did something like this:

protected void Button1_Click(object sender, EventArgs e)
{
    using (SqlConnection sqlcon = new SqlConnection(@"Data Source=PEDRO-PC\SQLEXPRESS;Initial Catalog=costumizado;Integrated Security=True"))
    {
        RNGCryptoServiceProvider rng = new RNGCryptoServiceProvider();
        byte[] buffer = new byte[1024];

        rng.GetBytes(buffer);
        string salt = BitConverter.ToString(buffer);
        var saltedPassword = TextBox2.Text + salt;

        string passstr = Encrypt(TextBox2.Text);
        // string Select_Query = "SELECT count (*) FROM Usuarios where(Nome = '" + TextBox1.Text + "' and PalavraPasse ='" + passstr + "');";
        //     Debug.Write(Select_Query);

        sqlcon.Close();
        using (sqlcon)
        {
            SqlCommand command = new SqlCommand("SELECT Nome, PalavraPasse, Funcao FROM Usuarios;", sqlcon);
            sqlcon.Open();
            SqlDataReader reader = command.ExecuteReader();

            if (reader.HasRows)
            {
                if (reader.Read())
                {
                    Debug.WriteLine("{0} {1} {2}",
                    reader["Nome"],
                    reader["PalavraPasse"],
                    reader["Funcao"]);
                }

                if (reader["Funcao"] == "1")
                {
                    Response.Redirect("~/StartupAdmin.aspx");
                } 
                else if (reader["Funcao"] == "2")
                {
                    Response.Redirect("~/StartupNormal.aspx");
                }
            }
            else
            {
                Debug.WriteLine("No rows found.");
            }   
            reader.Close();
            sqlcon.Close();
        }
    }
}

ps: I know that doing this:

if (count > 0 && function =="1" )

Doesn't make sense cause "Function" doesnt even is a variable, but but it was to show more or less what I wanted to do Output

No rows found.
1 1 1
1 1 1
1 1 1
1 1 1
1 1 1
1 1 1

the 1-1-1 was a user that I created jsut for test

`"SELECT COUNT(*) FROM Usuarios where(Name = '" + TextBox1.Text + "' and Password ='" + TextBox2.Text +"');";` is a huge injection problem and very strongly suggests that you are storing passwords as plain text in your database. Don't inject your values into your queries, parametrise them, and don't store plain text passwords (they need to be salted and hashed). Before anything, you need to fix the huge security flaws you have in your application. — Thom A, May 16 '19 at 13:31
@Yeah I need to do that thanks! but if you do know anything about this say it thanks! I will search how to do that — PuppyPoop, May 16 '19 at 13:33
I suggest LINQ queries. You probably can validate and use them. Try Dapper out. ORMs eliminate SQL injection issues — Narendran Pandian, May 16 '19 at 13:33
Rather than getting a count of the users, get the actual fields from that user's row in the database (e.g. SELECT Name, Role FROM Users`..etc. Since the count will only ever be 0 or 1 it's easier to use C# to check if there is one row. If there is, then you can get the "Role" value from the data to tell you the user's role, and therefore which page to redirect them to. — ADyson, May 16 '19 at 14:49
P.S. I really hope "StartupAdmin.aspx" (and any other admin-only) pages have security checks on them to stop non-admin users from accessing them. You might want to store the user's role in the Session along with their name, so you can test this. Otherwise someone could just type StartupAdmin.aspx into their browser's address bar and go straight to that page, without having the correct role to do so. — ADyson, May 16 '19 at 14:50
@ADyson Already did the session thing, or else I could route from the URL... btw Could you go more in depth in your first comment? do it in the answer... Sorry for being annoying — PuppyPoop, May 16 '19 at 15:38
I see you've put the name in the session...but not the role. Don't see how you could have put the role in the session already, because you aren't currently getting the user's role entry from the database. You need to put the role in the session or else you won't know if the current user is admin or not — ADyson, May 16 '19 at 15:56
P.S. which part of my first comment didn't you understand? Just so I can write a more useful answer. You can find a general example reading rows (instead of just counts) here in the Microsoft docs: https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/retrieving-data-using-a-datareader . In your case, from that example, you could just replace `while (reader.Read())` with `if (reader.Read())`, since you only want to try and get one row. And the `else` of that would be where you create an error message to say the login failed. — ADyson, May 16 '19 at 15:59
P.P.S. I really don't think you need `Response.Cookies["TextBox1.Text"].Value = TextBox1.Text;` - why do you want to add this cookie? The user's name is already in the session (which will be implemented using cookies, normally) — ADyson, May 16 '19 at 16:01
So I don't need the "count"? and in the link you gave me how I implement the "function" column? — PuppyPoop, May 17 '19 at 08:46
@ADyson I update my code look at it, nothing is happening when I try to loggin — PuppyPoop, May 17 '19 at 08:51
Try getting the values by the column name, it's easier: http://www.java2s.com/Code/CSharp/Database-ADO.net/ReferencedatainSqlDataReaderbycolumnname.htm — ADyson, May 17 '19 at 08:53
And forget about your `count` variable. If the code goes into the `if (reader.Read())` block then that means one row was was returned - so the user was found. That's where you read the function value and the name, and put them into the session. You don't need a separate count. If it doesn't enter that if block, then the login was invalid. — ADyson, May 17 '19 at 08:55
@ADyson Ok I'm getting the row in the debug output, so now how get it to the point that let me go to other page... *Code edited* — PuppyPoop, May 17 '19 at 09:14
So what is it doing instead of redirecting you? Try moving `if (reader["Funcao"] == "1") { Response.Redirect("~/StartupAdmin.aspx"); } else if (reader["Funcao"] == "2") { Response.Redirect("~/StartupNormal.aspx"); }` inside the `if (reader.Read())` block. You also need to add the values of reader["Funcao"] and reader["Nome"] to your session too, remember — ADyson, May 17 '19 at 09:19
P.S. A separate point which you can deal with later: I saw from your previous edit that you were using MD5 to hash the password (note, this is **hashing**, not **encryption**, not the same thing at all, you can google the difference). MD5 is obsolete, where did you get the idea for that? Use something more modern. e.g. https://stackoverflow.com/questions/54689183/how-to-hash-users-passwords — ADyson, May 17 '19 at 09:24
@ADyson it Still refresh and doesnt redirect to another page, just doing the output — PuppyPoop, May 17 '19 at 09:26
@ADyson Thanks! for the hint... I will try deal with that after this — PuppyPoop, May 17 '19 at 09:28
what is the datatype of the "funcao" column in your database? Int or varchar? — ADyson, May 17 '19 at 09:29
@ADyson it was int at first but now I changed it is in varchar — PuppyPoop, May 17 '19 at 09:30
Try `reader["Funcao"].ToString()` in the `if` statements, instead of just `reader["Funcao"]` — ADyson, May 17 '19 at 09:32

Setting Roles in Login page using a column in sql

0 Answers0