Kupezanso zambiri kuchokera pamagome a XtraDB popanda fayilo yamapangidwe pogwiritsa ntchito kusanthula kwa byte-byte pa fayilo ya ibd

prehistory

Zinachitika kuti seva idawukiridwa ndi kachilombo ka ransomware, yomwe, mwa "ngozi yamwayi," idasiya pang'ono mafayilo a .ibd (mafayilo a data yaiwisi a matebulo a innodb) osakhudzidwa, koma nthawi yomweyo adalemba kwathunthu mafayilo a .fpm ( Fayilo yamapangidwe). Apa, .idb ikhoza kugawidwa mu:

• zikuyenera kubwezeretsedwa kudzera mu zida zokhazikika ndi maupangiri. Pazochitika zotere, pali zabwino kwambiri kukhala;
• matebulo osungidwa pang'ono. Nthawi zambiri awa ndi matebulo akulu, omwe (monga momwe ndikumvera) owukirawo analibe RAM yokwanira kubisa kwathunthu;
• Chabwino, matebulo osungidwa bwino omwe sangathe kubwezeretsedwa.

Zinali zotheka kudziwa kuti matebulowo ndi amtundu wanji pongotsegula muzolemba zilizonse pansi pa encoding yomwe mukufuna (kwa ine ndi UTF8) ndikungoyang'ana fayiloyo kukhalapo kwamasamba, mwachitsanzo:

Komanso, koyambirira kwa fayilo mutha kuwona kuchuluka kwa ma byte 0, ndipo ma virus omwe amagwiritsa ntchito block encryption algorithm (odziwika kwambiri) nthawi zambiri amawakhudzanso.

Kwa ine, owukirawo adasiya chingwe cha 4-byte (1, 0, 0, 0) kumapeto kwa fayilo iliyonse yosungidwa, zomwe zidapangitsa kuti ntchitoyi ikhale yosavuta. Kuti mufufuze mafayilo osakhudzidwa, zolembazo zinali zokwanira:

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)

Chifukwa chake, zidapezeka kuti mafayilo amtundu woyamba. Yachiwiri imakhudza ntchito zambiri zamanja, koma zomwe zinapezeka zinali zokwanira kale. Zonse zikhala bwino, koma muyenera kudziwa mwatsatanetsatane dongosolo ndipo (ndithu) panabuka mlandu woti ndimayenera kugwira ntchito ndi tebulo losintha pafupipafupi. Palibe amene amakumbukira ngati mtundu wamunda wasinthidwa kapena gawo latsopano linawonjezedwa.

Wilds City, mwatsoka, sakanatha kuthandizira pa nkhaniyi, chifukwa chake nkhaniyi ikulembedwa.

Pezani mfundo

Pali dongosolo la tebulo la miyezi itatu yapitayo lomwe silikugwirizana ndi lomwe lilipo (mwina gawo limodzi, mwinanso lochulukirapo). Kapangidwe katebulo:

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

Pankhaniyi, muyenera kuchotsa:

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

Kuti mubwezeretse, kusanthula kwa byte-byte kwa fayilo ya .ibd kumagwiritsidwa ntchito, kutsatiridwa ndi kuwasintha kukhala mawonekedwe owerengeka. Popeza kuti tipeze zomwe tikufunikira, timangofunika kusanthula mitundu ya deta monga int ndi datatime, nkhaniyi idzafotokoza zokhazokha, koma nthawi zina tidzatchulanso mitundu ina ya deta, yomwe ingathandize pazochitika zina zofanana.

Vuto 1: minda yokhala ndi mitundu DATETIME ndi TEXT anali ndi NULL, ndipo amangolumphira mu fayilo, chifukwa cha izi, sikunali kotheka kudziwa momwe ndingabwezeretsere vuto langa. M'mizati yatsopano, mtengo wokhazikika unali wopanda pake, ndipo gawo linalake likhoza kutayika chifukwa cha zoikamo innodb_flush_log_at_trx_commit = 0, kotero kuti nthawi yowonjezera iyenera kugwiritsidwa ntchito kuti mudziwe kapangidwe kake.

Vuto 2: ziyenera kuganiziridwa kuti mizere yochotsedwa kudzera pa DELETE yonse idzakhala mu fayilo ya ibd, koma ndi ALTER TABLE mawonekedwe awo sangasinthidwe. Zotsatira zake, dongosolo la deta likhoza kusiyana kuyambira pachiyambi cha fayilo mpaka kumapeto kwake. Ngati nthawi zambiri mumagwiritsa ntchito OPTIMIZE TABLE, ndiye kuti simungathe kukumana ndi vutoli.

Samalani, mtundu wa DBMS umakhudza momwe deta imasungidwira, ndipo chitsanzo ichi sichingagwire ntchito kumitundu ina yayikulu. Kwa ine, mawindo a mawindo a mariadb 10.1.24 anagwiritsidwa ntchito. Komanso, ngakhale mu mariadb mumagwira ntchito ndi matebulo a InnoDB, kwenikweni ali Zithunzi za XtraDB, zomwe siziphatikiza kugwiritsa ntchito njirayo ndi InnoDB mysql.

