SQL Injection Prevention

🔥 Vibe Prompt

"Check this code for SQL injection risks and convert it to safe parameterized queries."

❌ Dangerous

user_input = "' OR '1'='1"
query = f"SELECT * FROM users WHERE email = '{user_input}'"
# Becomes: SELECT * FROM users WHERE email = '' OR '1'='1'
# Returns ALL users!!!

✅ Safe

import psycopg2
conn = psycopg2.connect("dbname=test")
cur = conn.cursor()
cur.execute("SELECT * FROM users WHERE email = %s", (user_input,))

ORM Protection (Supabase)

const { data } = await supabase
  .from('users')
  .select('*')
  .eq('email', userInput)  // Auto-parameterized

Rule of Thumb

Never concatenate user input into SQL strings. Always use parameterized queries or ORMs.

Chapter Summary

• Understand core concepts and principles
• Master implementation methods and techniques
• Familiar with common issues and solutions
• Able to apply in real projects

Further Reading

• Official documentation and API references
• Open source examples on GitHub
• Technical books and online courses
• Community discussions and tech blogs

Implementation Example

Basic Example

# This section provides a complete implementation example

Steps

1. Setup: Configure development environment
2. Data: Prepare required data
3. Implementation: Build core functionality
4. Testing: Verify correctness
5. Optimization: Improve performance

Common Errors

| Error Type | Cause | Solution | |------------|-------|----------| | Compilation | Syntax | Check code syntax | | Runtime | Environment | Verify dependencies installed | | Logic | Algorithm | Step-by-step debugging | | Performance | Efficiency | Use profilers |

Code Example

import sys

def main():
    print("Hello, World!")

if __name__ == "__main__":
    main()

References

• Official documentation
• API reference
• Open source examples
• Community discussions

Key Points

• Understand the core concepts thoroughly
• Practice with hands-on code examples
• Apply knowledge to real-world problems
• Review and reinforce through exercises

Further Learning

• Official documentation
• Open source projects on GitHub
• Community forums and discussions
• Related courses and tutorials

SQL Injection Attacks

What Is SQL Injection?

SQL injection occurs when user input is inserted into SQL queries without proper sanitization, allowing attackers to manipulate the query.

Attack Examples

| Input | Injected Query | Result | |-------|---------------|--------| | ' OR 1=1 -- | SELECT * FROM users WHERE email = '' OR 1=1 --' | Returns ALL users | | '; DROP TABLE users; -- | SELECT * FROM users WHERE id = ''; DROP TABLE users; --' | Deletes users table | | ' UNION SELECT * FROM credit_cards -- | SELECT name FROM users WHERE id = '' UNION SELECT card_number FROM credit_cards --' | Leaks credit cards |

Blind SQL Injection

Attackers may not see results directly but can infer data through boolean responses or timing.

# Boolean-based blind
' OR (SELECT SUBSTRING(password,1,1) FROM admins WHERE id=1) = 'a' --

# Time-based blind
'; IF (SELECT COUNT(*) FROM users) > 100 WAITFOR DELAY '0:0:5' --

Prevention Techniques

Parameterized Queries (Best)

// Node.js + PostgreSQL — parameterized
const result = await sql`
  SELECT * FROM users
  WHERE email = ${email}
  AND status = 'active'
`;

// Python + psycopg2 — parameterized
cur.execute(
  "SELECT * FROM users WHERE email = %s AND status = %s",
  (email, 'active')
)

ORM Protection

// Prisma — automatically parameterized
const user = await prisma.user.findUnique({
  where: { email }
});

// TypeORM — also parameterized
const user = await userRepository.findOne({
  where: { email }
});

Stored Procedures

CREATE PROCEDURE get_user_by_email(IN user_email VARCHAR(255))
BEGIN
  SELECT * FROM users WHERE email = user_email;
END;

What NOT to Use

| Method | Why It Fails | |--------|-------------| | Manual escaping with regex | Attackers bypass filters | | Blacklisting keywords | SELECT, DROP, UNION can be encoded | | Client-side validation only | Attackers send raw HTTP requests | | Stored procedures with dynamic SQL | Still injectable if concatenating |

Summary

SQL injection is one of the most dangerous web vulnerabilities. Always use parameterized queries or an ORM — never concatenate user input into SQL strings.

Key takeaways: | SQL injection: attacker inserts malicious SQL through input fields | | Attack types: in-band (direct output), blind (boolean/timing), out-of-band | | Prevention: parameterized queries ($1, %s, ? placeholders) | | ORMs (Prisma, TypeORM) automatically parameterize queries | | Manual escaping is unreliable — always use parameterization | | Never trust user input — validate and parameterize | | Stored procedures are safe only if they don't build dynamic SQL | | Blacklisting is ineffective — attackers encode and bypass |

What's Next: XSS and CSP

The next chapter covers cross-site scripting and content security policy.