SQL injection even after using jpa named parameter-CodePudding

My JPA method looks like

@Query(value="select em from Employee em where em.firstName like %:text% or em.lastName like %:text%")
Employee findBySearchText(String text);

As I am using named parameter there wouldn't be any risk of SQL injection.

But in checkmarx tool it is marked as a high vulnerability and suggesting to use OWASP esapi library to sanitize the input.

Is there any other way to fix this.

CodePudding user response：

Parameter values are bound as JDBC parameters, so there is no SQL injection possible here. The only thing you might want to sanitize, is the special wildcard characters ? and %. For this purpose, you can specify an escape character and prefix special characters in the parameter value with that escape character.

You could use something like this, which is safe and also more correct (your original HQL probably only works by accident):

default Employee findBySearchText(String text) {
  if (text == null || text.isBlank()) return null;
  String pattern = "%"   text.replace("\\", "\\\\").replace("?", "\\?").replace("%", "\\%")   "%";
  return findBySearchTextInternal(pattern);
}

@Query(value="select em from Employee em where em.firstName like :text escape '\' or em.lastName like :text escape '\'")
Employee findBySearchTextInternal(String text);

CodePudding user response：

Do some validations before sending the text to your database.

SQL injection can't inject SQL if it doesn't get to the database - Barack Obama, circa 2014

First names usually don't have semi-colons ;. If the text contains it, reject it and show an error message.

Same with / or \. Names usually don't contain that. Names usually don't have the sign as well. Those are the most common things that can be used for injection.