使用SQL Server时，在ASP.Net C＃prepared声明 [英] Prepared Statement in ASP.Net C# when using SQL Server

问题描述

我刚开始用ASP.NET C＃的工作，我的数据库是SQL Server中。我想编写一个查询，我想用prepared语句中使用。

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 变量的值总是 FALSE ，甚至当我进入存在的用户使用正确的密码。

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 变量获取值 TRUE 的存在用户。

tmp variable get value TRUE for exists users.

我试图用这个语法 INSERT INTO 查询和它工作正常。

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:

附近有语法错误'？'。声明（S）不可能是prepared。

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

请帮助我，
谢谢！

Help me please, Thanks!

推荐答案

您正在寻找的文本 @用户和'@通，而不是从参数的值;使用方法：

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.

顺便说一句，叫 prepare（）这里就不在这里帮助。我还打算堵塞短小精悍点网（免费/ OS​​S），这将使只是这整个事情：

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

这篇关于使用SQL Server时，在ASP.Net C＃prepared声明的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！