Someone recently told me that they don't need to worry about SQL injection because they are using an ORM.
Oh boy.
ORMs don't automatically prevent SQL injection
Watch this week's video on YouTube
Object-relational mapping (ORM) tools allow developers to easily access an application's data layer without having to write lots of redundant code.
Most ORMs will safely parameterize certain types of queries. The following examples use Entity Framework and SQL Server, but these examples should apply to most other major ORMs and RDBMSs).
Our LINQ query making it easy to access our data layer:
A beautiful, clean, LINQ query
And then the SQL query that our ORM generated.
A query structure that only a mother could love
You'll notice the generated SQL query is using sp_executesql that has parameterized our input variable value "TFly37". In this instance we can say the ORM did a good job following best practices in preventing a successful SQL injection attack.
But while ORMs may prevent some SQL injection attempts, there is no guarantee that they will prevent all injection attempts.
What follows are examples of when ORMs can allow for successful injection attacks.
Programatically assembled SQL
ORMs often provide the ability for developers to map the results of adhoc SQL queries to models. Adhoc queries mean potential injection vulnerabilities.
Consider the following:
Looks a lot like your typical SQL injection vulnerability doesn't it?
Entity Framework, or any ORM for that matter, won't be able to recognize a parameter concatenated directly into the query string.
Now hopefully the developer has some really strong input validation on the "username" parameter, but the fact still stands: this query is injectable and the ORM will happily execute it.
Stored Procedures
Does the logic needed in your app already exist as a stored procedure?
Excellent! Let's be DRY (don't-repeat-yourself) and call our procedure directly from our ORM:
Parameterized with the value AND datatype - give this developer a raise!
So in this example, the ORM and developer have done everything right. So where's the injection vulnerability?
Uh oh...
Now let me be clear: this injection vulnerability is not the ORM's fault.
However, it is the developer's fault if he is blindly using a procedure without knowing what it does. It's also the DBA's fault if she provided an injectable procedure assuming the developer would sanitize any input values in the app.
Security is hard. Everyone needs to work together and be responsible for doing everything within their means to secure their applications.
ORM injection
Technically this isn't an example of SQL injection.
But that's why the title of this post says "2.5" instead of "3".
In this example I'm using a dynamic LINQ to access my data:
Hello concatenation my old friend
If we pass the value " OR 1 == 1 OR UserName== we will have the ORM convert it to the following query:
Game over
Injection takes many forms and doesn't just come straight from SQL. It's important to follow best practices in all languages and tools to prevent security incidents.
Want to learn more?
If you are interested in learning more about how to protect yourself from SQL injection, be sure to watch my online session at GroupBy at 9am Eastern on March 16th, 2018.