Recherche sur le blog

mercredi 26 septembre 2012

[SQL] Créer des triggers basiques sur DB2/400

Il est toujours intéressant de savoir comment fonctionne une base de données s’exécutant sur un système que l’on connait peu, voire pas du tout. C’est la raison pour laquelle aujourd’hui, nous allons voir ensemble comment créer un trigger, ou en français, un “déclencheur”. Un trigger est une sorte de programme, qui permet d’effectuer une ou plusieurs actions spécifiques, en fonction des évènements qui se produisent. Les déclencheurs sont différents des procédures stockées par le fait qu’on ne les appelle pas explicitement. On les retrouve dans de nombreux SGBD (Système de Gestion de Base de Données) tels que Oracle ou MySQL, mais aussi dans DB2 (Linux, OS/400), malgré que la syntaxe puisse varier en fonction de celui utilisé.

Dans notre exemple, nous allons utiliser :
  • Une table de base, contenant deux champs, le code d’un article (10 caractères), ainsi qu’un libellé (50 caractères). Elle s’appellera “VLTEST”.
  • Une seconde table d’historique, contenant les deux champs de la table précédente, ainsi qu’un champ supplémentaire “date” (numérique de 8 dont 0) et un champ de 2 caractères pour indiquer le type de trigger (exemple : si c’est un déclencheur “After Insert”, on inscrira “AI” dans le champ). La table en question s’appellera “VLTESTHI”.
Des requêtes seront exécutées, aussi bien avec l’interpréteur SQL fourni dans le système i qu’avec un interpréteur basique écrit en WinDev, mais aussi par l’intermédiaire de fonctions natives WinDev (HAjoute, HModifie), et via des instructions en Java exploitant le pilote JDBC adéquat.

Nous supposons que le schéma de la base de données utilisées sera la bibliothèque “VLECOMTE”, spécialement créée pour tous les tests de ce genre. La norme d’écriture des requêtes reste liée au système, donc la notation “schéma + table” utilisée ressemblera à “nom_schema/nom_table.

Les triggers sur l’AS/400
 
Sachez que sur l’AS/400, les méthodes principales pour créer les déclencheurs sont les suivantes (peut-être en existe-t-il d’autres plus simples ?) :
  • Création d’un programme en RPG : cette façon de procéder est plus compliquée, car il faut jouer sur les tailles des différents “membres” d’une table. Le programme doit ensuite être associé à la table via la commande ADDPFTRG. Cette commande doit être répétée plusieurs fois pour tous les types de trigger que l’on veut créer (avant/après insertion, avant/après mise à jour, etc).
  • Création d’un trigger via le langage procédural SQL : il s’agit là de la méthode la plus facile, mais pas forcément disponible sur toutes les versions du système ; cela dépend de la version interne de la base de données. On utilise alors la syntaxe CREATE TRIGGER pour créer, et DROP TRIGGER pour supprimer. 

Triggers en PL/SQL

Le “Procedural Language / SQL” est un langage propriétaire, créé par Oracle et intégré à des SGBD comme TimesTen-In-Memory Database, DB2 depuis la version 9.7, et bien sûr, dans le système du même nom que son propriétaire. Il permet entre autres de coupler des requêtes SQL à des instructions simples comme des boucles ou des conditions, et de stocker cela sur le serveur sous forme de traitements particuliers. Les procédures stockées peuvent donc être écrites dans ce langage. 

Créer notre premier trigger 

Nous supposons ici que vous avez déjà créé vos tables et donc que vous êtes prêts à entamer le tutorial. N’oubliez pas que les requêtes SQL et les déclencheurs sont créés dans un schéma particulier, donc n’oubliez pas de changer le contenu de celles-ci si vous utilisez des bibliothèques de développement différentes.

Syntaxe

La documentation officielle d’IBM nous décrit, de manière très détaillée, les différentes clauses valides pour la création d’un trigger. Vous pouvez la trouver en suivant le lien suivant, “CREATE TRIGGER Statement” (en anglais), ou vous pouvez jeter un œil à l’image suivante, copiée depuis cette même source (cliquez pour agrandir).
 
SyntaxeCREATE_TRIGGER
 
Code et explications

Dans le premier exemple, nous allons créer un déclencheur qui réagit à l’ajout d’une ligne. Plus particulièrement, celui-ci devra déclencher “après insertion” pour chaque nouvelle ligne. Il exécutera une requête SQL qui insèrera des valeurs dans la table d’historique.
   
trigger1
  
