PHP MySQL 触发器 - 如何传递变量来触发?

2022-01-01 00:00:00 php mysql triggers

我正在尝试查找如何将查询中的变量发送到触发器，但它会影响第二个表.我正在使用它来创建一个日志表，其中更新或插入的任何内容都会记录在日志表中例如

I'm trying to find how to send variables in a query to the trigger, but it's going to affect a second table. I'm using this to create a log table, in which anything that gets updated or inserted gets recorded in the log table For example

//Inserts into the table the username and password
$sql = "INSERT INTO table VALUES ($_POST['username'], $_SESSION['password']);

触发 DDL 语句

DELIMITER $$

//Creates trigger to insert into table1 ( logs ) the userid and patientid ( which has to come from php )
USE `baemer_emr`$$

CREATE
DEFINER=`baemer_emr`@`localhost`
TRIGGER `table1`.`after_insert`
AFTER INSERT ON `baemer_emr`.`table1`
FOR EACH ROW
BEGIN
  INSERT INTO table2 VALUES (NEW.idn, $_POST[userid], $_SESSION[patientid]);
END$$

这可能吗?

推荐答案

修复 SQL 注入 

$username = mysql_real_escape_string($_POST['username']);
$password = mysql_real_escape_string($_POST['password']);
$sql = "INSERT INTO table1 VALUES ('username','password'); 
// You must quote your $vars       ^        ^ ^        ^  like this
// or syntax errors will occur and the escaping will not work!.

请注意，将未加密的密码存储在数据库中是一种大罪.
请参阅下文了解如何解决该问题.

Note that storing unencrypted passwords in a database is a cardinal sin.
See below on how to fix that.

触发器不允许参数
您只能访问刚刚插入表中的值.
插入触发器为此有一个虚拟表 new.
删除触发器有一个虚拟表 old 来查看要删除的值.
更新触发器有 old 和 new.

Triggers do not allow parameters
You can only access the values you just inserted into the table.
The Insert trigger has a dummy table new for this.
The Delete triger has a dummy table old to see the values that are to be deleted.
The Update trigger has both old and new.

除此之外，您无法访问任何外部数据.

Other than that you cannot access any outside data.

DELIMITER $$    

//Creates trigger to insert into table1 ( logs ) the userid and patientid ( which has to come from php )    

CREATE    
TRIGGER ai_table1_each AFTER INSERT ON `baemer_emr`.`table1`    
FOR EACH ROW    
BEGIN    
  INSERT INTO table2 VALUES (NEW.idn, NEW.username, NEW.patientid);    
END$$

解决方案
创建一个黑洞表.
黑洞表不存储任何内容，它们存在的唯一原因是用于复制目的，因此您可以为它们附加触发器.

The solution
Create a blackhole table.
Blackhole tables to not store anything, their only reason to exist is for replication purposes and so you can attach triggers to them.

CREATE TABLE bh_newusers (
  username varchar(255) not null,
  password varchar(255) not null,
  idn integer not null,
  patient_id integer not null,
  user_id integer not null) ENGINE = BLACKHOLE;

接下来将数据插入黑洞表并使用触发器进行处理.

Next insert data into the blackhole table and process that using a trigger.

CREATE    
TRIGGER ai_bh_newuser_each AFTER INSERT ON `baemer_emr`.bh_newuser
FOR EACH ROW    
BEGIN    
  DECLARE newsalt INTEGER;
  SET newsalt = FLOOR(RAND()*999999);
  INSERT INTO users (username, salt, passhash) 
    VALUES (NEW.username, newsalt, SHA2(CONCAT(newsalt, password), 512));
  INSERT INTO table2 VALUES (NEW.idn, NEW.username, NEW.patient_id);
END$$

触发器注意事项
您永远不应该将密码以明文形式存储在数据库中.
始终使用最安全的散列函数将它们存储为加盐散列 (当前 SHA2 的密钥长度为 512)，如触发器中所示.
您可以通过执行以下操作来测试某人是否拥有正确的密码:

Notes on the trigger
You should never store passwords in the clear in a database.
Always store them as a salted hash using the safest hash function (currently SHA2 with a 512 key length) , as shown in the trigger.
You can test to see if someone has the correct password by doing:

SELECT * FROM user 
WHERE username = '$username' AND passhash = SHA2(CONCAT(salt,'$password'),512)

链接
http://dev.mysql.com/doc/refman/5.0/en/blackhole-storage-engine.html
http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html
在 MySQL 中存储散列密码
来自Bobby Tables"的 SQL 注入是如何进行的?XKCD漫画作品?

相关文章