Having an issue with retrieving the ID from my login application C# SQL

Question

The button I have checks what is in the two username and password texts boxes in the table username and password columns and if they are both present it signs them in. I can't seem to figure how to set the TeacherID to a variable or let alone find out what it is so I can tell what user has signed in.

(I KNOW MY CODE IS VULNERABLE TO SQL INJECTION I WILL DEAL WITH IT LATER)

I would very much appreciate any help you could give me!

Here is my attempt to setting the TEACHER ID to an integer called x:

private void TeacherLoginButton_Click(object sender, EventArgs e)
    {



        string connectionString = ConfigurationManager.ConnectionStrings["myconnectionstring"].ConnectionString;
        SqlConnection connect = new SqlConnection(connectionString);
        connect.Open();


        int x = 0;
        SqlCommand command10 = new SqlCommand(@"SELECT [TeacherID], [Username], [Password] FROM TeacherDetails WHERE ( @x = [TeacherID] AND [Username]='" + this.usernameTlogin.Text + "' AND [Password]= '" + this.passwordTlogin.Text +"');", connect);

        command10.Parameters.AddWithValue(@"x", x ); 
        SqlDataReader reader;
        reader = command10.ExecuteReader();
        int count = 0;

            while (reader.Read())
            {
                count = count + 1;


            }
            if( count == 1)
            {
                MessageBox.Show("Usename and password is correct" + x.ToString());

                this.Hide();
                TeacherDashboardForm TeacherDashboard = new TeacherDashboardForm();
                TeacherDashboard.Show();
            }
            else if (count > 1)
            {

                MessageBox.Show("BEEP BOOP ERROR");
            }
            else
            {

                MessageBox.Show("Username or password is incorrect");
            }

This is my code before I attempted to find what the ID was:

 private void TeacherLoginButton_Click(object sender, EventArgs e)
    {



        string connectionString = ConfigurationManager.ConnectionStrings["myconnectionstring"].ConnectionString;
        SqlConnection connect = new SqlConnection(connectionString);
        connect.Open();



        SqlCommand command10 = new SqlCommand("SELECT [Username], [Password] FROM TeacherDetails WHERE ([Username]='" + this.usernameTlogin.Text + "' AND [Password]= '" + this.passwordTlogin.Text +"');", connect);

        SqlDataReader reader;
        reader = command10.ExecuteReader();
        int count = 0;

            while (reader.Read())
            {
                count = count + 1;


            }
            if( count == 1)
            {
                MessageBox.Show("Username and password is correct");

                this.Hide();
                TeacherDashboardForm TeacherDashboard = new TeacherDashboardForm();
                TeacherDashboard.Show();
            }
            else if (count > 1)
            {

                MessageBox.Show("BEEP BOOP ERROR");
            }
            else
            {

                MessageBox.Show("Username or password is incorrect");
            }

Here is my table:

CREATE TABLE [dbo].[TeacherDetails] (
[TeacherID]     INT           IDENTITY (1, 1) NOT NULL,
[First Name]    NVARCHAR (50) NULL,
[Last Name]     NVARCHAR (50) NULL,
[Title]         NVARCHAR (50) NULL,
[Username]      NVARCHAR (50) NULL,
[Password]      NVARCHAR (50) NULL,
[Email Address] NVARCHAR (50) NULL,
PRIMARY KEY CLUSTERED ([TeacherID] ASC)
);

score 2 · Answer 1 · edited May 23 '17 at 11:43

2

First thing I see;

WHERE ( @x = [TeacherID]

should be

WHERE ( [TeacherID] = @x

And when you add this parameter value, you need to use @ inside of string, not a verbatim string literal. Like;

command10.Parameters.AddWithValue("@x", x );

You said that but I want to say again because it is really important. You should always use parameterized queries. This kind of string concatenations are open for SQL Injection attacks.

Also strongly suspect you save you passwords as a plain text. Don't do that! Read: Best way to store password in database

And use using statement to dispose your SqlConnection, SqlCommand and SqlDataReader.

edited May 23 '17 at 11:43

Community

1
1

answered Apr 22 '15 at 11:35

Soner Gönül

97,193
102
206
364

1

He always tries to get the user whose ID is 0, but I suppose he wants to get the id from the user whose username and password have been supplied. – Nikola Davidovic Apr 22 '15 at 11:38
Okay thank you the code I produced hasn't seemed to do what is intended as count always becomes > 1 therefore saying the details are incorrect. The second code I produced works with the signing in part – mot375 Apr 22 '15 at 11:39
@NikolaDavidovic yes that is what I am trying to do – mot375 Apr 22 '15 at 11:40
I guess there is no way to have id = 0 with identity or I am wrong? – Nikola Davidovic Apr 22 '15 at 11:41
I'm not sure I was just setting a value for the variable @NikolaDavidovic but I want x to equal the ID of the user who signed in – mot375 Apr 22 '15 at 11:42
1

@mot375 You don't want to have the id in where part of the query. You want something like this: "SELECT [TeacherID] FROM TeacherDetails WHERE ( [Username]='" + this.usernameTlogin.Text + "' AND [Password]= '" + this.passwordTlogin.Text +"');" Keep in mind what is suggested in the answer, SQL injection problem – Nikola Davidovic Apr 22 '15 at 11:43
1

Then use `int x = reader.GetInt32(0);` after you call `reader.Read()` – Nikola Davidovic Apr 22 '15 at 11:45
Okay thank you very much!!!!!! @NikolaDavidovic how would I set this to a variable? Create an answer to my question if you would like to and I will accept it as the answer – mot375 Apr 22 '15 at 11:47
Oh don't worry you already answered the question ;) Thank you very much! @NikolaDavidovic – mot375 Apr 22 '15 at 11:48
There you have a broad answer. But keep in mind that everything that @SonerGönül told you is totally important and you should implement it according to his suggestions. Note I added the query as you were using parameters, beware of SQL injections or your teachers will have false marks in their log :) – Nikola Davidovic Apr 22 '15 at 11:51

score 1 · Accepted Answer · answered Apr 22 '15 at 11:49

1

You should change the query into "SELECT [TeacherID] FROM TeacherDetails WHERE ( [Username]=@username AND [Password]= @password)"

And then in the code

while (reader.Read())
 {
      x = reader.GetInt32(0);
 }

answered Apr 22 '15 at 11:49

Nikola Davidovic

8,556
1
27
33

Having an issue with retrieving the ID from my login application C# SQL

2 Answers2