有同事問到，如果需要以 in 的 sql 語法查詢時，要如何防止Sql Injection？
問題需要一步步的解決。首先寫個範例，是查詢在 AdventureWorks 的資料表是否有指定的資料表名稱。程式如下。
範例一
1: class Program
2: {
3: static void Main(string[] args)
4: {
5: Database db = DatabaseFactory.CreateDatabase();
6:
7: string query = "select name, object_id from sys.tables where name in ('Store','ProductPhoto','ProductProductPhoto','StoreContact')";
8: var tables = db.ExecuteSqlStringAccessor<Table>(query);
9: foreach (var table in tables)
10: Console.WriteLine("{0}:{1}", table.Object_Id, table.Name);
11: }
12: }
13:
14: class Table
15: {
16: public int Object_Id { get; set; }
17: public string Name { get; set; }
18: }
範例一中使用了 Enterprise Library 5 中的 DAAB 來讀取資料，並將符合條件的資料表的 name , object_id 列表出來。
現在呢？需要將查詢的資料表名稱參數化，作成一個 Method，方便 UI 呼叫。
範例二
1: static void Main(string[] args)
2: {
3:
4: string tableNames = "'Store','ProductPhoto','ProductProductPhoto','StoreContact'";
5: var tables = GetTables(tableNames);
6: foreach (var table in tables)
7: Console.WriteLine("{0}:{1}", table.Object_Id, table.Name);
8: }
9:
10: private static IEnumerable<Table> GetTables(string strTableNames)
11: {
12: Database db = DatabaseFactory.CreateDatabase();
13:
14: string query = "select name, object_id from sys.tables where name in (" + strTableNames + ")";
15: var tables = db.ExecuteSqlStringAccessor<Table>(query);
16: return tables;
17: }
18:
19: class Table
20: {
21: public int Object_Id { get; set; }
22: public string Name { get; set; }
23: }
要查詢的資料表名稱以符合 sql 語法查詢的方式GetTables 方法。GetTables 的實作中，直接以組字串的方式組成了 sql script。這樣寫，執行起來並沒有錯誤。卻是標準的Sql Injection 入侵範例。尤其是呼叫 GetTables 的 Layer 是網頁時，就更需要擔心了。
我們改成寫下面的範例三。
範例三
1: class Program
2: {
3: static void Main(string[] args)
4: {
5: string[] tableNames = { "Store","ProductPhoto","ProductProductPhoto","StoreContact"};
6: var tables = GetTables(tableNames);
7: foreach (var table in tables)
8: Console.WriteLine("{0}:{1}", table.Object_Id, table.Name);
9: }
10:
11: private static IEnumerable<Table> GetTables(string[] tableNames)
12: {
13: Database db = DatabaseFactory.CreateDatabase();
14:
15: List<string> tableName = (from t in tableNames
16: select string.Format("'{0}'", t)).ToList();
17: string strTableNames = string.Join(",", tableName.ToArray());
18: string query = "select name, object_id from sys.tables where name in (" + strTableNames + ")";
19: var tables = db.ExecuteSqlStringAccessor<Table>(query);
20: return tables;
21: }
22: }
23:
24: class Table
25: {
26: public int Object_Id { get; set; }
27: public string Name { get; set; }
28: }
要查詢的資料表名稱以陣列的方式傳入 GetTables 方法。GetTables 的實作中，仍然以組字串的方式組成了 'Store,ProductPhoto,ProductProductPhoto,StoreContact' 的字串，再以 in 的sql 查詢語法來下 sql script.
這樣寫，執行起來並沒有錯誤。Sql Injection 的可能性也不高，因為 tableNames 的字串陣列需要被重組。
但，仍是是組字串的方式來組合 sql script，容易被當成 sql injection 的攻擊漏洞，CAT.NET 還是會檢查出來的。
範例四
1: class Program
2: {
3: static void Main(string[] args)
4: {
5: string[] tableNames = { "Store", "ProductPhoto", "ProductProductPhoto", "StoreContact" };
6: var tables = GetTables(tableNames);
7: foreach (var table in tables)
8: Console.WriteLine("{0}:{1}", table.Object_Id, table.Name);
9: }
10:
11: private static IEnumerable<Table> GetTables(string[] tableNames)
12: {
13: Database db = DatabaseFactory.CreateDatabase();
14:
15: string strTableNames = string.Join(",", tableNames.ToArray());
16: string query = "select name, object_id from sys.tables where name in ( select value from dbo.fn_split(@strTableNames, ','))";
17:
18: IRowMapper<Table> rowMapper = MapBuilder<Table>.BuildAllProperties();
19: var accessor = new SqlStringAccessor<Table>(db, query, new MyParameterMapper(), rowMapper);
20: var tables = accessor.Execute(strTableNames);
21: return tables;
22: }
23:
24: }
25:
26: class Table
27: {
28: public int Object_Id { get; set; }
29: public string Name { get; set; }
30: }
31:
32: public class MyParameterMapper : IParameterMapper
33: {
34: public void AssignParameters(DbCommand command, object[] parameterValues)
35: {
36: DbParameter parameter = command.CreateParameter();
37: parameter.ParameterName = "@strTableNames";
38: parameter.Value = parameterValues[0];
39: command.Parameters.Add(parameter);
40: }
41: }
而 dbo.fn_split 列在下面
1: if exists (select * from dbo.sysobjects where id = OBJECT_ID(N'[dbo].[fn_Split]') and xtype in (N'FN', N'IF', N'TF'))
2: drop function [dbo].[fn_Split]
3: GO
4:
5: SET QUOTED_IDENTIFIER OFF
6: GO
7:
8: SET ANSI_NULLS OFF
9: GO
10:
11: CREATE FUNCTION fn_Split(@text varchar(8000), @delimiter varchar(20) = ' ')
12: RETURNS @Strings TABLE
13: (
14: position int IDENTITY PRIMARY KEY,
15: value varchar(8000)
16: )
17: AS
18: BEGIN
19: DECLARE @index int
20: SET @index = -1
21:
22: WHILE (LEN(@text) > 0)
23: BEGIN
24: SET @index = CHARINDEX(@delimiter , @text)
25: IF (@index = 0) AND (LEN(@text) > 0)
26: BEGIN
27: INSERT INTO @Strings VALUES (@text)
28: BREAK
29: END
30:
31: IF (@index > 1)
32: BEGIN
33: INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))
34: SET @text = RIGHT(@text, (LEN(@text) - @index))
35: END
36: ELSE
37: SET @text = RIGHT(@text, (LEN(@text) - @index))
38: END
39: RETURN
40: END
41: GO
42:
43: SET QUOTED_IDENTIFIER OFF
44: GO
45:
46: SET ANSI_NULLS ON
47: GO
這樣的方法就相當長了。但卻也相當好用。尤其是 fn_split 可用來拆解字串成為新的 table 並在 sql 中使用。
SqlStringAccessor 是 Enterprise Library 5 中 DAAB 的新成員，目的是建立出新的 DTO。
沒有留言:
張貼留言