String truncate. Column level Encryption / Corte de strings. Encriptação de colunas
Truncation of strings and it's impact on column level encryption (original version here: http://informix-technology.blogspot.com/2016/12/string-truncate-column-level-encryption.html)
English version
A recent costumer engagement re-activated a dormant issue on my mind... I've already mentioned this is posts, answers in IIUG mailing list, internal and external chats and discussions... Personally I think this is one of the top annoying things in Informix. I'm referring to the fact that on a non-ANSI informix database we truncate a string on insert if it's length exceeds the length of the field. And we do this silently... no error.
There are two RFEs opened for this (appropriately defined as duplicates). The original one is 33830 and the other (duplicate) is 53804. I've seen several reasons for not implementing this, which I'd like to oppose (again):
- It's stated in the ANSI standard that it should work like this
Although the ANSI standard is hard to read, there are some paragraphs that seem to suggest this. But even if it's clearly stated there, it isn't what people want. This can corrupt data. Anybody would prefer an error.
Additionally we don't truncate on ANSI mode databases. - We would be changing previous behavior
True. But that could be an option and by default we could keep the old behavior. The author of the RFE suggests a new parameter in $ONCONFIG. That is an option, but I'd prefer also an option on CREATE DATABASE (we already introduced an option for NLSCASE SENSITIVE). Ideally we would have a new ALTER DATABASE to change it. The $ONCONFIG parameter could and should be used to set the default (if the option was not specified on the CREATE DATABASE statement).
I would not create a new $ONCONFIG parameter. I'd prefer having more options on the EILSEQ_COMPAT_MODE parameter which already controls functionality around the same topic. - It would be hard to implement
Having basic programming knowledge and considering we don't do this for ANSI mode databases (see test below), I doubt this would be too hard to implement. Somehow I can imagine a piece of current code like
if ( ANSI_MODE_FLAG && length(input) > col_length)
raise_exception(1279);
which would become:
if ((ANSI_MODE_FLAG || AVOID_TRUNCATE_FLAG) && length(input) > col_length)
raise_exception(1279);
ANSI_MODE_FLAG and AVOID_TRUNCATE_FLAG are assumed to be flags set from the logging mode and from the database (or session) options
- That's not a database problem. The application must check it's inputs
Although I can agree with the idea that applications should check the inputs, I know many of them don't check the length. And as we don't silently truncate a big number (100000 for example) when inserted into a SMALLINT column, I can't understand why we do it with strings. The database must help keeping data integrity. And it fails doing that for strings
castelo@primary:informix-> dbaccess -e stores_ansi test_ansi_truncate.sql
Database selected.
DROP TABLE IF EXISTS test;
Table dropped.
CREATE TABLE test
(
col1 CHAR(5)
);
Table created.
INSERT INTO test VALUES('123456');
1279: Value exceeds string column length.
Error in line 7
Near character position 32
377: Must terminate transaction before closing database.
853: Current transaction has been rolled back due to error
or missing COMMIT WORK.
castelo@primary:informix->
Why don't we simply use ANSI mode databases? Well... They have other limitations (can't use datablades) and conversion of existing ones would require application changes.
Ok... so string truncation is a problem by itself. But this article is about a specially complex and nasty effect of string truncation.
Users looking into column level encryption will notice that the encrypted form of the data will be stored in a character column. And the encryption process will "enlarge" the length of the data. Even more complex, the length of encrypted data depends on various factors. The initial number of "characters" (or digits) is one. Others are if it uses hint or not and the encryption algorithm. Below is a table from the manual that maps the "N" original digits/characters to the final result depending on the algorithm and usage of the hint:
N
|
ENCRYPT_TDES
No Hint
|
ENCRYPT_AES
No Hint
|
ENCRYPT_TDES
With Hint
|
ENCRYPT_AES
With Hint
|
1 to 7
|
35
|
43
|
87
|
99
|
8 to 15
|
43
|
43
|
99
|
99
|
16 to 23
|
55
|
67
|
107
|
119
|
24 to 31
|
67
|
67
|
119
|
119
|
32 to 39
|
75
|
87
|
131
|
139
|
40 to 47
|
87
|
87
|
139
|
139
|
100
|
163
|
171
|
215
|
227
|
200
|
299
|
299
|
355
|
355
|
500
|
695
|
707
|
747
|
759
|
The previous values can be calculated using the following formulae (again, accordingly to the manual):
- Encryption by ENCRYPT_TDES( ) with no hint:
Encrypted size = (4 x ((8 x((N + 8)/8) + 10)/3) + 11) - Encryption by ENCRYPT_AES( ) with no hint:
Encrypted size = (4 x ((16 x((N + 16)/16) + 10)/3) + 11) - Encryption by ENCRYPT_TDES( ) with a hint:
Encrypted size = (4 x ((8 x((N + 8)/8) + 50)/3) + 11) - Encryption by ENCRYPT_AES( ) with a hint:
Encrypted size = (4 x ((16 x((N + 16)/16) + 50)/3) + 11)
So, effectively, using the above calculations it should be possible to validate if the value inserted by the user would fit a specific column length. But that would have to be implemented for each column. Could introduce some errors, and would make it impossible to implement column level encryption without changing the application, which is otherwise almost possible (the only change is to provide a password at the session level, if that fits the use case).
And after all this discussion, what is the problem in truncating the strings in this context? Pretty simply it will make it impossible to decrypt the data, so effectively it corrupts data in an unrecoverable way. There's no way to overstate this. An application, application developer or a DBA cannot afford to have that risk. As I usually say when I'm discussing this, if we loose a few characters of a name, email address, or street address, it's possible that a human can recover the missing data by looking at what is left. But with column level encryption even the loss of a single character means the data is lost. Not something we want to deal with, or have to explain to business managers.
What can we do? We could start by opening a bunch of PMRs and link them to the RFEs above... but apart from pressing IBM there's actually something very easy we can do to solve this for the very strict context of column level encryption.
As mentioned, recently I had a customer who is concerned with the upcoming EU data protection regulations and is considering their options to address the requirements in that new compliance challenge. One of the options is to use column level encryption, but they were highly concerned with the above scenario. So I gave this matter a considerable thought and I think I came up with a relatively reasonable workaround (although I hate to be forced to deal with this in the first place).
The solution is elegant, relatively lightweight, doesn't require any extra application changes and hopefully should be able to avoid the nasty hypotheses of data loss or corruption.
My first approach was to implement a trigger, and try to verify if the input data would fit the column. I quickly realized that the data as seen by the trigger is already truncated, so the approach would not work. But at that same moment it became clear that another approach would work: If the data is already truncated (or not, depending on the sizes of course), all I had to do was try to decrypt it. If the data was actually truncated, it would raise an error. And if I try that within a trigger, the triggering operation will naturally fail, which if the intended outcome. You can check the code at the end of the article.
Trying to decrypt the data would have the following problems:
- It would be expensive in terms of CPU resources
- If the data has been encrypted with a specific password passed to the encrypt functions (ENCRYPT_AES() or ENCRYPT_TDES() ), I would not be able to DECRYPT_CHAR(), without knowing the password
So basically I need to create a procedure that receives the encrypted data, apply the GETHINT() and let the magic happen. This procedure should be called from triggers set on the column/table (INSERT and UPDATE) that pass the "new" values as parameter. I choose to use LVARCHAR(32000) as it should cover most use cases. Smart blob encryption was not considered. In those cases we don't have a size limit...
The code is shown at the end of the article. Let's see what happens when we run it:
bica@primary:fnunes-> dbaccess -e stores test_truncate.sql
Database selected.
DROP TABLE IF EXISTS test_encrypt;
Table dropped.
CREATE TABLE test_encrypt
(
col1 SERIAL,
col2 CHAR(43)
);
Table created.
SET ENCRYPTION PASSWORD 'blog_password';
Encryption password set.
DROP PROCEDURE IF EXISTS check_encryption;
Routine dropped.
CREATE PROCEDURE check_encryption(str LVARCHAR(32000))
DEFINE dummy CHAR(32);;
SELECT GETHINT(str) INTO dummy FROM sysmaster:sysdual;;
END PROCEDURE;
Routine created.
;
CREATE TRIGGER i_test_encrypt INSERT ON test_encrypt REFERENCING NEW AS new_data
FOR EACH ROW
(
EXECUTE PROCEDURE check_encryption(new_data.col2)
);
Trigger created.
CREATE TRIGGER u_test_encrypt UPDATE OF col2 ON test_encrypt REFERENCING NEW AS new_data
FOR EACH ROW
(
EXECUTE PROCEDURE check_encryption(new_data.col2)
);
Trigger created.
INSERT INTO test_encrypt VALUES(0, ENCRYPT_AES('Row 1'));
1 row(s) inserted.
INSERT INTO test_encrypt VALUES(0, ENCRYPT_AES('Row 2'));
1 row(s) inserted.
INSERT INTO test_encrypt VALUES(0, ENCRYPT_AES('This longer row will probably not fit!'));
26005: The encrypted data is wrong or corrupted.
Error in line 32
Near character position 1
UPDATE test_encrypt SET col2 = ENCRYPT_AES('This longer row will probably not fit!') WHERE col1 = 1;
26005: The encrypted data is wrong or corrupted.
Error in line 34
Near character position 1
UPDATE test_encrypt SET col2 = ENCRYPT_AES('This fits!') WHERE col1 = 2;
1 row(s) updated.
Database closed.
bica@primary:fnunes->
So, as can be seen, I created a table with a column (col2) defined as CHAR(43) which won't be enough for some data I'll try to INSERT/UPDATE.
Then I setup the session encryption password. I create the procedure and the triggers on the table.
And I move on to the DML. The first two INSERTs use short values and they work. The third INSERT uses a longer string which encrypted version won't fit col2. It raises an error 26005. Then I try a similar string but within an UPDATE and I face the same error. And an update with short length works.
I believe this is a simple, clean and robust solution for this problem. But as usual, use it at your own risk. And don't forget this is a workaround for a specific scenario (column level encryption) to overcome a problem I believe should not exist in the first place.
Versão Portuguesa
Uma visita recente a um cliente reavivou um assunto que andava adormecido na minha mente... Já mencionei isto em artigos, respostas na lista de discussão do IIUG, conversas e discussões internas e externas... Pessoalmente penso que é uma das coisas mais irritantes no Informix. Refiro-me ao facto de que numa base de dados não-ANSI, o Informix corta strings na inserção e alteração se o comprimento das mesmas exceder o tamanho da coluna. E tal acontece de forma silenciosa. Sem erro.
Existem dois RFEs abertos para isto (apropriadamente detetados como duplicados). O original é o 33830 e o outro (duplicado) é o 53804. Já me foram sugeridas várias razões para a não implementação disto, as quais uma vez mais gostaria de refutar:
- É referido que o standard ANSI sugere que este é o comportamento correto
Embora o standard ANSI seja difícil de ler, existem alguns parágrafos que parecem sugerir isto. Mas mesmo que fosse claro, existem contra-argumentos para isto. Ainda que seja o standard, não é o que as pessoas querem. Isto pode corromper dados. Qualquer pessoa preferirá um erro. Adicionalmente e paradoxalmente nós não fazemos o corte em bases de dados criadas em modo ANSI - Estaria a mudar-se o comportamento anterior
Verdade. Mas isto poderia ser uma opção e por omissão manter-se o comportamento antigo. O autor do RFE sugere um parâmetro no $ONCONFIG. Seria uma opção, mas eu preferiria também uma opção no CREATE DATABASE (já introduzimos uma opção para NLSCASE SENDITIVE). Idealmente deveríamos também ter um novo ALTER DATABASE para mudar a opção. O parâmetro do $ONCONFIG poderia e deveria ser usado como a definição por omissão (se a opção não fosse especificada no CREATE DATABASE).
Pessoalmente não criaria um novo parâmetro, mas antes daria novos valores possíveis ao parâmetro EILSEQ_COMPAT_MODE, que já controla funcionalidades em torno deste tópico - Seria difícil de implementar
Tendo conhecimentos básicos de programação, e considerando que já hoje nós não cortamos as strings em bases de dados criadas em modo ANSI (ver o teste abaixo), duvido que isto fosse muito complexo de implementar. De alguma forma imagino um pedaço de código atual semelhante a:
if ( ANSI_MODE_FLAG && length(input) > col_length)
raise_exception(1279);
que deveria tornar-se:
if ((ANSI_MODE_FLAG || AVOID_TRUNCATE_FLAG) && length(input) > col_length)
raise_exception(1279);
ANSI_MODE_FLAG é assumido que será uma flag dependente do mode de logging da base de dados e AVOID_TRUNCATE_FLAG seria dependente da opção de criação da base de dados ou do parâmetro no $ONCONFIG - Isto não é um problema de base de dados. A aplicação tem de validar os seus inputs
Embora tenha de concordar com a ideia que as aplicações devem validar os seus inputs, sei que muitas delas não verificam o comprimento das strings ou fazem-no pelas definições de estruturas de dados. E se não cortamos de forma silenciosa um número grande (como 100000) ao tentar inserir num SMALLINT, não consigo entender porque o fazemos com strings. A base de dados tem como função essencial assegurar a integridade dos dados, de acordo com as definições dos mesmos. E falha quando se trata de o fazer em strings.
castelo@primary:informix-> dbaccess -e stores_ansi test_ansi_truncate.sql
Database selected.
DROP TABLE IF EXISTS teste;
Table dropped.
CREATE TABLE teste
(
col1 CHAR(5)
);
Table created.
INSERT INTO teste VALUES('123456');
1279: Value exceeds string column length.
Error in line 7
Near character position 32
377: Must terminate transaction before closing database.
853: Current transaction has been rolled back due to error
or missing COMMIT WORK.
castelo@primary:informix->
Porque não passamos simplesmente a usar bases de dados em modo ANSI? Bem.... têm outras limitações (não podem usar datablades) e isso requereria mudanças no código das aplicações.
Ok... Então o corte de strings é um problema em si mesmo. Mas este artigo é sobre um efeito complexo e devastador desse corte.
Utilizadores que estejam a considerar o uso de encriptação de colunas, verificarão que a forma encriptada dos dados deverá ser guardada numa coluna com um tipo de dados alfa-numéricos (CHAR ou uma variante). E saberão também que o processo de encriptação fará "crescer" o tamanho dos dados. Ainda mais complicado, o tamanho dos dados encriptados dependerá de vários fatores: O número de caracteres (alfa-numéricos) originais é um desses fatores. Outros serão se se usa ou não uma hint, e o algoritmo de encriptação. Abaixo está uma tabela retirada do manual que mapeia o número original de dígitos / caracteres com o tamanho final, dependendo do algoritmo usado e da utilização ou não de hint:
N
|
ENCRYPT_TDES
No Hint
|
ENCRYPT_AES
No Hint
|
ENCRYPT_TDES
With Hint
|
ENCRYPT_AES
With Hint
|
1 to 7
|
35
|
43
|
87
|
99
|
8 to 15
|
43
|
43
|
99
|
99
|
16 to 23
|
55
|
67
|
107
|
119
|
24 to 31
|
67
|
67
|
119
|
119
|
32 to 39
|
75
|
87
|
131
|
139
|
40 to 47
|
87
|
87
|
139
|
139
|
100
|
163
|
171
|
215
|
227
|
200
|
299
|
299
|
355
|
355
|
500
|
695
|
707
|
747
|
759
|
Os valores anteriores podem ser calculados com as seguintes fórmulas (de acordo com o manual):
- Encriptação com ENCRYPT_TDES( ) sem hint:
Tamanho dados encriptados = (4 x ((8 x((N + 8)/8) + 10)/3) + 11) - Encriptação com ENCRYPT_AES( ) sem hint:
Tamanho dados encriptados = (4 x ((16 x((N + 16)/16) + 10)/3) + 11) - Encriptação com ENCRYPT_TDES( ) com hint:
Tamanho dados encriptados = (4 x ((8 x((N + 8)/8) + 50)/3) + 11) - Encriptação com ENCRYPT_AES( ) com hint:
Tamanho dados encriptados= (4 x ((16 x((N + 16)/16) + 50)/3) + 11)
Portanto, efetivamente usando os cálculos acima deverá ser possível validar se um valor inserido por um utilizador caberá numa determinada coluna depois de encriptado. Mas isto teria de ser implementado para cada coluna. Poderia introduzir alguns erros, e tornaria impossível implementar a encriptação de colunas sem mudar a aplicação, o que de outra forma seria praticamente possível (a única alteração necessária seria o providenciar uma password de encriptação ao nível de sessão, se tal for permitido pelo caso de uso).
E depois de toda esta discussão, qual é o problema em cortar strings neste contexto? Muito simplesmente, tornará impossível efetuar a desencriptação, pelo que efetivamente tal situação corrompe dados de forma irrecuperável. Não há forma de exagerar esta conclusão. Uma aplicação, um programador ou um DBA não podem dar-se ao luxo de correr esse risco. Como habitualmente digo quando estou a discutir este assunto, se perdermos uns poucos caracteres de um nome, um endereço de correio ou endereço postal, é possível que um ser humano possa recuperar os dados cortados através da consulta do que sobrou. Mas com encriptação de colunas, mesmo a perda de um só carater implica que os dados estejam perdidos. Não é coisa com que queiramos lidar ou que tenhamos de explicar a pessoas responsáveis pelo negócio.
Então o que podemos fazer? Podíamos começar por abrir um rol de PMRs e ligá-los a este RFE acima., mas para além de pressionarmos a IBM, existe algo que efetivamente podemos fazer para resolver o contexto estrito de encriptação de colunas.
Como mencionado, recentemente tive contacto com um cliente que está preocupado com a nova regulação de proteção de dados da EU e está a considerar as opções que têm neste novo desafio de compliance. Uma das opções que têm é usar encriptação do nível da coluna, mas estavam altamente preocupados com o cenário acima. Por causa disso dediquei um considerável esforço ao tema e penso que cheguei a uma forma de contornar o problema (embora odeie ter de lidar com tal situação).
A solução é elegante, relativamente leve, não requer nenhuma modificação na aplicação e espero que esteja à altura de evitar a terrível hipótese de perda de dados ou corrupção.
A minha primeira abordagem foi implementar um trigger e tentar verificar se os dados de input (encriptados) caberiam na coluna. Rapidamente me apercebi que o trigger já vê os dados "cortados", pelo que a abordagem não resultaria. Mas no mesmo momento ficou claro que outra abordagem semelhante resultaria: se os dados já estão potencialmente "cortados", tudo o que deveria fazer seria desencriptá-los. Se tivessem sido efetivamente cortados isto despoletaria um erro. E se tal acontecer dentro de um trigger então a operação que despoletou o trigger iria por arrasto falhar. Pode verificar o código no final do artigo. Tentar desencriptar os dados levantaria os seguintes problemas::
- Seria dispendioso em termos de recursos de CPU
- Se os dados tivessem sido encriptados com uma password especifica passada às funções de encriptação (ENCRYPT_AES() or ENCRYPT_TDES() ), não seria capaz de efetuar a desencriptação sem saber a password
Assim, basicamente o que necessito é criar um procedimento que recebe os dados encriptados e aplica o GETHINT(), e deixar a magia acontecer. O procedimento deve ser chamado por triggers definidos na coluna / tabela (INSERT e UPDATE), que passam os "novos" valores como parâmetro
Escolhi o LVARCHAR(32000) como tipo do parâmetros dado que deverá cobrir a maioria dos casos. A encriptação de Smart Blobs não foi considerada. Até porque nesses casos não temos um limite de tamanho...
O código está visível no final do artigo. Vejamos o que acontece quando o corremos:
bica@primary:fnunes-> dbaccess -e stores test_truncate.sql
Database selected.
DROP TABLE IF EXISTS test_encrypt;
Table dropped.
CREATE TABLE test_encrypt
(
col1 SERIAL,
col2 CHAR(43)
);
Table created.
SET ENCRYPTION PASSWORD 'blog_password';
Encryption password set.
DROP PROCEDURE IF EXISTS check_encryption;
Routine dropped.
CREATE PROCEDURE check_encryption(str LVARCHAR(32000))
DEFINE dummy CHAR(32);;
SELECT GETHINT(str) INTO dummy FROM sysmaster:sysdual;;
END PROCEDURE;
Routine created.
;
CREATE TRIGGER i_test_encrypt INSERT ON test_encrypt REFERENCING NEW AS new_data
FOR EACH ROW
(
EXECUTE PROCEDURE check_encryption(new_data.col2)
);
Trigger created.
CREATE TRIGGER u_test_encrypt UPDATE OF col2 ON test_encrypt REFERENCING NEW AS new_data
FOR EACH ROW
(
EXECUTE PROCEDURE check_encryption(new_data.col2)
);
Trigger created.
INSERT INTO test_encrypt VALUES(0, ENCRYPT_AES('Row 1'));
1 row(s) inserted.
INSERT INTO test_encrypt VALUES(0, ENCRYPT_AES('Row 2'));
1 row(s) inserted.
INSERT INTO test_encrypt VALUES(0, ENCRYPT_AES('This longer row will probably not fit!'));
26005: The encrypted data is wrong or corrupted.
Error in line 32
Near character position 1
UPDATE test_encrypt SET col2 = ENCRYPT_AES('This longer row will probably not fit!') WHERE col1 = 1;
26005: The encrypted data is wrong or corrupted.
Error in line 34
Near character position 1
UPDATE test_encrypt SET col2 = ENCRYPT_AES('This fits!') WHERE col1 = 2;
1 row(s) updated.
Database closed.
bica@primary:fnunes->
Como se pode ver, crio uma tabela com uma coluna (col2) definida como CHAR(43) e que não será suficiente para alguns dados que irei tentar inserir/alterar.
Depois estabeleço a password de sessão. Crio o procedimento e os triggers na tabela. E passo ao DML. Os primeiros dois INSERTs usam valores curtos e vão funcionar. O terceiro INSERT usa uma string mais longa, cuja versão encriptada não cabe na coluna col2. Despoleta um erro 26005. Depois tento uma string semelhante mas via um UPDATE e encontramos o mesmo erro. Depois um UPDATE "curto" e funciona
Acredito que isto é simples, "limpo" e robusto para resolver o problema. Mas como é habitual, utilize por sua conta, peso e risco. E relembro que isto é uma forma de contornar um problema que penso que nunca deveria ter acontecido, num contexto específico que é a encriptação de colunas.
Code/Código
DROP TABLE IF EXISTS test_encrypt;
CREATE TABLE test_encrypt
(
col1 SERIAL,
col2 CHAR(43)
);
SET ENCRYPTION PASSWORD 'blog_password';
DROP PROCEDURE IF EXISTS check_encryption;
CREATE PROCEDURE check_encryption(str LVARCHAR(32000))
DEFINE dummy CHAR(32);
SELECT GETHINT(str) INTO dummy FROM sysmaster:sysdual;
END PROCEDURE;
CREATE TRIGGER i_test_encrypt INSERT ON test_encrypt REFERENCING NEW AS new_data
FOR EACH ROW
(
EXECUTE PROCEDURE check_encryption(new_data.col2)
);
CREATE TRIGGER u_test_encrypt UPDATE OF col2 ON test_encrypt REFERENCING NEW AS new_data
FOR EACH ROW
(
EXECUTE PROCEDURE check_encryption(new_data.col2)
);
INSERT INTO test_encrypt VALUES(0, ENCRYPT_AES('Row 1'));
INSERT INTO test_encrypt VALUES(0, ENCRYPT_AES('Row 2'));
INSERT INTO test_encrypt VALUES(0, ENCRYPT_AES('This longer row will probably not fit!'));
UPDATE test_encrypt SET col2 = ENCRYPT_AES('This longer row will probably not fit!') WHERE col1 = 1;
UPDATE test_encrypt SET col2 = ENCRYPT_AES('This fits!') WHERE col1 = 2;
Posts
No comments:
Post a Comment