Guia rápido para uso do RMAN - Don't Panic!

Você continua usando exp e expdp como ferramentas de backup? Porque?

Toda vez que assumo um ambiente de banco legado e me deparo com um backup sendo feito via Export (exp) ou Data Pump Export (expdp) ou ainda pior, "cold backup via shell script".
E toda vez eu sou obrigado a perguntar "porque vocês estão fazendo seus backups com export"? As respostas são das mais variadas possíveis, entre "não sei" e entre "não fomos nós que desenvolvemos a rotina", mas fico atônito quanto ouço respostas do tipo "é mais fácil do que o RMAN".

Esta semana isto ocorreu novamente. Bom, sinto em lhes informar, mas o uso do RMAN (Recovery Manager) é tão fácil e prático que, na minha opinião, a unica resposta válida para não usa-lo seria "eu não sei como se usa, por isto tenho medo de usar".

Além disto tal como consta na documentação do Oracle Database, o export (logical backup) pode ser utilizado como estratégia complementar para proteção dos dados, mas jamais como sendo a principal estratégia.

Admitir que não sabemos algo não é uma deficiência, afial de contas ninguém nasce sabendo tudo, mas continuar em uma zona de conforto e não querer aprender a usar uma ferramenta tão importante, neste caso em particular eu considero muito arriscado.

Por isto como eu disse no começo "DON'T PANIC", vamos desmistificar este dogma.

Para começar é preciso ter em mente que a principal função de uma "ferramenta de backup" não é apenas garantir um backup fácil e rápido, mas principalmente é garantir uma restauração rápida, eficiente e consistente dos dados perdidos.

Como é que você tem garantido a consistência dos seus dados usando "expdp"? Em 99% dos casos eu vejo scripts/rotinas de backup com "expdp" que não usam os parâmetros corretos para garantir a consistência da exportação de dados.

Então eis os 5 passos para começar a usar o Oracle Recovery Manager o quanto antes, afinal de contas, antes tarde do que nunca.

Prefácio

Para que seja possível efetuar o backup do banco de forma "online", primeiramente é necessário ativar o "modo archivelog" para que os redologs sejam armazenados e nenhuma transação seja perdida durante a execução do backup. Caso você ainda não tenha feito isto deve se reiniciar a instância de banco de dados da seguinte forma:

sqlplus / as sysdba
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;

Caso contrário será apenas possível efetuar backups com o banco desligado, veja a documentação a respeito.

Quanto ao destino padrão dos Archivelogs e Backups eu costumo sempre que possível utilizar a Flash Recovery Area (FRA). Para isto deve se configurar os parâmetros "db_recovery_file_dest" (local de destino) e "db_recovery_file_dest_size" (tamanho máximo da área).
Quando o uso da FRA atingir o valor do tamanho máximo da área definido o Oracle irá se encarregar de eliminar backupsets e archivelogs obsoletos automaticamente, veremos como fazer isto manualmente no parte 3 deste artigo.

Como destino da FRA use sempre um disco (ou grupo de discos LUN, LVM, ASM, etc) que de preferencia não seja físicamente o mesmo já utilizado por outro banco de dados vivo, a fim de evitar concorrência durante o backup.

Eu considero como uma política muito simples e eficiente manter de 1 a 15 dias de backup em disco (para acesso mais rápido) e os demais em unidades externas, preferencialmente unidades de fita. Lembre-se que a política de retenção de dados deve ser definida junto ao seu cliente ou gestores de segurança de dados, etc. não tome decisões sem antes consultar e discuti-las com os demais responsáveis pela segurança dos dados.

1) Bem vindo ao maravilhoso mundo novo, entre nele sem medo 

Para se conectar no Recovery  Manager como "sysdba" basta usar o comando "rman target /", mais fácil impossível:

sh> rman target /

Ao se conectar deve aparecer uma mensagem informando "connected to target database: <nomedobanco> (DBID=<numero>)". É importante anotar o número do DBID em um local seguro, pois ele pode ser necessário no futuro.

Existem diversas opções para o comando "rman" caso você use uma opção inválida, tal como "rman help" serão exibidas as opções válidas. Leia a documentação.

