LINE Pay款流程： 
reserve Page(紅科) 發送 reserve API => 取得 payment URL => 轉址到 payment URL => LINE Pay 檢查USER狀態 => 轉回confirm page(紅科), 發送 confirm API => DOWN.

付款需要程式： 
reserve API, confirm API:

send request JSON and read return JSON:
C#: 

using System.Net;

using System.IO;



public partial class TEST : System.Web.UI.Page

{

    DB.OrderMain oOrderObj = null;

    protected void Page_Load(object sender, EventArgs e)

    {

        //Setting request header

        HttpWebRequest httpWebRequest = (HttpWebRequest)WebRequest.Create("__APIRootURL__");

        httpWebRequest.ContentType = "application/json; charset=UTF-8";

        httpWebRequest.Method = "POST";

        httpWebRequest.Headers.Add("X-LINE-ChannelId", DB.SysConfig.LINEPay.ChannelId);

        httpWebRequest.Headers.Add("X-LINE-ChannelSecret", DB.SysConfig.LINEPay.SecretKey);



        //加入參數

        using (var streamWriter = new StreamWriter(httpWebRequest.GetRequestStream()))

        {

            string requestJSON = "{\"productName\": \"" + DB.SysConfig.SYSTEM_NAME + "\"," +

                                "\"productImageUrl\": \"" + DB.SysConfig.URL_Shopping + "images/Logo.jpg\"," +

                                "\"amount\": " + oOrderObj.TotalAmount() + "," +

                                "\"currency\": \"TWD\"," +

                                "\"orderId\": \"" + oOrderObj.DisplayOrderId + "\"," +

                                "\"confirmUrl\": \"" + DB.SysConfig.URL_Shopping + "Handler/LinePay/GotoComfirm.aspx?Id=" + oOrderObj.Id + "\"," +

                                "\"cancelUrl\": \"" + DB.SysConfig.URL_Shopping + "Shopping/OrderComplete.aspx?Id=" + oOrderObj.Id + "\"," +

                                "\"capture\": \"true\"," +

                                "\"confirmUrlType\": \"CLIENT\"}";



            streamWriter.Write(requestJSON);

            streamWriter.Flush();

            streamWriter.Close();

        }



        //取得回覆資訊

        var httpResponse = (HttpWebResponse)httpWebRequest.GetResponse();

        //解讀回覆資訊

        using (var streamReader = new StreamReader(httpResponse.GetResponseStream()))

        {

            var responseJSON = streamReader.ReadToEnd();

            //將 JSON 轉為物件

            GN.LinePay.reserveRes.ReturnJSON oReturnObj = (GN.LinePay.reserveRes.ReturnJSON)Newtonsoft.Json.JsonConvert.DeserializeObject(responseJSON, typeof(GN.LinePay.reserveRes.ReturnJSON));



            if (oReturnObj.returnCode == "0000")

            {

                //成功

            }

            else

            {

                //失敗

                string ErrMsg = "Error Code: " + oReturnObj.returnCode + "\r\n" + oReturnObj.returnMessage;

            }

        }

    }

}

.vb

Imports System.Net

Imports System.IO



