确定列是加密密钥还是纯文本 [英] Determining whether a column is an encryption key or plain text

问题描述

在SQL Server表中，我们有一列类型为 varchar（25）的列，当它们应该使用AES加密时，这些列错误地插入了纯文本值。我们将从数据库中删除纯文本值。该计划是验证字段的块大小，尽管这会导致一些未加密的值。有没有其他标准可以检查可靠地识别有效的加密数据？

我们需要它作为一个只有T-SQL的解决方案。

更新

只需挖一点更深层次，就可以从Web服务中获取价值。此Web服务使用ASP.Net中的AES对其进行加密。它需要返回的字节数组，然后使用此方法将字节数组转换为字符串：

  static public string ByteArrToString（ byte [] byteArr）
 {
 byte val; 
 string tempStr =; （int i = 0; i< = byteArr.GetUpperBound（0）; i ++）
 {
 val = byteArr [i]; 
 
 if（val<（byte）10）
 tempStr + =00+ val.ToString（）; 
 else if（val<（byte）100）
 tempStr + =0+ val.ToString（）; 
 else 
 tempStr + = val.ToString（）; 
} 
 return tempStr; 
} 
  

为了清楚起见，我应该说我最初没有写这个代码！ p>

干杯

解决方案

不是真的，特别是因为编码方法不看起来正常对我对数据进行base64编码更为常见，这使得它非常独特。这真的取决于未加密的数据是什么，确定数据是否被加密是多么容易 - 例如，它是字，数字，是否有空格等（因为编码数据没有空格）

看起来您的编码数据都将以数字形式表示为字符串，因此根据数据长度，您可以看到列是否将转换为BIGINT。 / p>

不知道我头上最好的方法，但是这里有一个答案可能会帮助你在T-SQL中尝试转换StackOverflow-8453861

We have a column of type varchar(25) in a SQL Server table that mistakenly had plain text values inserted when they should have been encrypted with AES. We are going to remove the plain text values from the database. The plan was to verify the block size of the field, though this would cause some unencrypted values to be left. Is there any other criteria I can check to reliably identify valid encrypted data?

We need it to be a T-SQL only solution.

Update

Just dug a little deeper, it's getting the values back from a web service. This web service encrypts them using AES in ASP.Net. It takes the returned byte array and then it uses this method to conver the byte array to a string:

static public string ByteArrToString(byte[] byteArr)
{
  byte val;
  string tempStr = "";
  for (int i = 0; i <= byteArr.GetUpperBound(0); i++)
  {
    val = byteArr[i];
    if (val < (byte)10)
      tempStr += "00" + val.ToString();
    else if (val < (byte)100)
      tempStr += "0" + val.ToString();
    else
      tempStr += val.ToString();
  }
  return tempStr;
}

For clarity, I should say I did not originally write this code!

Cheers

解决方案

Not really, especially since the encoding method doesn't look normal to me. It is more common to base64 encode the data which makes it very distinctive. It really depends what the unencrypted data consists of as to how easily it is to determine whether the data is encrypted or not - for instance, is it words, numbers, does it have spaces etc (since the encoded data has no spaces for instance).

It looks like your encoded data will all be numeric represented as a string so depending on length of data, you could see if your column will cast to a BIGINT.

Not sure the best way off the top of my head but there is an answer here that might help you "try cast" in T-SQL StackOverflow-8453861

这篇关于确定列是加密密钥还是纯文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！