Uma vez dentro da ferramenta é possível se fazer tudo que se deseja com relação a backup e recuperação de dados. E o Guia de Referencia possui uma relação de todos os comandos da ferramenta.

2) Configure, se quiser, apenas uma vez

O mais importante que deve ser lembrado é que estas configurações são persistentes, ou seja, uma vez atribuído um determinado valor ele não precisa ser reconfigurado a cada execução.

Para exibir a configuração atual da ferramenta execute:

RMAN> SHOW ALL;

Todos os comandos listados podem ser reexecutados com outros valores para que a alteração seja feita. A princípio basta que sejam reconfigurados os seguintes valores:

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

Este comando informa o RMAN que ele sempre que um backup for feito ele também deve extrair um backup dos arquivos "controlfile" e "spfile" automaticamente ao fim do backup.

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;

Este comando define a política de retenção de backups para uma janela de 2 dias. Veremos que esta configuração torna muito fácil a remoção dos backups antigos.
Ao definir o tamanho da janela deve se levar em conta o espaço disponível para armazenamento dos backups, portanto ajuste de acordo com o espaço que houver disponível no seu ambiente. 

Caso você não queira utilizar a Flash Recovery Area para armazenar seus backups é possível se alterar o destino padrão dos backups através do comando:

RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/adirectory/anotherdirectory/%U'

Acho que não preciso dizer que deve se ler a documentação para um entendimento completo dos parâmetros e opções de configuração.

3) Executando um backup simples em apenas 1 linha, ou talvez 2

Agora, toda vez que se desejar fazer um backup, basta executar um simples comando:

RMAN> BACKUP DATABASE;

Sinceramente me diga, quando isto é mais complicado do que um expdp e todos aqueles parametros: FULL, SCHEMAS, FLASHBACK_SCN ou FLASHBACK_TIME (muitos esquecem destes 2 para manter os dados consistentes, deixando o "backup lógico" Completamente inútil), DUMP_FILE, LOG_FILE, etc?

Este backup será dividido em um ou mais "backup sets" (guarde bem este nome), que são conjuntos de datafiles, archivelogs, etc., por padrão cada "backupset" é formado por 1 "backup piece" que consiste em um formato proprietário de arquivo. A grosso modo, tendo o(s) backupset(s) e os archivelogs é possível restaurar o banco para qualquer "ponto no tempo" da vida do banco de dados.

Tudo bem, vou ser sincero, eu menti um pouco, é que minhas rotinas de backup normalmente consistem em 3 linhas:

RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE;
RMAN> DELETE NOPROMPT OBSOLETE;
RMAN> SQL "alter database backup controlfile to trace as /adirectory/controlfile.txt"

A opção "incremental level 0" eu costumo usar sempre, o que me permite decidir se quero tornar os backups parte de uma política de backup incremental do dia para a noite. Não vou me aprofundar neste assunto no momento, caso não esteja familiarizado busque na documentação.

Já o comando "delete noprompt obsolete" é para remover qualquer backup e archivelog que não se enquadre na política de retenção. Como falei na etapa 2, a configuração de uma janela de retenção torna muito fácil a limpeza dos backups antigos, ou seja, todos os backups e archivelogs que já estejam "obsoletos" (fora da janela de retenção) são apagados com 1 simples comando.

E o SQL "alter database backup controlfile to trace" gera uma versão em texto, caso o controlfile precise ser recriado manualmente. Confie em mim, isto pode ocorrer.

Uma regra que é valida sempre em qualquer situação tanto nas áreas de TI quanto na vida real, quanto mais simples e claro for um sistema mais fácil de entende-lo, mante-lo e menos sujeita a falhas ele se torna. Tente manter suas rotinas de backup sempre o mais simples e diretas possíveis configurações de destinos, formatação, parallelismo, etc pode ser feita com o comando "CONFIGURE" e persistidas evitando scripts imensos para backups padronizados.

Outra recomendação importante, não adianta fazer backup dos archivelogs para dentro da própria Flash Recovery Area. Se ela já é o destino dos archivelogs, para que copia-los para onde eles já estão?
Faz sentido sim fazer backup dos archivelogs para um outro destino em disco, ou servidor de arquivos remoto, ou unidade de fita, etc. Os archivelogs já fazem parte da política de backup, por tanto não faça "backups de backups" sem a devida necessidade.

