LINQ to SQL and Security
by System Administrator August 7, 2009

LINQ to SQL and Security

Develop

Ever since LINQ to SQL came out I have been a fan. I feel it has sped up my development on many projects with varying scopes, even when I was restricted to using only stored procedures to access the database. I have also noticed how easy it is to be more secure while using LINQ to SQL versus ADO.NET. As we all know the best practice for sending data to a database is to use parameterized queries so that all the nasty SQL injection that goes on in the web gets foiled on our programs. LINQ to SQL makes using parameterized queries a lot easier because it always uses them. With ADO.NET you had to force yourself to use them and it was just a little more cumbersome to get to your data.

Even though LINQ to SQL takes care of protecting you from basic SQL injection attacks you still need to think about where else you might be vulnerable. The most obvious problem is the web.config of your web application with the data source connection string. If an attacker gained that through some crafty means they would be able to access your database with all the same privileges as your application. So you want to make sure to first lock down those privileges in your database, but with LINQ you tend to need access to all the tables in the database. One answer is to only allow access to views of the data. This significantly restricts what the attacker can do to the data. You could also use only stored procedures to restrict access to how data is viewed and modified. I would also suggest you do two other things on top of the previous suggestions, and that is secure your web.config by encrypting the connection strings section of your webconfig (along with any other sensitive data like smtp logins), and choose to use integrated security for SQL Server to prevent SQL logins entirely.  Even when all this is done it is still smart to sanitize your data coming from the user, and if you question this just think how embarrassing it would be if you had HTML injection going on in your website.

I leave you with a few other suggestions for security in general:

  1. Always sanitizing data you receive from the user. Not only does this prevent you from possible problems in the future, the data usually is much nicer to work with in the end.
  2. Don’t forget to install the latest security patches to prevent easy hacks.
  3. NEVER use concatenation in your SQL statements when using data from the user, including in a stored procedure.
  4. Do use a tool or library that forces you to use parameterized queries.