Using "Safe Updates" To Prevent Unbounded UPDATE And DELETE Statements In MySQL
Earlier this week on Twitter, I was joking / venting about how I accidentally ran an UPDATE statement without a WHERE clause and it [obviously] ended-up affecting every row in the given table. In response to this tweet, Karl Petrow mentioned that "safe updates" must have been turned off. I had never heard about the concept of "safe updates", so I did a little digging. In MySQL (although this is apparently available in other databases), enabling the variable "SQL_SAFE_UPDATES" prevents the MySQL database engine from executing UPDATE and DELETE statements that don't include WHERE clauses that reference the primary key column(s). This is kind of a fascinating idea, so I wanted to try it out for myself.
The "safe updates" mode can be enabled using the following configuration options:
- --safe-updates
- --i-am-a-dummy
I do think it's kind of funny that "I am a Dummy" is an alias for the Safe Updates setting. I certainly know that this is how I felt after running my unbounded UPDATE statement. Now, I should say that this mode is intended to prevent unbounded UPDATE and DELETE statements; but, according to the documentation, this mode also takes on some other side-effects. Specifically, "safe updates" starts taking into account the following configuration options as well:
- --select_limit : The automatic limit for SELECT statements when using --safe-updates.
- --max_join_size : The automatic limit for rows in a join when using --safe-updates.
That said, I don't often have control over how the MySQL database is configured. But, I do have control over the SQL that I write and - most of the time - I do have control over the way in which the datasources are configured. As such, that's where I'm gonna do my investigation.
First, let's try enabling "safe updates" directly in our SQL using the "SQL_SAFE_UPDATES" SESSION variable:
-- Check to see if "safe update mode" is enabled / disabled for this SESSION.
-- SHOW VARIABLES LIKE 'SQL_SAFE_UPDATES';
-- Enable "safe updates" / "i am a dummy" mode for this SESSION.
SET SQL_SAFE_UPDATES = 1;
-- Try to do something ill-advised, like an update an entire table.
UPDATE
user
SET
state = 'NY'
;
NOTE: This can also be set on the GLOBAL scope using "SET GLOBAL SQL_SAFE_UPDATES = 1;"
As you can see, I am trying to run an unbounded UPDATE statement here. However, since we are enabling "SQL_SAFE_UPDATES" on the current SESSION directly beforehand, executing these statements gives us the following error:
Error : You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
That's pretty cool. But, I don't want to have to include this setting in every SQL statement that I write. Ideally, what I'd like to do is have this configured in the underlying connection string for the datasource. Unfortunately, MySQL doesn't appear to expose a "safe updates" connection option. However, it does expose a "sessionVariables" option. This option defines which "SET" statements will be executed after the database connection is established.
To experiment with this idea, I went into the ColdFusion Administrator and I added the "sessionVariables" option to the connection string of a MySQL datasource:
allowMultiQueries=true&sessionVariables=SQL_SAFE_UPDATES=1
Now, let's try to execute an unbounded UPDATE or DELETE statement using this datasource:
<!--- Executing an unbounded UPDATE on the "safe updates" datasource. --->
<cfquery name="test" datasource="testing_safe">
UPDATE
user
SET
state = 'CO'
;
</cfquery>
When we do this, using the "safe updates" datasource configuration, MySQL throws an error which ColdFusion then bubbles up to our calling context:
That's awesome! Essentially, I can put the entire datasource into the "safe updates" mode. Even if this is something I couldn't turn on in production, it seems like it would be a nice safety-net to have enabled in the local development environment. I might not need it most of the time; but, it would still be nice to get an error before I'm about to execute an unbounded UPDATE or DELETE statement.
EPILOGUE: Thoughts On Running "Safe Updates" Mode In Production
As nice as "safe updates" mode seems, I would be hesitant to enable this in production. Often times, administrators need to use the production database in ways that don't exactly mirror the local development environment (ex, running migrations, analytics, reports, etc). As such, having this enabled in production could be unnecessarily limiting. And, besides, if you have "safe updates" mode enabled locally, you should be able to catch the unbounded SQL statements during development, making the mode irrelevant in production.
Want to use code from this post? Check out the license.
Reader Comments
@All,
With regard to my thoughts on running this in production, one of my co-workers -- Johannes Hoff -- just reminded me that you could always have it turned on be default; then, if someone did need to execute an unbounded query, they could always turn it off as needed for their session.
Also, to that point, you could (and maybe should) create different datasources for different types of users (ex, reporting vs transactional use-cases). Then, you could give each a different connection string.