Duas recomendação simples de performance para o backup:

• Evitar que o destino dos backup seja o mesmo disco onde estão os dados, evitando um "gargalo" tal como ja foi dito;
• Evitar que o destino inicial do backup seja uma unidade remota (ex. NFS) em uma rede de banda estreita e/ou tráfego intenso; 

A ideia é sempre fazer o backup do banco de forma rápida, para evitar um longo tempo de contenção de leitura dos datafiles. Eu costumo fazer um primeiro backup do database (ou seja, datafiles, controlfile e spfile) em disco, tal como ilustrei acima, e efetuar backup dos backupsets e archivelogs para unidades externas (fita ou NFS) usando os comandos "backup backupset" e "backup archivelog" (não vou abordar este script aqui pois ele depende da mídia de destino do backup), sendo que se for fazer via NFS recomendo que seja através de uma rede de banda Gigabit (ou superior) dedicada exclusivamente para tráfego de backup.

E por fim, para verificar se temos backup de todos os arquivos do database podemos utilizar o comando:

RMAN> REPORT NEED BACKUP;

4) Encontre e valide backups sem complicações

Para listar seus backups você basicamente irá utilizar o comando "LIST" em suas diversas variações. Um resumo geral dos seus backups pode ser visto com o comando:

RMAN> LIST BACKUP SUMMARY;

Já para obter mais detalhes sobre o conteúdo e local dos seus backups execute o comando:

RMAN> LIST BACKUP;

Este comando lhe trará o valor descrito como "Piece Name", que indica o nome do arquivo onde se encontra aquele "pedaço" do backup. Outro dado importante nesta relação é o valor "Tag", pois varias operações podem ser feitas utilizando-se este valor, tais como validação, remoção e restore.

O RMAN também é uma excelente ferramenta para encontrar possíveis corruções do banco de dados, veja a documentação e procure a referencia dos comandos "VALIDATE DATABASE" e "BACKUP DATABASE ... VALIDATE".

Mas tendo em mente que a função principal é garantir um restore de dados é considerada uma boa prática testar periódicamten se os backups não estão corrompidos.
Assim usamos a opção VALIDATE tal como no comando abaixo:

RMAN> RESTORE DATABASE VALIDATE;

Este comando irá efetuar uma leitura completa dos backupsets necessários para restaurar todos os datafiles, controlfiles e spfiles do banco, sem escreve-los em disco.
Mas também é uma boa prática de tempos em tempos se efetuar restores e recover completo do banco de dados em um servidor/host, assim testando a validade do seu backup e deixando você mais preparado caso o piór venha a acontecer. Para isto recomendo a leitura do seguinte documento.

5) RMAN na hora H - Restore e recover

Ninguém deseja perder seus dados, mas é um fato inegável que mais cedo ou mais tarde isto pode acontecer.

O Recovery Manager possui diversas opções de restore/recover de falhas de mídia, com ele é possível restaurar desde 1 unico bloco de dados corrompido, um datafile perdido, até o banco de dados inteiro. É importante ter em mente que se houve apenas a corrupção de 1 unico bloco, não existe necessidade de se restaurar todo o banco de dados. Por isto é importante que se leia atentamente a documentação sobre "Preparação e Planejamento da Estratégia de Restore e Recover" e também as informações a respeito de BLOCKRECOVER.

Para demonstrar o processo de restauração ilustraremos um caso muito ruim, não o pior, mas um caso de perda total do banco de dados. Lembramos aqui que existen dezenas se não centenas ou milhares de cenários possíveis para um restore de banco de dados, por isto é importante estudar e conhecer todas as funcionalidades para que as decisões corretas sejam tomadas no momento de se efetuar a restauração.

Cenário: digamos que tenha sido perdido o storage, ou os discos, onde estavam todos os arquivos que compõe o database (datafiles, controlfiles e redologs). Apenas se salvando o ORACLE_HOME, o SPFILE (que estava no ORACLE_HOME) e a Flash Recovery Area

Supondo que o destino original dos arquivos já esteja pronto para recebe-lo novamente (filesystems formatados e montados, ou grupo ASM criado e montado, etc.)

