I don’t know how many SQL injection demos I did in my life – and it is still surprising (or shocking rather) how many people don’t know about this. It is even more surprising how many people fight for their string concat ad hoc queries and come up with all kind of dodgy excuses why it makes sense to use them.

What is wrong with SQL parameters? ;)

Recently I played around with LINQ to SQL and think it is a compelling (and time saving) way to do database interactions. The thing I really like about LINQ to SQL is that it does the right thing by default (security wise) when it comes to parameters.

Consider this query:

var products = from p in context.products
               where p.description.StartsWith(_txtSearch.Text)
               select new
               {
                   p.description,
                   p.price,
                   p.stock
               };

This gets turned into the following SQL (for a search value of ‘sony’):

exec sp_executesql N’SELECT [t0].[description], [t0].[price], [t0].[stock]
FROM [dbo].[products] AS [t0]
WHERE [t0].[description] LIKE @p0′,N’@p0 varchar(5)’,@p0=’sony%’

A (malicious) search value with an appended or 1=1 where clause would look like this:

exec sp_executesql N’SELECT [t0].[description], [t0].[price], [t0].[stock]
FROM [dbo].[products] AS [t0]
WHERE [t0].[description] LIKE @p0′,N’@p0 varchar(16)’,@p0=’sony” or 1=1 –%’

Which will not yield any results.

So if you really like ad hoc SQL – but for some reason are too lazy to work with parameters, LINQ is a compelling alternative (again purely from a security view).

Usually the databases I design (and how I recommend it to my customers), don’t allow direct table access. All access is done via stored procedures. This is where LINQ to SQL comes in really handy for me – basically as a nice Sproc to C# code generator. Calling a GetData stored procedure and subsequent binding to a grid looks like this:

protected void _btnSearch_Click(object sender, EventArgs e)
{
    using (ComputerstoreDataContext context = new ComputerstoreDataContext())
    {
        var products = context.GetProducts(_txtSearch.Text);

        _gridProducts.DataSource = products;
        _gridProducts.DataBind();
    }
}

Which will result in this SQL:

declare @p4 int
set @p4=0
exec sp_executesql N’EXEC @RETURN_VALUE = [dbo].[GetProducts] @Search = @p0′,N’@p0 nvarchar(4),@RETURN_VALUE int output’,@p0=N’sony’,@RETURN_VALUE=@p4 output
select @p4

This works for me.

Disclaimer: There’s quite some controversy about LINQ to SQL and I am not a database expert enough to say if LINQ is good or bad. They way I use it (for Sprocs only) it seems to be fine. Again from a security point of view it does the right thing by default. And that’s what I mostly care about ;)