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

Question

I'm trying to create a login field using ASP.NET which will take input from the textbox fields and check them against the "user" table in my database. The columns are User ID and Password. But an error

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

appears when the login form is used. I don't see any issue with the syntax...

I'm new to this so please excuse me if the error is obvious!

public partial class Login_Page : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        lblErrorMessage.Visible = false;
        SqlConnection con = new SqlConnection("Data Source=JACKS-PC\\SQLEXPRESS;Initial Catalog=CBR;Integrated Security=True");
        con.Open();
    }

    protected void btnLogin_Click(object sender, EventArgs e)
    { 
            SqlConnection con = new SqlConnection();
            con.ConnectionString = "Data Source=JACKS-PC\\SQLEXPRESS;Initial Catalog=CBR;Integrated Security=True";
            con.Open();

            string userid = txtUsername.Text.Trim();
            string password = txtPassword.Text.Trim();

            SqlCommand cmd = new SqlCommand("select `user id`,`password` from user where `user id`='" + txtUsername.Text + "'and `password`='" + txtPassword.Text + "'", con);

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();

            da.Fill(dt);

            if (dt.Rows.Count > 0)
            {
                Session["username"] = txtUsername.Text.Trim();
                Response.Redirect("Homepage.aspx");
            }
            else
            {
                lblErrorMessage.Visible = true;
            }

            con.Close();
    }
}

Answer 1

there are many issues with your code :

1. Do not store plain text password in your program or app config.
2. Do not embed connection string into your program
3. ` is not SQL Server Syntax.
4. never use string concatenation in your queries specifically if inputs are coming from users. Use Parameterized queries.

.

using (SqlCommand cmd = new SqlCommand("select 1 from tbl where id=@id", conn))
{
     var idParameter = new SqlParameter()
     idParameter.ParameterName = "@id";
     idParameter.Value         = 1234; 
     cmd.Parameters.Add(idParameter);

     ....
}

5. always dispose objects when you finish your work with them. For this use using(SqlConnection conn = new SqlConnection()).
   • all methods which implements IDisposable can be used within using statement

Answer 2

1. Just remove the '`' characters to make it work.

2. Your code is vulnerable to injection try to add values with SqlCommand.Parameters.Add() method.

Use this code:

SqlCommand cmd = new SqlCommand("select userid, password from user where user id = @id and password = @password", con);

cmd.Parameters.Add("@id", SqlDbType.VarChar).Value = txtUsername.Text;
cmd.Parameters.Add("@password", SqlDbType.VarChar).Value = txtPassword.Text;

And as @marc_s mentioned, user id is not a valid column name, it should be like userid or if it has space in it is should be like: [user id]