Toe faʻaleleia faʻamatalaga mai laulau XtraDB e aunoa ma se faila faila e faʻaaoga ai le byte-by-byte auiliiliga o le faila ibd

prehistory

Na tupu na osofaia le server e se virus ransomware, lea, e ala i se "faalavelave laki," na tuua ai se vaega o faila .ibd (faila faʻamatalaga mataʻutia o laulau innodb) e leʻi paʻi, ae i le taimi lava e tasi na faʻapipiʻiina atoa ai faila .fpm ( fausaga faila). I lenei tulaga, .idb e mafai ona vaevaeina i:

• e mafai ona toe faʻaleleia e ala i meafaigaluega masani ma taʻiala. Mo ia tulaga, o loʻo i ai se tulaga lelei avea;
• laulau fa'ailoga fa'ailoga. O le tele lava o laulau tetele nei, lea (e pei ona ou malamalama ai) e leʻi lava le RAM a le au osofaʻi mo faʻamatalaga atoa;
• Ia, laulau fa'ailoga atoa e le mafai ona toe fa'afo'isia.

Na mafai ona iloa po o le a le filifiliga e tatau ona i ai laulau e ala i le na o le tatalaina i totonu o soʻo se tusitala o tusitusiga i lalo o le faʻailoga manaʻomia (i loʻu tulaga o le UTF8) ma na o le matamataina o le faila mo le i ai o faʻamaumauga, mo se faʻataʻitaʻiga:

E le gata i lea, i le amataga o le faila e mafai ona e matauina se numera tele o 0 bytes, ma siama e faʻaogaina le poloka encryption algorithm (sili ona taatele) e masani ona aʻafia ai foi.

I loʻu tulaga, na tuua e le au osofaʻi se manoa 4-byte (1, 0, 0, 0) i le pito o faila faʻailoga taʻitasi, lea na faafaigofieina ai le galuega. Ina ia su'e faila e le'i pisia, na lava le fa'amaumauga:

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)

O le mea lea, na maua ai faila o le ituaiga muamua. O le lona lua e aofia ai le tele o galuega lima, ae o le mea na maua ua lava. O le a lelei mea uma, ae e tatau ona e iloa matua sa'o fausaga ma (ioe) na tulai mai se mataupu e tatau ona ou galue i se laulau e sui soo. E leai se tasi na manatua pe na suia le ituaiga fanua pe faaopoopo se koluma fou.

Wilds City, o le mea e leaga ai, e le mafai ona fesoasoani i se tulaga faapena, o le mea lea ua tusia ai lenei tusiga.

Ia oo i le tulaga

O loʻo i ai se fausaga o se laulau mai le 3 masina talu ai e le fetaui ma le taimi nei (atonu e tasi le fanua, ma atonu e sili atu). Fa'atulagaina o laulau:

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

i lenei tulaga, e tatau ona e aveese:

• id_point int(11);
• id_user int(11);
• date_start ASO TAIMI;
• date_finish ASO TAIMI.

Mo le toe faʻaleleia, o se suʻesuʻega byte-by-byte o le faila .ibd e faʻaaogaina, sosoo ai ma le faʻaliliuina i latou i se pepa e sili atu ona mafai ona faitau. Talu ai e suʻe mea tatou te manaʻomia, e naʻo le faʻavasegaina o ituaiga faʻamatalaga e pei o le int ma le datatime, o le a faʻamatalaina e le tusiga naʻo i latou, ae o nisi taimi o le a tatou vaʻai foi i isi ituaiga faʻamatalaga, lea e mafai ona fesoasoani i isi faʻalavelave tutusa.

Fa'afitauli 1: fanua ma ituaiga DATETIME ma TEXT sa i ai NULL tau, ma ua na ona misia i le faila, ona o lenei mea, na le mafai ai ona fuafua le fausaga e toe faʻaleleia i loʻu tulaga. I koluma fou, o le tau le aoga e le aoga, ma o se vaega o le fefaʻatauaiga e mafai ona leiloa ona o le seti innodb_flush_log_at_trx_commit = 0, o lea e tatau ona faʻaalu se taimi faaopoopo e fuafua ai le fausaga.

Fa'afitauli 2: e tatau ona amanaia o laina na tapeina e ala i le DELETE o le a i totonu o le faila ibd, ae o le ALTER TABLE o le a le toe faʻafouina le latou fausaga. O se taunuuga, o le fausaga o faʻamaumauga e mafai ona fesuisuiaʻi mai le amataga o le faila i lona iuga. Afai e masani ona e faʻaaogaina le OPTIMIZE TABLE, o lona uiga e te ono le feagai ma se faʻafitauli.

Faʻalogo lelei, o le DBMS version e aʻafia ai le auala e teuina ai faʻamaumauga, ma o lenei faʻataʻitaʻiga atonu e le aoga mo isi lomiga tetele. I loʻu tulaga, o le windows version of mariadb 10.1.24 na faʻaaogaina. E le gata i lea, e ui lava i le mariadb e te galue ma InnoDB laulau, o le mea moni lava XtraDB, lea e le aofia ai le faʻaogaina o le metotia ma InnoDB mysql.

