0

I'm trying to duplicate a column, but I would like to encrypt it with not very high data value, and I was able to do it, well, just the first part.

CREATE TABLE probeta (
  id int primary key,
  numero_documento varchar ,
  documento_prefijo varchar,
  numero_documento_crypt varchar,
  documento_prefijo_crypt varchar 
);
id num_doc Doc Doc encrypt
1 11111 11
2 22222 22
3 33333 33
4 44444 44
5 55555 55

With this basic query, I can do what I need

UPDATE probeta SET numero_documento_crypt =(pgp_sym_encrypt(numero_documento::varchar, 'psw','compress-algo=1, cipher-algo=aes'))
where 1=1;
id num_doc Doc Doc encrypt
1 11111 11 \xc30d0407030248ab17f709+120
2 22222 22 \xc30d04070302abd258de7c+120
3 33333 33 \xc30d0407030298735de291+120
4 44444 44 \xc30d0407030210d81eb985+120
5 55555 55 \xc30d040703026cdab3d82f+120

but

The length of the encrypted field is very long, about 140 characters.

When I try to do it as follows:

UPDATE probeta SET numero_documento_crypt = encrypt(numero_documento, 'key', 'aes')
where 1=1;

this to try to decrease the size of the field, but I have not succeeded.

The idea of ​​the process is to deliver encrypted data, but, when you need to know what value an encrypted data means, I can answer it, so do not use one-way encryption.

i try whit this:

UPDATE probeta SET numero_documento_crypt =crypt('numero_documento', gen_salt('des')) where 1=1;

And this is the result:

id num_doc Doc Doc encrypt
1 11111 11 JiiU/WaYnqUwc
2 22222 22 C7W4f2lpkSKbI
3 33333 33 7DqjVN0kbzlI2
4 44444 44 m5psBwH03F./I
5 55555 55 skn3VAeCnXqA2

the doc_encrypt is short, and is unique, this solve my problem, butttt, again, the problem in this case is, that I cannot decipher that value, because it does not receive any seeds.

The idea of ​​this encryption is to pass only the encrypted data to a user, but when, when I need to know what value it refers to, I can decipher it.

Thanks to all who gave their opinion, I hope I have corrected what they required.

Could someone give me an opinion?

4
  • Could you please not use images for the reasons outlined in this link. What do you mean by but I would like to encrypt it with not very high data value,? Could you please provide a fiddle (dbfiddle.uk) with your table, sample data and your desired result. Nov 30 '21 at 9:44
  • Of course, in one moment edit my post.
    – Omar Noa
    Nov 30 '21 at 12:35
  • Please go to dbfiddle.uk and CREATE your tables and type in your data. Less chance of typo errors, no duplication of effort by those trying to answer and a single source of truth! Help us to help you! Nov 30 '21 at 16:30
  • topanswers.xyz/databases?q=1943
    – Omar Noa
    Nov 30 '21 at 20:14
0

I'm trying to duplicate a column, but I would like to encrypt it with not very high data value

If the Data is not "high value", then why do you want to encrypt it?

The length of the encrypted field is very long, about 140 characters.

This is perfectly normal.
What makes you think that this is a problem?

The idea of ​​the process is to deliver encrypted data, but, when you need to know what value an encrypted data means, I can answer it

I suspect that the Application you're writing that will make this Data available to Users will also need to be able to decrypt it (and possibly re-encrypt it as well).

If your Users really have no way of ever accessing the Data, then use the "Most Secure Data Protection of All"(TM) - don't store it!

2
  • Wait, let me understand something, I want to encrypt a value, a very large piece of data, just that, but your logic tells me just not to do it? It may be a simple college experiment, does that have a problem?
    – Omar Noa
    Nov 30 '21 at 12:40
  • Encrypting data needs space to store it in (it's /always/ larger than the original) and processing time to do the encryption/decryption. If you don't need to, why waste the time and effort? Having encrypted this Data, what are you going to /do/ with it? If you intend to read it back and put it in front of a User (through your Application) then your Application needs to be able to decrypt it. If you don't intend for the user to /ever/ see it, why should you store it /at all/, encrypted or not?
    – Phill W.
    Nov 30 '21 at 14:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.