Express PostgreSQL Parameterized Queries

⚠️ Critical Security Fix: Transform PostgreSQL queries to use parameterized queries and prevent SQL injection vulnerabilities (CWE-89).

This codemod automatically transforms PostgreSQL queries in Express applications that use string concatenation or template literals into safe parameterized queries using the pg (node-postgres) library.

🔒 Security Impact

Vulnerability: CWE-89 - SQL Injection
OWASP: A03:2021 - Injection
Severity: Critical

SQL injection occurs when user input is directly concatenated into SQL query strings, allowing attackers to manipulate the query structure and potentially:

• Access unauthorized data
• Modify or delete database records
• Execute administrative operations
• Bypass authentication

🚀 Installation

bash
Copy
npx codemod @codemod/express-postgres-parameterized-queries

✨ Transformations

Template Literals → Parameterized Queries

Before:

javascript
Copy
client.query(`SELECT * FROM users WHERE id = ${userId}`)

After:

javascript
Copy
client.query('SELECT * FROM users WHERE id = $1', [userId])

String Concatenation → Parameterized Queries

Before:

javascript
Copy
pool.query('SELECT * FROM products WHERE name = "' + productName + '"')

After:

javascript
Copy
pool.query('SELECT * FROM products WHERE name = $1', [productName])

Complex Multi-Parameter Queries

Before:

javascript
Copy
db.query(`INSERT INTO posts (title, content, author_id) VALUES ('${title}', '${content}', ${authorId})`)

After:

javascript
Copy
db.query('INSERT INTO posts (title, content, author_id) VALUES ($1, $2, $3)', [title, content, authorId])

DELETE and UPDATE Operations

Before:

javascript
Copy
client.query('UPDATE users SET email = "' + email + '" WHERE id = ' + id)

After:

javascript
Copy
client.query('UPDATE users SET email = $1 WHERE id = $2', [email, id])

🎯 Scope

✅ Transforms

• Files importing pg or node-postgres
• Express route handlers and middleware
• .query() method calls with:
  • Template literals containing variables (${variable})
  • String concatenation with + operator
  • Mixed quote styles and complex expressions

❌ Ignores

• Already parameterized queries (using $1, $2 syntax)
• Static queries without dynamic content
• Query builders (Knex, Sequelize, Prisma)
• Test files (*.test.*, *.spec.*)
• Database migration files
• Comments and documentation

🔧 Usage Examples

Express Route Handler

javascript
Copy
// Before
app.get('/user/:id', (req, res) => {
  client.query(`SELECT * FROM users WHERE id = ${req.params.id}`)
    .then(result => res.json(result.rows))
});

// After  
app.get('/user/:id', (req, res) => {
  client.query('SELECT * FROM users WHERE id = $1', [req.params.id])
    .then(result => res.json(result.rows))
});

Middleware Function

javascript
Copy
// Before
const authenticate = async (req, res, next) => {
  const user = await db.query(`SELECT * FROM users WHERE token = '${req.headers.authorization}'`);
  // ...
};

// After
const authenticate = async (req, res, next) => {
  const user = await db.query('SELECT * FROM users WHERE token = $1', [req.headers.authorization]);
  // ...
};

🔍 Why This Matters

Vulnerable Code Example

javascript
Copy
// DANGEROUS: SQL Injection vulnerability
app.post('/login', (req, res) => {
  const { email, password } = req.body;
  client.query(`SELECT * FROM users WHERE email = '${email}' AND password = '${password}'`)
});

// Attacker input: email = "admin@example.com' OR '1'='1' --"
// Resulting query: SELECT * FROM users WHERE email = 'admin@example.com' OR '1'='1' --' AND password = '...'
// This bypasses authentication!

Secure Code After Transformation

javascript
Copy
// SECURE: Uses parameterized queries
app.post('/login', (req, res) => {
  const { email, password } = req.body;
  client.query('SELECT * FROM users WHERE email = $1 AND password = $2', [email, password])
});

// Attacker input is safely escaped as a parameter value
// No SQL injection possible

🏗️ Development

bash
Copy
# Run tests
pnpm test

# Test on specific case
pnpm test tests/template-literal

# Update test snapshots
pnpm test -u

# Validate workflow
npx codemod validate -w workflow.yaml

📚 References

• CWE-89: Improper Neutralization of Special Elements used in an SQL Command ('SQL Injection')
• OWASP SQL Injection Prevention Cheat Sheet
• PostgreSQL Extended Query Protocol
• node-postgres Parameterized Queries

📄 License

MIT