我如何执行SQL命令 [英] How do I execute SQL command

问题描述

我正在为应用程序编写登录函数，我在关键字'

I am writing a login function for an application and I am getting the

Incorrect syntax near the keyword 'USER'. at System.Data.SqlClient.SqlConnection

这是点击登录按钮时的代码

This is the code for when the login button is clicked

protected void loginButton_Click(object sender, EventArgs e)
     {
         try
         {

             dbConnection.Open();
             SqlCommand cmd = new SqlCommand("SELECT * FROM USER WHERE EmailAddress = @EmailAddress and Password = @Password", dbConnection);
             cmd.Parameters.AddWithValue("@EmailAddres", tbEmail.Text);
             cmd.Parameters.AddWithValue("@Password", tbPass.Text);
             SqlDataAdapter da = new SqlDataAdapter(cmd);
             DataTable dt = new DataTable();
             da.Fill(dt);
             if (dt.Rows.Count > 0)
             {
                 Response.Redirect("Default.aspx");
             }
             else
             {
                 lblText.Text = "WRONG USERNAME OR PASSWORD";
                
             }

         }

         catch (SqlException en)
         {
             lblText.Text = en.ToString();
         }
         finally
        {
             dbConnection.Close();
         }

     }

我尝试过：



谷歌搜索没有帮助。

What I have tried:

Google searches have not been helpful.

推荐答案

USER 是SQL中的保留关键字：

保留关键字（Transact-SQL） ） [ ^ ]



您需要将表名更改为其他名称，或将其括在方括号中：

USER is a reserved keyword in SQL:
Reserved Keywords (Transact-SQL)[^]

You either need to change your table name to something else, or enclose it in square brackets:

SELECT * FROM [USER] WHERE ...

您也不需要选择所有列和加载 DataTable 只是为了测试是否存在行：

You also don't need to select all columns and load a DataTable just to test whether a row exists:

using (SqlCommand cmd = new SqlCommand("SELECT 1 FROM USER WHERE EmailAddress = @EmailAddress and Password = @Password", dbConnection))
{
    cmd.Parameters.AddWithValue("@EmailAddres", tbEmail.Text);
    cmd.Parameters.AddWithValue("@Password", tbPass.Text);
    
    object result = cmd.ExecuteScalar();
    if (result == null || Convert.IsDBNull(result))
    {
        lblText.Text = "WRONG USERNAME OR PASSWORD";
    }
    else
    {
        Response.Redirect("Default.aspx");
    }
}

您将密码存储为纯文本。这是一个等待发生的安全漏洞。您应该只存储用户密码的盐渍哈希：

简单安全密码验证说明 [ ^ ]

Salted Password Hashing - 正确行事 [ ^ ]





你为什么要重新发明轮子？ ASP.NET已经有几个非常好的身份验证系统 - 例如：

ASP.NET Identity | ASP.NET网站 [ ^ ]

BrockAllen.MembershipReboot [ ^ ]

会员资格介绍 [ ^ ]

You're storing passwords as plain-text. That's a security vulnerability waiting to happen. You should only ever store a salted hash of the user's password:
Secure Password Authentication Explained Simply[^]
Salted Password Hashing - Doing it Right[^]


And why are you re-inventing the wheel? ASP.NET has several perfectly good authentication systems available already - for example:
ASP.NET Identity | The ASP.NET Site[^]
BrockAllen.MembershipReboot[^]
Introduction to Membership[^]

用户是一个保留的关键字，您可以尝试在方括号中使用它并且它应该工作



select *来自[user]

The user is a reserved keyword you could try using it in square brackets and it should work

select * from [user]

有一件事在EmailAddress出现错误（cmd.Parameters.AddWithValue（@ EmailAddres，tbEmail.Text）;）其中s缺失。

如果可能的话，尝试在SQL管理工作室中运行相同的查询

One thing it appears typo at EmailAddress at(cmd.Parameters.AddWithValue(@EmailAddres, tbEmail.Text);) where s is missing.
Also if possible try to run same query in SQL management studio

这篇关于我如何执行SQL命令的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！