C# Local SQL rows returned if statement

Question

I'm trying to get my login system to work. Currently I think I have everything in place for it to work except the if statement conditions (if row is returned, then if statement is true, else login unsuccessful). I'm not sure how to read in the number of rows returned, I did attempt to use the ExecuteReader Method but couldn't get it to work. Appreciate any help, thanks.

Code:

private void btn_login_Click(object sender, EventArgs e)
{
    SqlCeConnection connection = new SqlCeConnection(@"Data Source=C:\\temp\\Project\\WindowsFormsApplication2\\Database.sdf");

    connection.Open();

    SqlCeCommand command = new SqlCeCommand("SELECT * FROM Technician WHERE Name = '" + txt_username.Text + "' AND Password = '" + txt_password.Text + "' ");
    SqlCeDataAdapter dataAdapter = new SqlCeDataAdapter(command);

    if ()
    {
        MessageBox.Show("Login Successful");
        System.Threading.Thread t = new System.Threading.Thread(new System.Threading.ThreadStart(MainMenuForm));
        t.Start();
        this.Close();
    }
    else
    {
        MessageBox.Show("Login Unsuccessful");
        return;
    }

    connection.Close();
}

Please learn about what SQL Injection is and how hazardous a threat it can be: http://en.wikipedia.org/wiki/SQL_injection — Dennis Traub, Apr 19 '13 at 11:38
You should always use [_parameterized queries_](http://www.codinghorror.com/blog/2005/04/give-me-parameterized-sql-or-give-me-death.html). This kind of codes are open for [_SQL Injection_](http://en.wikipedia.org/wiki/SQL_injection) attacks. — Soner Gönül, Apr 19 '13 at 11:40

score 3 · Accepted Answer · edited May 23 '17 at 12:04

I have changed your code to use a simpler ExecuteScalar that returns the first column of the first row obtained by your query

Of course, it is of extreme importance that you don't write your sql commands concatenating strings because this could fail in spectacular ways. (What if your textboxes contains a single quote and what if your user writes malicious text like this

using(SqlCeConnection connection = new SqlCeConnection(.....)) 
{
     connection.Open();
     string sqlText = "SELECT Count(*) FROM Technician WHERE Name = @name AND Password=@pwd"
     SqlCeCommand command = new SqlCeCommand(sqlText, connection);
     command.Parameters.AddWithValue("@name", txt_username.Text);
     command.Parameters.AddWithValue("@pwd", txt_password.Text);
     int result = (int)command.ExecuteScalar();
     if (result > 0)
     {
          MessageBox.Show("Login Successful");
          System.Threading.Thread t = new System.Threading.Thread(new System.Threading.ThreadStart(MainMenuForm));
          t.Start();
          this.Close();
     }
     else
     {
           MessageBox.Show("Login Unsuccessful");
           return;
     }
 }

Notice also the using statement, in your previous code you exit from the procedure if no login is found but you forget to close the connection. This could become a big problem during lifetime of your application. The Using statement prevents this

Now I should start talking about the weakness of storing and trasmitting passwords in clear text, but that is another matter

Darren · Answer 2 · 2013-04-19T11:44:17.913

The method ExecuteNonQuery will return the number of rows affected.

int rowsAffected = command.ExecuteNonQuery();
bool userExists = rowsAffected > 0;
if (userExists) // The user exists
{

}

Note: However your application is vulnerable to SQL Injection. I.e. I could enter ;DROP TABLE Technician into the txt_password text box.

You should use a parameterized query instead or another authentication method which is more secure (ASP.NET membership for instance).

To use paramertised queries you can change the CommandText to:

 SqlCeCommand command = new SqlCeCommand("SELECT * FROM Technician WHERE Name=@username AND password=@password";

And then add the parameters in via:

    command.Parameters.AddWithValue("@username", txt_username.Text);  
    command.Parameters.AddWithValue("@password", txt_password.Text);

http://johnhforrest.com/2010/10/parameterized-sql-queries-in-c/

score 0 · Answer 3 · answered Apr 19 '13 at 14:42

private void btn_login_Click(object sender, EventArgs e) {

SqlConnection connection = new SqlConnection(@"Data Source=C:\\temp\\Project\\WindowsFormsApplication2\\Database.sdf");

connection.Open();

SqlCommand command = new SqlCommand("SELECT * FROM Technician WHERE Name = '" + txt_username.Text + "' AND Password = '" + txt_password.Text + "' ");
int row=command.ExecuteNonQuery();

if (row>0)
{
    MessageBox.Show("Login Successful");
    System.Threading.Thread t = new System.Threading.Thread(new System.Threading.ThreadStart(MainMenuForm));
    t.Start();
    this.Close();
}
else
{
    MessageBox.Show("Login Unsuccessful");
    return;
}

connection.Close();

}

score 0 · Answer 4 · answered Apr 26 '13 at 11:37

0

    a=1;
    b=1;


 if a=b
    {
    a=c;
    } 
 else
    {
    a=b;
    }

else if
{
MessageBox.Show("Login Unsuccessful");
return i;

answered Apr 26 '13 at 11:37

Admin

1

C# Local SQL rows returned if statement

4 Answers4