King的幸福國度

為了防止SQL Injection 在ASP.NET裡有提供一個機制 使用Parameters來防止惡意的 SQL Injection 以下程式碼將示範如何使用Parameters的add三種方法 來指定「@欄位名稱」的對應值

Private Sub UpdateDemographics(ByVal customerID As Integer, _
    ByVal demoXml As String, _
    ByVal connectionString As String)

    ' Update the demographics for a store, which is stored
    ' in an xml column.
    Dim commandText As String = _
     "UPDATE Sales.Store SET Demographics = @demographics " _
     & "WHERE CustomerID = @ID;"

    Using connection As New SqlConnection(connectionString)
        Dim command As New SqlCommand(commandText, connection)

        ' Add CustomerID parameter for WHERE clause.
        command.Parameters.Add("@ID", SqlDbType.Int)
        command.Parameters("@ID").Value = customerID

        ' Use AddWithValue to assign Demographics.
        ' SQL Server will implicitly convert strings into XML.
        command.Parameters.AddWithValue("@demographics", demoXml)

        Try
            connection.Open()
            Dim rowsAffected As Integer = command.ExecuteNonQuery()
            Console.WriteLine("RowsAffected: {0}", rowsAffected)

        Catch ex As Exception
            Console.WriteLine(ex.Message)
        End Try
    End Using
End Sub

紀錄一下，免得之後都要再查一次 以下是我的寫法

Dim dr As SqlDataReader '宣告一個SqlDataReader
        '要用以下的連線字串方法的話要 Imports System 跟 Imports System.Web.Configuration
        Using Conn As SqlConnection = New SqlConnection(WebConfigurationManager.ConnectionStrings("fantasy").ConnectionString.ToString)
            Dim cmd As SqlCommand = New SqlCommand("SELECT [Emp_no], [PassWd] FROM [sys5] WHERE [Emp_No]= @Emp_No AND [PassWd] = @PassWd", Conn)
            cmd.Parameters.AddWithValue("@Emp_No", account.Text)
            cmd.Parameters.AddWithValue("@PassWd", password.Text)
            dr = cmd.ExecuteReader
            If dr.HasRows Then
                Response.Write("alert('登入成功')")
                Session("login") = True
            End If
        End Using