Meilleures pratiques pour la révision SQL - Data Management

La fonctionnalité de révision SQL de Data Management DMS vous permet d'examiner des instructions SQL par lots et fournit des suggestions d'optimisation. Cela vous aide à éviter les requêtes SQL non conformes ou dépourvues d'index, et réduit les risques d'injection SQL. Cette rubrique explique comment configurer des règles de sécurité et réviser des instructions SQL contenues dans un fichier XML.

Contexte

Avant de déployer un projet en production, examinez toutes les instructions SQL associées afin d'éviter que du code non conforme n'affecte vos services actifs. L'examen manuel de chaque instruction SQL peut toutefois considérablement ralentir le processus de développement.

Pour relever ce défi, DMS propose la fonctionnalité SQL review. Cette fonctionnalité inspecte les instructions SQL sur la base d'un ensemble de règles de sécurité et fournit des conseils d'optimisation. Vous pouvez également personnaliser ces règles de sécurité. Cette rubrique utilise les règles de sécurité suivantes :

Il est recommandé d'inclure des clauses WHERE dans les instructions UPDATE et DELETE.
Détection des risques d'injection SQL.
Vérification des index dans le plan d'exécution.
Recommandation de mise à jour de la colonne modification time dans les instructions UPDATE.
Détection des risques liés à l'utilisation de force index.

Prérequis

Créez une table nommée test_sql_review_table dans votre instance de base de données cible. Ensuite, remplissez-la avec des données en utilisant la fonctionnalité test data construction. Utilisez l'instruction suivante pour créer la table :

