// ❌ VULNERABLE: Never do this
const userId = req.query.id;  // User input: "1 OR 1=1"
const query = `SELECT * FROM users WHERE id = ${userId}`;
// Result: SELECT * FROM users WHERE id = 1 OR 1=1
// Returns ALL users!

// Even "safer" concatenation is vulnerable
const username = req.body.username;  // "admin' --"
const password = req.body.password;
const query = `SELECT * FROM users 
               WHERE username = '${username}' 
               AND password = '${password}'`;
// Result: SELECT * FROM users WHERE username = 'admin' --' AND password = ''
// The -- comments out the password check. Admin access granted!

// ✅ SECURE: Node.js with pg (PostgreSQL)
const { Pool } = require('pg');
const pool = new Pool();

// Parameterized query — user input is data, never code
const result = await pool.query(
  'SELECT * FROM users WHERE id = $1 AND status = $2',
  [userId, 'active']
);

// ✅ SECURE: Python with psycopg2
cursor.execute(
    "SELECT * FROM users WHERE email = %s AND created_at > %s",
    (email, date_threshold)
)

// ✅ SECURE: Java with JDBC
PreparedStatement stmt = conn.prepareStatement(
    "SELECT * FROM users WHERE username = ? AND role = ?"
);
stmt.setString(1, username);
stmt.setString(2, role);
ResultSet rs = stmt.executeQuery();

// ✅ SECURE: PHP with PDO
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = :id");
$stmt->execute(['id' => $userId]);

// ❌ VULNERABLE: Sequelize raw query with interpolation
const users = await sequelize.query(
  `SELECT * FROM users WHERE name = '${req.query.name}'`,
  { type: QueryTypes.SELECT }
);

// ❌ VULNERABLE: Sequelize where with raw string
const users = await User.findAll({
  where: sequelize.literal(`email = '${email}'`)
});

// ❌ VULNERABLE: Django raw() with formatting
User.objects.raw("SELECT * FROM users WHERE id = %s" % user_id)

// ❌ VULNERABLE: Laravel DB::raw with interpolation
$users = DB::select("SELECT * FROM users WHERE role = '$role'");

// ✅ SECURE: Proper ORM usage
// Sequelize — parameterized automatically
const users = await User.findAll({
  where: { name: req.query.name }  // Safe!
});

// Django — ORM query
users = User.objects.filter(email=email)  // Safe!

// Laravel — query builder
$users = DB::table('users')->where('role', $role)->get();  // Safe!

// Node.js with Joi
const schema = Joi.object({
  userId: Joi.number().integer().positive().required(),
  email: Joi.string().email().required(),
  role: Joi.string().valid('user', 'admin', 'moderator').required(),
  sortBy: Joi.string().valid('name', 'created_at', 'last_login').default('created_at'),
  page: Joi.number().integer().min(1).default(1),
  limit: Joi.number().integer().min(1).max(100).default(20)
});

const { error, value } = schema.validate(req.query);
if (error) {
  return res.status(400).json({ error: 'Invalid input' });
}

// Now value is guaranteed safe — but STILL use parameterized queries!
const users = await pool.query(
  'SELECT * FROM users WHERE role = $1 ORDER BY ${sortColumn} LIMIT $2 OFFSET $3',
  [value.role, value.limit, (value.page - 1) * value.limit]
);

-- Create application user with minimal permissions
CREATE USER app_user WITH PASSWORD 'strong_random_password';

-- Grant only necessary permissions
GRANT SELECT, INSERT, UPDATE, DELETE ON users TO app_user;
GRANT SELECT, INSERT ON orders TO app_user;
GRANT SELECT ON products TO app_user;

-- Explicitly deny dangerous permissions
-- (Default: no permissions granted, so nothing to revoke)

-- Create read-only reporting user
CREATE USER reporter WITH PASSWORD 'another_strong_password';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporter;

-- Revoke dangerous permissions from all users
REVOKE ALL ON SCHEMA public FROM PUBLIC;

// ❌ VULNERABLE: Direct user input in MongoDB query
app.post('/login', async (req, res) => {
  const { username, password } = req.body;

  // Attacker sends: { "username": "admin", "password": { "$gt": "" } }
  // This matches ANY password!
  const user = await db.collection('users').findOne({
    username: username,
    password: password  // Direct user input — DANGEROUS
  });

  if (user) {
    return res.json({ token: generateToken(user) });
  }
});

// ✅ SECURE: Type validation and explicit comparison
app.post('/login', async (req, res) => {
  const username = String(req.body.username || '');
  const password = String(req.body.password || '');

  // Validate types
  if (typeof req.body.password !== 'string') {
    return res.status(400).json({ error: 'Invalid input' });
  }

  // Use explicit equality, never pass objects from user input
  const user = await db.collection('users').findOne({
    username: username
  });

  // Compare password with bcrypt (never store plaintext!)
  if (user && await bcrypt.compare(password, user.passwordHash)) {
    return res.json({ token: generateToken(user) });
  }

  return res.status(401).json({ error: 'Invalid credentials' });
});