Então para restaurarmos todo o banco de dados ao seu destino original, devemos abrir uma sessão do RMAN e podemos executar os seguintes comandos, aqui explicados passo a passo:

RMAN> SET DBID <número do DBID>;

RMAN> STARTUP FORCE NOMOUNT;

Uma vez que a instância esteja inicializada o RMAN saberá onde encontrar a Flash Recovery Area pelo parametro "db_recovery_file_dest".

RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP; 

Este comando efetua o restore do controlfile, pois é dentro dele que esta o repositório do RMAN que contém o catalogo de todos os backups, e sem ele a ferramenta não saberia  quais backupsets precisam ser restaurados, ou onde eles se encontram.

RMAN> ALTER DATABASE MOUNT; 

Com a montagem do banco (leitura do controlfile) permitimos que o RMAN tome conhecimento do repositório de catalogo dos backups. 

RMAN> RESTORE DATABASE PREVIEW;

Este comando lista todos os backupsets que serão utilizados para restaurar e recuperar o banco de dados por completo. Pode ser útil checar se todos os backup pieces e archivelogs estão disponíveis antes de iniciar o processo de recuperação.

RMAN> RESTORE DATABASE;

Efetua o restore completo dos datafiles que não estejam em seu destino e com o SCN apropriado. 

RMAN> RECOVER DATABASE;

É a ação que atualiza/sincroniza os datafiles para manter a integridade dos dados. Podemos dizer a grosso modo que com a execução do recover as transações são "refeitas"  em todo o banco, aplicando os arquivelogs necessários (e redologs se ainda existirem ao menos 1 cópia disponível) até o ultimo estado válido conhecido, fazendo com que o banco de dados esteja apto a ser aberto. 

Caso estejam faltando archivelogs e/ou apenas os redologs dizemos que este é um "recover incompleto", pois algumas transações serão perdidas. Neste caso é importante informar aos responsáveis pelas aplicações até que momento foi possível se recuperar os dados, pois talvez algumas atividades tenham que ser refeitas por parte dos usuários.

RMAN> ALTER DATABASE OPEN RESETLOGS; 

Caso os redologs tenham sido perdidos (que na minha experiência é o caso mais comum) o banco de dados é aberto novamente com o uso do "OPENRESETLOGS".

Após isto o procedimento de Restauração e Recuperação completa do banco é finalizado. E os usuários poderão voltar a utilizar as aplicações normalmente.

Epílogo

O repositório principal do catalogo de recuperação (RMAN REPOSITORY) fica dentro do próprio "control file" do database, e por tanto não é necessário ficar inventando formas de catalogar e indexar seus arquivos de dump ou cópias de datafiles dos cold backups com scripts mirabolantes.

Mas de qualquer forma é aconselhável criar um repositório externo, em uma base de dados Oracle que de preferencia esteja em outro servidor, para isto leia a documentação.


Para se aprofundar no assunto, arregace as mangas e leia os manuais:

• Documentação Básica Backup/Recovery Oracle 10gR2
• Documentação Avançada Backup/Recovery Oracle 10gR2
• Guia de referencia do RMAN p/ Oracle 10gR2 - sintaxe dos comandos 

PS.: Este uso básico é válido para diferentes versões do Oracle Database, mas recomendo que se leia sempre a documentação específica da versão em uso. Neste artigo nos baseamos na versão 10gR2.

Removendo chek constraints duplicadas em bases Oracle

Talvez você também esteja com problemas devido a check constraints do tipo "<coluna> not null" estarem duplicadas no seu banco.

No meu caso específico eu descobri que este problema ocorreu pois o time de "análise" utiliza uma ferramenta de UML (Enterprise Architect) para modelar um banco relacional quando deveria estar utilizando uma ferramenta de ER. A ferramenta definiu as colunas como não nulas, mas também criou check constraints para a forçar a restrição. Acontece que o Oracle (ao menos até a versão 10.2) não valida se constraints do tipo "check" possuem as mesmas clausulas de restrição.

Enfim vamos ao que interessa...

