Login screen using asp.net and SQL Server

Question

I am trying to create a login page but but my Login button does not work. I am selecting username and password from my sql server database.

Unfortunately, I get an error

System.Data.SqlClient.SqlException: Incorrect syntax near ''

on line 27:

int temp = Convert.ToInt32(com.ExecuteScalar().ToString());

Code below:

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["connect"].ConnectionString);
con.Open();

string checkuser = "select * from tb_Login where Username='" + txtUsername.Text + "' and Password='" + txtPassword.Text + "' ";

SqlCommand com = new SqlCommand(checkuser, con);

int temp = Convert.ToInt32(com.ExecuteScalar().ToString());
con.Close();

if (temp == 1)
{
    con.Open();
    string checkPass = "select Password from tb_Login where Username='" + txtUsername.Text + "'";

    SqlCommand passCom = new SqlCommand(checkPass, con);
    string password = passCom.ExecuteScalar().ToString().Replace(" ", "");

    if (password == txtPassword.Text)
    {
        Session["New"] = txtUsername.Text;
        Response.Write("Correct");
    }
    else
    {
        Response.Write("Not Correct");
    }
}
else
{
    Response.Write("Username not correct");
}

You are missing an `=` after Username and should use parametrised queries. Anyone can log in or worse via SQL injection. Also passwords should not be stored in plain text. — Martin Smith, Jan 21 '17 at 20:15
The universal password to access your application is: `' OR 1 == 1;--` — David, Jan 21 '17 at 20:18
Hi, I have added "=" but I am getting another error " System.NullReferenceException: Object reference not set to an instance of an object. int temp = Convert.ToInt32(com.ExecuteScalar().ToString());" This is just a rough program just to get my Login working. Then i will get into the detail — Lyubomir Valchev, Jan 21 '17 at 20:22

score 1 · Accepted Answer · edited Feb 09 '19 at 12:03

1

This line of code:

string checkuser = "select * from tb_Login where Username='" + txtUsername.Text + "' and Password='" + txtPassword.Text + "' ";

Is sending a query to the database and asking: "Give me all the columns from tb_Login whose UserName is the value in the txtUsername box and the Password is in the txtPassword box."

Then this line will take the value of the first column of the first row and try to convert it to an integer and if it cannot it will fail:

int temp = Convert.ToInt32(com.ExecuteScalar().ToString());

Change your query to select one column only: the column you need.

Also make sure you read this question on Stack Overflow so you can see how your code is a security threat to your own application.

edited Feb 09 '19 at 12:03

halfer

19,824
17
99
186

answered Jan 21 '17 at 20:24

CodingYoshi

25,467
4
62
64

Thanks for your help. I am just not understanding how to change this query: int temp = Convert.ToInt32(com.ExecuteScalar().ToString()); – Lyubomir Valchev Jan 21 '17 at 20:27
No that line is ok. You need to change your sql query because it is using `select *` so it will get many columns. You need to just get the column you need. I do not know the name of the column but if it is `Column1` then write `select Column1 from tb_Login where Username=` – CodingYoshi Jan 21 '17 at 20:31
Thank you for your help. I really appreciate it and your time – Lyubomir Valchev Jan 21 '17 at 20:36

Login screen using asp.net and SQL Server

1 Answers1