Foydalanuvchilar va xavfsizlik

-- Yaratish
CREATE USER 'dokon_app'@'localhost' IDENTIFIED BY 'KuchliParol123!';

-- Istalgan xostdan ulanish uchun
CREATE USER 'admin'@'%' IDENTIFIED BY 'ParolBuYerda';

-- Parolni o'zgartirish
ALTER USER 'dokon_app'@'localhost' IDENTIFIED BY 'YangiParol456!';

-- O'chirish
DROP USER 'dokon_app'@'localhost';

-- Ro'yxat
SELECT user, host FROM mysql.user;

-- Ma'lum bazaga to'liq huquq
GRANT ALL PRIVILEGES ON dokon.* TO 'dokon_admin'@'localhost';

-- Faqat kerakli amallar (tavsiya etiladi)
GRANT SELECT, INSERT, UPDATE, DELETE ON dokon.* TO 'dokon_app'@'localhost';

-- Faqat o'qish
GRANT SELECT ON dokon.* TO 'hisobotchi'@'localhost';

-- Bitta jadval
GRANT SELECT ON dokon.mahsulotlar TO 'katalog'@'localhost';

-- Bitta ustun
GRANT SELECT (id, nomi, narx) ON dokon.mahsulotlar TO 'narxlar'@'localhost';

-- Qo'llash
FLUSH PRIVILEGES;

-- Ko'rish
SHOW GRANTS FOR 'dokon_app'@'localhost';

-- Bekor qilish
REVOKE DELETE ON dokon.* FROM 'dokon_app'@'localhost';

// XAVFLI - hech qachon shunday yozmang
$ism = $_GET['ism'];
$sql = "SELECT * FROM talabalar WHERE ism = '$ism'";
$natija = $pdo->query($sql);

SELECT * FROM talabalar WHERE ism = 'Husanboy'

SELECT * FROM talabalar WHERE ism = '' OR '1'='1'

-- 1. Autentifikatsiyani chetlab o'tish
-- Kiritilgan: admin'--
SELECT * FROM users WHERE login = 'admin'--' AND parol = '...'
-- `--` dan keyingi hammasi izohga aylanadi, parol tekshirilmaydi

-- 2. Boshqa jadvaldan ma'lumot o'g'irlash
-- Kiritilgan: ' UNION SELECT login, parol, 1 FROM users--
SELECT nomi, tavsif, narx FROM mahsulotlar WHERE id = '' 
UNION SELECT login, parol, 1 FROM users--'

-- 3. Ma'lumotni yo'q qilish
-- Kiritilgan: '; DROP TABLE talabalar;--

// TO'G'RI: PDO bilan
$stmt = $pdo->prepare('SELECT * FROM talabalar WHERE ism = ?');
$stmt->execute([$ism]);
$talabalar = $stmt->fetchAll();

// Nomlangan parametrlar bilan (o'qishga qulay)
$stmt = $pdo->prepare(
    'SELECT * FROM talabalar WHERE shahar = :shahar AND ortacha_baho >= :baho'
);
$stmt->execute([
    'shahar' => $shahar,
    'baho'   => $baho,
]);

# Python
kursor.execute("SELECT * FROM talabalar WHERE ism = %s", (ism,))

# XATO - f-satr bilan
kursor.execute(f"SELECT * FROM talabalar WHERE ism = '{ism}'")

// Node.js (mysql2)
const [qatorlar] = await ulanish.execute(
    'SELECT * FROM talabalar WHERE ism = ?', [ism]
);

// TO'G'RI: PHP da
$xesh = password_hash($parol, PASSWORD_DEFAULT);

$stmt = $pdo->prepare('INSERT INTO users (login, parol_xesh) VALUES (?, ?)');
$stmt->execute([$login, $xesh]);

// Tekshirish
$stmt = $pdo->prepare('SELECT parol_xesh FROM users WHERE login = ?');
$stmt->execute([$login]);
$user = $stmt->fetch();

if ($user && password_verify($parol, $user['parol_xesh'])) {
    // kirish muvaffaqiyatli
}

-- Ustun uzunligi yetarli bo'lsin
CREATE TABLE users (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    login      VARCHAR(60) NOT NULL UNIQUE,
    parol_xesh VARCHAR(255) NOT NULL,        -- 60 emas, 255!
    yaratilgan TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Shifrlash
INSERT INTO hujjatlar (raqam)
VALUES (AES_ENCRYPT('AA1234567', 'maxfiy_kalit'));

SELECT AES_DECRYPT(raqam, 'maxfiy_kalit') AS raqam FROM hujjatlar;

-- Qidiruv uchun xesh (deterministik)
CREATE TABLE mijozlar (
    id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email_xesh   CHAR(64) NOT NULL UNIQUE,      -- qidiruv uchun
    email_shifr  VARBINARY(255) NOT NULL        -- ko'rsatish uchun
);

-- Hisobotlar uchun VIEW
CREATE VIEW mijozlar_maskalangan AS
SELECT
    id,
    ism,
    CONCAT(LEFT(email, 2), '***@', SUBSTRING_INDEX(email, '@', -1)) AS email,
    CONCAT('+998 ** *** ** ', RIGHT(telefon, 2))                    AS telefon
FROM mijozlar;

SELECT * FROM mijozlar_maskalangan;

// Ma'lumotlarni kodda saqlamang
$dsn      = getenv('DB_DSN');
$user     = getenv('DB_USER');
$password = getenv('DB_PASSWORD');

$pdo = new PDO($dsn, $user, $password, [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,      // haqiqiy tayyorlangan so'rov
]);

# my.cnf - faqat localhost dan ulanish
[mysqld]
bind-address = 127.0.0.1

// XAVFLI: baza tuzilmasini oshkor qiladi
try {
    $pdo->query($sql);
} catch (PDOException $e) {
    echo $e->getMessage();
    // "Table 'dokon.users' doesn't exist" - hujumchiga foydali ma'lumot
}

// TO'G'RI
try {
    $pdo->query($sql);
} catch (PDOException $e) {
    error_log($e->getMessage());              // jurnalga yozamiz
    echo 'Serverda xatolik yuz berdi.';       // foydalanuvchiga umumiy xabar
}

-- Kirish urinishlarini yozish
CREATE TABLE kirish_urinishlari (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ip         VARCHAR(45) NOT NULL,
    login      VARCHAR(60),
    muvaffaqiyat BOOLEAN NOT NULL DEFAULT FALSE,
    vaqt       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    KEY idx_ip_vaqt (ip, vaqt)
) ENGINE=InnoDB;

-- Oxirgi 15 daqiqadagi muvaffaqiyatsiz urinishlar
SELECT COUNT(*) FROM kirish_urinishlari
WHERE ip = ? AND muvaffaqiyat = FALSE
  AND vaqt > DATE_SUB(NOW(), INTERVAL 15 MINUTE);