Ma'lumotlar bazasi xavfsizligi

CREATE TABLE foydalanuvchilar (
    id     INT PRIMARY KEY AUTO_INCREMENT,
    ism    VARCHAR(50)  NOT NULL,
    email  VARCHAR(100) NOT NULL,
    rol    ENUM('foydalanuvchi', 'admin') NOT NULL DEFAULT 'foydalanuvchi'
);

INSERT INTO foydalanuvchilar (ism, email, rol) VALUES
    ('Husanboy', '[email protected]', 'foydalanuvchi'),
    ('Malika',   '[email protected]',   'foydalanuvchi'),
    ('Admin',    '[email protected]',    'admin');

-- Normal so'rov
SELECT id, ism, rol FROM foydalanuvchilar WHERE ism = 'Husanboy';

-- Hujum natijasida hosil bo'lgan so'rov
SELECT id, ism, rol FROM foydalanuvchilar WHERE ism = '' OR '1'='1';

-- Tayyorlangan so'rov: SQL va MA'LUMOT ajratilgan
PREPARE qidirish FROM 'SELECT id, ism, rol FROM foydalanuvchilar WHERE ism = ?';

SET @kirish = 'Husanboy';
EXECUTE qidirish USING @kirish;

PREPARE sanash FROM
    'SELECT COUNT(*) AS topildi FROM foydalanuvchilar WHERE ism = ?';

-- Hujum urinishi - endi u shunchaki MATN
SET @kirish = ''' OR ''1''=''1';
EXECUTE sanash USING @kirish;

DEALLOCATE PREPARE sanash;

-- Ilova foydalanuvchisi uchun huquqlar
SELECT
    'CREATE USER ilova@localhost IDENTIFIED BY ...' AS qadam
UNION ALL SELECT 'GRANT SELECT, INSERT, UPDATE, DELETE ON dukon.* TO ilova@localhost'
UNION ALL SELECT '-- DROP, ALTER, CREATE BERILMAYDI'
UNION ALL SELECT '-- Migratsiya uchun ALOHIDA foydalanuvchi';

-- NOTO'G'RI: ochiq matn
SELECT 'parol123' AS ochiq_parol,
       MD5('parol123')  AS md5_xesh,
       SHA1('parol123') AS sha1_xesh;

CREATE TABLE mijozlar (
    id           INT PRIMARY KEY AUTO_INCREMENT,
    ism          VARCHAR(100) NOT NULL,
    email        VARCHAR(100) NOT NULL,
    telefon      VARCHAR(20)  NOT NULL,
    karta_oxiri  CHAR(4)      NULL,
    yaratilgan   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO mijozlar (ism, email, telefon, karta_oxiri, yaratilgan) VALUES
    ('Husanboy Qodirov', '[email protected]', '901234567', '4242', '2026-01-10 10:00:00'),
    ('Malika Yusupova',  '[email protected]',   '911112233', NULL,   '2026-02-15 11:00:00');

-- Qo'llab-quvvatlash xodimi uchun ko'rinish
CREATE VIEW mijozlar_qisqa AS
SELECT
    id,
    ism,
    CONCAT(LEFT(email, 2), '***@', SUBSTRING_INDEX(email, '@', -1)) AS email,
    CONCAT('***', RIGHT(telefon, 4)) AS telefon,
    yaratilgan
FROM mijozlar;

SELECT id, ism, email, telefon FROM mijozlar_qisqa ORDER BY id;

CREATE TABLE audit (
    id          INT PRIMARY KEY AUTO_INCREMENT,
    jadval      VARCHAR(64)  NOT NULL,
    qator_id    INT          NOT NULL,
    amal        ENUM('INSERT', 'UPDATE', 'DELETE') NOT NULL,
    kim         VARCHAR(100) NOT NULL,
    qachon      DATETIME     NOT NULL,
    eski_qiymat JSON         NULL,
    yangi_qiymat JSON        NULL
);

INSERT INTO audit (jadval, qator_id, amal, kim, qachon, eski_qiymat, yangi_qiymat) VALUES
    ('mijozlar', 1, 'UPDATE', '[email protected]', '2026-03-01 09:15:00',
     '{"telefon":"901234567"}', '{"telefon":"999999999"}'),
    ('mijozlar', 2, 'DELETE', '[email protected]', '2026-03-02 14:20:00',
     '{"ism":"Test"}', NULL);

SELECT jadval, qator_id, amal, kim, qachon,
       JSON_VALUE(eski_qiymat, '$.telefon') AS eski_telefon
FROM audit
ORDER BY id;

SELECT
    'mysqldump --single-transaction --routines --triggers' AS buyruq,
    'InnoDB uchun jadvalni qulflamaydi' AS izoh
UNION ALL SELECT '--single-transaction', 'Izchil surat oladi'
UNION ALL SELECT '--routines --triggers', 'Protsedura va triggerlarni ham oladi'
UNION ALL SELECT '--set-gtid-purged=OFF', 'Replikatsiya bilan muammo bo''lmasin';