Prompt to run certain UPDATE/DELETE queries

Just an idea to throw out there for consideration...

I've got one employee who needs to have database access, but every once in a while he forgets to include a WHERE in an UPDATE or DELETE statement, affecting the entire table. An optional feature to prompt the user to run UPDATES or DELETES without WHERE clauses would be really nice so I don't have to spend time restoring backups when this happens.

There is already a MySQL server variable exactly for that purpose you are describing: "sql_safe_updates".

See more details in the documentation.

There are three issues with this:

(1) It requires the use of a column with a key in the WHERE clause. If a column referenced in the WHERE clause isn't part of a key, legitimate UPDATEs and DELETEs can't be done. So this option is much too strict for our needs.

(2) It doesn't appear to be an option that can be set in the configuration file. So it has to be set manually each time the server is restarted. I put it in my INI to try it out and the server is ignoring it. The documentation doesn't list it as a parameter that can be set in the config.

(3) It doesn't work with other databases (SQL Server, PostgreSQL).

Well I am hesitating here as HeidiSQL neither touches nor analyzes the user written SQL in any way yet. HeidiSQL is dumb in a certain way here, and it should be, as I think. But I understand there is a need for such a "newbie feature".

I could enable such an option by default. When Heidi is about to run a critical query, it could display a message box with a checkbox allowing to hide that the next time (similar to the message box which asks for saving unsaved query tab contents). An additional checkbox in the preferences dialog allows the user to activate it again.

That implementation sounds perfect. Most people would never even see the prompt, since most people do remember to include the WHERE clause. Such a feature would be very much appreciated.

I understand the hesitation. But I think a lot of people would benefit from this. How many entire tables have been updated or deleted when someone carelessly forgot to include the critical WHERE clause?

In my own case, one of my employees forgot to put a WHERE clause in an update to the password hash in a user account table, overwriting everyone's passwords. Our entire office couldn't log in to our support software for 6 hours while the previous night's backup was restored. (It is a large database, and all we have are mysqldump backups.)

Done in r4819.

The checkbox in the preferences dialog is still missing, so that will come later.

The SQL parser for finding UPDATEs/DELETEs without WHERE clause is probably a bit too simple. But well, I don't want HeidiSQL to be too CPU consuming here.

Did you check that already? Would be nice to get feedback from you whether this is correctly working. For me it does, but who knows.

Just checked it.... it works great. Thank you!

I don't believe that this needs to be comprehensive. A simple check for a WHERE inside of any statement that starts with DELETE or UPDATE is probably sufficient. Anybody writing queries that contain the word WHERE in them that isn't part of the base update/delete are probably going to be fine without a reminder. A full parser seems like it would be too much work with virtually no payoff. HeidiSQL isn't preventing anyone from running the query, just offering a reminder to double-check it, and it is easy enough to disable for those that don't want it.

Yeah, I was thinking the same.

I just sent a small donation as a thank you for adding this.