Kusanthula mafayilo

Mu python, mtundu wa data mabayiti () imawonetsa data ya Unicode m'malo mwa manambala okhazikika. Ngakhale mutha kuwona fayiloyo mwanjira iyi, kuti muthandizire mutha kusintha ma byte kukhala manambala posintha gulu la byte kukhala mndandanda wanthawi zonse (mndandanda(example_byte_array)). Mulimonsemo, njira zonsezi ndizoyenera kusanthula.

Mukayang'ana mafayilo angapo a ibd, mutha kupeza zotsatirazi:

Kuphatikiza apo, ngati mugawa fayilo ndi mawu osakirawa, mupeza zambiri ngakhale midadada ya data. Tidzagwiritsa ntchito infimum ngati dissor.

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

Chiwonetsero chochititsa chidwi: pa matebulo omwe ali ndi deta yochepa, pakati pa infimum ndi supremum pali cholozera pa chiwerengero cha mizere mu chipika.

- tebulo loyesera ndi mzere woyamba

- tebulo loyesera ndi mizere iwiri

Tebulo lamizere [0] litha kudumpha. Nditayang'ana pa izo, sindinathe kupeza deta ya tebulo laiwisi. Mwachidziwikire, chipikachi chimagwiritsidwa ntchito kusunga ma index ndi makiyi.
Kuyambira ndi tebulo[1] ndikumasulira mumagulu angapo, mutha kuzindikira kale mapatani ena, omwe ndi:

Izi ndi zinthu zomwe zimasungidwa mu chingwe. Biti yoyamba imasonyeza ngati nambalayo ndi yolondola kapena yolakwika. Kwa ine, manambala onse ndi abwino. Kuchokera pa ma byte atatu otsala, mutha kudziwa nambala pogwiritsa ntchito zotsatirazi. Zolemba:

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

Mwachitsanzo, 128, 0, 0, 1 = 1, kapena 128, 0, 75, 108 = 19308.
Gomelo linali ndi kiyi yoyamba yokhala ndi auto-increment, ndipo limapezekanso pano

Poyerekeza zomwe zalembedwa pamatebulo oyeserera, zidawululidwa kuti chinthu cha DATETIME chimakhala ndi ma byte 5 ndipo adayamba ndi 153 (mwina akuwonetsa kusiyanasiyana kwapachaka). Popeza mtundu wa DATTIME ndi '1000-01-01' mpaka '9999-12-31', ndikuganiza kuti kuchuluka kwa ma byte kumatha kusiyana, koma kwa ine, deta imagwera mu nthawi kuyambira 2016 mpaka 2019, ndiye tidzaganiza. kuti 5 mabayiti okwanira.

Kuti mudziwe nthawi yopanda masekondi, ntchito zotsatirazi zinalembedwa. Zolemba:

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}

Sizinali zotheka kulemba ntchito yogwira ntchito ya chaka ndi mwezi, kotero ndimayenera kuthyolako. Zolemba:

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

Ndine wotsimikiza kuti ngati muwononga n kuchuluka kwa nthawi, kusamvetsetsana kumeneku kungathe kukonzedwa.
Chotsatira, ntchito yomwe imabweza chinthu cha nthawi kuchokera pa chingwe. Zolemba:

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)

Amatha kuzindikira mayendedwe obwerezabwereza kuyambira int, int, datetime, datetime , zikuwoneka ngati izi ndi zomwe mukufuna. Komanso, kutsatizana koteroko sikubwerezedwa kawiri pamzere uliwonse.

Pogwiritsa ntchito mawu okhazikika, timapeza zofunikira:

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)

Chonde dziwani kuti pofufuza pogwiritsa ntchito mawuwa, sizingatheke kudziwa za NULL m'magawo ofunikira, koma kwa ine izi sizofunikira. Kenako timadutsa zomwe tapeza mu lupu. Zolemba:

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)

M'malo mwake, ndizo zonse, zomwe zimachokera pazotsatira ndizo zomwe tikufuna. ###PS.###
Ndikumvetsa kuti njirayi si yoyenera kwa aliyense, koma cholinga chachikulu cha nkhaniyi ndikufulumira kuchitapo kanthu m'malo mothetsa mavuto anu onse. Ndikuganiza kuti yankho lolondola kwambiri lingakhale kuti muyambe kuphunzira nokha gwero lanu mariadb, koma chifukwa cha nthawi yochepa, njira yamakonoyi inkawoneka ngati yofulumira kwambiri.

Nthawi zina, mutatha kusanthula fayiloyo, mudzatha kudziwa momwe mumakhalira ndikuyibwezeretsanso pogwiritsa ntchito njira imodzi yokhazikika kuchokera pamalumikizidwe omwe ali pamwambapa. Izi zidzakhala zolondola kwambiri ndikubweretsa mavuto ochepa.

Source: www.habr.com