How to use foreign keys to prevent application users from modifying SQL-CodePudding

Let's say you have SQL tables with foreign key relationships.

Users: id, has_many posts

Posts: id, has_many paragraphs

Paragraph: id, String text

Maybe you have SQL queries to view a post by the post's ID or view a paragraph by a paragraph's id. Or maybe your SQL schema is more complicated than this, but there's still clear ownership of resources by a user with a specific ID.

Given the ID of a user, is there any way in SQL to validate that SQL reads and writes can only be done for resources that are "owned" by that user ID. You can imagine writing functions like:

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

You can imagine the SQL schema can get really complicated and these become really annoying to write. Is there a built in SQL solution to this? I'm using SQLite right now, 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.

CodePudding user response：

If a user is logged in then often that involves them having a session established between their computer and the server. The server can assign session variables to each session which will be specic to each user. The users can not see these variables.

If you assign the user's id (uid) to a session variable when they log in, then for any SQL queries you construct you can use this in the WHERE condition. For example:

SELECT name, hair_color, etc FROM my_table WHERE uid = :uid

Here :uid indicates the uid session variable.

The implementation details are specific to each language but I think this is a common way of keeping all users' data only available to that user and only when they are logged into the site.