I have a Springboot application which uses Spring Data JPA module for database operations. When we scan the code, checkmarx is reporting lot of high&medium rated issues w.r.t SQL_Injection attacks. Following is one of the use cases, I need help in whether to mark the issue as False-Positive or not. If it is NOT False-Positive what should I do to fix the issue.?

AppController.Java

@Controller
public class AppController
{
    private static final Logger logger = LoggerFactory.getLogger(AppController.class);

    @Autowired
    private AppService appService;
    
    @RequestMapping(value = "/propertiesHistory", method = RequestMethod.POST)
    public String getPropertiesHistory(@ModelAttribute("propSearchForm") @Validated PropertiesSearch propertiesSearch, BindingResult result, Model model, final RedirectAttributes redirectAttributes)
    {
        String instanceName = propertiesSearch.getInstanceName();
        
        if (!propertiesSearch.getInstanceName().equalsIgnoreCase("NONE"))
        {
            List<String> propVersionDates = appService.getPropertyHistoryDates(instanceName);
            //Some Businees Logic
        }

        if (result.hasErrors())
        {
            logger.warn("getPropertiesHistory() : Binding error - "   result.getAllErrors());
        }
        else
        {
            //Some Businees Logic
        }
        return "app/prophist";
    }
}

AppService.java

@Service
public class AppService
{
    private static final Logger logger = LoggerFactory.getLogger(AppService.class);
    
    @Autowired
    private AppRepository appRepository;
    
    public List<String> getPropertyHistoryDates(String instanceName)
    {
        List<String> list = new ArrayList<String>();
        try
        {
            list = appRepository.findAllMDateDESCByProNotEmptyAndInstanceName(instanceName);
        }
        catch (Exception e)
        {
            logger.error("getPropertyHistoryDates(): Error while fetching data from database - ", e);
        }
        
        return list;
    }
}

AppRepository.java

public interface AppRepository extends JpaRepository<AppDataEntity, Long>
{
    @Query(value="SELECT mdate FROM tablexyz WHERE properties IS NOT NULL AND instanceName =:instanceName ORDER BY mdate DESC",nativeQuery=true)
    List<String> findAllMDateDESCByProNotEmptyAndInstanceName(@Param("instanceName") String instanceName);
}

I also have some methods like List<AppDataEntity> findAllByInstanceName(String instanceName); in the repository which makes use of Proxy class implementation but not the native query. In such cases also I am getting this Checkmarx issue - SQL_Injection.

I read that Spring Data doesn't change the way Hibernate works with entities as per the accepted answer here. Is it true and applicable for @Query(value="some query",nativeQuery=true).?

CodePudding user response：

As long as you use placeholders (:instanceName) instead of custom SQL with appended parameters your SQLs are not vulnerable for SQL injection.

Most probably you will be using Hibernate implementation underneath Spring Data JPA interfaces so it is taken care as PreparedStatements are used.

I have experience running Checkmarx on some of the repositories I used to code and it gives a lot of False Positives when it comes to SQL Injection and XSS in REST APIs. The reason being it can not see context or libraries used within the project.

It reported a lot of SQL Injection vulnerabilities for us even though we have used Spring JDBC Template which uses PreparedStatements heavily.

CodePudding user response：

SpringBoot Data JPA Repository is SAFE against SQL_Injection attacks as long as if we have named or indexed/positional parameters in @Query(JPQL) or @Query(nativeQuery).

Yes, for the below question. It is applicable for @Query() only condition is that the query should have either named (:paramname) or positional (?1) parameters.

I read that Spring Data doesn't change the way Hibernate works with entities as per the accepted answer here. Is it true and applicable for @Query(value="some query",nativeQuery=true).?

The following is SAFE against sql injection.

@Query(value="SELECT mdate FROM tablexyz WHERE properties IS NOT NULL AND instanceName =:instanceName ORDER BY mdate DESC",nativeQuery=true)
    List<String> findAllMDateDESCByProNotEmptyAndInstanceName(@Param("instanceName") String instanceName);

But the following is NOT SAFE against SQL_Injection as there is a string concatination of the parameter that cannot be escaped.

@Query(value="SELECT mdate FROM tablexyz WHERE properties IS NOT NULL AND instanceName = " instanceName " ORDER BY mdate DESC",nativeQuery=true)
    List<String> findAllMDateDESCByProNotEmptyAndInstanceName(String instanceName);

Simple reason is that Spring Data JPA uses hibernate behind the scenes which in turn uses PreparedStatements way to deal with database.

A very detailed explanation on how PreparedStatements way is protecting our application from SQL_Injection attacks can be found here: