Alberti或Caesar Cipher在Varchar Field上的SQL中掩盖了数字

发布于 2025-01-26 08:07:03 字数 475 浏览 2 评论 0原文

我使用了SAS中的Alberti密码来掩盖数字，该数字在不同的长度方面效果很好。但是，我在Redshift SQL中执行相同的任务遇到了麻烦。

如果我有一个相同长度的数字，我可以简单地使用子字符串的组合并拆开并摘取蒙面的数字。如果我的col1是123456789，那将是：

substring(col1,9,1)||substring(col1,2,2)||substring(acct_num_full,3,1)||...

但是，我要掩盖的列可以在5-40个字符的任何位置，因此上面的示例将不起作用。我已经尝试了lpad和rpad标准化长度，但对于较短的数字不起作用。

有没有人有任何想法，或者在不同角色列中在SQL中曾在SQL中做过Alberti或Caesar Cipher？我需要一种也可以逆转的算法，以便我可以解密。

原文

I've used an Alberti cipher in SAS to mask numbers which works great on varying lengths. However, I'm having trouble doing the same task in Redshift SQL.

If I have a number all the same length, I could simply use a combination of substrings and to pick apart and Frankenstein a masked number. If my COL1 is 123456789, it would be something like:

substring(col1,9,1)||substring(col1,2,2)||substring(acct_num_full,3,1)||...

However, the column I'm trying to mask could be anywhere from 5-40 characters and so the example above would not work. I've tried lpad and rpad to standardize the lengths but it doesn't work for the shorter numbers.

Does anyone have any ideas or have done Alberti or Caesar ciphering in SQL on a varying character column? I would need an algorithm that could be reversed as well so I could decrypt.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

同尘 2025-02-02 08:07:03

Translate（）函数可以执行字符替换-e href =“ https://docs.aws.amazon.com/redshift/latest/dg/r_translate.html” rel =“ nofollow noreferrer”> htttps：// docs。 aws.amazon.com/redshift/latest/dg/r_translate.html

只需提供凯撒（Caesar Cypher）作为chars_to_to_replace和chars_to_to_substitute strings。

======================================

来自wikipedia- https://en.wikipedia.org/wikipedia.org/wiki/wiki/caesar_cipher

select 'THE QUICK BROWN FOX JUMPS OVER THE LAZY DOG'::text as pt
  into foo;

select pt,
 translate(pt,
           'ABCDEFGHIJKLMNOPQRSTUVWXYZ',
           'XYZABCDEFGHIJKLMNOPQRSTUVW') as ct;

The translate() function can perform character substitutions - https://docs.aws.amazon.com/redshift/latest/dg/r_TRANSLATE.html

Just provide the caesar cypher as the chars_to_replace and chars_to_substitute strings.

===========================================

For example performing the caesar cypher example from wikipedia - https://en.wikipedia.org/wiki/Caesar_cipher

select 'THE QUICK BROWN FOX JUMPS OVER THE LAZY DOG'::text as pt
  into foo;

select pt,
 translate(pt,
           'ABCDEFGHIJKLMNOPQRSTUVWXYZ',
           'XYZABCDEFGHIJKLMNOPQRSTUVW') as ct;

回复收藏 0 原文

变身佩奇 2025-02-02 08:07:03

我能够使用SAS创建一个工作，这使我可以使用上面描述的技术将输出复制并粘贴到我的SQL语句中，并使用一堆substring语句：

/* create scaffold of column lengths */
data temp1; 
    do j=5 to 40;
        output;
    end;
run;

/* create SQL case text and randomize order of substring syntax */
data temp2; set temp1;
    do i=1 to j;
        str="substring(col1,"||strip(put(i,best.))||",1)";
        u = rand("Uniform");
        output;
    end;
run;

/* sort of column length of randomized order var */
proc sort data = temp2;
    by j u;
run;

/* put into wide format */
proc transpose data = temp2 out=wide;
    by j;
    var str;
run;

/* concatenate all SQL substring statements */
data wide1; set wide;
    format str $2000.;
    str="when length(col1)="||strip(put(j,best.))||" then "||catx("||", of col:);
    keep str;
run;

/* output as a text file and copy and paste into your SQL select staement between case ... end */

I was able to create a work-around using SAS which allows me to copy and paste the output into my SQL statement using the technique I described above with a bunch of substring statements:

/* create scaffold of column lengths */
data temp1; 
    do j=5 to 40;
        output;
    end;
run;

/* create SQL case text and randomize order of substring syntax */
data temp2; set temp1;
    do i=1 to j;
        str="substring(col1,"||strip(put(i,best.))||",1)";
        u = rand("Uniform");
        output;
    end;
run;

/* sort of column length of randomized order var */
proc sort data = temp2;
    by j u;
run;

/* put into wide format */
proc transpose data = temp2 out=wide;
    by j;
    var str;
run;

/* concatenate all SQL substring statements */
data wide1; set wide;
    format str $2000.;
    str="when length(col1)="||strip(put(j,best.))||" then "||catx("||", of col:);
    keep str;
run;

/* output as a text file and copy and paste into your SQL select staement between case ... end */

回复收藏 0 原文

~没有更多了~