Ukufumana kwakhona idatha kwiitafile ze-XtraDB ngaphandle kwefayile yesakhiwo usebenzisa uhlalutyo lwe-byte-byte yefayile ye-ibd

ukubuzwa

Kwenzeka ukuba umncedisi uhlaselwe yintsholongwane ye-ransomware, ethi, "ngengozi yethamsanqa," ngokuyinxenye ishiye iifayile ze-.ibd (iifayile zedatha ekrwada yeetafile ze-innodb) zingakhange zichukunyiswe, kodwa kwangaxeshanye zifihliwe ngokupheleleyo iifayile ze-.fpm ( iifayile zesakhiwo). Kulo mzekelo, .idb inokohlulwa ibe:

• ngokuxhomekeke ekubuyiselweni ngokusebenzisa izixhobo eziqhelekileyo kunye nezikhokelo. Kwiimeko ezinjalo, kukho ukugqwesa ibe;
• iitafile ezinoguqulelo oluntsonkothileyo. Ubukhulu becala ezi zitafile ezinkulu, apho (njengoko ndiqondayo) abahlaseli babengenayo i-RAM eyaneleyo yoguqulelo olupheleleyo;
• Ewe, iitafile ezifihliweyo ngokupheleleyo ezingenakubuyiselwa.

Bekunokwenzeka ukumisela ukuba loluphi ukhetho iitafile ezizezako ngokuyivula ngokulula nakuwuphi na umhleli wokubhaliweyo phantsi koguqulelo olufunekayo (kwimeko yam yi UTF8) kwaye ujonge ngokulula ifayile yobukho bemihlaba yokubhaliweyo, umzekelo:

Kwakhona, ekuqaleni kwefayile unokuqwalasela inani elikhulu le-bytes, kunye neentsholongwane ezisebenzisa i-algorithm yebhloko ye-encryption (eyona ixhaphakileyo) idla ngokuchaphazela nabo.

Kwimeko yam, abahlaseli bashiya umtya we-4-byte (1, 0, 0, 0) ekupheleni kwefayile nganye efihliweyo, eyenza lula umsebenzi. Ukukhangela iifayile ezingosulelwanga, iskripthi sasonele:

def opened(path):
    files = os.listdir(path)
    for f in files:
        if os.path.isfile(path + f):
            yield path + f

for full_path in opened("C:somepath"):
    file = open(full_path, "rb")
    last_string = ""
    for line in file:
        last_string = line
        file.close()
    if (last_string[len(last_string) -4:len(last_string)]) != (1, 0, 0, 0):
        print(full_path)

Ke, kwafumaniseka ukuba kufunyenwe iifayile zodidi lokuqala. Eyesibini ibandakanya umsebenzi omninzi wezandla, kodwa oko kwafunyanwayo kwakwanele. Yonke into iya kulunga, kodwa kufuneka ukwazi Ulwakhiwo oluchanekileyo kwaye (ngokuqinisekileyo) kwavela ityala lokuba kufuneka ndisebenze ngetafile eguquka rhoqo. Akukho mntu wakhumbula ukuba uhlobo lwentsimi lutshintshiwe okanye ikholomu entsha yongezwa.

IWild City, ngelishwa, ayikwazanga kunceda kwimeko enje, kungoko kubhalwe eli nqaku.

Yiya kwinqanaba

Kukho ulwakhiwo lwetheyibhile kwiinyanga ezi-3 ezidlulileyo ezingahambelaniyo nale yangoku (kusenokwenzeka intsimi enye, kwaye mhlawumbi nangaphezulu). Ubume betafile:

CREATE TABLE `table_1` (
    `id` INT (11),
    `date` DATETIME ,
    `description` TEXT ,
    `id_point` INT (11),
    `id_user` INT (11),
    `date_start` DATETIME ,
    `date_finish` DATETIME ,
    `photo` INT (1),
    `id_client` INT (11),
    `status` INT (1),
    `lead__time` TIME ,
    `sendstatus` TINYINT (4)
); 

