MySQL - Triggers

"Triggers" que no rasguñan (¿o será gatillos que no rasguñan?).

Los "Triggers" son pedazos de códigos asociados a una tabla que se ejecutan ante un cierta situación permitiendo manipular datos en pleno vuelo.

Si han tardado.

Leyendo la documentación de MySQL se puede uno dar cuenta de la lucha que mantienen por no perder rendimiento ante la compatibilidad. De hecho parece ser esta la excusa por el desarrollo de "triggers" con algo de retraso. El soporte para triggers aparece en la versión 5.0.2

De hecho los "triggers" (gatillos) son una gran solución cuando se requiere resolver ciertos aspectos de seguridad.

Ilustrando el caso

Tenemos un cliente remoto y un servidor, el cliente se conecta y agrega registros, por otro lado el cliente se conecta y anula registros, anular no es borrar; es simplemente llenar un campo llamado "anulacion"

Así que queremos resolver 2 cosas:

• quién y cuando agregó (además de estar seguro de qué se agregó)
• quién y cuando anuló

Los "triggers" nos ayudan a esto.


Partimos de la siguiente tabla, la llamaré "registro" (si es poco descriptivo), tenemos un campo id autoincremento con indice primario. Transaccion es un número que alguien asigna y el monto es un número. Simple es lo mínimo que necesitamos. El campo "anulacion" por defecto es NULL, si no lo es significa que el registro fue anulado y como el campo precisamente es tipo fecha y hora, tenemos el momento de la anulación o lo que diga quien anula que es el momento de anulación.

CREATE TABLE `registro` (

`id` INT NOT NULL AUTO_INCREMENT ,
`transaccion` VARCHAR( 32 ) NOT NULL ,
`monto` FLOAT NOT NULL ,
`anulacion` DATETIME NULL DEFAULT NULL ,
`quien_inserta` VARCHAR( 32 ) NULL DEFAULT NULL ,
`cuando_inserta` DATETIME NULL DEFAULT NULL ,
`quien_anula` VARCHAR( 32 ) NULL DEFAULT NULL ,
`cuando_anula` DATETIME NULL DEFAULT NULL ,
`crc` VARCHAR( 32 ) NULL DEFAULT NULL ,
PRIMARY KEY ( `id` )

) ENGINE = MYISAM

De los campos quien y cuando los usaremos internamente. Nuestro cliente no tiene que enterarse de ello. De esto hablaré un poco.

Supongamos que alguien sabe que tu aplicación se conecta a hippifeliz.com, si ese alguien tiene copia de aplicación puede intentar interceptar la comunicación. Modifica su archivo hosts, apunta hipifeliz.com a localhost, monta un tunel y tantea. Al final si lo logra queremos que vea lo mínimo. Esto es los primeros tres o cuatro campos. Manipulando privilegios impedimos que pueda insertar los últimos campos (Privilegio de inserción a la cuenta cliente solo en los primeros campos).

El hacker luego de tanto trastear con la aplicación, ve que se crean instrucciones SQL del tipo

INSERT INTO `registro` (
`transaccion` ,
`monto`
)
VALUES
('54352312g4523gdf41523', '123'),
('54352318g4523gd841523', '623'),
('54352372u4523gjf41523', '226'),
('54352612gk523gdf41523', '113'),
('54355312g45l3gdf41523', '128');

Corremos el riesgo de que el hacker meta información falsa. A esta altura quizás no lo podamos evitar, pero podemos tomar medidas.

Usar un calculo de algo como un CRC o HASH (habran notado que dejé un campo llamado CRC). Usaremos MD5, por supuesto casi todos los lenguajes de programación serios tienen alguna librería para calcular MD5, así que presumo que podrán conseguir alguna, además MySQL lo tiene, pero no la usaremos que no queremos que el hacker vea como calculamos el CRC.

Nótese que un crc o hash o md5 no es lo mismo, explicar cada cosa me da como "flojera" y además asumo que si usted esta leyendo sobre seguridad en sql sabe perfectamente que es cada uno de estos elementos y entiende que no importa el manejo indiferente que a cada concepto se da aquí. Así que disculpe y sigamos.

