I was going to name this blog post “You’re only as strong as your weakest Linq,” but I thought that would be trite (but funny enough not to not mention).
Here it is: Linq is not impervious to Sql Injection, as claimed in Eliminate SQL Injection Attacks Painlessly with LINQ. While I agree with the statement in the article that to eliminate SQL Injection, eliminate SQL; the reality for Linq is not so cut and dried. The author states that “every SQL query that Linq executes on your behalf is parameterized.” This is not true. In fact, inline SQL is recommended to improve the performance of certain Linq queries:
· see http://shrinkster.com/z2q for improved performance
· see http://shrinkster.com/z2p for bulk updating issues with Linq
· and here’s a fun one – passing the query to a function, http://shrinkster.com/z2o ).
It should be easy to see that the use of the DataContext ExecuteQuery and ExecuteCommand functions are problematic. Here is my proof of concept code using a simple example – a LinqDataSource and a web page with unvalidated input:
I am using Visual Studio 2008 and SQL Server 2005. For the datasource I needed to create a DataContext. I created a database with a table named Trade to query against:
Then I created the DBML for the DataContext by adding LINQ to SQL Classes and added my table to the design surface :
I created a simple page (includes a LinqDataSource, a ListView, a TextBox and a Button):
Default.aspx
<%@ Page Language="C#" AutoEventWireup="true"
CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Literal ID="Literal1" runat="server"></asp:Literal>
<br />
<br />
<asp:ListView ID="lstTrades" runat="server" DataKeyNames="TradeID"
DataSourceID="LinqDataSource1"> … template markup removed …
</asp:ListView>
<br />
<br />
<asp:TextBox ID="txtParams" runat="server" Width="352px"></asp:TextBox>
<br />
<br />
<asp:Button ID="btnExecuteQuery" runat="server" onclick="btnExecuteQuery_Click"
Text="Execute Query" />
<asp:LinqDataSource ID="LinqDataSource1" runat="server"
ContextTypeName="DriveHaxDataContext" TableName="Trades">
</asp:LinqDataSource>
</div>
</form>
</body>
</html>
Default.aspx.cs
using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Diagnostics;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Init(object sender, EventArgs e)
{
this.LinqDataSource1.Selecting += new EventHandler<LinqDataSourceSelectEventArgs>(LinqDataSource1_Selecting);
}
void LinqDataSource1_Selecting(object sender, LinqDataSourceSelectEventArgs e)
{
DriveHaxDataContext driveHax = new DriveHaxDataContext();
if (this.txtParams.Text.Length > 0)
{
string sql = "select * from Trade where DealMember='" + this.txtParams.Text + "'";
var trades = driveHax.ExecuteQuery<Trade>(sql);
e.Result = trades.ToList();
}
}
protected void btnExecuteQuery_Click(object sender, EventArgs e)
{
this.lstTrades.DataSourceID = this.LinqDataSource1.ID;
}
}Here’s a quick look at results and how SQL injection causes more data to be returned.
No Parameters:
Use a name that I know has a trade, and return data:
Use a simple SQL injection statement, and return more data:
I only did a query statement for this post; I have repeated this with ExecuteCommand with DML operations.
As you can see, while Linq has probably reduced the scope of SQL injection vulnerabilities for those who use it, it is certainly not impervious. What hacks and shortcuts have you done for performance? Or because it was too time consuming to learn a new syntax. Linq and its functional programming aspects will be new to many developers. I personally like Linq and think it is a very useful technology, but you should be aware of both its strengths and its weaknesses.