Quelques précisions :
  • La clause “Create Trigger” est suivie du nom du déclencheur. Mieux encore : on peut directement spécifier dans quel schéma celui-ci doit être créé.
  • La clause “After Insert On” est suivie du nom de la table sur laquelle on va appliquer le trigger. On a bien précisé “après insertion” dans ce cas, donc cela déclenchera bien une fois que les données auront été intégrées.
  • La clause “Referencing” permet de définir les alias pour les tables ou les anciennes et nouvelles variables, qui pourront être réutilisées lors du traitement, dans les conditions, les boucles, et même les requêtes.
  • La clause “For each row” indique que le traitement doit déclencher pour chaque nouvelle ligne insérée.
  • Entre la clause “Begin Atomic” et “End”, on a placé une requête SQL. On peut en placer autant que l’on veut (nous le verrons après). Certains types de traitements ne peuvent pas être exécutés en fonction du type de déclencheur.
  • La date utilisée ici est écrite en dur.
  • Contraintes à prendre en compte : voir plus bas.
Lorsque le trigger est créé, on reçoit un message de confirmation (s’il est créé via l’interpréteur “Start SQL” du système). En explorant la bibliothèque dans laquelle on l’a créé, on peut apercevoir un objet de type *PGM appelé “VLTEST_AI”, qui a été créé automatiquement et qui a été automatiquement associé à la table. C’est ce qu’on aurait du faire si on avait utilisé la méthode compliquée : coder le programme pour le déclencheur puis l’associer avec la commande AS/400 adéquate.

Insertion de la première ligne

INSERT INTO vlecomte/vltest VALUES ('05030101','ASTERIX');
En vérifiant dans la table d’historique (VLTESTHI), on retrouve bien une ligne avec le code article “05030101”, le libellé “Astérix”, la date en dur “20120926” et le type du trigger qui a déclenché.

Amélioration 

Afin d’insérer la bonne date à chaque fois que le trigger est exécuté, on doit modifier la requête d’insertion du trigger. On va donc le recréer mais d’abord, il faut que vous le supprimez au moyen de l’instruction “DROP TRIGGER”.

Code et explications

Dans l’exemple ci-dessous, nous allons nous servir des variables globales et des fonctions proposées par le système de base de données. Par exemple, CURRENT_DATE permet de renvoyer la date du jour dans un format spécifique. Il faut alors transformer dans une date qui nous convient, comme par exemple une date au format “AAAAMMJJ” (année-mois-jour).
   
trigger2
      
La partie qui nous intéresse est une nouvelle fois en rouge : il s’agit de générer une date au bon format avec les différentes fonctions et variables fournies. On traite les années, les mois, et les jours, et on additionne le tout pour obtenir une date comme ceci : “20120926” (c’est la date à laquelle cet article a été rédigé).

Insertion de la seconde ligne

C’est avec WinDev et les fonctions natives que nous créons une nouvelle ligne. Pour cela, il sera nécessaire d’utiliser le module Easycom (payant). Notez qu’il est aussi possible d’utiliser la fonction “HExecuteRequeteSQL” pour lancer directement une requête texte.
  
codewd  
Une fois ce code exécuté, regardez à nouveau le contenu de votre table d’historique. Vous verrez apparaitre l’article “05050102” avec le libellé “TEST ART” et la bonne date, cette fois-ci.

Trigger déclenchant après une mise à jour 

Voici un exemple de trigger qui déclenchera après une mise à jour. Etant donné que lorsqu’on écrit un déclencheur “avant mise à jour”, on ne peut exécuter de requête SQL dans le corps de celui-ci. C’est l’une des contraintes que nous évoquerons plus bas dans cet article.

Code et explications

Ce déclencheur s’exécutera lorsqu’une mise à jour sera effectuée (“après la mise à jour”), et ce pour chacune des lignes. On veut conserver, pour chaque ligne, le record avant et après sa modification.
    
trigger3
     
Quelques petites précisions :
  • On a déclaré un alias pour pouvoir interagir avec les anciennes valeurs (clause REFERENCINGOLD AS…). “Old” et “New” ne peuvent pas être utilisés dans tous les types de triggers. Remarque : il s’agit de variables de transition.
  • On a modifié la valeur à insérer dans la dernière colonne pour qu’il indique qu’il s’agit d’un déclencheur de mise à jour (“BU” et “AU”).

Mise à jour d’une ligne
   
Effectuez une requête de mise à jour sur l’article “05030101” inséré lors du premier exemple, et modifiez le libellé en “Modification Libellé”. Normalement, deux lignes s’ajouteront dans la table d’historique. Essayez également d’effectuer cette requête en Java en utilisant le bon pilote JDBC (cela a été expliqué sur ce blog).

