3

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']);

Trigger DDL Statements

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

Is this possible?

mu is too short
  • 426,620
  • 70
  • 833
  • 800
Skynight
  • 507
  • 2
  • 7
  • 24

2 Answers2

12

Fix that SQL-injection 

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

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

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)

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?

Community
  • 1
  • 1
Johan
  • 74,508
  • 24
  • 191
  • 319
  • I use the PHP Function MD5, is it good enough ? Great answer btw, so in other words I'd need to do an insert to that blackhole table, and from there do the necessary inserts. – Skynight Oct 13 '11 at 08:03
  • 1
    @Skynight, I would recommend against MD5, it has been broken for a while and SHA2 is secure, when it comes to hashing slowness is a **good** thing. – Johan Oct 13 '11 at 08:12
  • 1
    @Skynight, The insert into the blackhole will send all that data straight to the trigger and inside the trigger you can redistibute your data. This has the added benefit that all your inserts happen in a single transaction, because all code inside a triggers gets wrapped in a transaction. – Johan Oct 13 '11 at 08:14
  • thank you @Johan, by your post i was able to create trigger to update movie rating in my movie table based on the user_movie _rating table – Rajan Rawal May 25 '12 at 14:03
0

I don't think it is.

You could add userid and patientid columns to the VALUES table, populate them during the original insert and then reference those within the trigger when inserting into your log table.

alter table `baemer_emr`.`table1` add userId varchar(100);

alter table `baemer_emr`.`table1` add patientId int unsigned;

and then the the trigger:

DELIMITER $$

//Creates trigger to insert into table1 ( logs )
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, NEW.userId, NEW.patientId);
END$$

DELIMITER ;
Tom Mac
  • 9,693
  • 3
  • 25
  • 35