Executing shell command from MySQL

Question

I know what I'm looking for is probably a security hole, but since I managed to do it in Oracle and SQL Server, I'll give it a shot:

I'm looking for a way to execute a shell command from a SQL script on MySQL. It is possible to create and use a new stored procedure if necessary.

Notice: I'm not looking for the SYSTEM command which the mysql command line tool offers. Instead I'm looking for something like this:

BEGIN IF COND1... EXEC_OS cmd1; ELSE EXEC_OS cmd2; END;

where EXEC_OS is the method to invocate my code.

Answer 1

According to this post at the forums.mysql.com, the solution is to use the MySQL_Proxy.

Answer 2

see do_system() in http://www.nextgenss.com/papers/HackproofingMySQL.pdf

Answer 3

You might want to consider writing your scripts in a more featureful scripting language, like Perl, Python, PHP, or Ruby. All of these languages have libraries to run SQL queries.

There is no built-in method in the stored procedure language for running shell commands. This is considered a bad idea, not only because it's a security hole, but because any effects of shell commands do not obey transaction isolation or rollback, as do the effects of any SQL operations you do in the stored procedure:

START TRANSACTION;
CALL MyProcedure();
ROLLBACK;

If MyProcedure did anything like create or edit a file, or send an email, etc., those operations would not roll back.

I would recommend doing your SQL work in the stored procedure, and do other work in the application that calls the stored procedure.

Answer 4

This isn't so much an answer to the question as it is justification for this sort of functionality - hence negating those who would say "you should do something else" or "why would you want to".

I have a database which I am trying to keep strict rules on - I don't want orphans anywhere. Referential integrity checks help me with this on the table level, but I have to keep some of the data as files within the filesystem (this is a result from a direct order from my boss to not store any binary data in the database itself).

The obvious solution here is to have a trigger which fires on deletion of a record, which then automatically deletes the associated external file.

Now, I do realise that UDF's may provide a solution, but that seems like a lot of C/C++ work to simply delete a file. Surely the database permissions themselves would provide at least some security from would-be assailants.

Now, I do realise that I could write a shell script or some such which could delete the table record and then go and delete the related file, but again, that's outside the domain of the database itself. As an old instructor once told me "the rules of the business should be reflected in the rules of the database". As one can clearly see, I cannot enforce this using MySQL.