Partial Class TEST

    Inherits System.Web.UI.Page



    Dim oOrderObj As DB.OrderMain

    Private Sub Admin_TEST_Load(sender As Object, e As EventArgs) Handles Me.Load

        'Setting request header

        Dim httpWebRequest As HttpWebRequest = WebRequest.Create("__APIRootURL__")

        httpWebRequest.ContentType = "application/json; charset=UTF-8"

        httpWebRequest.Method = "POST"

        httpWebRequest.Headers.Add("X-LINE-ChannelId", DB.SysConfig.LINEPay.ChannelId)

        httpWebRequest.Headers.Add("X-LINE-ChannelSecret", DB.SysConfig.LINEPay.SecretKey)

        '加入參數

        Using streamWriter As New StreamWriter(httpWebRequest.GetRequestStream())

            Dim requestJSON As String = "{""productName"": """ + DB.SysConfig.SYSTEM_NAME + """," +

                                        """productImageUrl"": """ + DB.SysConfig.URL_Shopping + "images/Logo.jpg""," +

                                        """amount"": " + oOrderObj.TotalAmount() + "," +

                                        """currency"": ""TWD""," +

                                        """orderId"": """ + oOrderObj.DisplayOrderId + """," +

                                        """confirmUrl"": """ + DB.SysConfig.URL_Shopping + "Handler/LinePay/GotoComfirm.aspx?Id=" + oOrderObj.Id + """," +

                                        """cancelUrl"": """ + DB.SysConfig.URL_Shopping + "Shopping/OrderComplete.aspx?Id=" + oOrderObj.Id + """," +

                                        """capture"": ""true""," +

                                        """confirmUrlType"": ""CLIENT""}"



            streamWriter.Write(requestJSON)

            streamWriter.Flush()

            streamWriter.Close()

        End Using

        '取得回覆資訊

        Dim httpResponse As HttpWebResponse = httpWebRequest.GetResponse

        '解讀回覆資訊

        Using streamReader As New StreamReader(httpResponse.GetResponseStream())

            Dim responseJSON As String = streamReader.ReadToEnd()

            '將 JSON 轉為物件

            Dim oReturnObj As GN.LinePay.reserveRes.ReturnJSON = Newtonsoft.Json.JsonConvert.DeserializeObject(responseJSON, GetType(GN.LinePay.reserveRes.ReturnJSON))



            Dim ResMsg As String = ""

            If oReturnObj.returnCode = "0000" Then

                '成功



            Else

                '失敗

            End If

        End Using

    End Sub

End Class

工具網站：
JSON 轉物件 http://json2csharp.com/
C# to VB : http://converter.telerik.com/



#專案： 紅科

使用 SQL Server 管理介面設定欄位權限時要很小心陷井. 如下圖,
1. Col2 和 Id 欄位沒有被授予也沒有被拒絕.
2. 若是設定 Col2 欄位為授予, Id 欄位沒有做任何的修改.
3. 存檔後, Id 欄位會被拒絶存取.

 
以上的情況是用以下 SQL 生成:

GRANT SELECT ON OBJECT::S3..test TO webuser;
Deny SELECT ON OBJECT::S3..test(Col1) TO webuser;
 

我想大家一定會遇到要把資料匯出成 Excel 的需求. 以現有的工具, 大家想到作法大概都是先把資料放到一個 datatable 之中, 後叫用 UW.ExcelPOI.DTToExcelAndWriteToClient 就結束了.

前兩天遇到一個需求, 輸出的 Excel 要加上表頭, 如下圖



於是乎把  UW.ExcelPOI.DTToExcelAndWriteToClient 做了一些擴充, (其實應該說是幫 DTToWorkSheet 做了擴充), 過程如下.

1. 需求: 一個可以快速填入欄位的 Sub (method or function)
A. 每一個 Cell 可以設定內容(文字), 字型大小, 跨欄數, 對齊方式. (其它的未來再來擴充, 例如顔色).
B. 每一個 Row 由 Cell 組成, 由左到右.
C. 一次可以填多個 Row

2. 實作:
A. 先定義 Cell

    Public Class Cell

        Public Content As String

        Public Colspan As Int32 = 1

        Public Alignment As NPOI.SS.UserModel.HorizontalAlignment

        Public FontHeightInPoints As Int32 = 0



        Sub New(Content As String, Optional Colspan As Int32 = 1,

                Optional Alignment As NPOI.SS.UserModel.HorizontalAlignment = NPOI.SS.UserModel.HorizontalAlignment.General,

                Optional FontHeightInPoints As Int32 = 0)

            Me.Content = Content

            Me.Colspan = Colspan

            Me.Alignment = Alignment

            Me.FontHeightInPoints = FontHeightInPoints

        End Sub

    End Class

B. Row 的格式: 我想最直的覺的就是 List(of Cell) 了吧.

C. 多個 Row 的表示法: List(Of List(Of Cell))

D. 來把 Cell 填入 WorkSheet  吧, 

Public Shared Sub AddRows(WS As HSSFSheet, ltRows As List(Of List(Of Cell)), ByRef StartRow As Int32)

共有三個參數: WS  和 ltRows 應該不用解釋了. 最後一個 StartRow 用來指定插入資料的開始 Row.

E.  完整程式碼: (程式碼不看沒關係, 但要跳到 F. 重點講解哦)

