Ma'lumotlar Bazasi va SQL: Relyatsion Arxitektura va Xavfsizlik

-- 1. Foydalanuvchilar jadvali
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    full_name VARCHAR(100) NOT NULL,
    balance NUMERIC(10, 2) DEFAULT 0.00 CHECK (balance >= 0),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 2. Buyurtmalar jadvali (FK bilan)
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    total_price NUMERIC(10, 2) NOT NULL CHECK (total_price > 0),
    status VARCHAR(20) DEFAULT 'pending',
    ordered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- C (Create): Ma'lumot kiritish
INSERT INTO users (email, full_name, balance)
VALUES ('jasur@target.uz', 'Jasur Aliyev', 500000.00);

-- R (Read): Tanlash va saralash
SELECT id, full_name, balance FROM users
WHERE balance > 100000
ORDER BY balance DESC LIMIT 10;

-- U (Update) & D (Delete)
UPDATE users SET balance = balance - 50000 WHERE id = 1;
DELETE FROM orders WHERE status = 'cancelled';

-- INNER JOIN: Faqat buyurtmasi bor foydalanuvchilar

SELECT users.full_name, orders.id AS order_id, orders.total_price

FROM users

INNER JOIN orders ON users.id = orders.user_id;


-- LEFT JOIN: Barcha foydalanuvchilar (hatto buyurtmasi yo'qlar ham)

SELECT users.full_name, COUNT(orders.id) AS total_orders

FROM users

LEFT JOIN orders ON users.id = orders.user_id

GROUP BY users.id, users.full_name;

// ❌ XAVFLI: String konkatenatsiyasi (SQL Injection)

const userEmail = req.body.email; // Kiritildi: ' OR '1'='1

const query = `SELECT * FROM users WHERE email = '${userEmail}'`;

// Baza bajaradi: SELECT * FROM users WHERE email = '' OR '1'='1'

// NATIJA: Barcha foydalanuvchilar parollari o'g'irlanadi!


// ✅ 100% XAVFSIZ: Parameterized Query (Prepared Statement)

const safeQuery = 'SELECT * FROM users WHERE email = $1';

const result = await db.query(safeQuery, [userEmail]);

// Baza foydalanuvchi kiritgan matnni HECH QACHON kod deb o'ylamaydi!

// Prisma ORM orqali turga mos (Type-safe) so'rov

const userWithOrders = await prisma.user.findUnique({

  where: { email: 'jasur@target.uz' },

  include: {

    orders: {

      where: { status: 'completed' },

      orderBy: { total_price: 'desc' }

    }

  }

});

// TypeScript xatolarni kod yozish jarayonidayoq ushlaydi!