Why Entity Framework(ORM) is better and more secure.

ORM stands for Object-Relational Mapping (ORM) is a programming technique for converting data between relational databases and object-oriented programming languages such as Java, C#, etc. ORM uses objects to encapsulate database operations, so you don’t need to touch the SQL language. Developers only use object-oriented programming, interact directly with data objects, and do not care about the underlying database.

ORM has the following advantages.

1. The data model is defined in the same place, which is easier to update and maintain, and it is also conducive to code reuse. ORM has many tools, and many functions can be automatically completed, such as data disinfection, preprocessing, transactions, and so on.
2. It forces you to use the MVC architecture, ORM is the natural Model, which ultimately makes the code clearer.
3. ORM-based business code is relatively simple, with less code, and easy to understand.
4. You don’t have to write poorly performing SQL.

Entity Framework is an object-relational mapper (O/RM) that enables .NET developers to work with a database using .NET objects.

Why Entity Framework is more secure than ADO.NET

The SQL statement generated by EF uses parameters to pass the value, so there will be no SQL injection problem. And in Entity Framework, we could prohibit executing SQL commands directly and using entity SQL if it involves external input parameters.

And Entity Framework itself does handle some security issues such as SQL injection attacks if you use the LINQ to Entities queries.

Although query composition is possible in LINQ to Entities, it is performed through the object model API. Unlike Entity SQL queries, LINQ to Entities queries are not composed by using string manipulation or concatenation, and they are not susceptible to traditional SQL injection attacks.

And we could also add a validation function(entity SQL) in the model to prevent SQL injection attacks.

Examples:

function bool SS（string no）

{
try{
string sql = “select count(*) from Students where StudentNo=@No”;
var args = new DbParameter[] {
new SqlParameter { ParameterName = “NO”, Value = no},
};
return DataContext.SqlQuery<int>(sql, args).FirstOrDefault() > 0;

}

catch（Exception ex）
{
#Do something
}

}

We could also create a validation function at the model level, to filter out dangerous inputs that may cause SQL injection.

Compare LINQ with Stored procedure

Some disadvantages of LINQ vs stored procedure:

1. Stored procedures are faster as compared to LINQ queries since they have a predictable execution plan and can take full advantage of SQL features. Hence, when a stored procedure is being executed next time, the database used the cached execution plan to execute that stored procedure.
2. Stored procedures can take full advantage of a database’s feature set. LINQ tends to be more generic in its support. This is common in any kind of language abstraction (e.g. C# vs assembler).
3. If you need to make changes to the way you do data access, you need to recompile, version, and redeploy your assembly. Stored procedures can sometimes allow a DBA to tune the data access routine without a need to redeploy anything.
4. The complexity of the database can be hidden from the caller, and the process of data assembly can be encapsulated.

Advantages of LINQ over stored procedure:

1. You don’t have to learn T-SQL to do data access, nor do you have to learn the data access API (e.g. ADO.NET) necessary for calling the Stored procedure.
2. Compared with the traditional ADO.NET new and updated, the writing is much more concise.
3. I can use any .NET debugger to debug the queries. With Stored procedure, you cannot easily debug the SQL and that experience is largely tied to your database vendor (MS SQL Server provides a query analyzer, but often that isn’t enough).
4. LINQ works with lots of databases and the number of supported databases will only increase. Stored procedures are not always portable between databases, either because of varying syntax or feature support (if the database supports stored procedures at all).
5. Completely eliminate the risk of SQL Injection.
6. If the business data model changes, the stored procedure must be changed along with the business code.

About the security:

Any security risk of using LINQ with stored procs would be the same as using stored procs directly with the old school Db/SqlCommand approach. For example, you can protect your sensitive data by restricting access to the tables directly and putting ACLs on the sprocs. With LINQ, however, you can still restrict the direct access to tables and instead put ACLs on updatable table views to achieve a similar end.

For the most part, LINQ queries are protected from SQL Injection because the translation process, from C# to SQL, uses parameterized values. This provides the same type of protection that Stored Procedures would normally provide.

And the ‘ExecuteQuery’ function, does support parameters and can be used to prevent SQL Injection.

IEnumerable<User> oResults = db.ExecuteQuery<User> {
“select top 10 * from users where state = {0}”,
“CA”}

In this example, SQL Injection is avoided because the search string, “CA”, is passed into the query using a parameter (that’s the {0} part). That parameterized value is preserved in the translation from C# to SQL, and the query is protected.