UnionBasedSQLInjectionVulnerability.java

package org.sasanlabs.service.vulnerability.sqlInjection;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Map;
import java.util.Optional;
import javax.persistence.EntityManager;
import javax.persistence.TypedQuery;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Root;
import org.sasanlabs.internal.utility.LevelConstants;
import org.sasanlabs.internal.utility.Variant;
import org.sasanlabs.internal.utility.annotations.AttackVector;
import org.sasanlabs.internal.utility.annotations.VulnerableAppRequestMapping;
import org.sasanlabs.internal.utility.annotations.VulnerableAppRestController;
import org.sasanlabs.vulnerability.types.VulnerabilityType;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.web.bind.annotation.RequestParam;

/**
 * Union Based SQL Injection is another dangerous way to extract data from the database by combining
 * results of multiple queries. This is the second way which is generally tried by the hackers after
 * {@link ErrorBasedSQLInjectionVulnerability}
 *
 * @author preetkaran20@gmail.com KSASAN
 */
@VulnerableAppRestController(
        descriptionLabel = "SQL_INJECTION_VULNERABILITY",
        value = "UnionBasedSQLInjectionVulnerability")
public class UnionBasedSQLInjectionVulnerability {

    private final JdbcTemplate applicationJdbcTemplate;
    private final NamedParameterJdbcTemplate namedParameterJdbcTemplate;
    private final CarInformationRepository carInformationRepository;
    private final EntityManager entityManager;

    public UnionBasedSQLInjectionVulnerability(
            @Qualifier("applicationJdbcTemplate") final JdbcTemplate applicationJdbcTemplate,
            NamedParameterJdbcTemplate namedParameterJdbcTemplate,
            CarInformationRepository carInformationRepository,
            EntityManager entityManager) {
        this.applicationJdbcTemplate = applicationJdbcTemplate;
        this.namedParameterJdbcTemplate = namedParameterJdbcTemplate;
        this.carInformationRepository = carInformationRepository;
        this.entityManager = entityManager;
    }

    @AttackVector(
            vulnerabilityExposed = VulnerabilityType.UNION_BASED_SQL_INJECTION,
            description = "UNION_SQL_INJECTION_URL_PARAM_APPENDED_DIRECTLY_TO_QUERY",
            payload = "UNION_BASED_SQL_INJECTION_PAYLOAD_LEVEL_1")
    @VulnerableAppRequestMapping(
            value = LevelConstants.LEVEL_1,
            htmlTemplate = "LEVEL_1/SQLInjection_Level1")
    public ResponseEntity<CarInformation> getCarInformationLevel1(
            @RequestParam final Map<String, String> queryParams) {
        final String id = queryParams.get("id");
        return applicationJdbcTemplate.query(
                "select * from cars where id=" + id, this::resultSetToResponse);
    }

    @AttackVector(
            vulnerabilityExposed = VulnerabilityType.UNION_BASED_SQL_INJECTION,
            description =
                    "UNION_SQL_INJECTION_URL_PARAM_WRAPPED_WITH_SINGLE_QUOTE_APPENDED_TO_QUERY",
            payload = "UNION_BASED_SQL_INJECTION_PAYLOAD_LEVEL_2")
    @VulnerableAppRequestMapping(
            value = LevelConstants.LEVEL_2,
            htmlTemplate = "LEVEL_1/SQLInjection_Level1")
    public ResponseEntity<CarInformation> getCarInformationLevel2(
            @RequestParam final Map<String, String> queryParams) {
        final String id = queryParams.get("id");
        return applicationJdbcTemplate.query(
                "select * from cars where id='" + id + "'", this::resultSetToResponse);
    }

    @AttackVector(
            vulnerabilityExposed = VulnerabilityType.UNION_BASED_SQL_INJECTION,
            description =
                    "UNION_SQL_INJECTION_URL_PARAM_REMOVES_SINGLE_QUOTE_WITH_SINGLE_QUOTE_APPENDED_TO_QUERY")
    @VulnerableAppRequestMapping(
            value = LevelConstants.LEVEL_3,
            variant = Variant.SECURE,
            htmlTemplate = "LEVEL_1/SQLInjection_Level1")
    public ResponseEntity<CarInformation> getCarInformationLevel3(
            @RequestParam final Map<String, String> queryParams) {
        final String id = queryParams.get("id").replaceAll("'", "");
        return applicationJdbcTemplate.query(
                "select * from cars where id='" + id + "'", this::resultSetToResponse);
    }

