Bọsipọ data lati awọn tabili XtraDB laisi faili igbekalẹ nipa lilo itupalẹ baiti-byte ti faili ibd

prehistory

O ṣẹlẹ pe olupin naa ti kọlu nipasẹ ọlọjẹ ransomware, eyiti, nipasẹ “ijamba orire,” apakan fi awọn faili .ibd silẹ (awọn faili data aise ti awọn tabili innodb) laifọwọkan, ṣugbọn ni akoko kanna ti paroko awọn faili .fpm patapata ( awọn faili eto). Ni idi eyi, .idb le pin si:

• koko ọrọ si atunse nipasẹ boṣewa irinṣẹ ati awọn itọsọna. Fun iru awọn igba miran, nibẹ jẹ ẹya o tayọ di;
• apa kan ti paroko tabili. Okeene wọnyi ni o wa tobi tabili, fun eyi ti (bi mo ti ye) awọn attackers ko ni to Ramu fun ni kikun ìsekóòdù;
• O dara, awọn tabili ti paroko patapata ti a ko le mu pada.

O ṣee ṣe lati pinnu iru aṣayan ti awọn tabili jẹ si nipa ṣiṣi ṣiṣi silẹ ni eyikeyi olootu ọrọ labẹ fifi koodu ti o fẹ (ninu ọran mi o jẹ UTF8) ati wiwo faili ni irọrun fun wiwa awọn aaye ọrọ, fun apẹẹrẹ:

Paapaa, ni ibẹrẹ faili o le ṣe akiyesi nọmba nla ti awọn baiti 0, ati awọn ọlọjẹ ti o lo algorithm fifi ẹnọ kọ nkan (eyiti o wọpọ julọ) nigbagbogbo ni ipa lori wọn paapaa.

Ninu ọran mi, awọn ikọlu naa fi okun 4-baiti (1, 0, 0, 0) silẹ ni ipari faili ti paroko kọọkan, eyiti o jẹ irọrun iṣẹ naa. Lati wa awọn faili ti ko ni arun, iwe afọwọkọ naa ti to:

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)

Nitorinaa, o wa lati wa awọn faili ti o jẹ ti iru akọkọ. Èkejì wé mọ́ ọ̀pọ̀ iṣẹ́ àfọwọ́kọ, ṣùgbọ́n ohun tí a rí ti tó. Ohun gbogbo yoo dara, ṣugbọn o nilo lati mọ Egba kongẹ be ati (dajudaju) ọran kan dide pe Mo ni lati ṣiṣẹ pẹlu tabili iyipada nigbagbogbo. Ko si ẹnikan ti o ranti boya iru aaye ti yipada tabi ti ṣafikun iwe tuntun kan.

Wilds City, laanu, ko le ran pẹlu iru kan irú, ti o jẹ idi ti yi article ti wa ni kikọ.

Gba si ojuami

Eto tabili kan wa lati oṣu mẹta sẹyin ti ko ṣe deede pẹlu ti lọwọlọwọ (o ṣee ṣe aaye kan, ati boya diẹ sii). Ilana tabili:

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)
); 

Ni idi eyi, o nilo lati jade:

• id_point int (11);
• id_user int (11);
• date_start ÀKÓKÒ;
• date_finish DATETIME.

Fun gbigba pada, itupalẹ baiti-byte ti faili .ibd ti lo, atẹle nipa yiyipada wọn sinu fọọmu kika diẹ sii. Niwon lati wa ohun ti a nilo, a nilo nikan lati ṣe itupalẹ awọn iru data gẹgẹbi int ati akoko data, nkan naa yoo ṣe apejuwe wọn nikan, ṣugbọn nigbami a yoo tun tọka si awọn iru data miiran, eyiti o le ṣe iranlọwọ ni awọn iṣẹlẹ miiran ti o jọra.

Isoro 1: awọn aaye pẹlu awọn oriṣi DATETIME ati TEXT ni awọn iye NULL, ati pe wọn ti fo nirọrun ninu faili naa, nitori eyi, ko ṣee ṣe lati pinnu eto lati mu pada ninu ọran mi. Ninu awọn ọwọn tuntun, iye aiyipada jẹ asan, ati pe apakan idunadura naa le padanu nitori eto innodb_flush_log_at_trx_commit = 0, nitorinaa akoko afikun yoo ni lati lo lati pinnu eto naa.

Isoro 2: o yẹ ki o ṣe akiyesi pe awọn ori ila ti paarẹ nipasẹ DELETE gbogbo wọn yoo wa ninu faili ibd, ṣugbọn pẹlu ALTER TABLE eto wọn kii yoo ni imudojuiwọn. Bi abajade, eto data le yatọ lati ibẹrẹ faili si opin rẹ. Ti o ba nigbagbogbo lo OPTIMIZE TABLE, lẹhinna o ko ṣeeṣe lati pade iru iṣoro bẹ.

San ifojusi, Ẹya DBMS yoo ni ipa lori ọna ti data ti wa ni ipamọ, ati apẹẹrẹ yii le ma ṣiṣẹ fun awọn ẹya pataki miiran. Ninu ọran mi, ẹya windows ti mariadb 10.1.24 ti lo. Paapaa, botilẹjẹpe ni mariadb o ṣiṣẹ pẹlu awọn tabili InnoDB, ni otitọ wọn jẹ XtraDB, eyiti o yọkuro iwulo ti ọna pẹlu InnoDB mysql.

