Melhores práticas para revisão de SQL - Data Management: Central de Documentação do Alibaba Cloud

O recurso de revisão de SQL do Data Management DMS permite revisar instruções SQL em lote e fornece sugestões de otimização. Isso ajuda a evitar SQL fora dos padrões ou consultas sem índices, além de reduzir o risco de injeção de SQL. Este tópico demonstra como configurar regras de segurança e revisar instruções SQL em um arquivo XML.

Informações básicas

Antes de implantar um projeto em produção, revise todas as instruções SQL relacionadas para impedir que códigos não conformes afetem seus serviços ativos. No entanto, revisar manualmente cada instrução SQL pode atrasar significativamente o processo de desenvolvimento.

Para resolver esse desafio, o DMS oferece o recurso SQL review. Esse recurso inspeciona instruções SQL com base em um conjunto de regras de segurança e fornece recomendações de otimização. Você também pode personalizar essas regras de segurança. Este tópico utiliza as seguintes regras:

Recomendação de cláusulas WHERE em instruções UPDATE e DELETE.
Detecção de risco de injeção de SQL.
Verificação de índice no plano de execução.
Recomendação para atualizar a coluna modification time em instruções UPDATE.
Detecção de risco de force index.

Pré-requisitos

Crie uma tabela chamada test_sql_review_table na instância de banco de dados de destino. Em seguida, preencha-a com dados usando o recurso test data construction. 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;

Prepare o arquivo com as instruções SQL a serem revisadas. Este exemplo usa um arquivo XML baseado no framework MyBatis.

Você também pode clicar em MyBatis Sample code para baixar o arquivo. O arquivo contém o seguinte código:

<?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. As outras regras mantêm suas configurações padrão. O exemplo a seguir mostra como configurar essa regra no modo de colaboração de segurança. Para obter mais informações, consulte Configure SQL review optimization suggestions.

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

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

Nota
Se você usar o console do DMS no modo normal, escolha Security and disaster recovery (DBS) > Security Rules na barra de navegação superior.
No painel de navegação à esquerda, clique em SQL review optimization suggestions.
Na coluna Actions da regra de segurança desejada, clique em Edit.
Localize a regra de segurança WHERE clauses are recommended in UPDATE and DELETE statements e clique em Edit à direita.
Na caixa de diálogo Rule content configuration, defina Behavioral action como Must Improve e clique em OK.

Etapa 2: Enviar SQL para revisão

Este exemplo demonstra como revisar as instruções SQL no arquivo XML baseado em MyBatis e modificá-las conforme os resultados. Para obter mais informações, consulte SQL review.

Faça login no DMS 5.0.
Na barra de navegação superior, escolha Database Development > SQL Review > SQL Audit Ticket.

Nota
Se você usar o console do DMS no modo simples, passe o ponteiro sobre o ícone no canto superior esquerdo do console e escolha All Features > Database Development > SQL Review > SQL Audit Ticket.
Na página Apply for SQL Review Ticket, configure os parâmetros necessários e clique em Submit.
Nota
- Selecione a instância de banco de dados associada ao conjunto de regras de segurança desejado.
- Carregue o código de exemplo da seção de pré-requisitos.

Visualize os resultados da revisão de SQL e faça os ajustes necessários.

O sistema retorna o seguinte Check Result:

SQL	Resultado da revisão	Sugestão
`<delete id="deleteXxxx"> DELETE FROM test_sql_review_table </delete>`	Must Improve: Recomenda-se uma cláusula WHERE para instruções DELETE. Nota Por padrão, esta regra está definida como Suggest Improve. Na Etapa 1, ela foi alterada para Must Improve.	Adicione uma cláusula WHERE conforme seus requisitos de negócio. Se você pretende excluir todos os dados da tabela, use `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: Usar um cifrão ($) para concatenar SQL, como em `${pk}`, apresenta risco de injeção de SQL.	Substitua `${pk}` por `#{pk}` para prevenir 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: Instruções UPDATE também devem atualizar a coluna de hora de modificação: `gmt_modified`. Potential Issue: O plano de execução não utiliza um índice. Index Recommendation: O DMS recomenda adicionar um índice.	Adicione `gmt_modified = NOW()` à cláusula SET da instrução UPDATE. No painel Details, acesse a aba Index recommendations para obter a instrução SQL de adição do índice. Em seguida, envie uma alteração DDL sem bloqueio para executá-la. Para obter mais informações, consulte Perform lock-free schema changes using a lockless change ticket. Exemplo: `UPDATE test_sql_review_table SET db_id=#{dbId}, gmt_modified = NOW() WHERE detail_id=#{detailId} and is_delete='N'`

Nota

Para SQL dinâmico em um arquivo XML, o DMS tenta substituir variáveis para recuperar o Execution plan do SQL. Em caso de sucesso, visualize os detalhes do Execution plan no painel Details.
Também é possível substituir manualmente os resultados automatizados da revisão de SQL para problemas sem correção disponível ou desnecessários. Escolha Manual Review > Approve ou Disapprove.

Após fazer os ajustes, atualize a página e verifique novamente os resultados da revisão de SQL.

Clique em Submit for Approval. O administrador ou DBA revisará as instruções SQL novamente.

Após a aprovação, o processo do ticket é encerrado.

Nota
Se houver instruções SQL no estado Check Failed ou Parsing Exception, o sistema reportará um erro ao clicar em Submit for Approval.