修复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!.
请注意,将未加密的密码存储在数据库中是一个重要的罪过.
请参阅下文,了解如何解决此问题.
触发器不允许参数
您只能访问刚刚插入表中的值.
Insert触发器有一个新的虚拟表.
Delete triger有一个旧表,用于查看要删除的值.
Update触发器既有新旧,也有新旧.
除此之外,您无法访问任何外部数据.
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$$
解决方案
创建一个黑洞表.
黑洞表不存储任何内容,它们存在的唯一理由是复制目的,因此您可以将触发器附加到它们.
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;
接下来将数据插入到黑洞表中并使用触发器进行处理.
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)将它们存储为salted哈希,如触发器所示.
您可以通过执行以下操作来测试是否有人拥有正确的密码:
SELECT * FROM user
WHERE username = '$username' AND passhash = SHA2(CONCAT(salt,'$password'),512)