MS SQL專案 產生 script後使用 sqlcmd執行

利用 SQL專案的結構描述比較功能，在比較並產生指令碼後，產出的結果無法直接在 SSMS上直接執行
大概是長這個樣子

/*
TestDB 的部署指令碼

這段程式由工具產生。
...
*/
...
:setvar DatabaseName "TestDB"
:setvar DefaultFilePrefix "TestDB"
...

/* 偵測 SQLCMD 模式，如果不支援 SQLCMD 模式，則停用指令碼執行。
...
*/
...

使用 sqlcmd執行此 script的方式：
開啟 cmd，輸入下方指令後再按 Enter

sqlcmd -S TestDB -U userId -P password -i C:\TestDB_Update.publish.sql -o C:\output.txt

rework SQL Server

.NET SqlParameter(string paraName, object value) 字串 NVarchar

最近程式再被調教中
收到一封 mail說我們的 SQL script都是用 nvarchar，效能較差
要我們改成 varchar
查了一下是因為底層的 function寫得太便利，大家都是丟 object[] 進去直接賦值 大概是像這樣子

SqlParameter sqlPara = new SqlParameter("@" + temp[0], paraValues[i]);

如果 value是 int、dobule、datetime等都沒問題，產生出來的 SqlParameter內的屬性 SqlDbType會自動轉成對應的類別
但如果是字串，不管是 string、String還是 char，一律會被轉成 nvarchar
問題就來了


因為 table schema是放 varchar，query script的 where條件被轉成 nvarchar，就會導致效能變差~

Sargable

http://en.wikipedia.org/wiki/Sargable

SQL TRUNCATE TABLE [...]

跟 DELETE一樣  刪除資料

無法設 WHERE條件, 整張表清空
但是不保留紀錄、自動編號重設

有被 FOREIGN KEY條件限制時, 不可使用 

SqlBulkCopy

using (SqlConnection Bulkconn = new SqlConnection(conn.ConnectionString))
{
    Bulkconn.Open();

    using (SqlBulkCopy sqlBC = new SqlBulkCopy(Bulkconn))
    {
        sqlBC.DestinationTableName = "dbo." + dt.TableName;

        foreach (DataColumn dc in dt.Columns)
        {
            sqlBC.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);
        }

        sqlBC.WriteToServer(dt);
    }
}

SQL inner join v.s. exist

exist 是 left去比對 right, 找到符合的就回傳 (true)
inner join是兩邊全部 join後, 再去找符合的

#.exist在處理上遇到 null值可能會有問題

Security 資訊安全 1. SQL Injection

SQL Injection是一種設計邏輯上的疏失造成的系統漏洞，不管用什麼程式語言、資料庫，都可能會中獎。
一個不小心，就足以讓整個系統門戶大開，赤裸裸的給人看光。

舉一個簡易的例子：

string selectCommand = 
@"SELECT UserName FROM UserAccount WHERE UserID='" + Request("UID") + "' AND UserPWD='" + Request("PWD") + "'";

如果輸入 UID為 ' or 1=1 --
這樣上方例子中的 SQL指令變成：

SELECT UserName FROM UserAccount WHERE UserID='' or 1=1 -- ' AND UserPWD=''";

就可以直接登入

如果管理者帳號為 admin，輸入 UID為 admin'--

SELECT UserName FROM UserAccount WHERE UserID='admin'-- ' AND UserPWD=''";

瞬間就可以以管理員權限登入，想做什麼就做什麼!!

再衍生下去則可以利用錯誤訊息獲得管理者權，停掉資料庫、刪除資料或卸除資料庫等，甚至取得伺服器主機管理權限等，不可不防。


另外，辛辛苦苦完成的網站上線後，若沒有做自訂錯誤頁面，直接暴露出所有 error、exception訊息，則使用者也可以利用錯誤訊息，摸透資料庫的內容，接著幹出一堆很邪惡很邪惡的事。
(詳細步驟可看 SQL Injection (資料隱碼)– 駭客的 SQL填空遊戲(上))


重點!!
1. 過濾使用者輸入的資料。
2. 不要直接以字串串接組合出 SQL指令。
3. 不要把錯誤訊息顯示給使用者。


解決方式：
ASP .NET

• 使用 Command物件或 SqlCommand類別，透過參數執行 SQL 語法。
• 正式環境中，將 web.config內的 customErrors設為 "On"。
• 在伺服器端檢查與限制輸入變數的型別與長度，過濾掉不需要的內容，不要完全信任使用者輸入的內容。
• 登入 SQL Server的帳號不使用 sa或任何屬於 Sysadmin群組的帳號，避免有過大的權限。

參考資料：
網站
    SQL Injection (資料隱碼)– 駭客的 SQL填空遊戲(上) (下)
    『資料隱碼』SQL Injection的源由與防範之道
    SQL Injection 緊急應變建議
PDF
    ASP .NET 防駭指南
    你的網站在裸奔嗎? 一個SQL Injection實例的啟示

SQL Injection

今天在跟組長做面試練習時，組長問了怎麼防止 SQL Injection

我直接回答：不知道什麼是 SQL Injection

所以 組長 就簡單的解釋了一下 SQL Injection的做法

我頓時：哦 ~

然後在面試練習結束後，上網查了一下 SQL Injection
看到黑暗執行緒的 你的網站正在裸奔嗎? (裡面有個 pdf連結，下載來看)
才更清楚的了解了 SQL Injection的嚴重性

還好當初專案開發時就有被告知盡量不要用字串串接做 SQL查詢句