PHP MySQL触发器-如何传递变量以进行触发? [英] PHP MySQL Triggers - How to pass variables to trigger?

问题描述

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

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$$    

关于触发器的说明
永远不要将密码明文存储在数据库中.
始终使用最安全的哈希函数(当前为密钥长度为512的SHA2)将它们存储为盐腌哈希，如触发器所示.
您可以执行以下操作来测试某人是否具有正确的密码:

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中存储哈希密码
如何从鲍比表"中注入SQL? XKCD漫画作品?

Links
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
Storing hashed passwords in MySQL
How does the SQL injection from the "Bobby Tables" XKCD comic work?

这篇关于PHP MySQL触发器-如何传递变量以进行触发?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！