kule meko, kufuneka ukhuphe:

• id_point int(11);
• id_user int(11);
• date_start DATETIME;
• date_finish DATETIME.

Ukufumana kwakhona, uhlalutyo lwe-byte-byte yefayile ye-ibd isetyenzisiweyo, ilandelwa ngokuyiguqulela kwifom efundekayo ngakumbi. Ekubeni ukufumana oko sikudingayo, kufuneka sihlalutye kuphela iintlobo zedatha ezifana ne-int kunye nedatha, inqaku liza kuchaza kuphela, kodwa ngamanye amaxesha siya kubhekisela kwezinye iindidi zedatha, ezinokunceda kwezinye iziganeko ezifanayo.

Ingxaki 1: imihlaba eneentlobo DATETIME kunye TEXT zinexabiso NULL, kwaye zitsitywa ngokulula kwifayile, ngenxa yoku, khange kwenzeke ukumisela isakhiwo sokubuyisela kwimeko yam. Kwimiqolo emitsha, ixabiso elingagqibekanga lalingekho, kwaye inxalenye yentengiselwano inokulahleka ngenxa yolungiselelo lwe-innodb_flush_log_at_trx_commit = 0, ngoko ke ixesha elongezelelweyo liya kuchithwa ukumisela ubume.

Ingxaki 2: kufuneka kuthathelwe ingqalelo into yokuba iirowu ezicinywe ngoku DELETE ziya kuba zonke kwifayile ye-ibd, kodwa nge ALTER TABLE ubume bayo abayi kuhlaziywa. Ngenxa yoko, isakhiwo sedatha sinokuhluka ukusuka ekuqaleni kwefayile ukuya ekupheleni kwayo. Ukuba usebenzisa rhoqo i-OPTIMIZE TABLE, ngoko akunakufane udibane nengxaki enjalo.

Nika ingqalelo, inguqulelo yeDBMS ichaphazela indlela idatha egcinwa ngayo, kwaye lo mzekelo usenokungasebenzi kwezinye iinguqulelo ezinkulu. Kwimeko yam, iifestile ze-mariadb 10.1.24 zisetyenzisiwe. Kwakhona, nangona kwi-mariadb usebenza ngeetafile ze-InnoDB, enyanisweni zinjalo XtraDB, engabandakanyi ukusetyenziswa kwendlela nge-InnoDB mysql.

Uhlalutyo lwefayile

Kwipython, uhlobo lwedatha iibhayithi() ibonisa i-Unicode data endaweni yeseti yamanani rhoqo. Nangona ungayijonga ifayile kule fomu, ukwenzela ukuba kube lula ungaguqula ii-byte zibe kwifomu yamanani ngokuguqula uluhlu lwe-byte lube luluhlu oluqhelekileyo (uluhlu(umzekelo_byte_array)). Kwimeko nayiphi na into, zombini iindlela zifanelekile ukuhlalutya.

Emva kokujonga iifayile ezininzi ze-ibd, ungafumana oku kulandelayo:

Ngaphezu koko, ukuba uyahlulahlula ifayile ngala magama angundoqo, uya kufumana ubukhulu becala iibhloko zedatha. Siza kusebenzisa i-infimum njengesahluli.

table = table.split("infimum".encode())

Ukuqwalaselwa okuthakazelisayo: kwiitafile ezinomlinganiselo omncinci wedatha, phakathi kwe-infimum kunye ne-supremum kukho isalathisi kwinani lemiqolo kwibhloko.

- itheyibhile yovavanyo kunye nomqolo we-1

- itheyibhile yovavanyo enemiqolo emi-2

Itheyibhile yomqolo [0] inokutsitywa. Emva kokukhangela kuyo, andizange ndikwazi ukufumana idatha yetafile ekrwada. Okunokwenzeka, le block isetyenziselwa ukugcina izalathisi kunye nezitshixo.
Ukuqala ngetheyibhile[1] kwaye uyiguqulele kuluhlu lwamanani, ungazibona iipateni ezithile, ezizezi:

