LINQtoSQL vs Stored Procedures, Round 1

The web team is making some major changes to iQcommerce, and one of the technologies we looked into while reviewing our data access layer options was LInQ to SQL. For a good number of people, that raises three red flags:

  1. OMFGSECURITYWTF
  2. Sprocs are faster ‘cause they’re compiled!
  3. Aren’t they retiring LinQ to SQL for ADO.NET’s Entity Framework?

NOTE: I’m gonna call it LinQ instead of LinQ to SQL for convenience reasons, but know that I’m referring specifically to the “to SQL” flavor, as opposed to “to XML”, or “to Objects”, or other variants.

We ended up not choosing LinQ for specific reasons I’ll allude to later, but during our research we basically debunked all three of those myths, so I’d like to share what we learned in the hope that we clear things up and everyone appreciates what LinQ brings to the table, even if we’re not using it.

The first big one is security. Everyone I’ve talked to who doesn’t support LinQ immediately brings up the security concerns. And I admit it was my default response as well, having your SQL statements generated on the fly by your code just sounds insecure. But if you look at the generated code compared to what a stored procedure would look like, you’ll see there’s not much different going on. Let’s do just that.

Stored Procedure

1
2
3
4
5
6
7
CREATE PROCEDURE SelectProductByName
@Name varchar(16)
AS
SELECT * FROM Products WHERE ProductID = @Name

-Exec sproc:
EXEC SelectProductByID @ProductID = 'Phone'

LINQ

1
2
3
EXEC sp_executesql N'SELECT [t0].[ProductID], [t0].[Name]
FROM [dbo].[Products] AS [t0]
WHERE [t0].Name = @p0', N'@p0 varchar(16)', @p0 = 'Phone'

So what are we looking at here? Let’s break it down into similarities and differences. Similarities include:

  • Both use EXEC to execute the actual SQL statement. That’s pretty basic, it’s used to execute a sproc, UDF, or even straight up SQL (as in the LInQ example).
  • We’re selecting the same data from the same table using basically the same SQL syntax, although LinQ specifically identifies columns (we’ll address that later).
  • Both use parameters to provide the data to select against

And what about the differences?

  • Sprocs must be “created” before they can be used, whereas LInQ is just generating SQL statements that aren’t predefined on the server.
  • LInQ statements are executed using the sp_executesql system function, whereas sprocs are called directly.
  • LInQ specifies specific column names in the select statement, and never uses * to select all columns

A bit more information on sp_executesql. The primary purpose of the function is to pre-compile an execution plan before the SQL code is executed, very similar to how creating a stored procedure works. It takes 3 (or more) arguments, the first being the actual SQL to execute, the 2nd being a list of all parameters to use, and the 3rd a list of the parameter values. This way, if the server comes across the same SQL argument more than once, it can refer to a pre-compiled version of the code for all subsequent executions (again, identical to sprocs).

So, using this information, let’s address the two main security concerns leveraged against LinQ: SQL Injection and Permissions.

The only reason you can’t easily do SQL injection on a sproc is because of parameters. As from the MSDN website: “If you use the Parameters collection, input is treated as a literal value instead of as executable code.” Meaning it can’t be executed as SQL, period. It doesn’t matter if you use parameters within stored procedures, or within the sp_executesql function, or anywhere else in SQL Server that allows parameters. You get the exact same benefit regardless of execution.

As for permissions, with SQL Server you can assign specific permissions to individual stored procedures, such as “execute” or “alter”. With LInQ, queries are generated on the fly, so it’s pretty near impossible to assign permissions to it. What you CAN do however, is assign permissions to specific tables and views. It’s just as easy to assign a “execute” permission to a stored procedure as it is to assign a “select” or “update” permission to a table. If you need a way to lock down two tables but allow a user to view a report on specific columns on those tables, you can create a view and set select permissions on that. And really, I think (especially in an application like ours) security should be enforced at a code/domain-level as opposed to a database level anyways.

Hopefully that debunks the security myths around LInQ to SQL. This post is getting pretty long so I’m going to address some of the pros & cons we found to LinQ as well as the “what about EF?” question in a later post.

Comments