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: L
  • User interaction: N
  • Scope: U
  • Confidentiality: N
  • Integrity: L
  • Availability: N

Temporal​

  • Exploit code madurity: U
  • Remediation level: O
  • Report confidence: R

Result​

  • Vector string: CVSS:3.1/AV:N/AC:L/PR:L/UI:N/S:U/C:N/I:L/A:N/E:U/RL:O/RC:R
  • Score:
    • Base: 4.3
    • Temporal: 3.6
  • Severity:
    • Base: Medium
    • Temporal: Low

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​

free trial

Search for vulnerabilities in your apps for free with our automated security testing! Start your 21-day free trial and discover the benefits of our Continuous Hacking Machine Plan. If you prefer a full service that includes the expertise of our ethical hackers, don't hesitate to contact us for our Continuous Hacking Squad Plan.