Back to Details

sql-injection-prevention

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

SQL Injection Prevention

SQL注入防护

Detect and fix SQL injection vulnerabilities across any framework. This is Finding #1 from Malaysian PDPA court cases -- the most common and most dangerous vulnerability.
检测并修复任意框架中的SQL注入漏洞。这是马来西亚PDPA法庭案件中排名第一的问题——最常见也最危险的漏洞。

The Rule

规则

NEVER concatenate user input into SQL strings. ALWAYS use parameterized bindings.
绝对不要将用户输入拼接进SQL字符串中。务必使用参数化绑定。

Detection Patterns

检测模式

Node.js / TypeScript

Node.js / TypeScript

BAD (vulnerable):
typescript
// Template literal injection
const result = await db.query(`SELECT * FROM users WHERE id = '${userId}'`);

// String concatenation
const result = await db.query("SELECT * FROM users WHERE name = '" + name + "'");

// Tagged template without proper escaping
const result = await db.raw(`SELECT * FROM orders WHERE status = ${status}`);
GOOD (safe):
typescript
// Parameterized binding with ?
const result = await db.prepare('SELECT * FROM users WHERE id = ?').bind(userId).first();

// Named parameters
const result = await db.query('SELECT * FROM users WHERE id = $1', [userId]);

// ORM with built-in escaping
const result = await prisma.user.findUnique({ where: { id: userId } });
不安全（存在漏洞）：
typescript
// Template literal injection
const result = await db.query(`SELECT * FROM users WHERE id = '${userId}'`);

// String concatenation
const result = await db.query("SELECT * FROM users WHERE name = '" + name + "'");

// Tagged template without proper escaping
const result = await db.raw(`SELECT * FROM orders WHERE status = ${status}`);
安全：
typescript
// Parameterized binding with ?
const result = await db.prepare('SELECT * FROM users WHERE id = ?').bind(userId).first();

// Named parameters
const result = await db.query('SELECT * FROM users WHERE id = $1', [userId]);

// ORM with built-in escaping
const result = await prisma.user.findUnique({ where: { id: userId } });

Cloudflare D1

Cloudflare D1

BAD:
typescript
await db.exec(`INSERT INTO logs (msg) VALUES ('${userInput}')`);
GOOD:
typescript
await db.prepare('INSERT INTO logs (msg) VALUES (?)').bind(userInput).run();
await db.batch([
  db.prepare('UPDATE credits SET amount = amount - 1 WHERE user_id = ?').bind(userId),
  db.prepare('INSERT INTO transactions (user_id, amount) VALUES (?, ?)').bind(userId, -1),
]);
不安全：
typescript
await db.exec(`INSERT INTO logs (msg) VALUES ('${userInput}')`);
安全：
typescript
await db.prepare('INSERT INTO logs (msg) VALUES (?)').bind(userInput).run();
await db.batch([
  db.prepare('UPDATE credits SET amount = amount - 1 WHERE user_id = ?').bind(userId),
  db.prepare('INSERT INTO transactions (user_id, amount) VALUES (?, ?)').bind(userId, -1),
]);

Laravel / PHP

Laravel / PHP

BAD:
php
DB::raw("SELECT * FROM users WHERE email = '$email'");
DB::select("SELECT * FROM users WHERE id = " . $request->id);
$query->whereRaw("status = '$status'");
$query->orderByRaw($request->sort_column . ' ' . $request->sort_direction);
GOOD:
php
DB::select('SELECT * FROM users WHERE email = ?', [$email]);
$query->whereRaw('status = ?', [$status]);
$query->orderByRaw('?? ??', [$sortColumn, $sortDirection]);
User::where('email', $email)->first();
不安全：
php
DB::raw("SELECT * FROM users WHERE email = '$email'");
DB::select("SELECT * FROM users WHERE id = " . $request->id);
$query->whereRaw("status = '$status'");
$query->orderByRaw($request->sort_column . ' ' . $request->sort_direction);
安全：
php
DB::select('SELECT * FROM users WHERE email = ?', [$email]);
$query->whereRaw('status = ?', [$status]);
$query->orderByRaw('?? ??', [$sortColumn, $sortDirection]);
User::where('email', $email)->first();

Python

Python

BAD:
python
cursor.execute(f"SELECT * FROM users WHERE id = {user_id}")
cursor.execute("SELECT * FROM users WHERE name = '" + name + "'")
cursor.execute("SELECT * FROM users WHERE id = %s" % user_id)
GOOD:
python
cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,))
cursor.execute("SELECT * FROM users WHERE id = :id", {"id": user_id})
User.objects.filter(id=user_id).first()  # Django ORM
不安全：
python
cursor.execute(f"SELECT * FROM users WHERE id = {user_id}")
cursor.execute("SELECT * FROM users WHERE name = '" + name + "'")
cursor.execute("SELECT * FROM users WHERE id = %s" % user_id)
安全：
python
cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,))
cursor.execute("SELECT * FROM users WHERE id = :id", {"id": user_id})
User.objects.filter(id=user_id).first()  # Django ORM

Search Patterns

搜索模式

When auditing a codebase, search for these regex patterns:
undefined
审计代码库时，搜索以下正则表达式模式：
undefined

Node.js/TypeScript

Node.js/TypeScript

.*\$\{.*\}.*
near SELECT|INSERT|UPDATE|DELETE "." + . near query|execute|raw .raw(.*${ .exec(`
.*\$\{.*\}.*
near SELECT|INSERT|UPDATE|DELETE "." + . near query|execute|raw .raw(.*$ .exec(`

Laravel

Laravel

DB::raw(.$ whereRaw(.$ orderByRaw(.$ ->select(DB::raw(.$
DB::raw(.$ whereRaw(.$ orderByRaw(.$ ->select(DB::raw(.$

Python

Python

execute(f" execute("." + execute(".%s" %
undefined
execute(f" execute("." + execute(".%s" %
undefined

Fix Process

修复流程

  1. Find all database query locations -- search for query/prepare/execute/raw calls
  2. Check each one -- does it use 
    ?
    placeholders or string interpolation?
  3. Replace interpolation with bindings -- swap 
    ${var}
    with 
    ?
    and add 
    .bind(var)
  4. Verify with tests -- run existing tests to ensure queries still work
  5. Add integration tests -- test that malicious input is safely handled
  1. 查找所有数据库查询位置——搜索query/prepare/execute/raw调用
  2. 逐一检查——是否使用
    ?
    占位符还是字符串插值？
  3. 用绑定替换插值——将
    ${var}
    替换为
    ?
    并添加
    .bind(var)
  4. 通过测试验证——运行现有测试确保查询仍能正常工作
  5. 添加集成测试——测试恶意输入是否被安全处理

PDPA 2024 Consequence

PDPA 2024 后果

Seksyen 5(1) dan 5(2) PDPA 2010 (Pindaan 2024):
  • Denda sehingga RM1,000,000
  • Penjara sehingga 3 tahun
  • Atau kedua-duanya sekali
SQL injection is the #1 finding in forensic digital analysis of negligent systems.
2010年PDPA（2024修订版）第5(1)和5(2)条：
  • 最高罚款100万林吉特
  • 最高监禁3年
  • 或两者兼施
SQL注入是过失系统的数字取证分析中排名第一的问题。