Public Shared Sub AddRows(WS As HSSFSheet, ltRows As List(Of List(Of Cell)), ByRef StartRow As Int32)

        Dim WR As HSSFRow

        If ltRows IsNot Nothing Then

            For Each ltRow As List(Of Cell) In ltRows

                WR = WS.CreateRow(StartRow)

                Dim C As Int32 = 0

                For Each cell As Cell In ltRow

                    Dim ic As NPOI.SS.UserModel.ICell = WR.CreateCell(C)

                    ic.SetCellValue(cell.Content)



                    Dim cs As NPOI.SS.UserModel.ICellStyle = WS.Workbook.CreateCellStyle()

                    cs.Alignment = cell.Alignment

                    If cell.FontHeightInPoints > 0 Then

                        Dim oFont As NPOI.SS.UserModel.IFont = WS.Workbook.CreateFont()

                        oFont.FontHeightInPoints = cell.FontHeightInPoints

                        cs.SetFont(oFont)

                    End If



                    ic.CellStyle = cs



                    If cell.Colspan > 1 Then

                        WS.AddMergedRegion(New CellRangeAddress(StartRow, StartRow, C, C + cell.Colspan - 1))

                        C += cell.Colspan - 1

                    End If

                    C += 1

                Next



                StartRow += 1

            Next

        End If

    End Sub

F. 重點講解:
這個 function 在實作時有兩個卡點:
1. 如何合併欄: 

WS.AddMergedRegion(New CellRangeAddress(StartRow, StartRow, C, C + cell.Colspan - 1))

2. 如何設定字型大小和對齊方式:

                    Dim cs As NPOI.SS.UserModel.ICellStyle = WS.Workbook.CreateCellStyle()

                    cs.Alignment = cell.Alignment

                    If cell.FontHeightInPoints > 0 Then

                        Dim oFont As NPOI.SS.UserModel.IFont = WS.Workbook.CreateFont()

                        oFont.FontHeightInPoints = cell.FontHeightInPoints

                        cs.SetFont(oFont)

                    End If



                    ic.CellStyle = cs

這裡有件有有趣的事, 我一開始是這樣寫的.

ic.CellStyle.Alignment = cell.Alignment

結果是整個 WorkSheet 的對齊方式都被改了. 我猜當 WorkSheet 初建立時, CellStyle 都是用同一個. 所以改任一個 cell 的 CellStyle 會同時改到所有 cell 的.

G. 使用方式:

        Dim ltHeader As New List(Of List(Of UW.ExcelPOI.Cell))

        Dim ltLine As New List(Of UW.ExcelPOI.Cell)

        ltLine.Add(New UW.ExcelPOI.Cell(DB.SysConfig.SYSTEM_NAME & "應收明細表", 16,

                                        NPOI.SS.UserModel.HorizontalAlignment.Center, 28))

        ltHeader.Add(ltLine)



        '第二行

        ltLine = New List(Of UW.ExcelPOI.Cell)

        ltLine.Add(New UW.ExcelPOI.Cell("期間: " & Me.txtbl_date_s.Text & " ~ " & Me.txtbl_date_e.Text, 10,

                                        NPOI.SS.UserModel.HorizontalAlignment.Left, 20))

        ltLine.Add(New UW.ExcelPOI.Cell("製表日期: " & Now.ToString("yyyy-MM-dd"), 6,

                                        NPOI.SS.UserModel.HorizontalAlignment.Right, 20))

        ltHeader.Add(ltLine)



        UW.ExcelPOI.DTToExcelAndWriteToClient(newdt, ltHeader:=ltHeader)

先在 Server 上面建立好同名的帳號, 然後可以用這個指令

sp_change_users_login 'Auto_Fix', 'username'

若想要連 Server 上的帳號一併建立: 

sp_change_users_login [ @Action = ] 'action'

    [ , [ @UserNamePattern = ] 'user' ]

    [ , [ @LoginName = ] 'login' ]

    [ , [ @Password = ] 'password' ]

[;]



[ @Action= ] 'action'

描述此程序所要執行的動作。 動作是varchar （10)。 通常是 'Auto_Fix'



[ @UserNamePattern= ] 'user'

這是目前資料庫中的使用者名稱。 使用者是sysname，預設值是 NULL。

[ @LoginName= ] 'login'

這是 SQL Server 登入的名稱。 登入是sysname，預設值是 NULL。