Ayẹwo faili

Ni Python, iru data baiti() ṣe afihan data Unicode ni aaye ti ṣeto awọn nọmba deede. Botilẹjẹpe o le wo faili naa ni fọọmu yii, fun irọrun o le yi awọn baiti pada si fọọmu nomba nipa yiyipada titobi baiti sinu akojọpọ deede (akojọ(example_byte_array)). Ni eyikeyi idiyele, awọn ọna mejeeji dara fun itupalẹ.

Lẹhin wiwo nipasẹ ọpọlọpọ awọn faili ibd, o le wa atẹle naa:

Pẹlupẹlu, ti o ba pin faili naa nipasẹ awọn koko-ọrọ wọnyi, iwọ yoo gba paapaa paapaa awọn bulọọki ti data. A yoo lo infimum bi apinpin.

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

Akiyesi ti o nifẹ: fun awọn tabili pẹlu iye kekere ti data, laarin infimum ati supremum nibẹ ni itọka si nọmba awọn ori ila ninu bulọki naa.

- igbeyewo tabili pẹlu 1st kana

- tabili idanwo pẹlu awọn ori ila 2

Tabili orun ila[0] le fo. Lẹhin wiwa nipasẹ rẹ, Emi ko tun lagbara lati wa data tabili aise naa. O ṣeese julọ, bulọọki yii ni a lo lati tọju awọn atọka ati awọn bọtini.
Bibẹrẹ pẹlu tabili[1] ati titumọ si ọna nọmba, o le ṣakiyesi awọn ilana kan tẹlẹ, eyun:

Iwọnyi jẹ awọn iye int ti a fipamọ sinu okun kan. Baiti akọkọ tọkasi boya nọmba naa jẹ rere tabi odi. Ninu ọran mi, gbogbo awọn nọmba jẹ rere. Lati awọn baiti 3 ti o ku, o le pinnu nọmba naa nipa lilo iṣẹ atẹle. Iwe afọwọkọ:

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

Fun apẹẹrẹ, 128, 0, 0, 1 = 1tabi 128, 0, 75, 108 = 19308.
Tabili naa ni bọtini akọkọ pẹlu afikun-laifọwọyi, ati pe o tun le rii nibi

Lẹhin ti a ṣe afiwe data lati awọn tabili idanwo, o ṣafihan pe nkan DATETIME ni awọn baiti 5 ati pe o bẹrẹ pẹlu 153 (o ṣeese julọ n tọka si awọn aaye arin ọdọọdun). Niwọn igba ti iwọn DATTIME jẹ '1000-01-01' si '9999-12-31', Mo ro pe nọmba awọn baiti le yatọ, ṣugbọn ninu ọran mi, data naa ṣubu ni akoko lati ọdun 2016 si 2019, nitorinaa a yoo ro pe ti o 5 baiti to.

Lati pinnu akoko laisi iṣẹju-aaya, awọn iṣẹ atẹle ni a kọ. Iwe afọwọkọ:

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}

Ko ṣee ṣe lati kọ iṣẹ iṣẹ kan fun ọdun ati oṣu, nitorinaa Mo ni lati gige rẹ. Iwe afọwọkọ:

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

Emi ni daju wipe ti o ba ti o ba na n iye ti akoko, yi gbọye le ti wa ni atunse.
Nigbamii ti, iṣẹ kan ti o da ohun akoko ọjọ pada lati okun kan. Iwe afọwọkọ:

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)

Ti ṣakoso lati ṣawari awọn iye ti a tun sọ nigbagbogbo lati int, int, datetime, datetime , o dabi pe eyi ni ohun ti o nilo. Jubẹlọ, iru kan ọkọọkan ti wa ni ko tun lemeji fun ila.

Lilo ikosile deede, a wa data pataki:

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)

Jọwọ ṣe akiyesi pe nigba wiwa ni lilo ikosile yii, kii yoo ṣee ṣe lati pinnu awọn iye NULL ni awọn aaye ti o nilo, ṣugbọn ninu ọran mi eyi kii ṣe pataki. Lẹhinna a lọ nipasẹ ohun ti a rii ni lupu kan. Iwe afọwọkọ:

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)

Lootọ, iyẹn ni gbogbo rẹ, data lati inu abajade abajade jẹ data ti a nilo. ###PS.###
Mo loye pe ọna yii ko dara fun gbogbo eniyan, ṣugbọn ibi-afẹde akọkọ ti nkan naa ni lati yara iṣe kuku ju yanju gbogbo awọn iṣoro rẹ. Mo ro pe ojutu ti o pe julọ yoo jẹ lati bẹrẹ ikẹkọ koodu orisun funrararẹ mariadb, ṣugbọn nitori akoko to lopin, ọna ti o wa lọwọlọwọ dabi ẹnipe o yara julọ.

Ni awọn igba miiran, lẹhin itupalẹ faili naa, iwọ yoo ni anfani lati pinnu eto isunmọ ati mu pada ni lilo ọkan ninu awọn ọna boṣewa lati awọn ọna asopọ loke. Eyi yoo jẹ deede diẹ sii ati fa awọn iṣoro diẹ.

orisun: www.habr.com