0

I'm trying to get specific rows from the table by ids, but what I have below is not working.

@Query(value = "SELECT * FROM row r where r.row_id = :row_ids", nativeQuery = true)
    List<Object> temp(@Param("albumsIds") String row_ids);

row_ids is all the ids separated by an "or" - id:1 or id:2 or id:3

I'm just trying to do select * from row r where r.row_id = id:1, or r.row_id = id:2, or r.row_id = id:3

Does anyone have an idea what the problem is, or is there a better way to do it?

Daniel Morales
  • 33
  • 1
  • 7
  • `row_ids is all the ids separated by an "or" - id:1 or id:2 or id:3` This is a problem. You don't want to enable SQL injection. – adnan_e Jun 06 '22 at 07:51
  • I notice you're not a fan of marking answers as correct. It's only fair you mark answers as correct if people are devoting their own time to answer YOUR questions, for free I might add. – Alex Ander Aug 24 '22 at 12:52

1 Answers1

2

Simply use:

List<Object> findByIdIn(Collection<Integer> ids);

That will automatically setup the derived query from the query method name itself.

Alternatively, if you want to provide the query programmatically, then the query should be:

@Query(value = "SELECT * FROM row R WHERE R.row_id IN :ids", nativeQuery = true)
List<Object> temp(@Param("ids") Collection<Integer> ids);

MySQL lets use use the IN keyword in queries, which let's us provide a CSV for the IDs to parse and returns any records which ID is in the CSV. This obviously can be used on any data, just for this purpose we'll use the IDs as an example.

Using the method above should minimize the risk of SQL injection significantly, as the Java Collection type casting shouldn't allow a user to provide any values that can cause issues.

Alex Ander
  • 1,430
  • 12
  • 19