When members delete their posts, which is better?

  node.js, question

For example, a member with id 100 wants to delete a post with id 1000.
There are two deletion schemes
1.SELECT user_id FROM post WHERE id = 1000
Then it is determined whether the user_id is 100. if yes, it is deleted
2.DELETE FROM post WHERE id=1000 AND user_id=100
Efficiency is definitely the second highest, but the first is more flexible.
If the user_id is not 100, then the member with id 100 is not a good member, and then make a log.

Which do you usually use?

The main demand is very clear:

  1. The deletion request requires verification and authentication

  2. Verification: The requestor is any legal user in the system

  3. Authentication: Require the user_ID of the post to be equal to the user id of the requestor

  4. Let’s assume that this step of verification is completed and only the issue of authentication will be discussed here.

In this sense, analyze the two schemes:

Programme 1

“SELECT)– Data-Check Data (Do Authentication)-Execute Request (DELETE)”, the process itself is correct. In fact, this plan is also more respected. Because it can:

  1. If the request fails at a certain step midway, the reason for failure can be accurately identified.

  2. Keep more flexibility. If the authentication logic is more complicated in the future, it can be added more conveniently.

Only need to pay attention to is to prevent this processAtomicityDestroyed, for example:

  1. Program execution SELECT

  2. Other requests performed UPDATE, and this record was changed, causing authentication to have failed (e.g. the corresponding entry was transferred to another user)

  3. The program still authenticates with the record before modification, and the authentication that should have failed at this time can succeed.

  4. Program Error Executes Delete Operation

You may need some means such as database transactions to ensure that the records involved will not be accidentally rewritten for other reasons before all steps of this request are completed.

Option 2

Scheme 2: Two steps of authentication and deletion are completed in one statement. There are no problems in security and atomicity. Success is success, failure is complete failure, and there will be no inconsistency in data.

However, this method has a fatal flaw in the principle of API design: if there is an error, then you can only know that 0 lines have been deleted, whileIt is not recognized whether the record does not exist or whether the user does not have permission to delete the record.. The background can only return “processing request error” in a general way, even if it returns HTTP 400 or HTTP 403, it cannot be determined.

The request itself is invalid and the user does not have permission, which are two completely different things. Generally, there are great differences in error prompt and subsequent logic.If the backstage did such a foolish design, it would make the front end unable to be a human being.

Please don’t make such a design.

Just a few words

In addition, to some respondents: all front-end measures to block users from making requests are futile! You can’t stop:

  • [Deleted Content 1]

  • The user has the right to open the page, and then the right is withdrawn for some reason, but the page is still displayed on the user’s browser.

It is absolutely impossible to assume that the user’s input is reliable., the background design of this principle must not cross the line step by step! There is no excuse!

Some basic concepts

  1. To judge whether an action can be executed, one must pass the two passes of “authentication” and “authorization”. “Verification” checks whether the operator is the user he claims to be; “Authentication” indicates whether the user has the right to perform an action.

  2. Both are indispensable. In practice, the “authentication” of some actions is very simple or even non-existent, but it must be deliberately designed by the programmer after thinking, so as to be acceptable, and it is absolutely not allowed to ignore the intention subjectively.

  3. “Verification” and “authentication” must be prior and cannot be investigated later. (The cost of rolling back after data destruction is very high! )

Last but not least:This is a very good idea for the subject to log the requests that fail authentication.. In fact, users who repeatedly fail authentication are more likely to be “chickens” whose numbers have been stolen by hackers without knowing them, rather than attackers.

By recording the authentication failure, the user’s suspicious data can be accumulated. After the user’s suspiciousness is raised to a certain degree, the user is temporarily blocked, and the user is forced to identify the mobile phone/secret protection problem to unseal the account and reset the password. This is a very meaningful way to improve the security of the account system.

[Deleted Content 1] = “Attackers use automated browser tools (Selenium, etc.) to make requests-you cannot prevent this even if you are on CSRF.”
CSRF’s failure to prevent illegal requests is untenable, thanks to @shenyangyeshuai.