SQL Injection — qanday himoyalanish, parameterized query

~15 daq · PostgreSQL · Xavfsizlik va Boshqaruv

Login forma. Foydalanuvchi email va parol kiritadi. Kod:

const sql = `SELECT * FROM users WHERE email = '${email}' AND parol = '${parol}'`;

Hacker email ga quyidagini kiritdi:

' OR '1'='1

So'rov:

SELECT * FROM users WHERE email = '' OR '1'='1' AND parol = '...'

'1'='1' har doim TRUE — barcha foydalanuvchilar qaytadi. Hacker tizimga kirdi.

Bu SQL Injection — web xavfsizligining eng xavfli zaifliklaridan biri.

Parameterized Query — yechim

Foydalanuvchi ma'lumotini SQL ga qo'shmaslik — alohida parametr sifatida uzatish:

// ❌ — SQL Injection mumkin
const sql = `SELECT * FROM users WHERE email = '${email}'`;

// ✅ — parameterized query
const sql = 'SELECT * FROM users WHERE email = $1 AND parol = $2';
const { rows } = await client.query(sql, [email, parol]);

$1, $2 — parametrlar. PostgreSQL ularni matn sifatida qabul qiladi, hech qachon SQL sifatida bajarmaydi.

Node.js pg moduli

// ✅ — har doim shunday
const { rows } = await pool.query(
  'SELECT * FROM mahsulotlar WHERE id = $1',
  [id]
);

// ✅ — bir nechta parametr
const { rows } = await pool.query(
  'INSERT INTO users (ism, email) VALUES ($1, $2) RETURNING id',
  [ism, email]
);

LIKE bilan parametr

// ❌ — % user tomonidan kiritilishi mumkin
const { rows } = await pool.query(
  `SELECT * FROM mahsulotlar WHERE nomi LIKE '%${qidiruv}%'`
);

// ✅ — parametr ichida % xavfsiz
const { rows } = await pool.query(
  'SELECT * FROM mahsulotlar WHERE nomi ILIKE $1',
  [`%${qidiruv}%`]
);

ORM va query builder

ORM lar (Prisma, TypeORM, Sequelize) va query builder lar (Knex) avtomatik parameterized query ishlatadi:

// Prisma — xavfsiz
const users = await prisma.user.findMany({
  where: { email: email }
});

// Knex — xavfsiz
const users = await knex('users').where({ email });

Raw SQL ishlatilsa ham parametr bilan:

// Prisma raw — parametrli
const users = await prisma.$queryRaw`
  SELECT * FROM users WHERE email = ${email}
`;

Tez-tez uchraydigan xatolar

String interpolation bilan SQL:

// ❌ — HECH QACHON
const query = `DELETE FROM users WHERE id = ${req.params.id}`;

Hatto id raqam bo'lsa ham — doim parametr ishlatilsin.

LIKE da foydalanuvchi % kiritishi:

// Foydalanuvchi "%" kiritse — barcha qatorlar qaytadi
const { rows } = await pool.query(
  'SELECT * FROM users WHERE ism LIKE $1',
  [userInput] // "%" — barcha foydalanuvchilar!
);

// Agar kerak bo'lsa — escape qilish
const escaped = userInput.replace(/%/g, '\\%').replace(/_/g, '\\_');

Xulosa

SQL Injection — foydalanuvchi ma'lumoti SQL sifatida bajarilishi

Himoya:
  ✅ Parameterized query — $1, $2, ...
  ✅ ORM / Query builder
  ✅ Stored Procedure

  ❌ String interpolation: `WHERE id = ${id}`
  ❌ String concatenation: "WHERE id = " + id

// TO'G'RI YO'L — har doim
pool.query('SELECT * FROM t WHERE id = $1', [id]);
pool.query('INSERT INTO t (a, b) VALUES ($1, $2)', [a, b]);

Keyingi dars

55-dars: Backup va Restore — pg_dump, pg_restore, pg_basebackup