Iloiloga faila

I le python, ituaiga faʻamatalaga paita() fa'aalia fa'amaumauga Unicode e suitulaga i se seti masani o numera. E ui lava e mafai ona e vaʻai i le faila i lenei fomu, mo le faʻaogaina e mafai ona e faʻaliliuina le byte i le numera numera e ala i le faʻaliliuina o le byte array i se faasologa masani (lisi (example_byte_array)). I soʻo se tulaga, o auala uma e lua e fetaui lelei mo suʻesuʻega.

A maeʻa ona e vaʻavaʻai i le tele o faila ibd, e mafai ona e mauaina mea nei:

E le gata i lea, afai e te vaevaeina le faila i nei upu autu, o le ae mauaina le tele o poloka o faʻamaumauga. O le a matou faʻaaogaina le infimum e fai ma vaevaega.

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

O se mata'upu mata'ina: mo laulau ma sina vaega itiiti o fa'amaumauga, i le va o le infimum ma le maualuga o lo'o i ai se fa'asino ile numera o laina ile poloka.

- laulau su'ega ma le laina muamua

- laulau su'ega ma laina 2

E mafai ona faase'e le laulau fa'asologa o laina[0]. Ina ua uma ona ou suʻesuʻeina, sa le mafai lava ona ou mauaina faʻamatalaga mataʻutia o le laulau. E foliga mai, o lenei poloka e faʻaaogaina e teu ai faʻamatalaga ma ki.
Amata i le laulau[1] ma fa'aliliu i se fa'asologa numera, ua mafai ona e matauina nisi o mamanu, e pei o:

O tau ia int o lo'o teuina i totonu o se manoa. O le paita muamua e faʻaalia pe lelei pe leaga le numera. I lo'u tulaga, o numera uma e lelei. Mai le 3 paita o totoe, e mafai ona e fuafuaina le numera e faʻaaoga ai le galuega lea. Mau:

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

Mo se faataitaiga, 128, 0, 0, 1 = 1poʻo 128, 0, 75, 108 = 19308.
O le laulau sa i ai le ki autu ma le auto-increment, ma e mafai foi ona maua iinei

I le faʻatusatusaina o faʻamatalaga mai laulau suʻega, na faʻaalia ai o le DATETIME mea e aofia ai le 5 paita ma amata i le 153 (e foliga mai e faʻaalia ai taimi faʻaletausaga). Talu ai ona o le DATTIME laina o le '1000-01-01' i le '9999-12-31', ou te manatu o le numera o bytes e ono eseese, ae i loʻu tulaga, o faʻamaumauga e pa'ū i le vaitaimi mai le 2016 i le 2019, o lea o le a tatou manatu e lava le 5 paita.

Ina ia iloa le taimi e aunoa ma ni sekone, o galuega nei na tusia. Mau:

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}

Sa le mafai ona tusia se galuega galue mo le tausaga ma le masina, o lea na tatau ai ona ou taina. Mau:

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

Ou te mautinoa afai e te faʻaaluina le tele o le taimi, e mafai ona faʻasaʻo lenei le malamalama.
Soso'o, o se galuega e toe fa'afo'i mai ai se mea o le datetime mai se manoa. Mau:

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)

Pulea e su'esu'e fa'asolo fa'alua tau mai le int, int, datetime, datetime , e foliga mai o le mea lea e te manaʻomia. E le gata i lea, o sea faasologa e le toe faia faalua i le laina.

I le faʻaaogaina o se faʻamatalaga masani, matou te maua ai faʻamatalaga talafeagai:

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)

Faamolemole ia matau pe a suʻesuʻe le faʻaaogaina o lenei faʻamatalaga, o le a le mafai ona fuafua NULL tau i fanua manaʻomia, ae i loʻu tulaga e le taua. Ona tatou ui atu lea i le mea na tatou mauaina i se matasele. Mau:

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)

O le mea moni, na o le pau lava lena, o faʻamaumauga mai le faʻasologa o faʻamaumauga o faʻamaumauga matou te manaʻomia. ###PS.###
Ou te malamalama o lenei metotia e le talafeagai mo tagata uma, ae o le sini autu o le tusiga o le faʻamalosia o gaioiga nai lo le foia o au faʻafitauli uma. Ou te manatu o le tali sili ona saʻo o le amata suʻesuʻeina o le source code oe lava ia mariadb, ae ona o le utiuti o le taimi, o le auala o loʻo iai nei na foliga mai e sili ona vave.

I nisi tulaga, pe a uma ona suʻesuʻeina le faila, o le a mafai ona e fuafuaina le faʻatusatusaina o le fausaga ma toe faʻafoʻisia e faʻaaoga ai se tasi o auala masani mai sootaga i luga. O le a sili atu ona sa'o ma fa'aitiitia ai fa'afitauli.

puna: www.habr.com