ASP.NET登录功能报Invalid Column name 'password'错误如何解决

错误基本信息 #

• 触发场景：开发ASP.NET WebForm登录功能，执行登录按钮点击事件Button1Login_Click的密码查询逻辑时抛出异常
• 报错触发行：string password = passComm.ExecuteScalar().ToString();
• 错误提示：Invalid Column name 'password'（列名password无效）
• 报错截图：

关联业务代码 #

protected void Button1Login_Click(object sender, EventArgs e)
{
    SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["UserThomasConnectionString"].ConnectionString);
    conn.Open();
    string checkuser = "select count(*) from RegisterFormTable where User_Name='" + TextBox1Username.Text + "'";
    SqlCommand com = new SqlCommand(checkuser, conn);
    int temp = Convert.ToInt32(com.ExecuteScalar().ToString());
    conn.Close();
    if (temp == 1)
    {
        conn.Open();
        string checkPasswordQuery = "select password from RegisterFormTable where User_Name='" + TextBox1Username.Text + "'";
        SqlCommand passComm = new SqlCommand(checkPasswordQuery, conn);
        string password = passComm.ExecuteScalar().ToString();
        password = string.Join("", password.Split(default(string[]), StringSplitOptions.RemoveEmptyEntries));

        if (password == TextBox2Password.Text)  
        {
            Session["New"] = TextBox1Username.Text;
            Response.Write("Password is correct");
        }
        else
        {
            Response.Write("Password is not correct");
        }
    }
    else
    {
        Response.Write("Username is not correct");
    }
    conn.Close();
}

错误根因 #

该错误属于SQL执行阶段的元数据校验错误，核心原因是当前连接的数据库中，RegisterFormTable表不存在SQL语句中查询的password列，常见诱因有3种：

• 列名拼写不匹配：表中实际存储密码的列名是Pwd、UserPass、PassWord等其他命名，和代码中写的password拼写不一致
• 列名命中SQL保留字：如果列名确实叫password，该名称属于SQL Server保留关键字，查询时未做转义会被判定为语法元素而非列名
• 连接指向错误数据库：UserThomasConnectionString配置的数据源、初始目录和预期库不一致，连接到的库中RegisterFormTable表本身没有密码字段

修复方案 #

第一步：核对表结构修正列名 #

连接配置文件对应的数据库，执行如下SQL查询RegisterFormTable的实际列定义，找到存储密码的真实列名：

SELECT COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'RegisterFormTable'

• 如果实际列名不是password，直接把SQL语句里的password替换为真实列名即可
• 如果实际列名确实是password，查询时给列名加方括号转义，写法为select [password] from RegisterFormTable ...

第二步：修复代码的高危漏洞（必须处理） #

当前代码直接拼接用户输入到SQL语句，存在严重SQL注入风险，同时手动开关连接容易出现资源泄漏、空引用异常问题，建议直接替换为参数化查询+using自动释放资源的写法，参考如下：

protected void Button1Login_Click(object sender, EventArgs e)
{
    // using块会在代码执行结束后自动释放连接资源，无需手动调用Close
    using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["UserThomasConnectionString"].ConnectionString))
    {
        conn.Open();
        // 用户名校验用参数化查询，杜绝SQL注入
        const string checkUserSql = "select count(*) from RegisterFormTable where User_Name=@UserName";
        SqlCommand userCheckCmd = new SqlCommand(checkUserSql, conn);
        userCheckCmd.Parameters.AddWithValue("@UserName", TextBox1Username.Text.Trim());
        int userExist = Convert.ToInt32(userCheckCmd.ExecuteScalar());

        if (userExist != 1)
        {
            Response.Write("Username is not correct");
            return;
        }

        // 密码查询同样用参数化，注意替换为核对后的真实列名，这里保留[password]转义示例
        const string checkPassSql = "select [password] from RegisterFormTable where User_Name=@UserName";
        SqlCommand passCheckCmd = new SqlCommand(checkPassSql, conn);
        passCheckCmd.Parameters.AddWithValue("@UserName", TextBox1Username.Text.Trim());
        // 先判断查询结果是否为空，避免无结果时ToString()触发空引用异常
        object passResult = passCheckCmd.ExecuteScalar();
        if (passResult == null)
        {
            Response.Write("Username is not correct");
            return;
        }
        string dbPassword = passResult.ToString();
        dbPassword = string.Join("", dbPassword.Split(default(string[]), StringSplitOptions.RemoveEmptyEntries));

        if (dbPassword == TextBox2Password.Text)
        {
            Session["New"] = TextBox1Username.Text;
            Response.Write("Password is correct");
        }
        else
        {
            Response.Write("Password is not correct");
        }
    }
}

生产环境注意：禁止明文存储用户密码，必须对密码加盐做哈希处理后存储，登录时对比哈希值，避免数据库泄露后用户明文密码被盗。

内容的提问来源于stack exchange，提问作者Thomas Jowell Ypil