none
How to use ExecuteQuery with Parameter RRS feed

  • Question

  • Hello

    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


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

    Q2: Why is this problem?


    Thursday, September 7, 2017 4:52 PM

All replies

  • 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.

    Edit

    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


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Thursday, September 7, 2017 5:12 PM
    Moderator
  • In addition to Karen,

    ExecuteQuery is a method a programmer made who gave it that name. It is not related to the System.Data. method SQLNonQuery

    However, it looks as you thread it that way.


     


    Success
    Cor

    Thursday, September 7, 2017 6:44 PM