Calling a php file by using mysql trigger
mysql, php, sql, triggers
Solution
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:
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.
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.
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
- You change `DELIMITER` to `$$` but then terminate a trigger definition with `@@`.
- There is no need for `cmd` variable.
- 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 ;
Problem
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