    @VulnerableAppRequestMapping(
            value = LevelConstants.LEVEL_4,
            variant = Variant.SECURE,
            htmlTemplate = "LEVEL_1/SQLInjection_Level1")
    public ResponseEntity<CarInformation> getCarInformationLevel4(
            @RequestParam final Map<String, String> queryParams) {
        final String id = queryParams.get("id");

        return applicationJdbcTemplate.query(
                "select * from cars where id=?",
                prepareStatement -> prepareStatement.setString(1, id),
                this::resultSetToResponse);
    }

    @VulnerableAppRequestMapping(
            value = LevelConstants.LEVEL_5,
            variant = Variant.SECURE,
            htmlTemplate = "LEVEL_1/SQLInjection_Level1")
    public ResponseEntity<CarInformation> getCarInformationLevel5(
            @RequestParam final Map<String, String> queryParams) {
        final String id = queryParams.get("id");
        SqlParameterSource namedParameters = new MapSqlParameterSource().addValue("id", id);
        CarInformation s =
                namedParameterJdbcTemplate.queryForObject(
                        "select * from cars where id=:id",
                        namedParameters,
                        new BeanPropertyRowMapper<>(CarInformation.class));
        return new ResponseEntity<>(s, HttpStatus.OK);
    }

    @VulnerableAppRequestMapping(
            value = LevelConstants.LEVEL_6,
            variant = Variant.SECURE,
            htmlTemplate = "LEVEL_1/SQLInjection_Level1")
    public ResponseEntity<CarInformation> getCarInformationLevel6(
            @RequestParam final Map<String, String> queryParams) {
        final String id = queryParams.get("id");
        String jql = "from CarInformation where id = :id";
        TypedQuery<CarInformation> q =
                entityManager
                        .createQuery(jql, CarInformation.class)
                        .setParameter("id", Integer.valueOf(id));
        return new ResponseEntity<>(q.getSingleResult(), HttpStatus.OK);
    }

    @VulnerableAppRequestMapping(
            value = LevelConstants.LEVEL_7,
            variant = Variant.SECURE,
            htmlTemplate = "LEVEL_1/SQLInjection_Level1")
    public ResponseEntity<CarInformation> getCarInformationLevel7(
            @RequestParam final Map<String, String> queryParams) {
        final String id = queryParams.get("id");

        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        CriteriaQuery<CarInformation> cq = cb.createQuery(CarInformation.class);
        Root<CarInformation> root = cq.from(CarInformation.class);

        cq.select(root).where(cb.equal(root.get("id"), id));

        TypedQuery<CarInformation> q = entityManager.createQuery(cq);
        return new ResponseEntity<>(q.getSingleResult(), HttpStatus.OK);
    }

    @VulnerableAppRequestMapping(
            value = LevelConstants.LEVEL_8,
            variant = Variant.SECURE,
            htmlTemplate = "LEVEL_1/SQLInjection_Level1")
    public ResponseEntity<CarInformation> getCarInformationLevel8(
            @RequestParam final Map<String, String> queryParams) {
        final String id = queryParams.get("id");
        TypedQuery<CarInformation> q =
                entityManager
                        .createNamedQuery("findById", CarInformation.class)
                        .setParameter("id", Integer.valueOf(id));
        return new ResponseEntity<>(q.getSingleResult(), HttpStatus.OK);
    }

    @VulnerableAppRequestMapping(
            value = LevelConstants.LEVEL_9,
            variant = Variant.SECURE,
            htmlTemplate = "LEVEL_1/SQLInjection_Level1")
    public ResponseEntity<CarInformation> getCarInformationLevel9(
            @RequestParam final Map<String, String> queryParams) {
        final String id = queryParams.get("id");
        Optional<CarInformation> carInformation =
                carInformationRepository.findById(Integer.valueOf(id));
        return carInformation
                .map(information -> new ResponseEntity<>(information, HttpStatus.OK))
                .orElseGet(() -> new ResponseEntity<>(HttpStatus.NOT_FOUND));
    }

    private ResponseEntity<CarInformation> resultSetToResponse(final ResultSet rs)
            throws SQLException {
        final CarInformation carInformation = new CarInformation();
        if (rs.next()) {
            carInformation.setId(rs.getInt(1));
            carInformation.setName(rs.getString(2));
            carInformation.setImagePath(rs.getString(3));
        }
        return new ResponseEntity<>(carInformation, HttpStatus.OK);
    }
}