How do I prevent unauthorized users from deleting objects in my domain model?

Question

I got similar domain model

• 1) User. Every user got many cities. @OneToMany(targetEntity=adv.domain.City.class...)
• 2) City. Every city got many districts @OneToMany(targetEntity=adv.domain.Distinct.class)
• 3) Distintc

My goal is to delete distinct when user press delete button in browser. After that controller get id of distinct and pass it to bussiness layer. Where method DistinctService.deleteDistinct(Long distinctId) should delegate deliting to DAO layer.

So my question is where to put security restrictions and what is the best way to accomplish it. I want to be sure that i delete distinct of the real user, that is the real owner of city, and city is the real owner of distinct. So nobody exept the owner can't delete ditinct using simple url like localhost/deleteDistinct/5.

I can get user from httpSession in my controller and pass it to bussiness layer.

After that i can get all cities of this user and itrate over them to be sure, that of the citie.id == distinct.city_id and then delete distinct.

But it's rather ridiculous in my opinion.

Also i can write sql query like this ...

delete from 
  t_distinct 
where 
  t_distinct.city_id in (
    select 
      t_city.id 
     from 
       t_city 
       left join t_user on t_user.id = t_city.owner_id 
     where 
       t_user.id = ?
  ) 
  and t_distinct.id = ?

So what is the best practice to add restrictions like this.

I'm using Hibernate, Spring, Spring MVC by the way..

Thank you

Answer 1

With hibernate you don't have to worry about sql injection. It always uses prepared statements, so you are safe.

As for your concrete case, this is not an sql injection. But to prevent it, make validation in the controller - whether the currently logged user owns the desired ID.

Depending on the size of the application, you can implement some general security scheme, with ownership settings, and apply it (using AOP).

Answer 2

Just use your head, quote-escape* everything from an outside (or inside for that matter) source before it gets put in an SQL statement, and check data as it goes in. Or, use prepared statements.

*Edit: By "quote-escape" I meant functions like PHP's mysql_escape_string()

Answer 3

What you're asking for is not SQL Injection prevention. You need to ensure the user attempting the deletion is authorized.

As long as you check that the user accessing the page has the rights to delete the row your trying to delete (this would be checked in the Business layer), and ONLY allow the delete command if the user is authenticated and authorized to perform the action.

Answer 4

I understand that i want to be sure, the the user is real owner of Book The question was how to accomplish it. And yes, i know that user is authenticated and authorized. But another authorized user can easy delete pages of another user.

This can be done like this...

User userFromHttpSession ... Long bookId = load page, get bookId, load book, get bookId

List books = userFromHttpSession.getBooks(); ... iterate over books and find out if one of the book.id == bookId ... then if book owner is owner of httpSession, then proceed Delete

It's like too many sql queries, and too many code, probably there are better solution. Anyway thank you for your answers