Para solucionar o problema escrevi o código de uma procedure que efetua a remoção das 2, ou mais, constraints do tipo "not null" na mesma coluna e por fim altera a tabela para redefinir a coluna, esta redefinição precisa ser feita pois com a remoção das contraints a coluna passaria a ser "nullable", por isto não basta só remover as constraints.

Antes da procedure de remoção das duplicações em si, é necessário também criar a procedure abaixo, para converter a coluna "search_condition" da view "all_constraints" de LONG para VARCHAR2, para que seja possível efetuar as comparações dos strings.

CREATE OR REPLACE FUNCTION  f_get_cons_check( p_owner VARCHAR2,p_constraint VARCHAR2)
    RETURN VARCHAR2
AS
   l_cursor    INTEGER DEFAULT dbms_sql.open_cursor;
   l_n         NUMBER;
   l_long_val  VARCHAR2(4000);
   l_long_len  NUMBER;
   l_buflen    NUMBER := 4000;
   l_curpos    NUMBER := 0;

BEGIN
   dbms_sql.parse( l_cursor,
        'SELECT search_condition 
           FROM all_constraints
          WHERE owner = :x
            AND constraint_name = :y',
        dbms_sql.native );
   dbms_sql.bind_variable( l_cursor, ':x', p_owner );
   dbms_sql.bind_variable( l_cursor, ':y', p_constraint );
   dbms_sql.define_column_long(l_cursor, 1);
   l_n := dbms_sql.execute(l_cursor);
   IF (dbms_sql.fetch_rows(l_cursor)>0)
   THEN
      dbms_sql.column_value_long(l_cursor, 1, l_buflen, l_curpos , l_long_val, l_long_len );
   END IF;
   dbms_sql.close_cursor(l_cursor);
   RETURN l_long_val;
END f_get_cons_check;

Esta função é baseada no código do Thomas Kyte publicada na issue "Long to Varchar2 conversion" do "Ask Tom"


E então temos a procedure para remoção das constraints duplicadas. Observe que estou apenas imprimindo os comandos de DML para remoção das constraints e alteração da tabela, mas nada impede que se você se sentir seguro altere estas chamadas para um "execute immediate".

PROCEDURE p_remove_dup_notnull_cons(p_schema VARCHAR2)
AS
  TYPE dup_constraints_type  IS RECORD
    ( owner            all_constraints.owner%TYPE,
      table_name       all_constraints.table_name%TYPE,
      column_name      all_cons_columns.column_name%TYPE,
      constraint_name  all_constraints.constraint_name%TYPE,
      cnt_check        NUMBER,
      ord              NUMBER);
      
  TYPE ref_cursor IS REF CURSOR RETURN dup_constraints_type;
  
  c_dup_constraints ref_cursor;
  
  TYPE t_dup_constraints IS TABLE OF dup_constraints_type
      INDEX BY PLS_INTEGER;
  
  l_dup_constraints t_dup_constraints;
  
  l_row             PLS_INTEGER;
  v_cnt             INTEGER;
  v_ddl_modify_coln VARCHAR2(4000);
  
  
