How to prevent users modifying foreign keys in queries?-CodePudding

I have tables with foreign key relationships:

Users:
id,
has_many posts

Posts:
id,
has_many paragraphs

Paragraph:
id,
String text

I have queries to view a post by post's id and view a paragraph by a paragraph's id. My schema is more complicated than this, but there's still clear ownership of resources by a user with specific id. Given the id of a user, is there any way in SQL to validate queries can only be done for resources owned by that user?

validateUserForPostId(postID, userID):
IF CreateSQLQuery("SELECT parent.ID FROM posts child JOIN on users on parent.ID = child.userID WHERE child.ID = ?", postID) == userID:
return true; // Valid, so we should be able to read or write post with ID = postID

validateUserForParagraphId(paragraphID):
// Similar join logic

Schema can get complicated and become annoying to write. Is there a built in SQL solution to this? I'm using SQLite but a general solution also works.

CodePudding user response：

Before answering we need to be clear about one thing: sqllite here (or any other potential database engine) is not aware that the data inside your tables is users, ownership and so on. These are bytes, and bytes only, from the perspective of your DB engine.

If you’re trying to solve that problem on the DB Engine side, the only way is to JOIN to the ownership tables on every query and filter out the data. For example, if you’re trying to read a post, instead of doing:

select
    content, timestamp, userID
from
    posts

you’d need to pass in a parameter with current user ID into the query to minimize checks:

select
    posts.content,
    posts.timestamp,
    posts.userID
from
    posts child
    join users on parent.ID = child.userID
where
    parent.ID = '?'
    and child.ID = '?'

A popular solution for making it slightly more manageable is using things like Stored Procedures (a lot database engines support them). It becomes something like:

EXEC sp_getPost “postID”, “userID”

Another approach would be creating views on the database engine side that perform neccessary joins and always expose a consistent column name (like authorUserId) to filter on.

However, for large modern applications or services it’s common to address it on the application code, and not on the DB side.