Shulou(Shulou.com)06/01 Report--

This article shows you how to batch process sensitive information in the database, the content is concise and easy to understand, it will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Preface

For some sensitive data, it is often encrypted and then stored in storage, which is one of the simplest measures for data security.

Nothing is more common than mobile phone numbers and ID numbers. I believe there are still many companies that store these sensitive information in plain text.

In case someone discovers a loophole in the system, or is dragged into a library, it will basically be cool.

Lao Huang also recently found such a problem in a system within the company, which startled me when I first found it, such a naked plaintext mobile phone number and ID number.

The first reaction is to encrypt the two data.

Since encryption is required, the system in use will certainly be affected, and tens of millions of data can not be solved in a few minutes.

The database used in this system is Aliyun's RDS (SQL Server).

Let's briefly talk about the treatment plan on Lao Huang's side.

How to deal with it

The whole process is divided into three steps:

Modify the field length system in the database to update a version to do compatible processing, write to use a unified encryption method, read, add a length judgment, when the length is greater than 20, need to decrypt operation, in order to ensure that the ciphertext will not be displayed directly. Modify data

Previously, the database set the length of both fields to 20, but now it is adjusted to 150.

The company has unified a set of encryption and decryption methods, so the system adjustment of this piece is relatively simple, unified processing in the data layer.

The rest is to go to the database to change the data.

Unified encryption method, there is no way to use directly in the database, so we can only write a separate program to deal with.

The change data is also subdivided into the following three steps.

Read source data, encrypt related fields write encrypted data to a temporary table to update the relevant fields of the source table according to the temporary table

One of the core here is batch write and batch update. If you update one by one, I don't know how long it will take to deal with all of them.

First, write a console program, divide the encrypted data into batches according to ID, write the encrypted data at a frequency of 5, 000 pieces at a time, and take 1 million data as a batch.

Var flag = true

Var begin = 0

Var tmpEnd = begin + 5000

Var end = 1000000

While (flag)

{

/ / omit read data

Foreach (var item in list)

{

DataRow dr = dt.NewRow ()

Dr ["Id"] = item.Id

Dr ["IDCard"] = GetEncryptValue (item.IDCard? "")

Dr ["PhoneNo"] = GetEncryptValue (item.PhoneNo? "")

Dr ["IDCardRaw"] = item.IDCard? ""

Dr ["PhoneNoRaw"] = item.PhoneNo? ""

Dt.Rows.Add (dr)

}

Using (SqlConnection conn = new SqlConnection (connStr))

{

Conn.Open ()

SqlBulkCopy bulkCopy = new SqlBulkCopy (conn)

BulkCopy.DestinationTableName = "enc_tmp"

BulkCopy.BatchSize = dt.Rows.Count

BulkCopy.WriteToServer (dt)

}

Begin = tmpEnd

TmpEnd + = 5000

If (tmpEnd > = end | | list = = null | |! list.Any ())

{

Flag = false

}

Console.WriteLine (begin)

}

To ensure the speed of writing, don't index that temporary table until the data is written in, and then index Id.

After writing the data into the temporary table, the following is a batch update directly with the SQL script.

-- indexing

Create index idx_enc_tmp_id on enc_tmp (id)

-- batch updates

Update dbo.yourtable

Set PhoneNo= a.PhoneNo, IDCard = a.IDCard

From dbo.yourtable b

Inner join dbo.enc_tmp a

On a.id=b.id

-- the update here depends on the configuration of the database. If the configuration is high, you can update it at once, otherwise a batch of 250000 or 500000 is recommended.

-- where a.id > = 0 and a.id 0 and id

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.