[ @Password= ] 'password'

指派給新的密碼SQL Server藉由指定建立登入Auto_Fix。 如果符合的登入已經存在，對應的使用者和登入和密碼會被忽略。 如果符合的登入不存在，則 sp_change_users_login 會建立新SQL Server登入，並指派密碼當做新登入的密碼。 密碼是sysname，而且不可以是 NULL。

參考: https://msdn.microsoft.com/zh-tw/library/ms174378.aspx

--抓所有的 Table
Select * from INFORMATION_SCHEMA.TABLES

--抓所有的 COLUMNS
Select * from INFORMATION_SCHEMA.COLUMNS


--抓欄位的 Description
select 
    st.name [Table],
    sc.name [Column],
    sep.value [Description]
from sys.tables st
inner join sys.columns sc on st.object_id = sc.object_id
left join sys.extended_properties sep on st.object_id = sep.major_id
                                        and sc.column_id = sep.minor_id
                                        and sep.name = 'MS_Description'
where st.name = 'TableName'
and sc.name = 'ColumnName'

--修改欄位的 Description.
EXEC sp_updateextendedproperty 
@name = N'MS_Description', @value = 'Your description',
@level0type = N'Schema', @level0name = 'dbo', 
@level1type = N'Table',  @level1name = 'TableName', 
@level2type = N'Column', @level2name = 'Name';


EXEC sp_addextendedproperty 
@name = N'MS_Description', @value = 'Code description',
@level0type = N'Schema', @level0name = 'dbo', 
@level1type = N'Table',  @level1name = 'TableName', 
@level2type = N'Column', @level2name = 'ColumnName';



--新增 Table 的 extendedproperty
EXEC sp_addextendedproperty 
@name = N'Description', @value = 'Hey, here is TableName description!',
@level0type = N'Schema', @level0name = 'dbo',
@level1type = N'Table',  @level1name = 'TableName'
GO

--修改 Table 的 extendedproperty
EXEC sp_updateextendedproperty 
@name = N'Description', @value = 'Hey, here is my description! 123',
@level0type = N'Schema', @level0name = 'dbo',
@level1type = N'Table',  @level1name = 'TableName'
GO


--讀取 Extended Property
SELECT sys.objects.name AS TableName, ep.name AS PropertyName,
       ep.value AS Description
FROM sys.objects
CROSS APPLY fn_listextendedproperty(default,
                                    'SCHEMA', schema_name(schema_id),
                                    'TABLE', name, null, null) ep
WHERE sys.objects.name NOT IN ('sysdiagrams')
ORDER BY sys.objects.name

--讀取 Column 的 Description
SELECT objtype, objname, name, value  
FROM fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', 'TableName', 'column', default);  
GO

--讀取特定 Table 的 Description
SELECT *  
FROM fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', 'TableName', default, default);  
GO  

--讀取 所有 Table 的 Description
SELECT *  
FROM fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', default, default, default);  
GO  


