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!

Marc Gravell · Accepted Answer · 2011-12-01 09:44:30Z

9

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();

edited Dec 1, 2011 at 9:44

answered Dec 1, 2011 at 9:37

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

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

9 Comments

Vitali Shvarts Over a year ago

Thank you very much, now its work. I'll check the "salted hashes".

Jesse Webb Over a year ago

@MarkGravell - "BTW, calling Prepare() here isn't helping here." Could you elaborate on why using a prepared statement isn't helping in this usage?

Marc Gravell Over a year ago

@Jesse because most databases these days don't care: they have automatic query plan caching on ad-hoc queries that makes any difference basically unmeasurable

Meer Over a year ago

@MarcGravell can i get the query in a string from dapper like "select * from table_users where user_name = user12 and password = 123" with values

Meer Over a year ago

i need the query with parameter's values in string

|

Collectives™ on Stack Overflow

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

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related