Why is this an issue?
Database injections (such as SQL injections) occur in an application when the application retrieves data from a user or a third-party service and
inserts it into a database query without sanitizing it first.
If an application contains a database query that is vulnerable to injections, it is exposed to attacks that target any database where that query is
used.
A user with malicious intent carefully performs actions whose goal is to modify the existing query to change its logic to a malicious one.
After creating the malicious request, the attacker can attack the databases affected by this vulnerability without relying on any
pre-requisites.
What is the potential impact?
In the context of a web application that is vulnerable to SQL injection:
After discovering the injection, attackers inject data into the
vulnerable field to execute malicious commands in the affected databases.
Below are some real-world scenarios that illustrate some impacts of an attacker exploiting the vulnerability.
Identity spoofing and data manipulation
A malicious database query enables privilege escalation or direct data leakage from one or more databases. This threat is the most widespread
impact.
Data deletion and denial of service
The malicious query makes it possible for the attacker to delete data in the affected databases.
This threat is particularly insidious if the
attacked organization does not maintain a disaster recovery plan (DRP).
Chaining DB injections with other vulnerabilities
Attackers who exploit SQL injections rely on other vulnerabilities to maximize their profits.
Most of the time, organizations overlook some
defense in depth measures because they assume attackers cannot reach certain points in the infrastructure. This misbehavior can lead to multiple
attacks with great impact:
- When secrets are stored unencrypted in databases: Secrets can be exfiltrated and lead to compromise of other components.
- If server-side OS and/or database permissions are misconfigured, injection can lead to remote code execution (RCE).
How to fix it in Entity Framework Core
Code examples
The following code is an example of an overly simple authentication function. It is vulnerable to SQL injection because user-controlled data is
inserted directly into a query string: The application assumes that incoming data always has a specific range of characters, and ignores that some
characters may change the query logic to a malicious one.
In this particular case, the query can be exploited with the following string:
foo' OR 1=1 --
By adapting and inserting this template string into one of the fields (user or pass), an attacker would be able to log in
as any user within the scoped user table.
Noncompliant code example
public class ExampleController : Controller
{
private readonly UserAccountContext Context;
public IActionResult Authenticate(string user, string pass)
{
var query = "SELECT * FROM users WHERE user = '" + user + "' AND pass = '" + pass + "'";
var queryResults = Context
.Database
.FromSqlRaw(query);
if (queryResults == 0)
{
return Unauthorized();
}
return Ok();
}
}
Compliant solution
public class ExampleController : Controller
{
private readonly UserAccountContext Context;
public IActionResult Authenticate(string user, string pass)
{
var query = "SELECT * FROM users WHERE user = {0} AND pass = {1}";
var queryResults = Context
.Database
.FromSqlRaw(query, user, pass);
if (queryResults == 0)
{
return Unauthorized();
}
return Ok();
}
}
How does this work?
Use prepared statements
As a rule of thumb, the best approach to protect against injections is to systematically ensure that untrusted data cannot break out of an
interpreted context.
For database queries, prepared statements are a natural mechanism to achieve this due to their internal workings.
Here is an example with the
following query string (Java SE syntax):
SELECT * FROM users WHERE user = ? AND pass = ?
Note: Placeholders may take different forms, depending on the library used. For the above example, the question mark symbol '?' was used as
a placeholder.
When a prepared statement is used by an application, the database server compiles the query logic even before the application passes the literals
corresponding to the placeholders to the database.
Some libraries expose a prepareStatement function that explicitly does so, and
some do not - because they do it transparently.
The compiled code that contains the query logic also includes the placeholders: they serve as parameters.
After compilation, the query logic is frozen and cannot be changed.
So when the application passes the literals that replace the placeholders,
they are not considered application logic by the database.
Consequently, the database server prevents the dynamic literals of a prepared statement from affecting the underlying query, and thus sanitizes
them.
On the other hand, the application does not automatically sanitize third-party data (for example, user-controlled data) inserted directly into a
query. An attacker who controls this third-party data can cause the database to execute malicious code.
How to fix it in Dapper
Code examples
The following code is an example of an overly simple authentication function. It is vulnerable to SQL injection because user-controlled data is
inserted directly into a query string: The application assumes that incoming data always has a specific range of characters, and ignores that some
characters may change the query logic to a malicious one.
In this particular case, the query can be exploited with the following string:
foo' OR 1=1 --
By adapting and inserting this template string into one of the fields (user or pass), an attacker would be able to log in
as any user within the scoped user table.
Noncompliant code example
public class ExampleController : Controller
{
private readonly string ConnectionString;
public IActionResult Authenticate(string user, string pass)
{
using (var connection = new SqlConnection(ConnectionString))
{
var query = "SELECT * FROM users WHERE user = '" + use + "' AND pass = '" + pass + "'";
var result = connection.QueryFirst<User>(query);
if (result == null) {
Unauthorized();
}
}
return Ok();
}
}
Compliant solution
public class ExampleController : Controller
{
private readonly string ConnectionString;
public IActionResult Authenticate(string user, string pass)
{
using (var connection = new SqlConnection(ConnectionString))
{
var query = "SELECT * FROM users WHERE user = @UserName AND password = @Password";
var parameters = new { UserName = user, Password = pass };
var result = connection.QueryFirst<User>(query, parameters);
if (result == null) {
Unauthorized();
}
}
return Ok();
}
}
How does this work?
Use prepared statements
As a rule of thumb, the best approach to protect against injections is to systematically ensure that untrusted data cannot break out of an
interpreted context.
For database queries, prepared statements are a natural mechanism to achieve this due to their internal workings.
Here is an example with the
following query string (Java SE syntax):
SELECT * FROM users WHERE user = ? AND pass = ?
Note: Placeholders may take different forms, depending on the library used. For the above example, the question mark symbol '?' was used as
a placeholder.
When a prepared statement is used by an application, the database server compiles the query logic even before the application passes the literals
corresponding to the placeholders to the database.
Some libraries expose a prepareStatement function that explicitly does so, and
some do not - because they do it transparently.
The compiled code that contains the query logic also includes the placeholders: they serve as parameters.
After compilation, the query logic is frozen and cannot be changed.
So when the application passes the literals that replace the placeholders,
they are not considered application logic by the database.
Consequently, the database server prevents the dynamic literals of a prepared statement from affecting the underlying query, and thus sanitizes
them.
On the other hand, the application does not automatically sanitize third-party data (for example, user-controlled data) inserted directly into a
query. An attacker who controls this third-party data can cause the database to execute malicious code.
Resources
Articles & blog posts
Standards