BEGIN

  OPEN  c_dup_constraints FOR 
        SELECT owner, table_name, column_name, constraint_name, cnt_check, ord
          FROM( SELECT owner, table_name, column_name, constraint_name, 
                       count(*) over (partition by owner, table_name, column_name) as cnt_check,
                       row_number() over (partition by owner, table_name, column_name order by last_change) as ord
                  FROM (SELECT owner, table_name, constraint_name, last_change, trim('"' FROM substr(cons_check,1,instr(cons_check,' ')-1)) as column_name
                          FROM (SELECT owner, table_name, constraint_name, last_change, f_get_cons_check( owner, constraint_name) as cons_check
                                  FROM all_constraints
                                 WHERE owner = p_schema
                                   AND constraint_type IN ('C','?'))
                          WHERE cons_check LIKE '%IS NOT NULL'))
              --WHERE cnt_check > 1
            ORDER BY table_name, column_name, ord;

  LOOP
    FETCH c_dup_constraints BULK COLLECT INTO l_dup_constraints LIMIT 1000;
    DBMS_OUTPUT.PUT_LINE('-- ' || l_dup_constraints.COUNT || ' DUPLICATED CONSTRAINTS FOUND');
    EXIT WHEN l_dup_constraints.COUNT = 0;
    l_row := l_dup_constraints.FIRST;
    WHILE l_row IS NOT NULL
    LOOP
       IF l_dup_constraints(l_row).ord = 1
       THEN
         -- GERA DDL PARA REDEFINIR A COLUNA COMO "NOT NULL" APÓS A REMOÇÃO DAS CONSTRAINTS
         v_ddl_modify_coln := f_ddl_mod_tab_column(l_dup_constraints(l_row).owner,
                                                   l_dup_constraints(l_row).table_name,
                                                   l_dup_constraints(l_row).column_name,
                                                   ' NOT NULL ENABLE');
       END IF;
       
       -- DROPA CONSTRAINT DUPLICADA
       DBMS_OUTPUT.PUT_LINE('ALTER TABLE ' || l_dup_constraints(l_row).owner || 
                          '.' || l_dup_constraints(l_row).table_name ||
                          ' DROP CONSTRAINT ' || l_dup_constraints(l_row).constraint_name || ';');
       
       IF l_dup_constraints(l_row).ord = l_dup_constraints(l_row).cnt_check
       THEN
         -- EXECUTA DDL MODIFY PARA REDEFINIR A COLUNA COMO "NOT NULL"
         DBMS_OUTPUT.PUT_LINE(v_ddl_modify_coln || ';');
       END IF;
       l_row := l_dup_constraints.NEXT(l_row);
    END LOOP;
  
  END LOOP;

  IF c_dup_constraints%ISOPEN
  THEN
    CLOSE c_dup_constraints;
  END IF;

EXCEPTION 
WHEN OTHERS THEN
    IF c_dup_constraints%ISOPEN
    THEN
      CLOSE c_dup_constraints;
    END IF;
    RAISE;
END p_remove_dup_notnull_cons;

Sinta-se livre para utilizar e modificar estas rotinas como bem entender, por sua conta e risco. Mas se for publica-la por favor referencie minha autoria.

Mamães e SQL injection

Caro leitor, este é meu primeiro "post", então estou um pouco nervoso.

Eu espero que vocês sejam gentis comigo nesta primeira vez.

Até pensei em pagar um jantar romântico ou um cinema antes, mas como estou meio duro vamos logo ao que interessa.

Sem mais delongas, já vou começando com uma inserção fatal... e uma mãe louca.

O problema

Certamente nós "garotos de programa" sempre estamos a mercê das loucuras que mães de filhos infelizes podem nos trazer, apesar de normalmente o problema maior ser os "filhos das mães". Bom sou fã do XKCD blog e outro dia me matei de rir com uma tirinha deles. Enviei esta tirinha para meus colegas DBAs e muitos deles, ou não entenderam, ou não tem senso de humor, o que me deixou muito decepcionado, então resolvi escrever sobre ela.

  

Entendeu a piada?

Caso tenha entendido, ou não da a mínima, vá ver outra bobagem na internet ou volte pro Facebook/Orkut e não perca seu tempo com o que escrevi abaixo, porque a coisa agora vai realmente ficar séria.

Caso você não tenha entendido, então você precisa saber que no mundo existem mães que conhecem o problema do SQL Injection, e elas sabem que muitos desenvolvedores como você ainda não conhecem este tipo de falha.

Comecemos com o papo NERD pra valer.

Análise do problema:

SQL injection é uma falha muito comum em aplicações que utilizam bancos de dados, que permite que um código malicioso seja inserido em um banco de dados, e assim permitindo tanto que dados sejam apagados, ou consultados, ou até mesmo que a peformance do banco seja posta em risco causando o chamado "denial-of-service" (negação de serviço).

Tenho visto códigos que permitem este tipo de ataque aos montes ao longo da minha carreira então acho que é mais do que meu dever ajudar meus amigos analistas a entender este tipo de problema.

Apesar de toda informação disponível na internet, ainda existem inúmeros desenvolvedores que insistem em somente concatenar a entrada de suas aplicações com suas chamadas SQL (SQL statements), tanto para DQL (select) como para DML (insert, delete e update). Possibilitando que se "insira o código malicioso", por isto chamamos este tipo de ataque de SQL injection, ou também, SQL insertion.