INSERT INTO `registro` (
`transaccion` ,
`monto`,
`crc`
)
VALUES
('54352312g4523gdf41523', '123', '5b4b95f7c316fbfd5dda9c8b2303bd92'),
('54352318g4523gd841523', '623', '797f8e8515c88e0b981f28769bf388a8'),
('54352372u4523gjf41523', '226', '0eb43e356b5bfec0c78e512531e927b8'),
('54352612gk523gdf41523', '113', '462b5cbde9c8f9032aaf237750bb0ad8'),
('54355312g45l3gdf41523', '128', 'dd434e67ef8be7e5a5cea7824947c3ad');

Verán que nuestro instrucción SQL ahora luce más intimidante.

El hacker ve lo que se mueve, pero si no sabe como se calcula (seguro se dará cuenta que es un hash o MD5 pero eso no implica que lo pueda generar) se verá tentado a mandar información falsa, es decir intentará tantear.

Podemos hacer una secuencia de código en SQL para gran cantidad de cosa, a mi se me ocurre es que si el CRC está malo se anule la transacción y esto de forma automática (podemos hacer que llene una bitácora también)

La sintaxis para crear "triggers" es

CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt

trigger_time puede ser BEFORE o AFTER
trigger_event puede ser INSERT o UPDATE o DELETE

Como vamos a tomar acción justo antes de introducir datos nos toca "BEFORE INSERT"

CREATE TRIGGER registro_ok_in
BEFORE INSERT ON registro
FOR EACH ROW

SET new.anulacion = if( new.CRC = MD5(
CONCAT( new.transaccion,
"en bosque de la china" ,
QUOTE( new.monto ) ) )
, NULL , NOW( ) ) ;


trigger_stmt es algo complicado pero es exactamente donde se genera el código. Debemos tener en cuenta que los campos se refieren como new. simplemente no han entrado a la tabla (existe old como contraparte, pero no tiene sentido aquí)

Calculamos el CRC y lo comparamos con lo que viene desde la conexión cliente. Si esta mal es la fecha de hora lo que se almacena, si todo esta bien se almacena NULL y no hay nada para alarmarse.


Probemos , en la siguiente instrucción se agregan dos filas. La segunda tiene mal calculado el CRC


INSERT INTO `registro` (
`transaccion` ,
`monto`,
`crc`
)
VALUES
('54312534', '123', '223da1451e49503b3a5351e96c0e3936'),
('5432534', '623', '797f8e8515c88e0b981f28769bf388a8');


Verán que el segundo registro queda automáticamente anulado, todo esto sin que nuestro hacker sepa el porque.

Nos queda un asunto, saber cuando y con que cuenta se intentó meter data. Modificamos el "trigger".

Hay hay que tener en cuenta tres cosas:

• Cambiar el delimitador por aquello de que MySQL no puede distinguir si el ; que enviamos debe ser procesado o almacenado.
• La instrucción "DROP TRIGGER IF EXISTS " para eliminar antes de agregar la nueva versión.
• usar BEGIN END dado que ya tenemos un bloque de instrucciones.

DELIMITER //
DROP TRIGGER IF EXISTS `test`.`registro_ok_in`//
CREATE TRIGGER `test`.`registro_ok_in` BEFORE INSERT ON `test`.`registro`
FOR EACH ROW BEGIN
SET new.anulacion = if( new.CRC = MD5(
CONCAT( new.transaccion,
"en bosque de la china" ,
QUOTE( new.monto ) ) )
, NULL , NOW( ) );
SET new.cuando_inserta = now();
SET new.quien_inserta = user();
END
//
DELIMITER ;


Volvemos a insertar registros y esta vez tendremos cuando se hizo y con que cuenta.


Nos queda varias cosas más que hacer, pero confío que lo anterior será útil.

La siguiente entrada usaremos los campos de quien anula y cuando, por lo que requerimos ya no trabajar con INSERT sino con UPDATE.

Bienvenido

Saludos.

Soy ingeniero y programador de esos a los que le gusta. Actualmente utilizo herramientas como Delphi, Lazarus, MySQL, Php. Pero tengo una larga historia en esto por lo que he pasado a través de Fortran, Turbo Pascal, C, Clipper, dBase, cosas particulares como Fumal FUMAL (FUndamental Machine Assembly Language) y MicroIsis. Programación microcontroladores, Y bueno no me quiero acordar de cuantos Basic diferentes aprendí. Desde CPM hasta Linux Open Suse.

Por allí hay algunos artículos míos de programación en Delphi. Por lo pronto me iré dedicando a Lazarus dada mi necesidad de apoyar Linux.

MySQL es realmente difícil para programar, más bien incomodo. Espero dedicarle algunas líneas que puedan ser de utilidad general.

Gracias.