Skip to content

Prevent entire update/delete into specific table

Overview

When update/delete, there can be easily met programming errors to update/delete entire tables.

This can be create various problems:

  • Avoid delete an entire table which can be from acccidentally executing in browser (or client).

  • Wrong update en entire table based on subscript the programming values.

Concepts

This action block the action of UPDATE or DELETE in an entire table. This makes it possible to catch UPDATE and DELETE statements where keys are not used properly and that would probably change or delete a large number of rows

This can be implement both in server side or client side.

Implementation

Database: MySQL

MySQL has a server system variable called sql_safe_updates that handle this concept to force provide a key in the where clause or limit for update and delete. Despite the name, it applies to both updates and deletes.

If this variable is enabled, UPDATE and DELETE statements that do not use a key in the WHERE clause or a LIMIT clause produce an error. This feature is OFF by default so you can set in the global variable. This can also be set in the MySQL client.

For the mysql client, sql_safe_updates can be enabled by using the --safe-updates option. For more information, see Using Safe-Updates Mode (--safe-updates).

Database: Postgres

For Postgres, this can archived by using extension of pg-safeupdate

For the introduction from the extension

pg-safeupdate is a PostgreSQL extension designed to prevent users from accidentally updating or deleting too many records in a single statement by requiring a "where" clause in all update and delete statements.

See more: Supabase - Guide database extension pg-safeupdate

For more implement tips, go go: Usage for safeupdate

Reference