Combining DROP USER and DROP DATABASE with SELECT .. WHERE query?

Question

I'd like to make a very simple thing, replicate the functionality of mysql's interactive mysql_secure_installation script. My question is that is there a simple, built-in way in MySQL to combine the output of a SELECT query with the input of a DROP user or DROP database script?

For example, if I'd like to drop all users with empty passwords. How could I do that with DROP USER statement? I know an obvious solution would be to run everything for example from a Python script,

1. run a query with mysql -Bse "select..."
2. parse the output with some program
3. construct the drop query
4. run it.

Is there an easy way to do it in a simple SQL query? I've seen some example here, but I wouldn't call it simple: https://stackoverflow.com/a/12097567/518169

Would you recommend making a combined query, or just to parse the output using for example Python or bash scripts/sed?

Answer 1

You can build SQL dynamically, for example:

SELECT CONCAT('DROP USER ', GROUP_CONCAT(QUOTE(User), '@', QUOTE(Host)))
INTO   @sql
FROM   mysql.user
WHERE  PASSWORD = ''

Then prepare a statement from it:

PREPARE stmt FROM @sql

Then execute that statement:

EXECUTE stmt

Finally, deallocate the prepared statement:

DEALLOCATE PREPARE stmt