Calling a php file by using mysql trigger

Question

I am trying to call .php by using mysql trigger.This is the code of mysql

delimiter $$     
DROP TRIGGER IF EXISTS qwertyuiop$$      
CREATE TRIGGER qwertyuiop    
AFTER UPDATE ON testing
FOR EACH ROW    
BEGIN    
 DECLARE cmd CHAR(255);    
 DECLARE result int(10);    
 SET cmd=CONCAT('C:/wamp/www/index.php');    
 SET result = sys_exec(cmd);    
END    
@@     
Delimiter; 

thanks in advance

Answer 1

Even though it's technically possible with the help of lib_mysqludf_sys library you shouldn't be doing this. It's wrong in all possible ways. To mention just a few:

1. Using these UDFs on its own is a huge security threat. Here is a short quote from the lib's 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.

2. Doing any non-transactional operations in a trigger are wrong. Data changes made by DML statement (in your case it's an update) can be and will be rolled back in a real world scenario. You won't be able to undo calls to your php script.

3. You're prolonging the time for update transaction possibly causing lock-wait-timeouts for other update/insert operations.

Recommended reading:

• The Trouble with Triggers

---

Now even if we set aside everything mentioned above you have several issues with your code

1. You change DELIMITER to $$ but then terminate a trigger definition with @@.
2. There is no need for cmd variable.
3. A trigger is executed in a context of an OS user under which MySQL is running therefore you have to provide absolute paths both to the php executable and a php script

That being said a working version might look like

DELIMITER $$
CREATE TRIGGER qwertyuiop    
AFTER UPDATE ON testing
FOR EACH ROW    
BEGIN    
  DECLARE result INT;    
  SET result = sys_exec('C:/php/php.exe C:/path/to/script.php');     
END$$
DELIMITER ;