Sql-Server
創建隨機詞以混淆 SQL 列
有沒有人有任何 SQL 程式碼來自動生成單詞（全是字母，沒有數字）。
是否有任何內聯 SQL 可以在列上執行此操作？
我們正在嘗試混淆表中的名字/姓氏和其他單詞列。
此答案目前不適用於列，因為它在列中生成相同的結果。
select top (@stringlength) char(abs(checksum(newid())) % 26 + ascii('A')) from sys.all_objects for xml path('')我試過這個，但是功能很慢，內聯查詢可以更快，請隨時改進或重寫
CREATE VIEW [dbo].[RandomValueVw] AS SELECT RAND() as random_value CREATE function dbo.RandomWordGenerate(@length int) returns varchar(50) as begin DECLARE @text nvarchar(255), @i int SET @i = 0 SET @text = '' WHILE (@i < @length) BEGIN SET @text = @text + CHAR((select * from [dbo].[RandomValueVw]) * 26 + 65) SET @i = @i + 1 END return @text end
CRYPT_GEN_RANDOM是你這種事情的朋友。它創建了一個您想要的長度的簡單數字，並且與加密強度隨機數一樣輕巧。RAND()，相比之下，當使用相同的種子值呼叫時，總是會生成相同的數字序列，因此絕對不是密碼安全的。無論如何，根據您之前的問題，我們將添加一
[First Name]列和一[Last Name]列，如下所示：USE tempdb; GO DROP TABLE IF EXISTS dbo.MyTable; CREATE TABLE dbo.MyTable ( MyTableID int NOT NULL CONSTRAINT PK_MyTable PRIMARY KEY CLUSTERED IDENTITY(1,1) , [Date of Birth] date NOT NULL , [First Name] nvarchar(100) NOT NULL , [Last Name] nvarchar(100) NOT NULL ); GO在這裡，我們將插入幾個測試行：
INSERT INTO dbo.MyTable ([Date of Birth], [First Name], [Last Name]) VALUES ('1990-01-01', 'Max', 'Vernon') , ('1989-01-01', 'Apple', 'Book'); SELECT * FROM dbo.MyTable;結果：
+-----------+---------------+------------+-----------+ | 我的表ID | 出生日期 | 名字 | 姓氏 | +-----------+---------------+------------+-----------+ | 1 | 1990-01-01 | 最大 | 弗農 | | 2 | 1989-01-01 | 蘋果 | 圖書 | +-----------+---------------+------------+-----------+現在，我們將對錶執行更新語句：
DECLARE @MinAge int; DECLARE @MaxAge int; SET @MinAge = 10; SET @MaxAge = 20; UPDATE dbo.MyTable SET [Date of Birth] = DATEADD(DAY , (1 - (CONVERT(int, CRYPT_GEN_RANDOM(2)) % ((@MaxAge - @MinAge) * 365))) , CONVERT(date, DATEADD(YEAR, 1 - @MinAge, GETDATE())) ) , [First Name] = CONVERT(nvarchar(100), CRYPT_GEN_RANDOM(10)) , [Last Name] = CONVERT(nvarchar(100), CRYPT_GEN_RANDOM(10));現在，如果我們查看表格內容，我們會看到：
SELECT * FROM dbo.MyTable;+-----------+---------------+------------+-----------+ | 我的表ID | 出生日期 | 名字 | 姓氏 | +-----------+---------------+------------+-----------+ | 1 | 2003-07-22 | ⢢蝤偕龿ື | 槈ស㎫答덌 | | 2 | 2008-09-13 | ❈敘坄 | ꈨ氕㽲公園ે | +-----------+---------------+------------+-----------+如您所見，所有數據都發生了巨大變化。
如果您對上面範例中生成的隨機字元不太滿意，您可以使用這種替換
[First Name]and[Last Name]位來生成更少的混淆值：, [First Name] = REPLICATE(CHAR(CONVERT(int, (CRYPT_GEN_RANDOM(1) % 26) + 65)), 10) , [Last Name] = REPLICATE(CHAR(CONVERT(int, (CRYPT_GEN_RANDOM(1) % 26) + 65)), 10);這將產生以下輸出：
+-----------+---------------+------------+------------+ | 我的表ID | 出生日期 | 名字 | 姓氏 | +-----------+---------------+------------+------------+ | 1 | 2005-08-13 | PPPPPPPPP | DDDDDDDD | | 2 | 2002-08-12 | QQQQQQQQQQ | LLLLLLLLLL | +-----------+---------------+------------+------------+如果您真的需要人類可讀的單詞，您可以使用單詞列表將值隨機替換為實際單詞。在下面的範例中，我使用了BIP -0039 英語單詞表的一小部分；但是您可以輕鬆使用任何合適的單詞列表。
DROP TABLE IF EXISTS dbo.WordList; CREATE TABLE dbo.WordList ( Word varchar(20) NOT NULL CONSTRAINT PK_WordList PRIMARY KEY CLUSTERED , OrderBy AS (CONVERT(int, CRYPT_GEN_RANDOM(2))) ) ON [PRIMARY]; INSERT INTO dbo.WordList VALUES ('abandon') , ('ability') , ('able') , ('about') , ('above') , ('absent') , ('absorb') , ('abstract') , ('absurd') , ('abuse') , ('access') , ('accident') , ('account') , ('accuse') , ('achieve') , ('acid') , ('acoustic') , ('acquire') , ('across') , ('act') , ('action') , ('actor') , ('actress') , ('actual') , ('adapt') , ('add') , ('addict') , ('address') , ('adjust') , ('admit') , ('adult') , ('advance') , ('advice') , ('aerobic') , ('affair') , ('afford') , ('afraid') , ('again') , ('age') , ('agent') , ('agree') , ('ahead') , ('aim') , ('air') , ('airport') , ('aisle') , ('alarm') , ('album') , ('alcohol') , ('alert') , ('alien') , ('all') , ('alley') , ('allow') , ('almost') , ('alone') , ('alpha') , ('already') , ('also') , ('alter') , ('always') , ('amateur') , ('amazing') , ('among') , ('amount') , ('amused') , ('analyst') , ('anchor') , ('ancient') , ('anger') , ('angle') , ('angry') , ('animal') , ('ankle') , ('announce') , ('annual') , ('another') , ('answer') , ('antenna') , ('antique') , ('anxiety') , ('any') , ('apart') , ('apology') , ('appear') , ('apple') , ('approve') , ('april') , ('arch') , ('arctic') , ('area') , ('arena') , ('argue') , ('arm') , ('armed') , ('armor') , ('army') , ('around') , ('arrange') , ('arrest') , ('arrive') , ('arrow') , ('art') , ('artefact') , ('artist') , ('artwork') , ('ask') , ('aspect') , ('assault') , ('asset') , ('assist') , ('assume') , ('asthma') , ('athlete') , ('atom') , ('attack') , ('attend') , ('attitude') , ('attract') , ('auction') , ('audit') , ('august') , ('aunt') , ('author') , ('auto') , ('autumn') , ('average') , ('avocado') , ('avoid') , ('awake') , ('aware') , ('away') , ('awesome') , ('awful') , ('awkward') , ('axis') , ('baby') , ('bachelor') , ('bacon') , ('badge') , ('bag') , ('balance') , ('balcony') , ('ball') , ('bamboo') , ('banana') , ('banner') , ('bar') , ('barely') , ('bargain') , ('barrel') , ('base') , ('basic') , ('basket') , ('battle') , ('beach') , ('bean') , ('beauty') , ('because') , ('become') , ('beef') , ('before') , ('begin') , ('behave') , ('behind') , ('believe') , ('below') , ('belt') , ('bench') , ('benefit') , ('best') , ('betray') , ('better') , ('between') , ('beyond') , ('bicycle') , ('bid') , ('bike') , ('bind') , ('biology') , ('bird') , ('birth') , ('bitter') , ('black') , ('blade') , ('blame') , ('blanket') , ('blast') , ('bleak') , ('bless') , ('blind') , ('blood') , ('blossom') , ('blouse') , ('blue') , ('blur') , ('blush') , ('board') , ('boat') , ('body') , ('boil') , ('bomb') , ('bone') , ('bonus') , ('book') , ('boost') , ('border') , ('boring') , ('borrow') , ('boss') , ('bottom') , ('bounce') , ('box') , ('boy') , ('bracket') , ('brain') , ('brand') , ('brass') , ('brave') , ('bread') , ('breeze') , ('brick') , ('bridge') , ('brief') , ('bright') , ('bring') , ('brisk') , ('broccoli') , ('broken') , ('bronze') , ('broom') , ('brother') , ('brown') , ('brush') , ('bubble') , ('buddy') , ('budget') , ('buffalo') , ('build') , ('bulb') , ('bulk') , ('bullet') , ('bundle') , ('bunker') , ('burden') , ('burger') , ('burst') , ('bus') , ('business') , ('busy') , ('butter') , ('buyer') , ('buzz') , ('cabbage') , ('cabin') , ('cable') , ('cactus') , ('cage') , ('cake') , ('call') , ('calm') , ('camera') , ('camp') , ('can') , ('canal') , ('cancel') , ('candy') , ('cannon') , ('canoe') , ('canvas') , ('canyon') , ('capable') , ('capital') , ('captain') , ('car') , ('carbon') , ('card') , ('cargo') , ('carpet') , ('carry') , ('cart') , ('case') , ('cash') , ('casino') , ('castle') , ('casual') , ('cat') , ('catalog') , ('catch') , ('category') , ('cattle') , ('caught') , ('cause') , ('caution') , ('cave') , ('ceiling') , ('celery') , ('cement') , ('census') , ('century') , ('cereal') , ('certain') , ('chair') , ('chalk') , ('champion') , ('change') , ('chaos') , ('chapter') , ('charge') , ('chase') , ('chat') , ('cheap') , ('check') , ('cheese') , ('chef') , ('cherry') , ('chest') , ('chicken') , ('chief') , ('child') , ('chimney') , ('choice') , ('choose') , ('chronic') , ('chuckle') , ('chunk') , ('churn') , ('cigar') , ('cinnamon') , ('circle') , ('citizen') , ('city') , ('civil') , ('claim') , ('clap') , ('clarify') , ('claw') , ('clay') , ('clean') , ('clerk') , ('clever') , ('click') , ('client') , ('cliff') , ('climb') , ('clinic') , ('clip') , ('clock') , ('clog') , ('close') , ('cloth') , ('cloud') , ('clown') , ('club') , ('clump') , ('cluster') , ('clutch') , ('coach') , ('coast') , ('coconut') , ('code') , ('coffee') , ('coil') , ('coin') , ('collect') , ('color') , ('column') , ('combine') , ('come') , ('comfort') , ('comic') , ('common') , ('company') , ('concert') , ('conduct') , ('confirm') , ('congress') , ('connect') , ('consider') , ('control') , ('convince') , ('cook') , ('cool') , ('copper') , ('copy') , ('coral') , ('core') , ('corn') , ('correct') , ('cost') , ('cotton') , ('couch') , ('country') , ('couple') , ('course') , ('cousin') , ('cover') , ('coyote') , ('crack') , ('cradle') , ('craft') , ('cram') , ('crane') , ('crash') , ('crater') , ('crawl') , ('crazy') , ('cream') , ('credit') , ('creek') , ('crew') , ('cricket') , ('crime') , ('crisp') , ('critic') , ('crop') , ('cross') , ('crouch') , ('crowd') , ('crucial') , ('cruel') , ('cruise') , ('crumble') , ('crunch') , ('crush') , ('cry') , ('crystal') , ('cube') , ('culture') , ('cup') , ('cupboard') , ('curious') , ('current') , ('curtain') , ('curve') , ('cushion') , ('custom') , ('cute') , ('cycle') , ('dad') , ('damage') , ('damp') , ('dance') , ('danger') , ('daring') , ('dash') , ('daughter') , ('dawn') , ('day') , ('deal') , ('debate') , ('debris') , ('decade') , ('december') , ('decide') , ('decline') , ('decorate') , ('decrease') , ('deer') , ('defense') , ('define') , ('defy') , ('degree') , ('delay') , ('deliver') , ('demand') , ('demise') , ('denial') , ('dentist') , ('deny') , ('depart') , ('depend') , ('deposit') , ('depth') , ('deputy') , ('derive') , ('describe') , ('desert') , ('design') , ('desk') , ('despair') , ('destroy') , ('detail') , ('detect') , ('develop') , ('device') , ('devote') , ('diagram') , ('dial') , ('diamond') , ('diary') , ('dice') , ('diesel') , ('diet') , ('differ') , ('digital') , ('dignity') , ('dilemma') , ('dinner') , ('dinosaur') , ('direct') , ('dirt') , ('disagree') , ('discover') , ('disease') , ('dish') , ('dismiss') , ('disorder') , ('display') , ('distance') , ('divert') , ('divide') , ('divorce') , ('dizzy') , ('doctor') , ('document') , ('dog') , ('doll') , ('dolphin') , ('domain') , ('donate') , ('donkey') , ('donor') , ('door') , ('dose') , ('double') , ('dove') , ('draft') , ('dragon') , ('drama') , ('drastic') , ('draw') , ('dream') , ('dress') , ('drift') , ('drill') , ('drink') , ('drip') , ('drive') , ('drop') , ('drum') , ('dry') , ('duck') , ('dumb') , ('dune') , ('during') , ('dust') , ('dutch') , ('duty') , ('dwarf') , ('dynamic') , ('eager') , ('eagle') , ('early') , ('earn') , ('earth') , ('easily') , ('east') , ('easy') , ('echo') , ('ecology') , ('economy') , ('edge') , ('edit') , ('educate') , ('effort') , ('egg') , ('eight') , ('either') , ('elbow') , ('elder') , ('electric') , ('elegant') , ('element') , ('elephant') , ('elevator') , ('elite') , ('else') , ('embark') , ('embody') , ('embrace') , ('emerge') , ('emotion') , ('employ') , ('empower') , ('empty') , ('enable') , ('enact') , ('end') , ('endless') , ('endorse') , ('enemy') , ('energy') , ('enforce') , ('engage') , ('engine') , ('enhance') , ('enjoy') , ('enlist') , ('enough') , ('enrich') , ('enroll') , ('ensure') , ('enter') , ('entire') , ('entry') , ('envelope') , ('episode') , ('equal') , ('equip') , ('era') , ('erase') , ('erode') , ('erosion') , ('error') , ('erupt') , ('escape') , ('essay') , ('essence') , ('estate') , ('eternal') , ('ethics') , ('evidence') , ('evil') , ('evoke') , ('evolve') , ('exact') , ('example') , ('excess') , ('exchange') , ('excite') , ('exclude') , ('excuse') , ('execute') , ('exercise') , ('exhaust') , ('exhibit') , ('exile') , ('exist') , ('exit') , ('exotic') , ('expand') , ('expect') , ('expire') , ('explain') , ('expose') , ('express') , ('extend') , ('extra') , ('eye') , ('eyebrow') , ('fabric'); INSERT INTO dbo.WordList VALUES ('lab') , ('label') , ('labor') , ('ladder') , ('lady') , ('lake') , ('lamp') , ('language') , ('laptop') , ('large') , ('later') , ('latin') , ('laugh') , ('laundry') , ('lava') , ('law') , ('lawn') , ('lawsuit') , ('layer') , ('lazy') , ('leader') , ('leaf') , ('learn') , ('leave') , ('lecture') , ('left') , ('leg') , ('legal') , ('legend') , ('leisure') , ('lemon') , ('lend') , ('length') , ('lens') , ('leopard') , ('lesson') , ('letter') , ('level') , ('liar') , ('liberty') , ('library') , ('license') , ('life') , ('lift') , ('light') , ('like') , ('limb') , ('limit') , ('link') , ('lion') , ('liquid') , ('list') , ('little') , ('live') , ('lizard') , ('load') , ('loan') , ('lobster') , ('local') , ('lock') , ('logic') , ('lonely') , ('long') , ('loop') , ('lottery') , ('loud') , ('lounge') , ('love') , ('loyal') , ('lucky') , ('luggage') , ('lumber') , ('lunar') , ('lunch') , ('luxury') , ('lyrics') , ('machine') , ('mad') , ('magic') , ('magnet') , ('maid') , ('mail') , ('main') , ('major') , ('make') , ('mammal') , ('man') , ('manage') , ('mandate') , ('mango') , ('mansion') , ('manual') , ('maple') , ('marble') , ('march') , ('margin') , ('marine') , ('market') , ('marriage') , ('mask') , ('mass') , ('master') , ('match') , ('material') , ('math') , ('matrix') , ('matter') , ('maximum') , ('maze') , ('meadow') , ('mean') , ('measure') , ('meat') , ('mechanic') , ('medal') , ('media') , ('melody') , ('melt') , ('member') , ('memory') , ('mention') , ('menu') , ('mercy') , ('merge') , ('merit') , ('merry') , ('mesh') , ('message') , ('metal') , ('method') , ('middle') , ('midnight') , ('milk') , ('million') , ('mimic') , ('mind') , ('minimum') , ('minor') , ('minute') , ('miracle') , ('mirror') , ('misery') , ('miss') , ('mistake') , ('mix') , ('mixed') , ('mixture') , ('mobile') , ('model') , ('modify') , ('mom') , ('moment') , ('monitor') , ('monkey') , ('monster') , ('month') , ('moon') , ('moral') , ('more') , ('morning') , ('mosquito') , ('mother') , ('motion') , ('motor') , ('mountain') , ('mouse') , ('move') , ('movie') , ('much') , ('muffin') , ('mule') , ('multiply') , ('muscle') , ('museum') , ('mushroom') , ('music') , ('must') , ('mutual') , ('myself') , ('mystery') , ('myth') , ('naive') , ('name') , ('napkin') , ('narrow') , ('nasty') , ('nation') , ('nature') , ('near') , ('neck') , ('need') , ('negative') , ('neglect') , ('neither') , ('nephew') , ('nerve') , ('nest') , ('net') , ('network') , ('neutral') , ('never') , ('news') , ('next') , ('nice') , ('night') , ('noble') , ('noise') , ('nominee') , ('noodle') , ('normal') , ('north') , ('nose') , ('notable') , ('note') , ('nothing') , ('notice') , ('novel') , ('now') , ('nuclear') , ('number') , ('nurse') , ('nut') , ('oak') , ('obey') , ('object') , ('oblige') , ('obscure') , ('observe') , ('obtain') , ('obvious') , ('occur') , ('ocean') , ('october') , ('odor') , ('off') , ('offer') , ('office') , ('often') , ('oil') , ('okay') , ('old') , ('olive') , ('olympic') , ('omit') , ('once') , ('one') , ('onion') , ('online') , ('only') , ('open') , ('opera'); INSERT INTO dbo.WordList VALUES ('vacant') , ('vacuum') , ('vague') , ('valid') , ('valley') , ('valve') , ('van') , ('vanish') , ('vapor') , ('various') , ('vast') , ('vault') , ('vehicle') , ('velvet') , ('vendor') , ('venture') , ('venue') , ('verb') , ('verify') , ('version') , ('very') , ('vessel') , ('veteran') , ('viable') , ('vibrant') , ('vicious') , ('victory') , ('video') , ('view') , ('village') , ('vintage') , ('violin') , ('virtual') , ('virus') , ('visa') , ('wild') , ('will') , ('win') , ('window') , ('wine') , ('wing') , ('wink') , ('winner') , ('winter') , ('wire') , ('wisdom') , ('wise') , ('wish') , ('witness') , ('wolf') , ('woman') , ('wonder') , ('wood') , ('wool') , ('word') , ('work') , ('world') , ('worry') , ('worth') , ('wrap') , ('wreck') , ('wrestle') , ('wrist') , ('write') , ('wrong') , ('yard') , ('year') , ('yellow') , ('you') , ('young') , ('youth') , ('zebra') , ('zero') , ('zone') , ('zoo');要使用上面的單詞表，您可以執行以下操作：
DECLARE @WordListCount int; SELECT @WordListCount = COUNT(1) FROM dbo.WordList; UPDATE dbo.MyTable SET dbo.MyTable.[First Name] = wlFirst.Word , dbo.MyTable.[Last Name] = wlLast.Word FROM dbo.MyTable mt INNER JOIN (SELECT wl.Word, rn = ROW_NUMBER() OVER (ORDER BY wl.OrderBy) FROM dbo.WordList wl) wlFirst ON (mt.MyTableID % @WordListCount) = wlFirst.rn INNER JOIN (SELECT wl.Word, rn = ROW_NUMBER() OVER (ORDER BY wl.OrderBy) FROM dbo.WordList wl) wlLast ON (mt.MyTableID % @WordListCount) = wlLast.rn GO結果：
SELECT * FROM dbo.MyTable;╔═══════════╦═══════════════╦════════════╦═══════════╗ ║ MyTableID ║ 出生日期 ║ 名字 ║ 姓氏 ║ ╠═══════════╬═══════════════╬════════════╬═══════════╣ ║1║1990-01-01║腎║複製║ ║ 2 ║ 1989-01-01 ║ 輸入 ║ 鏡像 ║ ╚═══════════╩═══════════════╩════════════╩═══════════╝1 - 我不能在此處包含整個 BIP-0039 單詞表，因為答案的正文限制為 30,000 個字元