Skip to main content

SQL injection - C Sharp SQL API

Description

Dynamic SQL statements are generated without the required data validation and without using parameterized statements or stored procedures.

Impact

Inject SQL statements, with the possibility of obtaining information about the database, as well as extract information from it.

Recommendation

Perform queries to the database through sentences or parameterized procedures.

Threat

Authenticated attacker from the Internet.

Expected Remediation Time

โŒš 15 minutes.

Score

Default score using CVSS 3.1. It may change depending on the context of the vulnerability.

Base

  • Attack vector: N
  • Attack complexity: L
  • Privileges required: N
  • User interaction: N
  • Scope: U
  • Confidentiality: N
  • Integrity: N
  • Availability: N

Temporal

  • Exploit code madurity: X
  • Remediation level: X
  • Report confidence: X

Result

  • Vector string: CVSS:3.1/AV:N/AC:L/PR:N/UI:N/S:U/C:N/I:N/A:N/E:X/RL:X/RC:X
  • Score:
    • Base: 0.0
    • Temporal: 0.0
  • Severity:
    • Base: None
    • Temporal: None

Code Examples

Compliant code

The application should use parameterized queries to avoid SQL injections

using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using WebApplication1.Controllers;

namespace WebApplicationDotNetCore.Controllers{
public class RSPEC3649SQLiNoncompliant{
public IActionResult Authenticate(UserAccountContext context, string user){
string secure_query = "UPDATE Sales.Store SET Demographics = @demographics " + "WHERE CustomerID = @ID;";
secure_obj = new SqlCommand(secure_query);
secure_methd = sqcontext.Database.ExecuteSqlCommand(secure_query);
}
}
}

Non compliant code

An open query that could get injected to get sensitive information

using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using WebApplication1.Controllers;

namespace WebApplicationDotNetCore.Controllers{
public class RSPEC3649SQLiNoncompliant{
public IActionResult Authenticate(UserAccountContext context, string user){
//This query does not validate input data
string insecure_query = "SELECT * FROM Users WHERE Username = user;
insecure_obj = new SqlCommand(insecure_query);
insecure_methd = sqcontext.Database.ExecuteSqlCommand(insecure_query);
}
}
}

Details

In Spring Data JPA framework you can create SQL queries in many ways:

public interface UserRepository extends JpaRepository<User, Long> {
// Using Java Persistence Query Language (JPQL)
@Query("select u from User u where u.emailAddress = ?1")
List<User> findByEmailAddress1(String emailAddress);

// Using Spring Expression Language (SpEL)
@Query("select u from User u where u.emailAddress = :#{[0]}")
List<User> findByEmailAddress2(@Param("emailAddress") String emailAddress);
}

@Entity
// Using named queries
@NamedQuery(
name = "User.findByEmailAddress3",
query = "select u from User u where u.emailAddress = ?1"
)
public class User { /* ... */ }

Abuse cases

No framework is silver-bullet, though.

Java Persistence Query Language

By default, the JPQL engine will escape the following user-supplied input parameters

  • JPQL Positional Parameters: ?1
  • JPQL Named Parameters: :emailAddress

In other words, emailAddress will be interpreted by the SQL engine as a string literal, with possible special characters in a SQL context escaped.

However, if you write a JPQL query like this one:

@Query("select u from User u where u.emailAddress like %?1")
User findByEmailAddress(String emailAddress);

Notice the % we added in front of the JPQL positional parameter ?1.

An attacker that manages to supply an emailAddress equal to a will fetch all email addresses from the database that end with the letter a. The resulting query will be:

SELECT u FROM User u WHERE u.emailAddress LIKE '%a'

We highly recommend you avoid mixing

  • LIKE conditions (or similar in its kind)
  • hard-coded special wildcard characters
  • user-supplied input

As noted in the example, sanitizing is no solution at all. The problem is having a hard-coded % in the JPQL statement.

Spring Expression Language

By default, SpEL Expressions are not escaped. This happens because SpEL is designed as an expression language, not a SQL language.

In other words SpEL Expressions Bindings like :#{[0]} or ?#{[0]} will just copy the value of [0] into the SQL operation to be executed by the database.

If you write a SpEL query like this one:

@Query("select u from User u where u.emailAddress = :#{[0]}")
List<User> findByEmailAddress2(@Param("emailAddress") String emailAddress);

An attacker that manages to supply an emailAddress equal to %, will fetch all email addresses from the database. The evaluated query will be

SELECT u FROM User u WHERE u.emailAddress LIKE '%'

We highly recommend you to use the escape function from SpEL context as follow:

@Query(
"select u from User u " +
"where u.emailAddress like ?#{escape([0])} " +
"escape ?#{escapeCharacter()}"
)
List<User> findByEmailAddress2(@Param("emailAddress") String emailAddress);

In this case the evaluated query would be:

SELECT u FROM User u WHERE u.emailAddress LIKE '\%' ESCAPE '\'

Requirements