[VB.NET]程式中使用SQL字串IN參數化設定

 

一，寫程式時在使用SQL字串時，WHERE條件的參數，我們需要參數化，以防止SQL Injection發生，

ADO.Net 有提供相關參數化設定，但是當我們遇到使用IN子句時，要如何使用參數化?

 
            Dim parameString As String = "123,456,789"
            Dim parameArray() As String
            Dim temp As String
            comm.CommandText = "SELECT * FROM TEST WHERE ID IN (@ID)"


            parameArray = parameString.Split(",")


            For i = 0 To parameArray.Length - 1
                temp &= String.Format("@ID{0},", i)
            Next
            comm.CommandText = comm.CommandText.Replace("@ID", temp.Substring(0, temp.Length - 1))
            For j = 0 To parameArray.Length - 1
                comm.Parameters.Add(String.Format("@ID{0}", j), SqlDbType.VarChar).Value = parameArray(j)
            Next
            comm.ExecuteReader()

[AOP]Crosscutting Concerns實作

Crosscutting Concerns 使用System.Runtime.Remoting.Proxies.RealProxy類別來實作

一，說明Hello這類別就是用來繼承MarshalByRefObject類別，在AOP中表示Pointcut。
Hello.vb

 
Public Class Hello
    Inherits MarshalByRefObject
End Class

二，說明IHello這類別就是定義一個Interface，在AOP中表示Weave。
IHello.vb

 
Public Interface IHello
    Sub hello(ByVal name As String)
End Interface

三，說明LogHandler這類別就是用來做動態Proxy Pattern，繼承RealProxy類別，在AOP中表示Advices。
LogHandler.vb

 
Imports System.Runtime.Remoting.Proxies
Imports System.Runtime.Remoting.Messaging
Imports System.Runtime.Remoting
Imports System.Runtime.Remoting.Services

Public Class LogHandler
    Inherits RealProxy

    Private myMarshalByRefObject As MarshalByRefObject

    Public Sub New(ByVal myType As Type)
        MyBase.New(myType)
        myMarshalByRefObject = CType(Activator.CreateInstance(myType), MarshalByRefObject)
    End Sub

    Public Overrides Function Invoke(ByVal myIMessage As IMessage) As IMessage
        Dim calls As IMethodCallMessage = myIMessage
        Dim back As IMethodReturnMessage = Nothing

        Console.WriteLine("呼叫方法的名稱:" + calls.MethodName)

        back = RemotingServices.ExecuteMessage(myMarshalByRefObject, calls)

        Console.WriteLine("顯示的結果:" + back.ReturnValue.ToString())

        Return back
    End Function
End Class

四，說明HelloSpeaker這類別就是用來這類別就是用來繼承Hello類別並實作IHello介面，在AOP中表示Joinpoint。
HelloSpeaker.vb

 
Public Class HelloSpeaker
    Inherits Hello
    Implements IHello

    Public Sub hello(name As String) Implements IHello.hello
        Console.WriteLine("Hello：" + name)
    End Sub

End Class

五，執行
ProxyDemo.vb

 
Module ProxyDemo
    Sub Main()
        Dim proxy As LogHandler = New LogHandler(New HelloSpeaker().GetType())
        Dim Hello As IHello = proxy.GetTransparentProxy()
        Hello.hello("123")
        Console.ReadKey()
    End Sub
End Module

SyntaxHighlighter Manual

 
 您的程式碼請貼此處。 

SyntaxHighlighter/manual/brushes/

Brush name	Brush aliases	File name
ActionScript3	as3, actionscript3	shBrushAS3.js
Bash/shell	bash, shell	shBrushBash.js
ColdFusion	cf, coldfusion	shBrushColdFusion.js
C#	c-sharp, csharp	shBrushCSharp.js
C++	cpp, c	shBrushCpp.js
CSS	css	shBrushCss.js
Delphi	delphi, pas, pascal	shBrushDelphi.js
Diff	diff, patch	shBrushDiff.js
Erlang	erl, erlang	shBrushErlang.js
Groovy	groovy	shBrushGroovy.js
JavaScript	js, jscript, javascript	shBrushJScript.js
Java	java	shBrushJava.js
JavaFX	jfx, javafx	shBrushJavaFX.js
Perl	perl, pl	shBrushPerl.js
PHP	php	shBrushPhp.js
Plain Text	plain, text	shBrushPlain.js
PowerShell	ps, powershell	shBrushPowerShell.js
Python	py, python	shBrushPython.js
Ruby	rails, ror, ruby	shBrushRuby.js
Scala	scala	shBrushScala.js
SQL	sql	shBrushSql.js
Visual Basic	vb, vbnet	shBrushVb.js
XML	xml, xhtml, xslt, html, xhtml	shBrushXml.js