Prepared Statement in ASP.Net C# when using SQL Server

Question

I am just starting work with ASP.NET C# and my database is SQL Server. I am trying to write a query where I want to use with prepared statement.

This is a query that allowing log in to user:

    SqlParameter UserName = new SqlParameter("@user", SqlDbType.NVarChar, 30);
    SqlParameter Password = new SqlParameter("@pass", SqlDbType.NVarChar, 20);

    UserName.Value = user.ToLower();
    Password.Value = pass;

    SqlCommand command = new SqlCommand(null, conn);
    command.Parameters.Add(UserName);
    command.Parameters.Add(Password);
    command.CommandText = "SELECT * FROM table_users WHERE user_name = '@user' AND password = '@pass';";

    command.Prepare();
    SqlDataReader reader = command.ExecuteReader();

    bool tmp = reader.HasRows;

tmp variable value always FALSE, even when I enter exist user with correct password.

If i just remove parameters and write the query this way:

command.CommandText = "SELECT * FROM table_users WHERE user_name = '"+user+"' AND password = '"+ pass+"';";

tmp variable get value TRUE for exists users.

I tried to use this syntax for INSERT INTO queries and it works correctly.

I already read all the suggestions about changing @ to ? and it doesn't work. I had an error:

Incorrect syntax near '?'. Statement(s) could not be prepared.

Help me please, Thanks!

Answer 1

You are looking for the literals '@user' and '@pass', rather than the value from the parameter; use:

 command.CommandText =
      "SELECT * FROM table_users WHERE user_name = @user AND password = @pass;";

instead. Then look into "salted hashes", and why you should never actually store passwords.

BTW, calling Prepare() here isn't helping here. I'm also going to plug dapper-dot-net (free/OSS), which would make this entire thing just:

bool authenticated = conn.Query(
    @"select 1 from table_users where user_name = @user and password = @pass",
    new {user = user.ToLower(), pass} ).Any();

or, if you want the record:

var tableUser = conn.Query<TableUser>(
    @"select * from table_users where user_name = @user and password = @pass",
    new {user = user.ToLower(), pass} ).SingleOrDefault();