Quelques contraintes à prendre en compte

  • On ne peut pas créer un trigger sur une table en cours d’utilisation.
  • Pour pouvoir créer un trigger, il faut suffisamment de privilèges.
  • Les instructions suivantes disponibles pour le corps du déclencheur :
         
Available statements
  • Tables et variables de transition exploitables ou non avec la clause “Referencing”, en fonction du type de déclencheur :
        
available referencing     
Sources

Syntaxe du “Create Trigger”.
Complément (variables, exemples, registres).

lundi 3 septembre 2012

[Sécurité] Trojan Winlock, Virus “Gendarmerie”

Nous avons récemment eu un logiciel malveillant sur des machines, qui est en fait un trojan de type Winlock, s’étant fortement répandu fin 2011. Il est communément appelé “Virus Gendarmerie” ou même “Virus Bundespolizei”. Celui-ci bloque votre ordinateur en faisant apparaitre un message vous demandant de débourser une certaine somme pour une soi-disant amende, voire encore une sorte de rançon, afin que l’ordinateur soit de nouveau fonctionnel. Le message peut varier et les images aussi.

Celui-ci peut s’être installé tranquillement dans votre système à cause de failles web. Certains logiciels non mis à jour et permettant de traiter du contenu spécifique, peuvent contenir des failles, et celles-ci pourraient avoir été exploitées lors de l’infection. Par exemple, des logiciels comme Flash Player, Adobe Reader et Java doivent régulièrement être mis à jour, ainsi que le navigateur lui-même.

Trojan.Winlock

Le site de Malekal répertorie les différentes variantes de ce trojan et explique comment les supprimer.

Suppression avec l’utilitaire ComboFix

L’une des variantes empêche l’ordinateur de se lancer en mode normal, mais permet de lancer celui-ci en mode sans échec, c’est-à-dire en pressant la touche F8 juste avant l’apparition du logo de démarrage de Windows. C’est ici que ComboFix intervient : il s’agit d’un utilitaire dont nous avons déjà parlé sur ce blog et qui s’occupe de nettoyer beaucoup de saletés. Pour le télécharger, rendez-vous sur la page officielle ComboFix.org. Lancez cet utilitaire en mode sans échec, et suivez les indications à l’écran. En général, vous ne devez rien faire de spécial.

Cet utilitaire est mis à jour régulièrement et doit donc être téléchargé périodiquement, car il intègre toujours plus de nouveautés. Notez aussi que vous pouvez consulter le tutorial en anglais disponible sur BleepingComputer.

Variante créant une clé de registre “Run”

L’une des variantes crée une clé de registre de type “Run” qui permet normalement de lancer un simple programme au démarrage. L’utilisation de Malwarebytes Anti-Malware en version gratuite suffira à éradiquer le trojan. D’autres programmes de sécurité permettent aussi de l’enlever, comme RogueKiller.

Variante modifiant la clé de registre “Shell”

Il s’agit d’une version qui modifie la clé "Shell" du registre de Windows pour lancer le programme malveillant au démarrage. Cette version sera malheureusement active dès lors qu’on sera entré en mode sans échec. Il faut alors choisir l’option “Invite de commandes en mode sans échec”.

  • Redémarrer en invite de commandes. Pour cela, redémarrez l'ordinateur, avant le logo Windows, appuyer sur sur la touche F8, un menu va apparaitre, choisir alors “invite de commandes en mode sans échec” et appuyer sur la touche entrée du clavier.
  • Une fois sur l'invite de commandes, tapez la commande : regedit.
  • Déroulez l'arborescence suivante en cliquant sur les icônes [+] : HKEY_LOCAL_MACHINE\Software\Microsoft\Windows NT\CurrentVersion\Winlogon\.
  • Positionnez-vous sur la clé "Winlogon", de telle manière à voir les valeurs apparaitre dans la partie droite.
  • Localisez la valeur "Shell"  (de type chaine) ; vous devriez y trouver "explorer.exe", effacez tout et saisissez à nouveau "explorer.exe" (oui il faut remettre la même chose).
  • Fermer l'éditeur de registre.
  • Sur l'invite de commandes, saisir la commande : shutdown /r.
  • Redémarrez l'ordinateur en mode normal.
  • Si cela ne fonctionne pas, essayez de placer le programme ComboFix à la racine, puis de le lancer via l’invite de commandes.

Sources

Malekal
Comment ça marche