使用mysql触发器调用php文件 [英] Calling a php file by using mysql trigger

问题描述

我正在尝试使用mysql触发器调用.php.这是mysql的代码

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; 

预先感谢

推荐答案

即使在lib_mysqludf_sys库的帮助下在技术上是可能的，您也不应该这样做.在所有可能的方式上都是错误的.仅举几例:

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. 单独使用这些UDF是巨大的安全威胁.以下是lib文档的简短报价:

1. Using these UDFs on its own is a huge security threat. Here is a short quote from the lib's documentation:

在确定是否需要此功能时要格外小心. UDF是 所有数据库用户均可使用-您无法授予EXECUTE特权 为他们.由于传递给sys_exec的命令字符串可以做很多事情 一切，暴露该功能会带来非常真实的安全隐患. 即使是良性用户，也可能会意外地执行很多操作 破坏它.呼叫将以的特权执行 运行MySQL的os用户，因此删除MySQL的完全可行 数据目录，或更糟糕的是.

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.

在触发器中执行任何非事务性操作是错误的. DML语句(在您的情况下为更新)所做的数据更改可以并且将在实际场景中回滚.您将无法撤消对php脚本的调用.

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.

推荐阅读:

• 带触发器的麻烦

现在，即使我们忽略了上面提到的所有内容，您的代码也有几个问题

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

1. 将DELIMITER更改为$$，然后用@@终止触发器定义.
2. 不需要cmd变量.
3. 触发器是在运行MySQL的OS用户的上下文中执行的，因此您必须提供php可执行文件和php脚本的绝对路径

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 ;

这篇关于使用mysql触发器调用php文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！