CODEWITHSUNDEEP
spring-bootjpasql-injectionjpql
Andyally
Andyally

Reputation: 1069

Prevent JPQL query sql injection

I was advised that below query is not safe as parameter :searchFor coming from input field in front end can be used for SQL injection. Plase advise what is the best solution to prevent SQL injection in below code?

@Query("SELECT u FROM User u WHERE lower(u.username) LIKE %:searchFor% " +
        " OR lower(concat(u.firstname, ' ', u.lastname)) LIKE %:searchFor% " +
        " OR lower(u.email) LIKE %:searchFor%")
Page<User> findAllAndSearch(@Param(value = "searchFor") String searchFor, Pageable pageable);

I am not using "+" to join strings, but provide parameter instead (:searchFor). Not sure if this still unsafe.

Upvotes: 2

Views: 2114

Answers (2)

luboskrnac
luboskrnac

Reputation: 24561

I believe the underlying ORM framework would sanitise input if passed as parameter this way.

Upvotes: 2

davidxxx
davidxxx

Reputation: 131396

I was advised that below query is not safe as parameter :searchFor

You should challenge this advise.

SQL injections occur when the parameter value passed by the client may transmit additional query logic (generally undesirable) and that that will be allowed in the executed query such as.
For example instead of a simple foo textual value for searchFor, the parameter value could contain also additional logic in the query, for example : foo OR ''=''. That is SQL injection.

In your case, SQL injection is not possible because you don't set the parameter manually but rather you rely on a safe way to bind thesearchFor parameter : Spring.
Indeed, Spring binds the parameter values in a safe way as JPA implementations do, that is setting the parameter value from a JPA Query instance that is protected against SQL injections for declared parameters.

For example take this query (I removed the % part to simplify) : 

"SELECT u FROM User u WHERE lower(u.username) LIKE :searchFor"

And try to set the param of searchFor with the String "foo OR ''==''" to try to inject a SQL condition that is always true.
If you turn on the log of your JPA implementation to output the paramaters binding (for Hibernate : logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE) you could see something like :

TRACE 11012 --- [nio-8080-exec-8] o.h.type.descriptor.sql.BasicBinder : binding parameter [1] as [VARCHAR] - [foo OR ''=='']

The binding is only performed for the parameter value and not as a way to add new query logic. The final query part protected is so protected as : 

SELECT u FROM User u WHERE lower(u.username) LIKE "foo OR ''==''"

Upvotes: 6

Related Questions