La ngamaxabiso angaphakathi agcinwe kumtya. I-byte yokuqala ibonisa ukuba inani li-positive okanye li-negative. Kwimeko yam, onke amanani alungile. Ukusuka kwii-bytes ezi-3 eziseleyo, unokumisela inani usebenzisa lo msebenzi ulandelayo. Ushicilelo:

def find_int(val: str):  # example '128, 1, 2, 3'
    val = [int(v) for v in  val.split(", ")]
    result_int = val[1]*256**2 + val[2]*256*1 + val[3]
    return result_int

Ngokomzekelo, 128, 0, 0, 1 = 1okanye 128, 0, 75, 108 = 19308.
Itheyibhile yayinesitshixo sokuqala kunye nokunyuswa okuzenzekelayo, kwaye inokufumaneka apha

Emva kokuthelekisa idatha kwiitheyibhile zovavanyo, kwatyhilwa ukuba into ye-DATETIME iqulethwe ngama-bytes ayi-5 kwaye yaqala nge-153 (ebonakala ibonakalisa amaxesha onyaka). Ekubeni uluhlu lweDATTIME luyi-'1000-01-01' ukuya '9999-12-31', ndicinga ukuba inani leebhayithi lingahluka, kodwa kwimeko yam, idatha iwela kwixesha elisuka kwi-2016 ukuya kwi-2019, ngoko siya kucinga. ukuba 5 bytes ngokwaneleyo.

Ukumisela ixesha ngaphandle kwemizuzwana, le misebenzi ilandelayo yabhalwa. Ushicilelo:

day_ = lambda x: x % 64 // 2  # {x,x,X,x,x }

def hour_(x1, x2):  # {x,x,X1,X2,x}
    if x1 % 2 == 0:
        return x2 // 16
    elif x1 % 2 == 1:
        return x2 // 16 + 16
    else:
        raise ValueError

