SQL Log-In System

Question

This is my first time asking a question on StackOverflow, so I apologize in advance if I ask someone improper. I couldn't find anything to help me while researching this for the past few days, so thank you in advance to anyone who tries to help.

I am making a database that allows people to register and log-in. I am using C# in VS2012. Below is my log-in code and I am running into some trouble when testing. It iterates through everyone in the database and tells me that log-in has failed till it gets to the right user.

    private void button1_Click_1(object sender, EventArgs e)
    {
        try
        {
            cn.Open();
        }
        catch (Exception)
        {
            MessageBox.Show("Did not connect");
        }


        SqlCommand cmd = new SqlCommand("SELECT * FROM [Users]", cn);
        cmd.Connection = cn;
        SqlDataReader reader = null;
        reader = cmd.ExecuteReader();
        while (reader.Read())
        {
            if (textBox1.Text == (reader["Username"].ToString()) && textBox2.Text == (reader["Password"].ToString()))
            {
                MessageBox.Show("Logged in");
            }
            else
            {
                MessageBox.Show("Login has failed. Please check your Username and Password.");
            }
        }
        cn.Close();
    }

As for my registration portion, I'm not sure if it is a VS2012 thing or what, but the information doesn't get saved into the database after I end debug and then go back to debug again.

    private void button1_Click_1(object sender, EventArgs e)
    {
        cn.Open();
        SqlCommand cm1 = new SqlCommand("INSERT INTO Users (Username, Password) VALUES (@Username, @Password)", cn);
        SqlCommand cm2 = new SqlCommand("INSERT INTO Contact(Name, Address, City, State, PostalCode, Email, PhoneNumber) VALUES(@Name, @Address, @City, @State, @PostalCode, @Email, @PhoneNumber)", cn);



        cm1.Parameters.AddWithValue("@Username", textBox1.Text);
        cm1.Parameters.AddWithValue("@Password", textBox2.Text);
        cm2.Parameters.AddWithValue("@Name", textBox3);
        cm2.Parameters.AddWithValue("@Address", textBox4);
        cm2.Parameters.AddWithValue("@City", textBox5);
        cm2.Parameters.AddWithValue("@State", textBox6);
        cm2.Parameters.AddWithValue("@PostalCode", textBox7);
        cm2.Parameters.AddWithValue("@Email", textBox8);
        cm2.Parameters.AddWithValue("@PhoneNumber", textBox9);

        try
        {
            int affectedRows =  cm1.ExecuteNonQuery(); //+cm2.ExecuteNonQuery();

            if (affectedRows > 0)
            {
                MessageBox.Show("Insert Sucsess!", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            else
            {
                MessageBox.Show("Insert Failed!", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message, Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
        cn.Close();
    }

score 4 · Accepted Answer · edited May 23 '17 at 11:55

When you have a database file in you project and you build the project, the database file could be copied from the root project folder into the output (bin\debug or bin\release) folder.
This behavior is controlled by the Copy To Output Directory property of the database file.

If you have this property set to Copy Always then, every time you build your project a fresh copy of the database file is copied from the root project folder to the output directory overwriting the one already there and destroying the changes you have made in the previous debug session.

A suggested fix is to change this property to Copy Never or Copy if Newer

See a detailed explanation on MSDN at this page

For the first part of your question you could avoid to loop on every user adding a WHERE clause to your sql text. Just be aware that you should never use string concatenation to build your sql strings, instead you use ALWAYS the parameters. (Why? You avoid Sql Injection and text single quote parsing/doubling)

string sqlText = "SELECT * FROM [Users] WHERE Username = @usr AND [Password] = @psw";
SqlCommand cmd = new SqlCommand(sqlText, cn);
cmd.Parameters.AddWithValue("@usr", textbox1.Text);
cmd.Parameters.AddWithValue("@psw", textbox2.Text);
SqlDataReader reader = cmd.ExecuteReader();
if(reader.HasRows)
    // You have found the user....

Another bit of advice. Do not store the passwords in clear text inside your database. Store always an hash of this string and, on search, compute the hash value and search for it instead of a clear password.

Thank you so much for this, it works flawlessly. I need to research how to hash the passwords now. Note: In the fourth line, could you change "@pwd" to "@psw" — AlphaIrri, Dec 05 '12 at 00:46

score 2 · Answer 2 · edited May 23 '17 at 10:24

2

In order for you to get this working you will need a WHERE clause in your SELECT. However, I would not recommend to use

SqlCommand cmd = new SqlCommand("SELECT * FROM [Users] WHERE Username='" + textBox1.Text + "'", cn);

because of possible SQL injection.

Please learn how to use Stored Procedures and how to Execute them from your C# code.

edited May 23 '17 at 10:24

Community

1
1

answered Dec 04 '12 at 23:46

Hanlet Escaño

17,114
8
52
75

2

Stored Procedures are good, but paramaterized SQL is sufficient in this case I think. – Nate Dec 04 '12 at 23:58

SQL Log-In System

2 Answers2