CREATE TABLE `test_sql_review_table` (
  `id` BIGINT(20) UNSIGNED  NOT NULL AUTO_INCREMENT,
  `gmt_create` DATETIME NOT NULL,
  `gmt_modified` DATETIME NOT NULL,
  `detail_id` BIGINT(20) UNSIGNED DEFAULT NULL,
  `name` VARCHAR(256) DEFAULT NULL,
  `db_id` BIGINT(20) DEFAULT NULL,
  `is_delete` VARCHAR(1) DEFAULT NULL,
  `file_content_id` BIGINT(20) UNSIGNED DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Préparez le fichier contenant les instructions SQL que vous souhaitez examiner. Cet exemple utilise un fichier XML basé sur le framework MyBatis.

Vous pouvez également cliquer sur MyBatis Sample code pour télécharger le fichier. Le fichier contient le code suivant :

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.xxx.namespace">
    <sql id="SELECT_ALL_FROM">
        SELECT
        id,
        gmt_create,
        gmt_modified,
        detail_id,
        name,
        db_id,
        is_delete,
        file_content_id
        FROM test_sql_review_table sf
    </sql>
    <select id="getByPK" resultType="com.xxx.TestSQLReviewTableDO">
        <include refid="SELECT_ALL_FROM"/>
        WHERE id=${pk}
    </select>
    <select id="getXxxList" resultType="com.xxx.TestSQLReviewTableDO">
        <include refid="SELECT_ALL_FROM"/>
        WHERE
        <foreach collection="pks" open="sf.id in (" item="item" separator="," close=")">
            #{item}
        </foreach>
        <if test="searchKey != null and searchKey!=''">
            AND sd.name like concat('%',#{searchKey}, '%')
        </if>
        AND sf.is_delete='N'
    </select>
    <update id="updateAaaa">
        UPDATE test_sql_review_table
        SET
        db_id=#{dbId}
        WHERE detail_id=#{detailId}
        AND is_delete='N'
    </update>
    <delete id="deleteXxxx">
        DELETE FROM test_sql_review_table
    </delete>
</mapper>

Étape 1 : Configurer les règles de révision SQL

Dans cet exemple, la règle de sécurité WHERE clauses are recommended in UPDATE and DELETE statements doit être personnalisée. Les autres règles de sécurité utilisent leurs configurations par défaut. L'exemple suivant montre comment configurer cette règle de sécurité en mode de collaboration sécurisé. Pour plus d'informations, consultez Configure SQL review optimization suggestions.

Connectez-vous à la console DMS avec un compte administrateur.

Placez le pointeur sur l'icône située dans le coin supérieur gauche, puis choisissez All Features > Security and disaster recovery (DBS) > Security Rules.

Remarque
Si vous utilisez la console DMS en mode normal, choisissez Security and disaster recovery (DBS) > Security Rules dans la barre de navigation supérieure.
Dans le volet de navigation de gauche, cliquez sur SQL review optimization suggestions.
Dans la colonne Actions de la règle de sécurité cible, cliquez sur Edit.
Repérez la règle de sécurité WHERE clauses are recommended in UPDATE and DELETE statements et cliquez sur Edit à droite.
Dans la boîte de dialogue Rule content configuration , définissez Behavioral action sur Must Improve et cliquez sur OK.

Étape 2 : Soumettre le code SQL pour révision

Cet exemple montre comment examiner les instructions SQL contenues dans un fichier XML basé sur MyBatis et les modifier en fonction des résultats. Pour plus d'informations, consultez SQL review.

Connectez-vous à DMS 5.0.
Dans la barre de navigation supérieure, choisissez Database Development > SQL Review > SQL Audit Ticket.

Remarque
Si vous utilisez la console DMS en mode simple, placez le pointeur sur l'icône située dans le coin supérieur gauche de la console, puis choisissez All Features > Database Development > SQL Review > SQL Audit Ticket.
Sur la page Apply for SQL Review Ticket , configurez les paramètres requis et cliquez sur Submit.
Remarque
- Sélectionnez l'instance de base de données associée à l'ensemble de règles de sécurité cible.
- Téléchargez l'exemple de code présenté dans la section Prérequis.

Consultez les résultats de la révision SQL et ajustez le code si nécessaire.

Le système renvoie le Check Result suivant :

SQL	Résultat de la révision	Suggestion
`<delete id="deleteXxxx"> DELETE FROM test_sql_review_table </delete>`	Must Improve : Il est recommandé d'inclure une clause WHERE dans les instructions DELETE. Remarque Par défaut, cette règle est définie sur Suggest Improve. À l'étape 1, elle a été modifiée pour Must Improve.	Ajoutez une clause WHERE en fonction de vos besoins métier. Si vous souhaitez supprimer toutes les données de la table, utilisez `WHERE 1 = 1`. Exemple : `DELETE FROM test_sql_review_table WHERE id = #{pk}`
`<select id="getByPK" resultType="com.xxx.TestSQLReviewTableDO"> <include refid="SELECT_ALL_FROM"/> WHERE id=${pk} </select>`	Potential Issue : L'utilisation d'un signe dollar ($) pour concaténer du code SQL, comme dans `${pk}`, présente un risque d'injection SQL.	Remplacez `${pk}` par `#{pk}` pour prévenir l'injection SQL. Exemple : `<include refid="SELECT_ALL_FROM"/> WHERE id=#{pk}`
`<update id="updateAaaa"> UPDATE test_sql_review_table SET db_id=#{dbId} WHERE detail_id=#{detailId} AND is_delete='N' </update>`	Suggest Improve : Les instructions UPDATE doivent également mettre à jour la colonne de temps de modification : `gmt_modified`. Potential Issue : Le plan d'exécution n'utilise pas d'index. Index Recommendation : DMS recommande d'ajouter un index.	Ajoutez `gmt_modified = NOW()` à la clause SET de l'instruction UPDATE. Dans le panneau Details, accédez à l'onglet Index recommendations pour obtenir l'instruction SQL permettant d'ajouter l'index. Ensuite, soumettez une modification sans verrou basée sur DDL pour l'exécuter. Pour plus d'informations, consultez Perform lock-free schema changes using a lockless change ticket. Exemple : `UPDATE test_sql_review_table SET db_id=#{dbId}, gmt_modified = NOW() WHERE detail_id=#{detailId} and is_delete='N'`

Remarque

Pour le SQL dynamique contenu dans un fichier XML, DMS tente de remplacer les variables afin de récupérer le Execution plan pour le code SQL. En cas de succès, vous pouvez afficher les détails du Execution plan dans le panneau Details .
Vous pouvez également remplacer manuellement les résultats automatisés de la révision SQL pour les problèmes qui ne peuvent pas être corrigés ou qui ne nécessitent pas de correction. Choisissez Manual Review > Approve ou Disapprove.

Après avoir effectué les ajustements, actualisez la page et vérifiez à nouveau les résultats de la révision SQL.

Cliquez sur Submit for Approval. L'administrateur ou l'administrateur de base de données examine à nouveau les instructions SQL.

Une fois l'approbation accordée, le processus du ticket prend fin.

Remarque
Si des instructions SQL sont à l'état Check Failed ou Parsing Exception , le système signale une erreur lorsque vous cliquez sur Submit for Approval.