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 src.

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: C

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:C
  • Score:
    • Base: 4.3
    • Temporal: 3.8
  • Severity:
    • Base: Medium
    • Temporal: Low

Score 4.0

Default score using CVSS 4.0 . It may change depending on the context of the src.

Base 4.0

  • Attack vector: N
  • Attack complexity: L
  • Attack Requirements: N
  • Privileges required: L
  • User interaction: N
  • Confidentiality (VC): N
  • Integrity (VI): L
  • Availability (VA): N
  • Confidentiality (SC): N
  • Integrity (SI): N
  • Availability (SA): N

Threat 4.0

  • Exploit madurity: U

Result 4.0

  • Vector string: CVSS:4.0/AV:N/AC:L/AT:N/PR:L/UI:N/VC:N/VI:L/VA:N/SC:N/SI:N/SA:N/E:U
  • Score:
    • CVSS-BT: 1.3
  • Severity:
    • CVSS-BT: Low

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

Fixes

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.