MySql Event Scheduler Call PHP Script

Question

I am playing around with MySql Events for the first time. Here is the event...

DELIMITER $$
CREATE EVENT testEvent
ON SCHEDULE EVERY 1 minute STARTS '2014-01-01 03:00:00' 
DO BEGIN
  /*
  INSERT INTO test(text) VALUES ('text');* <- THIS WORKS JUST FINE
  */
  SET @exec_var = sys_exec('c:\wamp\bin\php\php5.4.12\php c:\mySite\testit.php');
END $$
DELIMITER;

The sys_exec() call to php doesn't seem to work and I can't figure out why. There isn't much on the internet about this. I know the php script works because when I run c:\wamp\bin\php\php5.4.12\php c:\mySite\testit.php at the command line I get results. testit.php just does the same exact insert that is commented out in the event. Any ideas why sys_exec() isn't running my script? Or if there are errors how would I go about logging or viewing them?

(I know there are probably security concerns and other things I haven't thought of yet. This is just initial proof of concept stuff. But if you see any reason why I shouldn't go down this path and use a PHP daemon instead I'd be interested in reasons why. This way just seems like it'd be much simpler to get up and running than learning/setting up a PHP daemon.)

Thanks!

This is all that is in the testit.php script...

try
{
    $dbh = new PDO("mysql:host=********;dbname=********", ********, ********);
    $dbh->setAttribute( PDO::MYSQL_ATTR_USE_BUFFERED_QUERY,true );
    $dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );
}
catch(PDOException $e) { echo $e->getMessage(); }

$q1 = $dbh->prepare("INSERT INTO test(text) VALUES ('text')");
$q1->execute();

score 8 · Accepted Answer · answered Jan 14 '14 at 05:43

Don't do this. MySQL events as all other types of stored routines (triggers, functions and procedures) are not meant to be used to call external processes. Instead whole point of stored routines is in keeping all the db processing compartmentalized from the outside world.

And even though you can technicaly do this, it doesn't mean that it's a right thing to do. Your sample code is a great illustration that it's absolutely meaningless. You start a php just to make a connection back to the database and insert a row which you could've done easily just issuing insert in the event itself.

If you have a need to periodically run a php script use a specifically designed tool for that - OS scheduler cron or in your case Windows Task Scheduler.

If on the other hand all your script doing is manipulating data in your database and doesn't need any interaction with OS other than through build it statements (like LOAD DATA or SELECT INTO OUTFILE) just do it properly in your event.

Besides the usage of sys_exec UDF is a huge security hazard for your DB instance! In case you didn't read or forget about it here is A Note of Caution from a library documentation

Be very careful in deciding whether you need this function. UDFs are available to all database users - you cannot grant EXECUTE privileges for them. As the commandstring passed to sys_exec can do pretty much everything, exposing the function poses a very real security hazard.

Even for a benign user, it is possible to accidentally do a lot of damage with it. The call will be executed with the privileges of the os user that runs MySQL, so it is entirely feasible to delete MySQL's data directory, or worse.

The function is intended for specialized MySQL applications where one needs extended control over the operating system. Currently, we do not have UDF's for ftp, email and http, and this function can be used to implement such functionality in case it is really necessary (datawarehouse staging areas could be a case in example).

You have been warned! If you don't see the hazard, please don't try to find it; just trust me on this.

If you do decide to use this library in a production environment, make sure that only specific commands can be run and file access is limited by using AppArmor.

Thank you. Yes, the example is meaningless, but it's just an example. What I really need to do is very different. "Currently, we do not have UDF's for ftp, email and http, and this function can be used to implement such functionality in case it is really necessary". I need those functionalities. — gfrobenius, Jan 14 '14 at 05:53
And there would be no variables in the sys_exec call for anyone to manipulate. So how could there be a security risk in that scenario? Basically just want it to run a script every minute. The script will handle everything else. Still not worth doing? — gfrobenius, Jan 14 '14 at 05:58
The UDF is available to **all** database users. You can't limit access to it. And it's called in a security context of a system account under which mysql is running. To say the least any user can just delete all the data files in your database instance and mysql itself. All needed is just a db connection. And when you start using other things with it you will have to grant additional rights in OS for mysql user. By doing so you'll inevitably increase the the threat surface because effectively you're granting all your database users this rights in your OS. — peterm, Jan 14 '14 at 06:06
You can implement all above mentioned functionalities outside of mysql process. — peterm, Jan 14 '14 at 06:06
Well that doesn't sound good. So how do I remove this UDF? Or is that why my call isn't working, because I never added it? If it's such a security risk then I would be surprised if it was loaded by default. I never "loaded" a UDF. But my Event compiled so I assumed I had it. Is that not the case? — gfrobenius, Jan 14 '14 at 06:17
`DROP FUNCTION IF EXISTS sys_exec;` if you installed all functions from `lib_mysqludf_sys` then drop them too. After that delete plugin library `lib_mysqludf_sys.so` or on windows `lib_mysqludf_sys.dll`. — peterm, Jan 14 '14 at 06:35

MySql Event Scheduler Call PHP Script

1 Answers1