如何在参数中使用ExecuteQuery [英] How to use ExecuteQuery with Parameter

问题描述

Hello

Q1：传递给LINQ的参数使用ExecuteQuery是否可以安全地进行注入？如下例所示

此函数工作正常

Q1: is the parameter passed to LINQ  using ExecuteQuery is safe against injection? as in the below example
This function works fine

Function GetData_v1(Of t)(table_name$,pk$,pkvalue As Object) As IQueryable(Of t)
    Dim s = "SELECT * FROM {0} WHERE {1} = {2}"
        s = String.Format(s,table_name,pk,pkvalue)
    Dim r = db.ExecuteQuery(Of t)(s)    
    Return r
End Function

我试图创建我在此处找到的参数  如何：执行参数化查询

I tried to create the parameter as I found in here How to: Execute a Parameterized Query

Dim CNN2$ = "metadata=res://*/ef_database.csdl|res://*/ef_database.ssdl|res://*/ef_database.msl;provider=System.Data.SqlClient;provider connection string='data source=.\;initial catalog=_mydb_;persist security info=True;user id=sa;password=_mypw_;multipleactiveresultsets=True;application name=EntityFramework'"
Function GetData_v2(Of t)(table_name$,key_name$,key_value As Object) As IQueryable(Of t)
    Dim sql_query$ = "SELECT * FROM {0} WHERE {1} = @p1"
        sql_query$ = String.Format(sql_query,table_name,key_name)

    ' This what I find it while searching
    Dim object_context As New ObjectContext(CNN2)
    Dim object_query   As New ObjectQuery(Of t)(sql_query,object_context)
        object_query.Parameters.Add(New ObjectParameter("p1",key_value))

    Dim result = From r In object_query Select r
        
    Return result
End Function

此函数给出了错误：  查询语法无效。近期'*'，第1行，第9列。

This function is giving me error : The query syntax is not valid. Near term '*', line 1, column 9.

Q2：为什么会出现此问题？

Q2: Why is this problem?

推荐答案

对于LINQ查询，我强烈建议不要使用常规SQL，而是使用为您生成的强类型类，实体框架也是如此（在访问和更改数据时高出一步）。据说你会在你提供的页面上使用第一个代码
样本。

For LINQ queries I highly recommend not using regular SQL but instead use the strong type classes that are generated for you, same goes for entity framework (one step higher up in accessing and changing data). With that said you would use the first code sample on the page you provided.

编辑

即使我不建议你问的方式我一会儿发现了一个代码样本。而不是选择所有字段，这是一个子集。

Even though I don't recommend the way you are asking I found a code sample a while back. Rather than selecting all fields this does a subset.

Module Module1
    Sub Main()
        Using db As New NorthwindEntities

            Dim UK_Query = db.Database.SqlQuery(Of CustomerSubSet) _
                    (
                        <SQL>
                            SELECT 
                                CustomerID, 
                                CompanyName, 
                                Country, 
                                PostalCode
                            FROM 
                                Customers 
                            WHERE country=@Country                            
                        </SQL>.Value,
                        New SqlClient.SqlParameter With
                        {
                            .ParameterName = "@Country",
                            .DbType = DbType.String,
                            .Value = "UK"
                        }
                    ).ToList

            Console.WriteLine("Begin")
            For Each cust In UK_Query
                Console.WriteLine(cust.CountryPostalCode)
            Next
        End Using
    End Sub
End Module
<Serializable()>
Public Class CustomerSubSet
    Public Property CustomerID As String
    Public ReadOnly Property ID As String
        Get
            Return CustomerID
        End Get
    End Property
    Public Property CompanyName As String
    Public Property PostalCode As String
    Public Property Country As String
    <DatabaseGenerated(DatabaseGeneratedOption.Computed)>
    Public Property CountryPostalCode As String
        Set(value As String)

        End Set
        Get
            Return Country & " : " & PostalCode
        End Get
    End Property
    Public Sub New()
    End Sub
    Public Overrides Function ToString() As String
        Return String.Format("{0,-8}{1,-40},{2}", ID, CompanyName, Country)
    End Function
End Class
Partial Class Customer
    <DatabaseGenerated(DatabaseGeneratedOption.Computed)>
    Public Property CountryPostalCode As String
        Set(value As String)

        End Set
        Get
            Return Country & " : " & PostalCode
        End Get
    End Property

    Public Overrides Function ToString() As String
        Return String.Format("{0,-8}{1,-40},{2}", CustomerID, CompanyName, Country)
    End Function
End Class

这篇关于如何在参数中使用ExecuteQuery的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！