min_ = lambda x1, x2: (x1 % 16) * 4 + (x2 // 64)  # {x,x,x,X1,X2}

Kwakungenakwenzeka ukuba ndibhale umsebenzi osebenzayo wonyaka nenyanga, ngoko ke kwafuneka ndiyiqhekeze. Ushicilelo:

ym_list = {'2016, 1': '153, 152, 64', '2016, 2': '153, 152, 128', 
           '2016, 3': '153, 152, 192', '2016, 4': '153, 153, 0',
           '2016, 5': '153, 153, 64', '2016, 6': '153, 153, 128', 
           '2016, 7': '153, 153, 192', '2016, 8': '153, 154, 0', 
           '2016, 9': '153, 154, 64', '2016, 10': '153, 154, 128', 
           '2016, 11': '153, 154, 192', '2016, 12': '153, 155, 0',
           '2017, 1': '153, 155, 128', '2017, 2': '153, 155, 192', 
           '2017, 3': '153, 156, 0', '2017, 4': '153, 156, 64',
           '2017, 5': '153, 156, 128', '2017, 6': '153, 156, 192',
           '2017, 7': '153, 157, 0', '2017, 8': '153, 157, 64',
           '2017, 9': '153, 157, 128', '2017, 10': '153, 157, 192', 
           '2017, 11': '153, 158, 0', '2017, 12': '153, 158, 64', 
           '2018, 1': '153, 158, 192', '2018, 2': '153, 159, 0',
           '2018, 3': '153, 159, 64', '2018, 4': '153, 159, 128', 
           '2018, 5': '153, 159, 192', '2018, 6': '153, 160, 0',
           '2018, 7': '153, 160, 64', '2018, 8': '153, 160, 128',
           '2018, 9': '153, 160, 192', '2018, 10': '153, 161, 0', 
           '2018, 11': '153, 161, 64', '2018, 12': '153, 161, 128',
           '2019, 1': '153, 162, 0', '2019, 2': '153, 162, 64', 
           '2019, 3': '153, 162, 128', '2019, 4': '153, 162, 192', 
           '2019, 5': '153, 163, 0', '2019, 6': '153, 163, 64',
           '2019, 7': '153, 163, 128', '2019, 8': '153, 163, 192',
           '2019, 9': '153, 164, 0', '2019, 10': '153, 164, 64', 
           '2019, 11': '153, 164, 128', '2019, 12': '153, 164, 192',
           '2020, 1': '153, 165, 64', '2020, 2': '153, 165, 128',
           '2020, 3': '153, 165, 192','2020, 4': '153, 166, 0', 
           '2020, 5': '153, 166, 64', '2020, 6': '153, 1, 128',
           '2020, 7': '153, 166, 192', '2020, 8': '153, 167, 0', 
           '2020, 9': '153, 167, 64','2020, 10': '153, 167, 128',
           '2020, 11': '153, 167, 192', '2020, 12': '153, 168, 0'}

def year_month(x1, x2):  # {x,X,X,x,x }

    for key, value in ym_list.items():
        key = [int(k) for k in key.replace("'", "").split(", ")]
        value = [int(v) for v in value.split(", ")]
        if x1 == value[1] and x2 // 64 == value[2] // 64:
            return key
    return 0, 0

Ndiqinisekile ukuba ukuba uchitha n ixesha, oku kungaqondani kunokulungiswa.
Okulandelayo, umsebenzi obuyisela into yexesha ukusuka kumtya. Ushicilelo:

def find_data_time(val:str):
    val = [int(v) for v in val.split(", ")]
    day = day_(val[2])
    hour = hour_(val[2], val[3])
    minutes = min_(val[3], val[4])
    year, month = year_month(val[1], val[2])
    return datetime(year, month, day, hour, minutes)

Ilawulwa ukufumanisa amaxabiso aphindaphindiweyo ukusuka kwi-int, int, ixesha lomhla, ixesha lomhla , kubonakala ngathi le yinto oyifunayo. Ngaphezu koko, ulandelelwano olunjalo aluphindwa kabini ngomgca ngamnye.

Ukusebenzisa intetho eqhelekileyo, sifumana idatha efunekayo:

fined = re.findall(r'128, d*, d*, d*, 128, d*, d*, d*, 153, 1[6,5,4,3]d, d*, d*, d*, 153, 1[6,5,4,3]d, d*, d*, d*', int_array)

Nceda uqaphele ukuba xa uphendla usebenzisa le ntetho, ayizukwenzeka ukumisela amaxabiso e-NULL kwimimandla efunekayo, kodwa kwimeko yam le ayibalulekanga. Emva koko sidlula kwinto esiyifumene kwi-loop. Ushicilelo:

result = []
for val in fined:
    pre_result = []
    bd_int  = re.findall(r"128, d*, d*, d*", val)
    bd_date= re.findall(r"(153, 1[6,5,4,3]d, d*, d*, d*)", val)
    for it in bd_int:
        pre_result.append(find_int(bd_int[it]))
    for bd in bd_date:
        pre_result.append(find_data_time(bd))
    result.append(pre_result)

Ngokwenyani, yiyo yonke loo nto, idatha evela kuluhlu lweziphumo yidatha esiyidingayo. ###PS.###
Ndiyaqonda ukuba le ndlela ayifanelekanga kumntu wonke, kodwa injongo ephambili yenqaku kukukhawuleza isenzo kunokusombulula zonke iingxaki zakho. Ndicinga ukuba esona sisombululo sichanekileyo iya kuba kukuqala ukufunda ikhowudi yomthombo ngokwakho mariadb, kodwa ngenxa yexesha elilinganiselweyo, indlela yangoku ibonakala iyeyona ikhawulezayo.

Kwezinye iimeko, emva kokuhlalutya ifayile, uya kukwazi ukumisela isakhiwo esisondeleyo kwaye usibuyisele usebenzisa enye yeendlela eziqhelekileyo ezivela kwiikhonkco ezingentla. Oku kuya kuchaneka ngakumbi kwaye kubangele iingxaki ezimbalwa.

umthombo: www.habr.com