Q.yuhen

一个合格的程序员，应该有很强的安全意识。尤其是数据库相关的编程中，我们要时刻清醒地认识到 "SQL Injection" 危害的严重性。就算你用了 ORM，用了 Entity Framework，依然要绷紧这根神经。

下面的代码相信很多人都曾写过(自觉点，说的就是你，呵呵)，自然也可能带到 EF 当中来。

static bool Validate(string username, string password)
{
    using (var context = new TestEntities())
    {
        var esql = "select value u from TestEntities.Users as u where u.UserName = '" + username + 
            "' and u.Password='" + password + "'";

        var query = context.CreateQuery<User>(esql);
        Console.WriteLine(query.ToTraceString());

        return query.Count() > 0;
    }
}

该方法用于验证登录用户名和密码，粗看上去似乎没啥问题。真的是这样吗？看看它生成的 T-SQL。

SELECT
[Extent1].[Id] AS [Id],
[Extent1].[Version] AS [Version],
[Extent1].[Username] AS [Username],
[Extent1].[Password] AS [Password]
FROM [dbo].[User] AS [Extent1]
WHERE ([Extent1].[Username] = 'user1') AND ([Extent1].[Password] = 'password')

非参数执行方式，很显然，这是所有 "菜鸟黑客" 的最爱，也是最没有技术含量的攻击方式。

攻击者可以将密码写成下面这样。

abc' or '1'='1

再看看执行结果。

SELECT
[Extent1].[Id] AS [Id],
[Extent1].[Version] AS [Version],
[Extent1].[Username] AS [Username],
[Extent1].[Password] AS [Password]
FROM [dbo].[User] AS [Extent1]
WHERE (([Extent1].[Username] = 'user1') AND ([Extent1].[Password] = 'abc')) OR ('1' = '1')

这意味着攻击者可以用 "任意密码" 进行登录，假如不是 "user1" 而是 "admin"，那么后果就相当可怕了。

下面这个例子来源于我处理过的真实案例，不过我将其转换成 Entity SQL 来说事而已。这位老兄的代码貌似很精明，还增加了 u.UserName != 'admin' 判断，但实际效果是零，或者说是自作聪明。

static void ChangePassword(string username, string oldPassword, string password)
{
    using (var context = new TestEntities())
    {
        var esql = "select value u from TestEntities.Users as u where u.UserName != 'admin' and u.UserName = '" + 
            username + "' and u.Password='" + oldPassword + "'";

        var query = context.CreateQuery<User>(esql);
        Console.WriteLine(query.ToTraceString());

        var user = query.FirstOrDefault();

        if (user != null)
        {
            user.Password = password;
            context.SaveChanges();
        }
    }
}

先看看一个正常的修改密码 T-SQL。

SELECT
[Extent1].[Id] AS [Id],
[Extent1].[Version] AS [Version],
[Extent1].[Username] AS [Username],
[Extent1].[Password] AS [Password]
FROM [dbo].[User] AS [Extent1]
WHERE ([Extent1].[Username] <> 'admin') AND 
    ([Extent1].[Username] = 'user1') AND 
    ([Extent1].[Password] = '123456')

接下来，某位前离职程序员因为某种原因，决定开个 "玩笑"，他输入了如下信息。

Username: user1
OldPassword: xxx' or u.Username = 'admin
NewPassword: abcdefg

于是，悲哀的事情发生了，被改的是管理员(Admin)的密码。拿到管理员密码后，接下来要做什么就不是本文的探讨范畴了。

SELECT
[Extent1].[Id] AS [Id],
[Extent1].[Version] AS [Version],
[Extent1].[Username] AS [Username],
[Extent1].[Password] AS [Password]
FROM [dbo].[User] AS [Extent1]
WHERE (([Extent1].[Username] <> 'admin') AND ([Extent1].[Username] = 'user1') AND 
    ([Extent1].[Password] = 'xxx')) 
    OR ([Extent1].[Username] = 'admin')

要避免这类危害，最有效的方法还是用 "参数"。

static bool Validate(string username, string password)
{
    using (var context = new TestEntities())
    {
        var esql = @"select value u from TestEntities.Users as u 
            where u.UserName = @username and u.Password=@password";

        var query = context.CreateQuery<User>(esql, 
            new ObjectParameter("username", username), new ObjectParameter("password", password));

        Console.WriteLine(query.ToTraceString());

        return query.Count() > 0;
    }
}

static void ChangePassword(string username, string oldPassword, string password)
{
    using (var context = new TestEntities())
    {
        var esql = @"select value u from TestEntities.Users as u where u.UserName != 'admin' and 
            u.UserName = @username and u.Password=@password";

        var query = context.CreateQuery<User>(esql,
            new ObjectParameter("username", username), new ObjectParameter("password", oldPassword));

        Console.WriteLine(query.ToTraceString());

        var user = query.FirstOrDefault();
        if (user != null)
        {
            user.Password = password;
            context.SaveChanges();
        }
    }
}

static void Main(string[] args)
{
    using (var context = new TestEntities())
    {
        Console.WriteLine(Validate("user1", "abc' or '1'='1"));
        ChangePassword("user1", "xxx' or u.Username = 'admin", "abcdefg");
    }

输出:

SELECT
[Extent1].[Id] AS [Id],
[Extent1].[Version] AS [Version],
[Extent1].[Username] AS [Username],
[Extent1].[Password] AS [Password]
FROM [dbo].[User] AS [Extent1]
WHERE ([Extent1].[Username] = @username) AND ([Extent1].[Password] = @password)

False

SELECT
[Extent1].[Id] AS [Id],
[Extent1].[Version] AS [Version],
[Extent1].[Username] AS [Username],
[Extent1].[Password] AS [Password]
FROM [dbo].[User] AS [Extent1]
WHERE ([Extent1].[Username] <> 'admin') AND ([Extent1].[Username] = @username) AND 
    ([Extent1].[Password] = @password)

同样的攻击方式，但这次失效了。显然，所有 "SQL Injection" 都是针对那些有 "坏习惯" 的家伙。

LINQ to Entities 默认就使用参数方式。

static bool Validate(string username, string password)
{
    using (var context = new TestEntities())
    {
        var query = from u in context.Users 
            where u.Username == username && u.Password == password 
            select u;

        Console.WriteLine((query as ObjectQuery).ToTraceString());

        return query.Count() > 0;
    }
}

输出:

SELECT
[Extent1].[Id] AS [Id],
[Extent1].[Version] AS [Version],
[Extent1].[Username] AS [Username],
[Extent1].[Password] AS [Password]
FROM [dbo].[User] AS [Extent1]
WHERE ([Extent1].[Username] = @p__linq__1) AND ([Extent1].[Password] = @p__linq__2)

哦，别忘了在 EntityClient EntityCommand.CommandText 中也不要用拼接字符串的方式构建 Entity SQL。