Então vamos ver como isto ocorre. Vou ilustrar aqui apenas o erro conhecido por filtro incorreto de caracteres de escape, tomando como exemplo o ataque da mamãe do pequeno Bobby Tables:

1) O desenvolvedor implementa da seguinte forma a simples inserção de um aluno:

statement = "insert into STUDENTS(student_name) values ('" + varStudentName  + "');"

2) Ao adicionar o nosso pequeno Bobby Tables no sistema da escola a secretária ingenuamente insere o nome do menino na aplicação, assim a variável varStudentName é preenchida:

varStudentName = "Robert'); drop table STUDENTS;--"

3)  Durante a execução do insert temos o caos:

statement = "insert into STUDENTS(student_name) values ('Robert'); drop table STUDENTS;--');"

Veja que inserção é realizada com sucesso, mas logo em seguida temos um "drop TABLE" e o fim do statement é ignorado como comentário. Por fim, caso o user da aplicação possua grant para efetuar o "drop table", a tabela é deliberadamente apagada.


Sem levarmos em consideração tecnologias como Flashback Table e etc., a escolinha do pequenino Bobby Tables, infelizmente terá que recuperar seus dados do ultimo backup válido.

Existem inúmeras outras possibilidades de códigos que podem ser inseridos com diferentes propósitos em diferentes circunstâncias. Aqui temos um cenário absurdo com uma mãe NERD psicótica, mas o mais preocupante é quando este tipo de brecha é implementada em aplicações Web, onde o próprio usuário final pode inserir códigos ao seu bel-prazer.

Vale lembrar, que além da falha de segurança em si, este tipo de implementação de execução dos SQL statements pode degradar consideravelmente o desempenho do banco de dados pois não faz uso adequado do "library cache" (no caso de um SGDB Oracle), mas talvez abordemos isto em um post futuramente.

Como resolvê-lo:

Existem diversas formas de se resolver este tipo de falha, algumas mais elegantes porém mais trabalhosas para o desenvolvedor, outras mais fáceis e também mais susceptíveis a erros, mas enfim, a minha sugestão é a utilização de uma destas duas técnicas:

1) Parameterized statements (binded variables): é uma pratica própria para os desenvolvedores da camada de persistência na aplicação, que consiste em utilizar rotinas de preparo e execução dos SQL statements, passando os valores das variáveis como parâmetros e não simplesmente concatenando o SQL em um grande string.  

    Diversas linguagens oferecem diferentes APIs, por tanto se familiarize com a linguagem na qual esteja programando. A API Java JDBC, por exemplo, oferece a classe "PreparedStatement" que encapsula a associação de variáveis e a execução dos SQL statements.

Exemplo:

PreparedStatement objPrep = objCon.prepareStatement("insert into STUDENTS(student_name) values(?)");
objPrep.setString(1,varStudentName);
objPrep.executeQuery();

2)    User roles + pl/sql packages: esta é uma forma que gosto muito de trabalhar, pois permite um maior controle pelo desenvolvedor SQL de todas as chamadas que as aplicações fazem ao banco, tanto do ponto de vista de segurança quanto de desempenho.

    No caso específico do Oracle cria-se "packages" PL/SQL, que abstraem a idéia de classes, com seus getters e setters, ou seja, métodos próprios para inserção e consulta dos dados, por meio de procedures e funções que retornam cursores para consultas. E isto permite que sejam criados "user roles" distintos, com permissões especificas de execução para estes pacotes. 

   Assim, todas as solicitações de acesso aos dados são tratadas dentro do código PL/SQL. Este método tem diversos "prós" e "contras", mas eu considero esta uma solução muito boa para se "espremer bits" do banco de dados, mas é importantíssimo que se tenha um bom domínio da linguagem PL/SQL. Lembrando que no caso do Oracle os pacotes procedurais também pode ser implementados em código nativo (C, C++, Java, etc.), o que pode lhe trazer  tanto ganho quanto perda no desempenho.

   É importante não confundir este método de implementação com o "mapeamento objeto-relacional", na verdade este método pode dificultar o mapeamento, assim como dificulta bastante a portabilidade do código para outras plataformas.

Referências:

 - Wikipedia - SQL injection

 - JDBC PreparedStatement Class