--新增或修改資料表說明
IF not exists(SELECT * FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', '資料表名稱', NULL, NULL))  
           BEGIN  
            exec sp_addextendedproperty 'MS_Description', '資料表說明', 'user', 'dbo', 'table', '資料表名稱' 
           END  
ELSE 
           BEGIN  
            exec sp_updateextendedproperty 'MS_Description', '資料表說明', 'user', 'dbo', 'table', '資料表名稱' 
           END

--新增或修改欄位說明
IF not exists(SELECT * FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', '資料表名稱', 'column', '欄位名稱')) 
           BEGIN  
            exec sp_addextendedproperty 'MS_Description', '欄位說明', 'user', 'dbo', 'table', '資料表名稱', 'column', '欄位名稱' 
           END  
ELSE 
           BEGIN  
            exec sp_updateextendedproperty 'MS_Description', '欄位說明', 'user', 'dbo', 'table', '資料表名稱', 'column', '欄位名稱' 
           END

錯誤訊息如下, 完全沒有錯誤訊息, 以及沒有錯誤的程式碼位置

 <Item time="2016-01-11T05:39:01" page="/fr/iconic-bright-cushion-spf-50-pa-nude-perfection-compact-foundation/p/5490/c/30"

url="http://www.shopunt.com/fr/iconic-bright-cushion-spf-50-pa-nude-perfection-compact-foundation/p/5490/c/30?utm_source=edm&amp;utm_medium=email&amp;utm_content=20160107_cushion_4&amp;utm_campaign=makeup&amp;OutAD_Id=5825" username="Not Member" browserName="Chrome" browserVersion="34.0" userAgent="Mozilla/5.0 (Linux; Android 5.1.1; SAMSUNG SM-N915FY Build/LMY47X) AppleWebKit/537.36 (KHTML, like Gecko) SamsungBrowser/2.1 Chrome/34.0.1847.76 Mobile Safari/537.36" RemoteIP="37.160.206.7" Ref="No Ref" RequestType="GET" Ver="3">

    <ErrMsg>

    </ErrMsg>

    <ErrStack> 於 System.Web.CachedPathData.ValidatePath(String physicalPath)

於 System.Web.HttpApplication.PipelineStepManager.ValidateHelper(HttpContext context)</ErrStack>

    <Post>

    </Post>

    <Cookie>

    </Cookie>

</Item>

查了一下，原來網址後面多了空白 (%20) , 也就是 ? 前面多了空白
只是exception物件會自作聰明把他濾掉了，反而從 exception log 看不到資料
測試過，userd可以正常看網站，只是server會有不斷 excetion產生，有點煩
網路上雖有一些解法，但我想還是要求下廣告時，要注意網址問題


 

http://sechow.com/bricks/docs/content-page-4.html

因為 useragent 是可以改的 所以也是 injection  的入口

對於PC版大尺吋的螢幕，當網站要 feed 資料到 facebook 時，可以使用 javascript SDK 的
FB.ui => method:'feed' 方式處理，參考位置。此方法會popup小視窗讓user可以分享資料出去。

但是遇到手機版網頁就有點麻煩，實測上手機版的 chrome 會出現轉不停的情況，無法分享；而iPhone則是另開新頁面處理，可以成功但是會多一個分頁。所以手機版網頁建議使用 redirect 方式處理分享機制，參考位置
​

    var fbUrl = "https://www.facebook.com/dialog/feed?" +

    "app_id=122465741241119&display=touch" +

    "&link=" + encodeURIComponent("http://www.shopunt.com/tch/FixPage.aspx?id=525") +

    "&picture=" + encodeURIComponent("http://www.shopunt.com/tch/event/2014-nail-enrollment/fb_200x200.jpg") +

    "&description=" + encodeURIComponent("市價不斐的光療DIY教學，UNT傾囊相授！10場巡迴免費教學，讓妳輕鬆掌握光療DIY訣竅，並搶先體驗秋冬最新流行色！現場打卡，再送時尚美甲工具組") +

    "&redirect_uri=" + encodeURIComponent("http://www.shopunt.com/tch/event/2014-nail-enrollment/Handler.ashx?fun=FBCallback");

​

當然以 redirect 處理與 javascript 方式處理是兩種不同的方式，redirect 方式的 callback 網址 (redirect_uri)要接收facebook 導回的 post_id , javascript 方式則是 callback function 處理 post_id

SELECT s.name

FROM sys.schemas s

WHERE s.principal_id = USER_ID('YourUserID');



ALTER AUTHORIZATION ON SCHEMA::YourSchemaName TO dbo;

參考：http://blog.sqlauthority.com/2011/12/26/sql-server-fix-error-15138-the-database-principal-owns-a-schema-in-the-database-and-cannot-be-dropped/

自從網站上了 net4.0 之後，網站會有為數不少的 "潛在危險" 的 exception
大都來自不友善的攻擊，想要測試網站的漏洞
網站做這層防護是好事，只是這個東西太敏感了，連簡單的冒號 & 符號都會跳 exception
更慘的是 Google Analytics 會在一些 user cookies 寫入xml文字 ( __utmz=... )
導致正常的 User 都不能正常瀏覽我們網站

解法有兩種:
1. 直接在 web.config 直接設定 不檢查

<system.web>

    <httpRuntime requestValidationMode="2.0" />

    <pages validateRequest="false" />

</system.web>

2. 自訂 RequestValidate (4.0以上才可以用)
請參考此文章 http://msdn.microsoft.com/en-us/library/system.web.util.requestvalidator(v=vs.100).aspx

後者比較算是正解 基本上處理掉 <script 我想 XSS 就解決一大半