October 19, 2014
Hot Topics:
RSS RSS feed Download our iPhone app

OP-ED: Why LINQ to SQL is a Better Option than Straight SQL

  • August 15, 2008
  • By Paul Kimmel
  • Send Email »
  • More Articles »

Listing 3: Code to permit a user (represented by the local variable) to input a value to filter a result set.

Imports System.Linq
Imports System.Data.Linq
Imports System.Data.Linq.Mapping


Module Module1

   Sub Main()
      CorrectedVersionWithLINQToSQL()
      End Sub
   Sub CorrectedVersionWithLINQToSQL()

      Dim northwind As Northwind = New Northwind
      Dim customers As Table(Of Customer) = _
         northwind.GetTable(Of Customer)()

      Dim param As String = "ALFKI"

      Dim results = From cust In customers _
                    Where cust.CustomerID = param _
                    Select cust

      For Each c In results
         Console.WriteLine(c.CustomerID)
         Console.WriteLine(c.CompanyName)
      Next

      Console.ReadLine()

   End Sub

End Module

Public Class Northwind
   Inherits DataContext

   Public Shared ReadOnly connectionString AsString = _
      "Data Source=.\SQLExpress;Initial _
         Catalog=northwind;Integrated Security=True"

   Public Sub New()
      MyBase.New(connectionString)
   End Sub
End Class

<Table(Name:="Customers")> _
Public Class Customer

   Private FCustomerID As String
   <Column()> _
   Public Property CustomerID() As String
      Get
         Return FCustomerID
      End Get
      Set(ByVal Value As String)
         FCustomerID = Value
      End Set
   End Property

   Private FCompanyName As String

<Column()> _ Public Property CompanyName() As String Get Return FCompanyName End Get Set(ByVal Value As String) FCompanyName = Value End Set End Property End Class

The Northwind class represents everything you need to connect to the underlying SQL database. You don't use ADO.NET directly. Note that Northwind inherits from DataContext and calls its base constructor with the connection string. The Customer class is called an entity class. It is mapped to the Customers table with the TableAttribute, and as designed, it will only return the CustomerID and CompanyName from the Customers table. The DataContext and Table class are incapable of returning any other data from anywhere else.

The Module contains the LINQ to SQL query. Simply create an instance of the Northwind class, request the Table(Of Customers), and write a LINQ query to select the desired data. As demonstrated by the LINQ sample, LINQ starts with a From clause to set the context and aid Intellisense and the Select clause comes last. (A little odd looking at first, but easy enough to get used to.)

Disclaimer: There seem to be many clever people working to wreak havoc, so no single approach is guaranteed to prevent all attacks. The likelihood of attack and the potential for damage must be weighed against the time, effort, and money required to protect information. And, security is a holistic problem that must consider the people inside and outside of your organization and how they behave.

I know all of you are very smart, so I challenge anyone interested to figure out how to inject a malicious value into param and get anything useful to work. The conditions are you can only change the value of param, and you are encouraged to publish your results if successful.

Summary

LINQ to SQL eliminates SQL injection because the user is, in effect, working with compiled code and not dynamic SQL. This means the end user can't pass a query in masked as a parameter. LINQ to SQL eliminates destructive SQL injection because the query language only supports the Select construct—not Update, Delete, or Insert. All modifications are made using method calls that obviously can't be injected. (The only way injection is likely to work is if you write the code using the CodeDOM to dynamically compile user-input strings.)

Finally, my favorite part about all of this is that LINQ to SQL is easier to write and use than writing all of that ADO.NET plumbing. If you really want to master LINQ, pick up my book LINQ Unleashed for C# from Amazon.com and fine bookstores everywhere. Even if you only know VB, the explanations in my book are in English, the code is downloadable, and if you can read VB you can read C#; they're like fraternal twins.

About the Author

Paul Kimmel is the VB Today columnist for www.codeguru.com and has written several books on object-oriented programming and .NET. Check out his upcoming book LINQ Unleashed for C#; order your copy today at Amazon.com. Paul Kimmel is an Application Architect for EDS. You may contact him for technology questions at pkimmel@softconcepts.com.

Copyright © by Paul T. Kimmel 2008. All Rights Reserved.





Page 4 of 4



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Sitemap | Contact Us

Rocket Fuel