2019年7月21日 星期日

程式設計師應有的程式技能-進階SQL語法-後端資料庫

技能一、如何重新合併資料表欄位內的資料

T-SQL：如何將整欄資料合併

如果我有一個欄位資料如下：

DocNum

386
352
385
我想把DocNum整欄的資料(共3筆)合併變成一欄如下：
AllNumber
386,352,385
備註:要先把數值轉換成文字~才可以整併
declare @Tmp table(
 DocNum int
)

insert into @Tmp
values(386)
,(352)
,(385)

select STUFF(
 (
 select ',' + Convert(varchar,DocNum)
 from @Tmp
 for xml path('')
 )
,1,1,''
) as AllNumber
https://ithelp.ithome.com.tw/upload/images/20180620/20061369lLN0RPHPpn.png
【資料引用資訊】
google關鍵字:T-SQL：如何將整欄資料合併

技能二、比UNION ALL還好用的指令CTE (Common Table Expression)




2010-02-02

SQL - 使用 一般資料表運算式 CTE (Common Table Expression)


摘要:SQL - 使用 一般資料表運算式 CTE (Common Table Expression)
早期可以透過暫存資料表(# 或 ##)或者 table 來存放資料，現在也可以使用 一般資料表運算式 CTE (Common Table Expression) 來暫存資料。雖然 CTE 的查詢是使用 Select 查詢，但是依照 MSDN 的說明，仍有一些限制，那就是在 CTE 中不可以使用以下的子句：

1.COMPUTE 或 COMPUTE BY
2.ORDER BY (除非指定了 TOP 子句)
3.INTO
4.含有查詢提示的 OPTION 子句
5.FOR XML
6.FOR BROWSE

以下就來實作一些 CTE 的範例...

範例一：單一的 CTE 用法

WITH OrdersTable (訂單ID, 客戶ID, 員工ID) as
(
 Select OrderID, CustomerID, EmployeeID
 from dbo.Orders
)
Select *
From OrdersTable




範例二：兩個 CTE 作 Join

WITH OrdersTable (訂單ID, 客戶ID, 員工ID) as
(
 Select OrderID, CustomerID, EmployeeID
 from dbo.Orders
),
OrderDetailsTable as
(
 Select OrderID as '訂單ID', ProductID as '產品ID', UnitPrice as '價格'
 from dbo.[Order Details]
)
Select *
From OrdersTable A inner join OrderDetailsTable B
on A.訂單ID = B.訂單ID




範例三：CTE 遞迴 (參考：黑暗大的部落格文章)

資料清單


Code：

--如果CTE的WITH不在第一列, 前方要加上;
;WITH 公司組織結果(部門名稱, 直屬部門, 層級, 排序欄位)
AS
(
    --Recursive CTE分為兩個部分, 第一部分為Anchor Member
    --指不會被遞迴呼叫到的部分
    SELECT 部門名稱, 直屬部門, 0, CONVERT(nvarchar(128), 部門ID)
    FROM dbo.公司組織 WHERE 直屬部門=N'ROOT'

    UNION ALL

    --UNION ALL後方的部分稱為Recursive Member, 會在遞迴過程中反覆執行,
    --直到無任何查詢結果為止
    SELECT P.部門名稱, P.直屬部門, B.層級+1,
    CONVERT(nvarchar(128), B.排序欄位 + '-' + CONVERT(nvarchar(128), P.部門ID))
    FROM dbo.公司組織 P, 公司組織結果 B
    WHERE P.直屬部門=B.部門名稱
)
SELECT (REPLICATE('       ', 層級) + 部門名稱) as '部門名稱', 層級, 排序欄位
FROM 公司組織結果
ORDER BY 排序欄位




另一個 CTE 遞迴範例 (參考：Journey to SQL Authority with Pinal Dave)

Code：

USE AdventureWorks
GO
WITH Emp_CTE AS (
 --找出 ManagerID 為 NULL 的資料當作 Anchor Member
 SELECT EmployeeID, ContactID, LoginID, ManagerID, Title, BirthDate
 FROM HumanResources.Employee
 WHERE ManagerID IS NULL

 UNION ALL

 --之後以 Anchor Member 為依據遞迴查詢
 SELECT e.EmployeeID, e.ContactID, e.LoginID, e.ManagerID, e.Title, e.BirthDate
 FROM HumanResources.Employee e
 INNER JOIN Emp_CTE ecte ON ecte.EmployeeID = e.ManagerID
)
SELECT *
FROM Emp_CTE
GO



參考：
SQL 2005 T-SQL Enhancement: Common Table Expression
SQL SERVER – Simple Example of Recursive CTE
WITH common_table_expression (Transact-SQL)
利用CTE將分頁資料取出-Stored Procedure篇
【資料引用資訊】
google關鍵字:SQL - 使用 一般資料表運算式 CTE (Common Table Expression)

技能三、代替JOIN的語法CROSS APPLY和OUTER APPLY 

14. CROSS APPLY 與 OUTER APPLY

2018鐵人賽 tsql sqlserver

8136 瀏覽

前言

第一次看到 cross apply 與 outer apply 的時候是在 code review 的時候，充滿陌生感，查了一些資料隱約猜到其行為。第二次因為偵錯修改程式時又遇到相同的語法，但自己卻忘了一乾二淨，又得重新查資料。故趁這次鐵人賽好好學習這兩個語法。

介紹

APPLY 主要用於將某一個子查詢合一個資料表函式做 JOIN 運算，有別於 JOIN 運算，APPLY 不使用 ON 選擇關聯欄位，則是將欄位帶入方式使用。子查詢結果會被當作為 left ，而資料表函式當作 right 進行運算。
SELECT A.column1, ... ,B.column1, ...
   FROM TableA AS A
CROSS APPLY TableFunction (A.Id) AS TF
CROSS APPLY 和 OUTER APPLY 是從 SQL Server 2005 新增的語法，其使用時資料庫相容層級要90。這兩個語法效果等同於 INNER JOIN 與 LEFT OUTER JOIN。
這邊舉一個例子來看看 (範例有一點差，但我們直接看效果)
Employee 資料表內容如下
IdNameCountryName
1DuranKaohsiung
2OwenTaipei
3MazeTaichung
4BertTaipei
Dept 資料表內容如下：
IdNameCountryName
1PMTaipei
2DEVTaichung
3DESIGNTapei
4DEVTaipei
5SUPPORTTaichung
6SUPPORTTainan
7ITKaohsiung
首先我們先建立資料表函式
    CREATE FUNCTION GetDept(@CountryName varchar(25))
    RETURNS TABLE
    RETURN
    (
        SELECT Id, Name, CountryName
          FROM Dept
         WHERE CountryName = @CountryName
    )
我們使用 CROSS APPLY
         SELECT E.Id, E.Name, E.CountryName, TD.Name, E.CountryName
           FROM EMPLOYEE AS E
    CROSS APPLY GetDept(E.CountryName) TD
接下來我們使用 OUTER APPLY
        SELECT E.Id, E.Name, E.CountryName, TD.Name, E.CountryName
           FROM EMPLOYEE AS E
    OUTER APPLY GetDept(E.CountryName) TD

參考資料

【資料引用資訊】
google關鍵字:CROSS APPLY 與 OUTER APPLY

技能四、資料庫的資訊安全

[Day04] 資料儲存安全(SQL加密函數)

2017鐵人賽 sql server

7416 瀏覽
昨天我們開始戍衛京師周邊的任務，先試著用雜湊演算法(hash)將欄位PAN(Primary account number)賦予不可讀性(unreadable)及不可逆性(not reversible)，但如果想要資料是可逆(reversible)的，也就是說有一天想轉回原來的資料時，這個時候就很適合套加密演算法(encrypt)替資料加密。

加密演算法(Encrypt Algorithm)

先淺淺的認識Encrypt algorithm，加密演算法是用1~N把金鑰(key)作為事先約定好的參數，將明文透過取代、置換及數學運算流程產生成密文，加密後的密文，也可以依照加密時選擇的金鑰(key)解密成明文。
我們來試試SQL Server中的加密函數。

簡單加密函數

如果想要單純使用金鑰加密，在T-SQL中內建了一種加密函數(EncryptByPassPhrase)，她是使用Triple DES(TDEA) 演算法，也許可以符合我們的需求。
DECLARE @TDEAKey nvarchar(128);  
SET @TDEAKey = '0123456789ABCDEF';  
--加密  
DECLARE @ciphertext varbinary(max)
= EncryptByPassPhrase(@TDEAKey, '3123456789012345')  
SELECT @ciphertext
--解密
DECLARE @plainttext varchar(16) 
= CONVERT(varchar(16),DecryptByPassphrase(@TDEAKey, @ciphertext))  
SELECT @plainttext
執行結果:
http://ithelp.ithome.com.tw/upload/images/20161219/20103434QRLZbrl2Hf.png
最大的缺點就是金鑰不容易隱藏，很容易在T-SQL被野生捕獲。
SQL Server還有另一種把加密金鑰儲存在憑證或透過其他金鑰保護的作法，我們來試試憑證這種。

建立Master KEY、建立憑證、建立資料加密金鑰

在進行加密之前，我們得先在資料庫中建立加密金鑰、儲存加密金鑰的憑證以及DMK(Database Master key)。

1.建立MASTER KEY

CREATE MASTER KEY  
ENCRYPTION BY PASSWORD = 'imMKpassword1' 
GO

2.建立憑證

CREATE CERTIFICATE [Cert]  
WITH SUBJECT = '測試憑證' 
GO

3.建立金鑰

CREATE SYMMETRIC KEY AES256key 
WITH ALGORITHM = AES_256 ENCRYPTION 
BY CERTIFICATE [Cert]  
GO

加密資料

--1.開啟憑證、金鑰 
OPEN SYMMETRIC KEY AES256key DECRYPTION BY CERTIFICATE [Cert] 
GO
--2.欄位加密 
INSERT INTO cardholder 
VALUES
((EncryptByKey(Key_GUID('AES256key'),'4123456789012345')),N'大衛王','101','1299'), 
((EncryptByKey(Key_GUID('AES256key'),'4223456789012346')),N'查理曼','101','1299') 
GO
--3.關閉金鑰 
CLOSE ALL SYMMETRIC KEYS 
GO
觀察寫入資料表結果:
SELECT * FROM cardholder 
GO
http://ithelp.ithome.com.tw/upload/images/20161219/20103434hn3cgsXbqH.png
加密和雜湊後的資料都是不可讀(unreadable)的狀態。

加密結果

加密函數預設是採動態初始向量(IV)的方式，同一個明文加密後可能有不同結果，我們來實際驗證看看。
OPEN SYMMETRIC KEY AES256key DECRYPTION BY CERTIFICATE [Cert] 
GO 
SELECT EncryptByKey(Key_GUID('AES256key'),'4223456789012346') 
SELECT EncryptByKey(Key_GUID('AES256key'),'4223456789012346')
http://ithelp.ithome.com.tw/upload/images/20161219/201034345LemnY4QC7.jpg
果然不同!
如果需要查詢資料，看起來必須將加密欄位解密後再來和條件比對。

查詢加密資料

--1.開啟憑證、金鑰  
OPEN SYMMETRIC KEY AES256key DECRYPTION BY CERTIFICATE [Cert] 
--查詢不到
SELECT * FROM cardholder 
where pan = CONVERT(VARBINARY(MAX),EncryptByKey(Key_GUID('AES256key'),'4223456789012346')) 

--查詢的到  
select * from cardholder 
WHERE CONVERT(varchar,DecryptByKey(pan)) = '4223456789012346'
http://ithelp.ithome.com.tw/upload/images/20161219/20103434Gm7iNmJvPj.png
第一段把條件加密後進資料庫查詢不到資料，
第二段則把資料解密後和條件比較則可以正確查詢到第二筆資料!
msdn說明:
加密演算法會定義資料轉換，讓未經授權的使用者無法輕鬆地反轉資料轉換。 SQL Server 可讓管理員和開發人員在數種演算法中進行選擇，包括 DES、Triple DES、TRIPLE_DES_3KEY、RC2、RC4、128 位元 RC4、DESX、128 位元 AES、192 位元 AES 和 256 位元 AES。
小結:
  • SQL Server 2016之後，因為其他演算法已經陸續被破解，AES之外的演算法都不建議使用。
  • 可以針對加密金鑰設定使用權限。
  • 嗯，在效能上把資料先解密再來和條件比對似乎不是很理想。

風險

雖然我們把資料成功加解密了，但加密所使用的金鑰和加密後的資料都放在同一台機器(DB Server)，就像金庫的密碼和金鑰沒有分人保管，難免有球員兼裁判 之嫌，此時獨立安全的Key management更顯得重要。
其他的解決方案:
  • EKM(Extensible Key Management) HSM?
  • SQL Server 2016 Always always encrypted
有關 PA DSS Requirement 2:Protect stored cardholder data，在章節 2.3對於PAN的說明。
Render PAN unreadable anywhere it is stored (including data on portable digital media, backup media, and in logs) by using any of the following approaches:
• One - way hashes based on strong cryptography(hash must be of the entire PAN)
• Truncation(hashing cannot be used to replace the truncated segment of PAN)
• Index tokens and pads (pads must be securely stored)
• Strong cryptography with associated key - management processes and procedures.
Notes:
• It is a relatively trivial effort for a malicious individual to reconstruct original PAN data if they have access to both the truncated and hashed version of a PAN. Where hashed and truncated versions of the same PAN are generated by a payment application, additional controls should be in place to ensure that hashed and truncated versions cannot be correlated to reconstruct the original PAN.
• The PAN must be rendered unreadable anywhere it is stored, even outside the payment application(for example, log files output by the application for storage in the merchant environment)

參考:

【資料引用資訊】
google關鍵字:[Day04] 資料儲存安全(SQL加密函數)
張貼者： 於

沒有留言:

張貼留言

訂閱： 張貼留言 (Atom)