Building and executing parameterless queries would have fewer usages, whereas most usages in any application would be based on parameters/conditions. Introducing parameters in flat/raw queries would expose us to a threat of SQL injection. How are we going to protect our application from such a threat? We cannot ignore them in a real-world application, as we have applications that are hugely dependent on inline queries.
The most commonly injected value would be OR 1 = 1 , which would pump an entire record set instead of using the input value for filtering. For instance, in our blogging system, if a hacker tries to retrieve user information he could tweak the following query (assuming we have such a query in the system):
SELECT * FROM USERS WHERE Id = + userId +. userId could be a field storing the user input value.
Considering the userId passed as 5 from the application, it could be constructed as follows:
SELECT * FROM USERS WHERE Id = 5
The preceding query displays or returns only one user matching the Id as 5.
If the hacker injects the previously mentioned value, it would be as follows:
SELECT * FROM USERS WHERE Id = 5 OR 1 = 1
The preceding SQL injection would return all users' information, rather than returning only the user matching ID 5. This is a simple SQL injection.
Parameterized queries are the mechanism used to protect us from such threats. They ensure that the SQL injection doesn't happen, and the previously mentioned injection problem would not happen with the parameterized queries:
public async Task<IActionResult> Details(int? id)
{
if (id == null)
{
return NotFound();
}
var blog = await _context.Blogs.FromSql("Select * from dbo.Blog WHERE
Id = {0}", id).FirstOrDefaultAsync();
if (blog == null)
{
return NotFound();
}
return View(blog);
}
Instead of directly passing the input value as a parameter in the FromSql method, we could also use the SqlParameter object to construct the parameter, which also ensures that the query is safe from SQL injection. The following code would illustrate parameterised query execution in EF Core:
public async Task<IActionResult> Details(int? id)
{
if (id == null)
{
return NotFound();
}
var blog = await _context.Blogs.FromSql("Select * from dbo.Blog
WHERE Id = @id", new SqlParameter("id", id)).FirstOrDefaultAsync();
if (blog == null)
{
return NotFound();
}
return View(blog);
}
The following screenshot displays a blog item as usual without any changes to its previous state. The only difference is its rendering through a parameterized query:
If we try to inject the value through any of the following means, the system would still be handling the injection of the model binder itself since we are dealing with identifiers that were exposed as an integer. Let's visit the following paths:
http://localhost:54346/Blogs/Details/1OR1=1
http://localhost:54346/Blogs/Details?id=1OR1=1
Trying to access anyone of the preceding link would throw the following error:
For the sake of argument, let's change the parameter type of the identifier to a string and try injecting the value again to the Details action to see how SQL injection is really handled by parameterized queries. It is evident in the following screenshot that the parameterized query is trying to convert the value to an integer that actually fails and the execution is stopped, thereby protecting the application against SQL injection:
Still not convinced? Me neither. Unless we handle injection for a string column, I won't be convinced. Let's tweak the system just for the purpose of illustration by adding the following action, which would filter blogs using a wildcard search in the Title column, returning the first item from the result set and finally rendering them in an existing Details view:
public async Task<IActionResult> GetBlogByTitle(string keyword)
{
if (keyword == null)
{
return NotFound();
}
var blog = await _context.Blogs.FromSql("Select * from dbo.Blog WHERE
Title like '%' + @keyword + '%'",
new SqlParameter("keyword", keyword)).FirstOrDefaultAsync();
if (blog == null)
{
return NotFound();
}
return View("Details", blog);
}
Try to inject the values as we did earlier, by visiting the following paths:
http://localhost:54346/Blogs/GetBlogByTitle?keyword=core OR 1=1
Still, the system would be handling the injection, but now at the database end as it would be treated as a string value. The query fails to return any records, thereby returning NotFound() from the action:
We have exhaustively seen the inline parameterized queries. Let us explore them using stored procedures, which are also one of the techniques in handling SQL injections. The SP execution looks similar to the parameterized inline query, still protecting the application from SQL injection. Let's tweak the system just for the purpose of illustration by adding the following action which would filter blogs by category, return the list of blog items, and finally render them in an existing Index view:
public async Task<IActionResult> BlogsByCategory(int categoryId)
{
return View("Index", await _context.Blogs.FromSql("EXEC
[dbo].[GetBlogsByCategory] @categoryId = {0}",
categoryId).ToListAsync());
}
The preceding query could be tweaked to accommodate the SqlParameter object, which streamlines the parameterized query in a more readable manner. The following would consume SqlParameter in the parameterised SQL execution:
public async Task<IActionResult> BlogsByCategory(int categoryId)
{
return View("Index", await _context.Blogs.FromSql("EXEC
[dbo].[GetBlogsByCategory] @categoryId = @Id",
new SqlParameter("id", categoryId)).ToListAsync());
}
The following screenshot displays a list of filtered blog items in the Index view; only it is processed from a different action and mainly uses parameterized queries:
We have seen parameterized inline queries or SPs so far, which satisfy the use cases in real-time applications. We will further explore leveraging them with the LINQ queries to filter/process data in the Composing with LINQ section.