Should I store credentials (like email, hashed password) in a separate table than the user's profile information (bio, gender, etc)?
My main concern is that I'll be sending other user's information (that doesn't include credentials) to the front-end for the user to see.
CodePudding user response：
There are usually other more important factors for data security, but main reason to consider storing credentials in a separate table (to other profile info) is to reduce the chances that you accidentally expose these fields in a way that the identity critical information can be accessed or modified through the same CRUD mechanisms that you use to expose the other profile information.
Splitting a table like this is one way to protect against over-post attacks, or to mitigate the chances of users or developers executing
SELET *which could leave the critical data in a vulnerable memory space or worse, being transferred in plain text over HTTP.SQL Injection should be mitigated through the use of an ORM or at least by using parameterized queries, do not split the table to prevent SQL Injection.
If you are using code generation tools or ORMs you might find it easier to isolate or remove a whole table from the schema instead of masking or removing individual fields, it you remove the critical information from the business model schema then you will need to manually code a pathway that allows you to update/verify password hashes or change assigned user identity.
My main concern is that I'll be sending other user's information
Good data storage practises are not the same as good data access practises. Storing data across different tables doesn't immediately protect you much if your API still allows for navigation to related records.
To decide if you should store this data in a separate table requires some understanding and analysis of your data access strategy. If your current strategy is for the UI to SELECT * FROM TableX and manage most of the CRUD operations from the UI directly, then splitting this data across multiple tables is a better default strategy than doing nothing at all, but on its own this really just ignoring your security responsibility.
There are other strategies that can be used but their implementation and effectiveness will depend greatly on the data access code or pattern you choose to implement.