EXECUTE IMMEDIATE: SQL injection prevention / EXECUTE IMMEDIATE: Prevenção SQL injection

This article is written in Englsih and Portuguese (original version here)
Este artigo está escrito em Inglês e Português (versão original aqui)


English version
Recently I was requested to implement some stored procedures on a customer site. The request was urgent and I must admit I didn't pay enough attention. The procedures needed to accept some values that would be used inside some SQL statements. Basic best practices mandate that we must check all parameters and in such cases use prepared statements. These principles provide for better interaction with the calling layer and more important, help us avoid a classic and nasty security flaw called SQL injection.
The idea behind SQL injection is terribly simple and efficient: By supplying specially crafted parameters we explore the developer's mistake of not sanitizing those parameters and blindly use them to construct SQL statements. By doing so we may be able to change the meaning of those statements or eventually terminate them and include our own. A couple of examples:

1. An authentication page uses the SQL statement:
   

   SELECT
       COUNT(*)
   FROM
       user_table
   WHERE
       user_name = $USER AND user_pass = '$PASS'
   

   
   and we supply these arguments:
   

   'john_doe'
   'dummy'' OR ( user_name = ''john_doe'' )'
   

   
   
2. An online banking uses the SQL statement:
   

   UPDATE
       balance_account
   SET
       ammount = ammount - $VALUE
   WHERE
       account_number = $SOME_NUMBER
   

   
   and we supply these arguments:
   

   - 1000
   our_account_number
   

   
   

Well, after the procedures were created and some basic testing was done I had a bit more time to look at the code and I noticed I used the SQL instruction "EXECUTE IMMEDIATE..." where I was just concatenating some SQL with the provided arguments. Some of them were being slightly checked, but others were not checked at all. I was alarmed and ashamed by that piece of code and I decided to prove it was crappy... I tried to send weird parameters which included a semi-colon and a full statement following it. As an example consider something like:

CREATE PROCEDURE test_proc(table_name VARCHAR(20))
    EXECUTE IMMEDIATE "DROP TABLE " || table_name
END PROCEDURE;

And I tried to send "some_table;DROP DATABASE d"

(consider that some_table is an existent table name and "d" and existing database)
It raised error

26058: EXECUTE IMMEDIATE and PREPARE in SPL cannot allow multiple SQL statements

Which was a bit of a surprise. I tried other variations and they all failed.
The error description is clear. And the manual too:

http://www-01.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.sqls.doc/ids_sqs_0768.htm?lang=en

As a security measure or not, EXECUTE IMMEDIATE and PREPARE, inside stored procedures don't allow the execution of more than one instruction. A reason for that can be to avoid this specific type of SQL injection exploitation. So it was a good surprise to see that the good people in R&D have thought about something that I failed to (at my first attempt). This is a good security measure. But don't get too enthusiastic.... Terminating a statement and including another on in a variable or argument is just one of the SQL injection attacks. It will be useless on the two examples above. So be aware and never do the same error I was about to do: Always check your parameters and if possible PREPARE your statements. That's the way to avoid these security issues.
For the so called "cursory" statements we can use PREPARE, DECLARE, OPEN, FETCH, CLOSE and FREE. For non-cursory statements we need to use EXECUTE IMMEDIATE. In any case, it's essential to validate all inputs!





Versão Portuguesa
Recentemente foi-me pedido que implementasse algumas stored procedures num cliente. O pedido era urgente e devo admitir que não prestei a devida atenção. Os procedimentos necessitavam de aceitar alguns valores que seriam depois usados em instruções SQL. As boas práticas mais simples ditam que temos sempre de validar os inputs e sempre que possível usar prepared statements. Estes princípios permitem uma melhor interação com a camada que executa os procedimentos (ou genericamente funções), e mais importante, ajudam-nos a evitar uma falha de segurança bastante má, chamada SQL injection.
A ideia base do SQL injection é terrivelmente simples mas eficiente: Fornecendo argumentos "construidos", exploramos falhas dos programadores que não validam nem purificam os argumentos que lhes são passados, usando-os cegamente na construção de instruções SQL. Ao fazê-lo podemos alterar o sentido dessas instruções e eventualmente até terminá-las e escrevendo outras imediatamente a seguir. Um par de exemplos:

1. Uma página de autenticação usa a seguinte instrução:
   

   SELECT
       COUNT(*)
   FROM
       tabela_utilizadores
   WHERE
       nome_utilizador = $USER AND pass_utilizador = '$PASS'
   

   
   e fonecemos estes argumentos:
   

   'john_doe'
   'dummy'' OR ( nome_utilizador = ''john_doe'' )'
   

   
   
2. Uma aplicação de banco online utiliza esta instrução SQL:
   

   UPDATE
       tabela_contas
   SET
       saldo = saldo - $VALUE
   WHERE
       numero_conta = $UM_NUMERO
   

   
   e fornecemos estes argumentos:
   

   - 1000
   our_account_number
   

   
   

Bom, depois de ter criado os procedimentos, e após alguns testes básicos de funcionalidade, tive um pouco mais de tempo para examinar o código e verifiquei que estava a usar a instrução "EXECUTE IMMEDIATE...", e estava apenas a concatenar um pedaço de SQL com os argumentos fornecidos. Alguns tinham algum tipo de validação muito básica, mas outros não eram validados de todo. Fiquei alarmado e envergonhado com algum daquele código e decidi provar a mim mesmo que aquilo não prestava.... Tentei enviar parâmetros compostos de forma específica, incluind um ";" e uma instrução completa em seguida. A título de exemplo considere algo como:

CREATE PROCEDURE test_proc(table_name VARCHAR(20))
    EXECUTE IMMEDIATE "DROP TABLE " || table_name
END PROCEDURE;

E tentei usar como argumento "tabela_teste;DROP DATABASE d"

(considere que "tabela_teste" era o nome de uma tabela existente e que "d" era o nome de uma base de dados também existente)
Obtive um erro:

26058: EXECUTE IMMEDIATE and PREPARE in SPL cannot allow multiple SQL statements

Isto foi um pouco surpreendente. Tentei outras variações e todas falharam.
A descrição do erro parecia clara. E o manual também:

http://www-01.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.sqls.doc/ids_sqs_0768.htm?lang=en

Por medida de segurança ou não, o EXECUTE IMMEDIATE e o PREPARE dentro de procedimentos SPL não permitem a execução de mais que uma instrução. Uma razão para tal pode ser evitar este tipo específico de exploração de SQL injection. A ser assim foi uma surpresa agradável que o pessoal de I&D se tenha lembrado de algo que eu me esqueci (na primeira versão). Isto constitui uma boa medida de segurança. Mas não fique demasiado entusiasmado.... Terminar uma instrução e incluir outra é apenas uma das variantes do SQL injection. É inútil nos dois exemplos acima. Portanto esteja atento e nunca cometa o mesmo erro que estive á beira de cometer: verifique sempre os parâmetros e se possível utilize o PREPARE das suas instruções SQL. Essa é ainda a melhor forma de evitar estes problemas.
Para as instruções baseadas em cursores podemos usar o PREPARE, DECLARE, OPEN, FETCH, CLOSE e FREE. Para instruções que não retornem um conjunto de resultados necessitamos de usar o EXECUTE IMMEDIATE. Em qualquer caso é essencial validar todos os inputs!