Skip to content

Commit b8e7fec

Browse files
christophstroblodrotbohm
christophstrobl
authored and
odrotbohm
committed
DATAJPA-965 - Fix potential blind SQL injection in Sort when used in combination with @query.
We now decline sort expressions that contain functions such as ORDER BY LENGTH(name) when used with repository having a String query defined via the @query annotation. Think of a query method as follows: @query("select p from Person p where LOWER(p.lastname) = LOWER(:lastname)") List<Person> findByLastname(@param("lastname") String lastname, Sort sort); Calls to findByLastname("lannister", new Sort("LENGTH(firstname)")) from now on throw an Exception indicating function calls are not allowed within the _ORDER BY_ clause. However you still can use JpaSort.unsafe("LENGTH(firstname)") to restore the behavior. Kudos to Niklas Särökaari, Joona Immonen, Arto Santala, Antti Virtanen, Michael Holopainen and Antti Ahola who brought this to our attention.
1 parent c227c67 commit b8e7fec

File tree

5 files changed

+491
-6
lines changed

5 files changed

+491
-6
lines changed

Diff for: ‎src/main/asciidoc/jpa.adoc

+36
Original file line numberDiff line numberDiff line change
@@ -303,6 +303,42 @@ public interface UserRepository extends JpaRepository<User, Long> {
303303

304304
This also works with named native queries by adding the suffix `.count` to a copy of your query. Be aware that you probably must register a result set mapping for your count query, though.
305305

306+
[[jpa.query-methods.sorting]]
307+
=== Using Sort
308+
309+
Sorting can be done be either providing a `PageRequest` or using `Sort` directly. The properties actually used within the `Order` instances of `Sort` need to match to your domain model, which means they need to resolve to either a property or an alias used within the query. The JPQL defines this as a _state_field_path_expression_.
310+
311+
[NOTE]
312+
====
313+
Using any non referenceable path expression leads to an Exception.
314+
====
315+
316+
Using `Sort` together with <<jpa.query-methods.at-query, @Query>> however allows you to sneak in non path checked `Order` instances containing _functions_ within the `ORDER BY` clause. This is possible because the `Order` is just appended to the given query string. By default we will reject any `Order` instance containing function calls, but you can use `JpaSort.unsafe` to add potentially unsafe ordering.
317+
318+
.Using Sort and JpaSort
319+
====
320+
[source, java]
321+
----
322+
public interface UserRepository extends JpaRepository<User, Long> {
323+
324+
@Query("select u from User u where u.lastname like ?1%")
325+
List<User> findByAndSort(String lastname, Sort sort);
326+
327+
@Query("select u.id, LENGTH(u.firstname) as fn_len from User u where u.lastname like ?1%")
328+
List<Object[]> findByAsArrayAndSort(String lastname, Sort sort);
329+
}
330+
331+
repo.findByAndSort("lannister", new Sort("firstname")); <1>
332+
repo.findByAndSort("stark", new Sort("LENGTH(firstname)")); <2>
333+
repo.findByAndSort("targaryen", JpaSort.unsafe("LENGTH(firstname)")); <3>
334+
repo.findByAsArrayAndSort("bolton", new Sort("fn_len")); <4>
335+
----
336+
<1> Valid `Sort` expression pointing to property in domain model.
337+
<2> Invalid `Sort` containing function call. Thows Exception.
338+
<3> Valid `Sort` containing explicitly _unsafe_ `Order`.
339+
<4> Valid `Sort` expression pointing to aliased function.
340+
====
341+
306342
[[jpa.named-parameters]]
307343
=== Using named parameters
308344

Diff for: ‎src/main/java/org/springframework/data/jpa/domain/JpaSort.java

+202-1
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
/*
2-
* Copyright 2013-2015 the original author or authors.
2+
* Copyright 2013-2016 the original author or authors.
33
*
44
* Licensed under the Apache License, Version 2.0 (the "License");
55
* you may not use this file except in compliance with the License.
@@ -32,6 +32,7 @@
3232
*
3333
* @author Thomas Darimont
3434
* @author Oliver Gierke
35+
* @author Christoph Strobl
3536
*/
3637
public class JpaSort extends Sort {
3738

@@ -83,6 +84,10 @@ private JpaSort(List<Order> orders, Direction direction, List<Path<?, ?>> paths)
8384
super(combine(orders, direction, paths));
8485
}
8586

87+
private JpaSort(List<Order> orders) {
88+
super(orders);
89+
}
90+
8691
/**
8792
* Returns a new {@link JpaSort} with the given sorting criteria added to the current one.
8893
*
@@ -117,6 +122,30 @@ public JpaSort and(Direction direction, Path<?, ?>... paths) {
117122
return new JpaSort(existing, direction, Arrays.asList(paths));
118123
}
119124

125+
/**
126+
* Returns a new {@link JpaSort} with the given sorting criteria added to the current one.
127+
*
128+
* @param direction can be {@literal null}.
129+
* @param properties must not be {@literal null} or empty.
130+
* @return
131+
*/
132+
public JpaSort andUnsafe(Direction direction, String... properties) {
133+
134+
Assert.notEmpty(properties, "Properties must not be null!");
135+
136+
List<Order> orders = new ArrayList<Order>();
137+
138+
for (Order order : this) {
139+
orders.add(order);
140+
}
141+
142+
for (String property : properties) {
143+
orders.add(new JpaOrder(direction, property));
144+
}
145+
146+
return new JpaSort(orders, direction, Collections.<Path<?, ?>> emptyList());
147+
}
148+
120149
/**
121150
* Turns the given {@link Attribute}s into {@link Path}s.
122151
*
@@ -174,6 +203,51 @@ public static <A extends Attribute<T, S>, T, S> Path<T, S> path(A attribute) {
174203
return new Path<T, S>(Arrays.asList(attribute));
175204
}
176205

206+
/**
207+
* Creates new unsafe {@link JpaSort} based on given properties.
208+
*
209+
* @param properties must not be {@literal null} or empty.
210+
* @return
211+
*/
212+
public static JpaSort unsafe(String... properties) {
213+
return unsafe(Sort.DEFAULT_DIRECTION, properties);
214+
}
215+
216+
/**
217+
* Creates new unsafe {@link JpaSort} based on given {@link Direction} and properties.
218+
*
219+
* @param direction must not be {@literal null}.
220+
* @param properties must not be {@literal null} or empty.
221+
* @return
222+
*/
223+
public static JpaSort unsafe(Direction direction, String... properties) {
224+
225+
Assert.notNull(direction, "Direction must not be null!");
226+
Assert.notEmpty(properties, "Properties must not be empty!");
227+
Assert.noNullElements(properties, "Properties must not contain null values!");
228+
229+
return unsafe(direction, Arrays.asList(properties));
230+
}
231+
232+
/**
233+
* Creates new unsafe {@link JpaSort} based on given {@link Direction} and properties.
234+
*
235+
* @param direction must not be {@literal null}.
236+
* @param properties must not be {@literal null} or empty.
237+
* @return
238+
*/
239+
public static JpaSort unsafe(Direction direction, List<String> properties) {
240+
241+
Assert.notEmpty(properties, "Properties must not be empty!");
242+
243+
List<Order> orders = new ArrayList<Order>();
244+
for (String property : properties) {
245+
orders.add(new JpaOrder(direction, property));
246+
}
247+
248+
return new JpaSort(orders);
249+
}
250+
177251
/**
178252
* Value object to abstract a collection of {@link Attribute}s.
179253
*
@@ -233,4 +307,131 @@ public String toString() {
233307
return builder.length() == 0 ? "" : builder.substring(0, builder.lastIndexOf("."));
234308
}
235309
}
310+
311+
/**
312+
* @author Christoph Strobl
313+
*/
314+
public static class JpaOrder extends Order {
315+
316+
private final boolean unsafe;
317+
private final boolean ignoreCase;
318+
319+
/**
320+
* Creates a new {@link JpaOrder} instance. if order is {@literal null} then order defaults to
321+
* {@link Sort#DEFAULT_DIRECTION}
322+
*
323+
* @param direction can be {@literal null}, will default to {@link Sort#DEFAULT_DIRECTION}.
324+
* @param property must not be {@literal null}.
325+
*/
326+
private JpaOrder(Direction direction, String property) {
327+
this(direction, property, NullHandling.NATIVE);
328+
}
329+
330+
/**
331+
* Creates a new {@link Order} instance. if order is {@literal null} then order defaults to
332+
* {@link Sort#DEFAULT_DIRECTION}.
333+
*
334+
* @param direction can be {@literal null}, will default to {@link Sort#DEFAULT_DIRECTION}.
335+
* @param property must not be {@literal null}.
336+
* @param nullHandlingHint can be {@literal null}, will default to {@link NullHandling#NATIVE}.
337+
*/
338+
private JpaOrder(Direction direction, String property, NullHandling nullHandlingHint) {
339+
this(direction, property, nullHandlingHint, false, true);
340+
}
341+
342+
private JpaOrder(Direction direction, String property, NullHandling nullHandling, boolean ignoreCase,
343+
boolean unsafe) {
344+
345+
super(direction, property, nullHandling);
346+
this.ignoreCase = ignoreCase;
347+
this.unsafe = unsafe;
348+
}
349+
350+
/*
351+
* (non-Javadoc)
352+
* @see org.springframework.data.domain.Sort.Order#with(org.springframework.data.domain.Sort.Direction)
353+
*/
354+
@Override
355+
public JpaOrder with(Direction order) {
356+
return new JpaOrder(order, getProperty(), getNullHandling(), isIgnoreCase(), this.unsafe);
357+
}
358+
359+
/*
360+
* (non-Javadoc)
361+
* @see org.springframework.data.domain.Sort.Order#with(org.springframework.data.domain.Sort.NullHandling)
362+
*/
363+
@Override
364+
public JpaOrder with(NullHandling nullHandling) {
365+
return new JpaOrder(getDirection(), getProperty(), nullHandling, isIgnoreCase(), this.unsafe);
366+
}
367+
368+
/*
369+
* (non-Javadoc)
370+
* @see org.springframework.data.domain.Sort.Order#nullsFirst()
371+
*/
372+
@Override
373+
public JpaOrder nullsFirst() {
374+
return with(NullHandling.NULLS_FIRST);
375+
}
376+
377+
/*
378+
* (non-Javadoc)
379+
* @see org.springframework.data.domain.Sort.Order#nullsLast()
380+
*/
381+
@Override
382+
public JpaOrder nullsLast() {
383+
return with(NullHandling.NULLS_LAST);
384+
}
385+
386+
/*
387+
* (non-Javadoc)
388+
* @see org.springframework.data.domain.Sort.Order#nullsNative()
389+
*/
390+
public JpaOrder nullsNative() {
391+
return with(NullHandling.NATIVE);
392+
}
393+
394+
/**
395+
* Creates new {@link Sort} with potentially unsafe {@link Order} instances.
396+
*
397+
* @param properties must not be {@literal null}.
398+
* @return
399+
*/
400+
public Sort withUnsafe(String... properties) {
401+
402+
Assert.notEmpty(properties, "Properties must not be empty!");
403+
Assert.noNullElements(properties, "Properties must not contain null values!");
404+
405+
List<Order> orders = new ArrayList<Order>();
406+
for (String property : properties) {
407+
orders.add(new JpaOrder(getDirection(), property, getNullHandling(), isIgnoreCase(), this.unsafe));
408+
}
409+
return new Sort(orders);
410+
}
411+
412+
/*
413+
* (non-Javadoc)
414+
* @see org.springframework.data.domain.Sort.Order#ignoreCase()
415+
*/
416+
@Override
417+
public JpaOrder ignoreCase() {
418+
return new JpaOrder(getDirection(), getProperty(), getNullHandling(), true, this.unsafe);
419+
}
420+
421+
/*
422+
* (non-Javadoc)
423+
* @see org.springframework.data.domain.Sort.Order#isIgnoreCase()
424+
*/
425+
@Override
426+
public boolean isIgnoreCase() {
427+
return super.isIgnoreCase() || ignoreCase;
428+
}
429+
430+
/**
431+
* @return true if {@link JpaOrder} created {@link #withUnsafe(String...)}.
432+
*/
433+
public boolean isUnsafe() {
434+
return unsafe;
435+
}
436+
}
236437
}

0 commit comments

Comments
 (0)