［Util］打擊 SQL Injection–Order By 篇
今天發現一支很有趣的程式
1: string sql = "SELECT * FROM CUST WHERE CUST_NAME Like @NAME";
2: if(!string.IsNullOrEmpty(sort))
3: sql = string.Concat(sql, "ORDER BY IP_ID");
4: // ...
5: command.Parameters.AddWithValue("@CUST_NAME", "%" + cust_name + "%" );
6:
第5行符合正規 SQL Injection 教戰手冊,
But 第3行好像讓一切努力付諸流水 (煙)
自己試了一下, 原來 Parameters 是不能用來做 Order By 傳遞的 (驚)
網路上有很多解法, 我這回實作的是Safe Order By, 引用 Regex,
將 sortExpression 送進一個filter的方法 SafeOrderBy( ),
而方法的核心就是這段 Regex
((,)?(\s)*\b(IP_ID|CUST_NAME)\b(\s)*(\b(ASC|DESC)(,)?\b)?)+
完整的內容如下:
public string SafeOrderBy(string sortExpression, params string[] allow)
{
StringBuilder result = new StringBuilder();
if (string.IsNullOrEmpty(sortExpression))
{
return string.Empty;
}
if (allow == null || allow.Count() == 0)
{
return string.Empty;
}
StringBuilder pattern = new StringBuilder(@"((,)?(\s)*\b(");
foreach (string s in allow)
{
pattern.Append(s).Append("|");
}
pattern.Remove(pattern.Length - 1, 1);
pattern.Append(@")\b(\s)*(\b(ASC|DESC)(,)?\b)?)+");
foreach (Match match in Regex.Matches(sortExpression, pattern.ToString()))
{
if (match.Success)
{
result.Append(match.Value);
}
}
return result.ToString();
}
套一句 91哥常說的 : 只允許朕指定的欄位，朕沒給的，你不能要 XD
單元測試 (充版面?!)
[TestMethod()]
public void TestSafeOrderBy()
{
SQL_CRM_CUST_AO_DAO target = new SQL_CRM_CUST_AO_DAO();
string expected = "IP_ID";
string actual = target.Util.SafeOrderBy("IP_ID", "IP_ID");
Assert.AreEqual(expected, actual);
expected = "";
actual = target.Util.SafeOrderBy("CUST_NAME", "IP_ID");
Assert.AreEqual(expected, actual);
expected = "IP_ID DESC";
actual = target.Util.SafeOrderBy("IP_ID DESC", "IP_ID", "CUST_NAME");
Assert.AreEqual(expected, actual);
expected = "IP_ID ASC";
actual = target.Util.SafeOrderBy("IP_ID ASC", "IP_ID", "CUST_NAME");
Assert.AreEqual(expected, actual);
expected = "";
actual = target.Util.SafeOrderBy("IP_IDX", "IP_ID");
Assert.AreEqual(expected, actual);
expected = "IP_ID ";
actual = target.Util.SafeOrderBy("IP_ID ASCX", "IP_ID");
Assert.AreEqual(expected, actual);
expected = "";
actual = target.Util.SafeOrderBy("XIP_ID", "IP_ID");
Assert.AreEqual(expected, actual);
expected = "IP_ID ";
actual = target.Util.SafeOrderBy("IP_ID XASC", "IP_ID");
Assert.AreEqual(expected, actual);
expected = "IP_ID , CUST_NAME";
actual = target.Util.SafeOrderBy("IP_ID , CUST_NAME", "IP_ID", "CUST_NAME");
Assert.AreEqual(expected, actual);
expected = "IP_ID , CUST_NAME";
actual = target.Util.SafeOrderBy("IP_ID , CUST_NAME, MOBILE_TEL", "IP_ID", "CUST_NAME");
Assert.AreEqual(expected, actual);
expected = "IP_ID,CUST_NAME";
actual = target.Util.SafeOrderBy("IP_ID,CUST_NAME", "IP_ID", "CUST_NAME");
Assert.AreEqual(expected, actual);
expected = "IP_ID DESC,CUST_NAME";
actual = target.Util.SafeOrderBy("IP_ID DESC,CUST_NAME", "IP_ID", "CUST_NAME");
Assert.AreEqual(expected, actual);
expected = "IP_ID DESC,CUST_NAME ASC";
actual = target.Util.SafeOrderBy("IP_ID DESC,CUST_NAME ASC", "IP_ID", "CUST_NAME");
Assert.AreEqual(expected, actual);
}