2010年5月12日 星期三

如何使用 In 的查詢語法,而且防止 Sql Injection

有同事問到,如果需要以 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。

沒有留言:

Share with Facebook