OP-ED: Why LINQ to SQL is a Better Option than Straight SQL
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.
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 firstname.lastname@example.org.
Copyright © by Paul T. Kimmel 2008. All Rights Reserved.
Page 4 of 4