Databases Reference
In-Depth Information
Generation of the parameterized SQL is important for two reasons. On one hand, it
improves application performance as database servers can reuse the same execution plan
with different parameter values. If the parameter values were embedded in the SQL text,
each parameter value would make a unique new query for which the server would have to
recalculate and cache the execution plan, consuming additional CPU and RAM resources.
On the other hand, parameterized SQL statements are immune to SQL injection attacks,
very common in applications that generate dynamic SQL statements as strings. Here is an
example that illustrates the problem:
string city = “'; drop database Northwind; select '“;
string sql = string.Format(
“select * from Customers where City = '{0}'”, city);
Console.WriteLine(sql);
Imagine that in the code snippet just shown the value of the
city
variable is supplied by
the user and the application uses it to create a query that returns only the customers in
the specified city. With this naïve implementation, a malicious user could enter a specially
constructed string that modifies the original SQL statement the developer intended to
execute and injects one or more unexpected SQL statements, allowing him to destroy data
or get access to restricted information. Following is the SQL statement this code generates.
Notice that instead of an innocent
SELECT
statement, we have a
DROP DATABASE
injected in
an otherwise valid SQL batch:
select * from Customers where City = '';
drop database Northwind;
select ''
However, in the SQL statement generated by the Entity Framework, the
city
value is
passed to the database server as a
parameter
, making it impossible to modify the original
SQL statement and inject a malicious payload. Even if the value was hard-coded in the
application like in the first example in this section, the automatic encoding of literal
values performed by the Entity Framework would still prevent the injection.
Compiled Queries
It takes a certain amount of CPU and time resources for Entity Framework to translate the
LINQ queries into their equivalent SQL statements before they can be executed. When a
particular query is executed repeatedly, you can improve application performance by
precompiling it, as shown in the following example:
static Func<NorthwindEntities, string, IQueryable<Customer>>
customersByCity =
CompiledQuery.Compile<NorthwindEntities, string, IQueryable<Customer>>(
(context, city) =>
from c in context.Customers
where c.City == city
select c
);
