"Incorrect syntax near 'admin'

Question

this programm when i enter username and password go to data base and compare from table,but when i enter username admin ,password admin(exist in table) compalier show error "Incorrect syntax near 'admin'" in line int temp = Convert.ToInt32(com.ExecuteScalar().ToString());

protected void Button1_Click(object sender, EventArgs e)
{

    SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\1\Documents\DB.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True");
    conn.Open();
    string checkuser = "select count(*) from [Users] where Username '" + TextBoxUserName.Text + "'";
    SqlCommand com = new SqlCommand(checkuser,conn);
    int temp = Convert.ToInt32(com.ExecuteScalar().ToString());
    conn.Close();

    if (temp == 1)
    {
        conn.Open();
        string checkpassword = "select Password from Users where Password'" + TextBoxPassword.Text + "'";
        SqlCommand passComm = new SqlCommand(checkpassword, conn);
        string password = passComm.ExecuteScalar().ToString();
        if (password == TextBoxPassword.Text)
        {
            //Session["NEW"] = TextBoxUserName.Text;
            Response.Redirect("Welcome.aspx");
        }

        else
        {
            Response.Redirect("Error.aspx");
        }

    }

Please read about SQL injection, you are vulnerable to it. Here is one of many resources to get you started: http://www.troyhunt.com/2013/07/everything-you-wanted-to-know-about-sql.html Storing plain-text passwords in a database is also generally frown upon. — dee-see, Oct 14 '14 at 16:58

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

The error is simply caused by the missing equals before the values concatenated in the sql command text.

But also fixing it, your code is wrong for other reasons.

You should ALWAYS use a parameterized query to avoid Sql Injection and parsing problems,
You could remove the COUNT function that causes an unnecessary load of all records just to confirm the existence of your searched data
You need to identify your user searching for both password and username on the SAME record, as it is now, the code above search first the username and then a password, but I can type an existing user name (first if passed) and use a password of a different user (second if passed) and then gain access to your site.

.

string checkuser = "IF EXISTS(select 1 from [Users] where Username = @usr AND Password=@pwd)
                    SELECT 1 ELSE SELECT 0";
using(SqlConnection conn = new SqlConnection(....))    
using(SqlCommand com = new SqlCommand(checkuser,conn))
{
     conn.Open();
     com.Parameters.AddWithValue("@usr", TextBoxUserName.Text);
     com.Parameters.AddWithValue("@pwd", TextBoxPassword.Text);
     int temp = Convert.ToInt32(com.ExecuteScalar());
     if (temp == 1)
        Response.Redirect("Welcome.aspx");
     else
        Response.Redirect("Error.aspx");
}

Other things changed in the example above are the USING STATEMENT to be sure that your connection and command are disposed at the end of the operation also in case of exceptions

score 0 · Answer 2 · answered Oct 14 '14 at 16:57

Try changing this line

string checkuser = "select count(*) from [Users] where Username '" + TextBoxUserName.Text + "'";

to this

string checkuser = "select count(*) from [Users] where Username = '" + TextBoxUserName.Text + "'";

you are missing an = sign

you'll need to do the same to your password select as well, you also missed the = sign there.

string checkpassword = "select Password from Users where Password = '" + TextBoxPassword.Text + "'";

JayHach · Answer 3 · 2014-10-14T18:41:49.200

When checking the Password, you should also include the UserName:

string checkpassword = "select Password from Users where UserName = '" + TexBoxUserName.Text + "' AND Password = '" + TextBoxPassword.Text + "'";

If you do not include the UserName the it is only validating that some user has that password.

The following code will prevent SQL injection by paramterizing the command text

SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\1\Documents\DB.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True");
conn.Open();
string checkuser = "SELECT Count(UserName) FROM USERS WHERE UserName = @UserName";
SqlCommand com = new SqlCommand(checkuser,conn);
SqlParameter parmUserName = new SqlParameter("UserName", TextBoxUserName.Text);
com.Parameters.Add(parmUserName);

int temp = Convert.ToInt32(com.ExecuteScalar().ToString());
conn.Close();
if (temp == 1)
    {
        conn.Open();
        string checkpassword = "SELECT Password FROM USERS WHERE UserName = @UserName AND Password = @Password";

        SqlCommand passComm = new SqlCommand(checkpassword, conn);
        SqlParameter parmPassword = new SqlParameter("Password", TextBoxPAssword.Text);

        com.Parameters.Add(parmUserName);
        com.Parameters.Add(parmPassword);

        string password = passComm.ExecuteScalar().ToString();

"Incorrect syntax near 'admin'

3 Answers3

Linked