Incorrect Syntax in SQL?

Question

I'm making a user login screen in c# winforms, I want to be able to check a user's username and password against records in an SQL database as per this link, however my code throws an exception saying "Incorrect syntax near User".

Could anyone help me figure out what's wrong with my code please? offending code is below.

 private bool CompareStrings(string string1, string string2)
    {
        return String.Compare(string1, string2, true, System.Globalization.CultureInfo.InvariantCulture) == 0 ? true : false;
    }

    private void LoginBtn_Click(object sender, EventArgs e)
    {
        //var username = textBox1.Text;
        //var password = maskedTextBox1.Text;
        try
        {
            SqlConnection Conn = new SqlConnection("Data Source=***********;Initial Catalog=*********;Persist Security Info=True;User ID=*********;Password=*******");
            SqlCommand com = new SqlCommand();
            com.Connection = Conn;
            Conn.Open();

            com.CommandText = ("SELECT (Username) AS User, (Password) as Pass FROM dbname WHERE User='" + textBox1.Text + "'");
            SqlDataReader reader = com.ExecuteReader();
            var username = textBox1.Text;
            var password = maskedTextBox1.Text;
            while (reader.Read())
            {
                if (this.CompareStrings(reader["User"].ToString(), username) &&
                    this.CompareStrings(reader["Pass"].ToString(), password))
                {
                    MessageBox.Show("Login Authenticated!");

                }
                else
                {
                    MessageBox.Show("Login failed!");

                }
                Conn.Close();
                reader.Close();
            }
        }
        catch(Exception ex)
        {
            MessageBox.Show(ex.ToString());
        }

Please use parameterized queries. Your code is vulnerable to SQL injection by building that query with unsanitized user input. It also looks as though you're storing user credentials in plain text... — DGibbs
– DGibbs, Commented Jun 9, 2014 at 10:48

Soner Gönül · Accepted Answer · 2014-06-09 11:01:18Z

7

USER is a reserved keyword in T-SQL. You should use it with square brackets like [USER]. However, the best solution is to change the name to a nonreserved word.

Always use parameterized queries. This kind of string concatenations are open for SQL Injection attacks.

USER is an alias for Username column. You should use it's original name in your WHERE clause.

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

using(SqlConnection Conn = new SqlConnection(connString))
using(SqlCommand com = Conn.CreateCommand())
{
    com.CommandText = "SELECT (Username) AS [User], (Password) as Pass FROM dbname WHERE Username = @user";
    com.Parameters.AddWithValue("@user", textBox1.Text);
    Conn.Open();
    using(SqlDataReader reader = com.ExecuteReader())
    {
       ...
    }
}

edited Jun 9, 2014 at 11:01

answered Jun 9, 2014 at 10:45

Soner Gönül

99.1k103 gold badges224 silver badges375 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Reece Over a year ago

Thanks Soner, this resolved my initial problem but before I mark as answered could you help me with another problem? it appears that my while loop in the above code will not fire when I try to breakpoint it, can you see any errors which could cause this behavior?

Mohit Over a year ago

@Reece Check my answer there is error in your where clause

Reece Over a year ago

That in conjunction with this answer has resolved my issue, thanks for your help Gents; most appreciated!

Mohit · Accepted Answer · 2014-06-09 10:56:40Z

2

You must use Square brackets if you are using any keyword that is predefined OR Reserved by Sql Server

like here you are using USER you shall use [USER]

and also your query is wrong you shall use username instead of user in where clause

You should use this

 com.CommandText = ("SELECT (Username) AS [User], (Password) as Pass FROM dbname WHERE Username='" + textBox1.Text + "'");

edited Jun 9, 2014 at 10:56

answered Jun 9, 2014 at 10:50

Mohit

11.3k16 gold badges70 silver badges95 bronze badges

Collectives™ on Stack Overflow

Incorrect Syntax in SQL?

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related