Dapper 防 sql 注入，同一条 SQL 支持多种数据库 -

反骨仔

2023-05-13 2023-05-13 约 563 字预计阅读 2 分钟 - 次阅读

前言

防 SQL 注入，常用的方案是使用 Dapper 执行 SQL 的参数化查询。例如：

1
2
3
4
5
6
7


using (IDbConnection conn = CreateConnection())
{
    string sqlCommandText = @"SELECT * FROM USERS WHERE ID=@ID";

    Users user = conn.Query<Users>(sqlCommandText, new { ID = 2 }).FirstOrDefault();
    Console.WriteLine(user.Name);
}

但是，不同数据库支持不同的 sql 参数格式，例如，ORACLE 必须使用 :ID，否则上述代码会报错：

Pseudo-Positional Parameters

查看 Dapper 的源代码，发现有这样一段：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33


cmd.CommandText = pseudoPositional.Replace(cmd.CommandText, match =>
{
    string key = match.Groups[1].Value;
    
    if (!consumed.Add(key))
    {
        throw new InvalidOperationException("When passing parameters by position, each parameter can only be referenced once");
    }
    else if (parameters.TryGetValue(key, out IDbDataParameter param))
    {
        if (firstMatch)
        {
            firstMatch = false;
            cmd.Parameters.Clear(); // only clear if we are pretty positive that we've found this pattern successfully
        }
        
        // if found, return the anonymous token "?"
        if (Settings.UseIncrementalPseudoPositionalParameterNames)
        {
            param.ParameterName = (++index).ToString();
        }
        
        cmd.Parameters.Add(param);
        parameters.Remove(key);
        consumed.Add(key);
        return "?";
    }
    else
    {
        // otherwise, leave alone for simple debugging
        return match.Value;
    }
});

通过查看 Dapper 教程，原来，这是实现被称为 Pseudo-Positional Parameters（伪位置参数）的代码，作用是为了不支持命名参数的数据库提供者能够使用参数化 SQL，例如 OleDB：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


//代码
var docs = conn.Query<Document>(@"
     select * from Documents
     where Region = ?region?
     and OwnerId in ?users?", new { region, users }).AsList();

//SQL
select * from Documents
     where Region = ?
     and OwnerId in (?,?,?)

自定义 Pseudo-Positional Parameters

可以实现自己的 Pseudo-Positional Parameters，以便支持更多的数据提供者：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13


private static readonly Regex pseudoRegex = new Regex(@"\$([\p{L}_][\p{L}\p{N}_]*)\$", RegexOptions.IgnoreCase | RegexOptions.CultureInvariant | RegexOptions.Compiled);

public static string ReplacePseudoParameter(IDbConnection conn, string cmdText)
{
    return pseudoRegex.Replace(cmdText, match => {
        var key = match.Groups[1].Value;
        if (conn is OleDbConnection)
        {
            return "?" + key + "?";
        }
        return (conn is OracleConnection ? ":" : "@") + key;
    });
}

使用方式是在参数名称两边加上 $:

1
2
3


string sqlCommandText = @"SELECT * FROM USERS WHERE ID=$ID$";

Users user = conn.Query<Users>(ReplacePseudoParameter(conn, sqlCommandText), new { ID = 2 }).FirstOrDefault();

结论

通过实现 Pseudo-Positional Parameters 功能，我们让 Dapper 防 sql 注入支持了多种数据库。

参考资料

[1] 源代码: https://github.com/DapperLib/Dapper/blob/main/Dapper/SqlMapper.cs#L1783 [2] Dapper 教程: https://riptutorial.com/Dapper/example/13835/pseudo-positional-parameters--for-providers-that-don-t-support-named-parameters-

赞赏