Possible Vulnerability to SQL Injection in ReviewDao.java

[fkhan000]

In our project, we want users to be able to see the list of reviews posted by a user, belonging to a course, or associated with a professor. Because the list can be very long, we decided to also limit the number of reviews shown per page and we also wanted to give users the option to sort the list of reviews by properties such as net_likes or the date they were created and in either ascending or descending order.

So if a user wanted the top 15 reviews by likes associated with the course with course_id 10000, the sql statement would be:

SELECT * FROM Reviews WHERE course_id = 10000 ORDER BY net_likes DESC LIMIT 15 OFFSET 0

Unfortunately, there doesn't seem to be a way to replace column names in the java sql PreparedStatement classes: https://stackoverflow.com/questions/3135973/variable-column-names-using-prepared-statements. So in order to prevent having to write multiple variations of the above sql statement, I made a single sql statement in the ReviewDao.java file as can be seen below.

Then when executing the statement (same file), I replaced words such as source, order_by, and direction with their corresponding column names.

Then in our springboot server, the url for reaching the pages where the reviews are listed contains the arguments for the sql command.

This could make our website vulnerable to a sql injection attack as someone could change the url and access data that they shouldn't have had access to. For example, a user could replace source and id with 1 and be able to see all of the reviews in our database. Luckily because we limit the number of reviews per page, this wouldn't cause a significant strain on our server and while this is data that the user in theory has access to, they should not have been able to reach this page.

[fkhan000]

This is no longer an issue since from the frontend the user wouldn't be able to mess with the endpoint like that.