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:
- 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'' )'
- 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
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:
- 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'' )'
- 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
CREATE PROCEDURE test_proc(table_name VARCHAR(20))
EXECUTE IMMEDIATE "DROP TABLE " || table_name
END PROCEDURE;
(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
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!
Posts
