Data Management:Use o recurso de revisão de SQL

Informações básicas

Geralmente, antes da publicação de um projeto, é necessário revisar todas as instruções SQL utilizadas para evitar que instruções fora dos padrões de desenvolvimento sejam implantadas no ambiente de produção. No entanto, esse processo manual pode consumir muito tempo.

O DMS oferece o recurso de SQL review, que analisa instruções SQL automaticamente com base em regras predefinidas e fornece sugestões de otimização. Você pode personalizar essas regras diretamente no console. As seguintes regras de revisão de SQL são utilizadas neste tópico:

• Recomendação de cláusulas WHERE em instruções UPDATE e DELETE

• Detecção de risco de injeção de SQL

• Verificação de índices nos planos de execução

• Recomendação de atualização de colunas de horário de modificação em instruções UPDATE

• Detecção de risco de uso forçado de índice

Preparações

1. Crie uma tabela chamada test_sql_review_table na instância de banco de dados de destino e insira dados nessa tabela usando o recurso de construção de dados de teste. Use a seguinte instrução para criar a tabela:

   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;

2. Prepare o arquivo para revisão. Neste exemplo, usa-se um arquivo XML baseado no framework MyBatis contendo o código de amostra abaixo.

   Clique em Código de amostra MyBatis para baixar o código de amostra.

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

Etapa 1: Configurar regras de revisão de SQL

Neste exemplo, personalize a regra de segurança WHERE clauses are recommended in UPDATE and DELETE statements. Mantenha as configurações padrão para as demais regras. O exemplo a seguir mostra como configurar essa regra no modo de colaboração de segurança. Para mais informações, consulte Configurar revisão de SQL e sugestões de otimização.

1. Faça login no console do DMS com uma conta de administrador.

2. Passe o ponteiro sobre o ícone no canto superior esquerdo e selecione All Features > Security and disaster recovery (DBS) > Security Rules.

   Nota

   Se usar o console do DMS no modo normal, selecione Security and disaster recovery (DBS) > Security Rules na barra de navegação superior.

3. No painel de navegação à esquerda, clique em SQL audit optimization recommendations.

4. Localize o conjunto de regras de segurança a modificar e clique em Edit na coluna Actions.

5. Encontre a regra de segurança WHERE clauses are recommended in UPDATE and DELETE statements e clique em Edit na coluna Actions.

6. Na caixa de diálogo Rule content configuration, defina Behavioral action como Must Improve e clique em OK.

Etapa 2: Enviar instruções SQL para revisão

No exemplo a seguir, as instruções SQL de um arquivo XML compatível com o framework MyBatis são revisadas e modificadas conforme os resultados da verificação. Para mais informações, consulte Revisão de SQL.

1. Faça login no DMS 5.0.

2. Passe o ponteiro sobre o ícone no canto superior esquerdo do console do DMS e selecione All Features > Database Development > SQL Review > SQL Audit Ticket.

   Nota

   Se usar o console do DMS no modo normal, selecione Database Development > SQL Review > SQL Audit Ticket na barra de navegação superior.

3. Na página Apply for SQL Review Ticket, clique em SQL Review e configure os parâmetros na seção Application. Clique em Submit.

   Nota

   • Selecione a instância de banco de dados associada ao conjunto de regras de segurança desejado.

   • Neste exemplo, faça upload do código de amostra obtido na seção Preparações deste tópico.

4. Visualize os resultados da revisão de SQL e modifique as instruções conforme necessário.

   O seguinte Check Result é retornado:

   SQL	Resultado da revisão de SQL	Sugestão de otimização de SQL
   <delete id="deleteXxxx"> DELETE FROM test_sql_review_table </delete>	Must Improve: Cláusulas WHERE são recomendadas em instruções DELETE. Nota Por padrão, o parâmetro Behavioral action desta regra de segurança está definido como Suggest Improve. Neste exemplo, o parâmetro Behavioral action foi definido como Must Improve na seção "Etapa 1: Configurar regras de revisão de SQL para um banco de dados".	Adicione a cláusula WHERE conforme necessário. Se precisar excluir toda a tabela, use a seguinte cláusula WHERE: WHERE 1 = 1. Exemplo: 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: Cifrões ($) são usados para concatenar instruções SQL, o que gera riscos de injeção de SQL.	Substitua ${pk} por #{pk} na instrução SQL para evitar riscos de injeção de SQL. Exemplo: <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: Recomenda-se atualizar colunas de horário de modificação em instruções UPDATE: gmt_modified Potential Issue: O plano de execução de SQL não utiliza índices. Index Recommendation: O DMS recomenda adicionar 1 índice.	Adicione gmt_modified = NOW() à cláusula SET da instrução UPDATE. Obtenha a instrução SQL que utiliza um índice na aba Index recommendations do painel Details. Em seguida, envie um ticket para alteração sem bloqueio baseada em DDL. Para mais informações, consulte Realizar alterações de esquema sem bloqueio usando tickets de alteração sem bloqueio Exemplo: UPDATE test_sql_review_table SET db_id=#{dbId}, gmt_modified = NOW() WHERE detail_id=#{detailId} and is_delete='N'

   Nota

   • Se um arquivo XML contiver instruções SQL dinâmicas, o DMS tentará substituir as variáveis e consultará o Execution plan das instruções SQL. Para visualizar mais detalhes sobre o Execution plan de uma instrução SQL, clique em Details na coluna Operation.

   • Por motivos históricos, suas instruções SQL podem conter problemas que atualmente não podem ou não precisam ser resolvidos. Nesse caso, revise manualmente essas instruções e marque-as como aprovadas ou reprovadas. Na coluna Operation, selecione Manual Review > Approve ou Manual Review > Disapprove.

   Após as modificações, atualize a página no console do DMS e verifique novamente os resultados da revisão de SQL.