SQL Injection Prevention: Protecting Your Database

What is SQL Injection?

Imagine your database is like a treasure chest. SQL Injection is when someone tricks your website into giving them the key to this chest. They can then steal, change, or even delete your precious data!

⚠️ Real Example

If someone types this into a login form:

username: admin' --
password: anything

And your website uses this unsafe code:

// ❌ Bad Code
const query = `SELECT * FROM users WHERE username = '${username}' AND password = '${password}'`;

They could log in as admin without knowing the password!

How to Stop SQL Injection

Here are simple ways to protect your database:

1. Use Parameterized Queries

Think of parameters as special boxes that keep bad code locked up. Here’s how to use them:

// ✅ Good Code with Node.js
const { Pool } = require('pg');
const pool = new Pool();

async function getUser(username, password) {
    const query = 'SELECT * FROM users WHERE username = $1 AND password = $2';
    const values = [username, password];
    return pool.query(query, values);
}

// ✅ Good Code with PHP
$stmt = $pdo->prepare('SELECT * FROM users WHERE username = :username AND password = :password');
$stmt->execute(['username' => $username, 'password' => $password]);

2. Use ORM (Object-Relational Mapping)

ORM is like a translator that keeps your database safe. Here’s how to use it:

// Using Sequelize (Node.js)
const User = require('./models/user');

async function getUser(username, password) {
    return User.findOne({
        where: {
            username: username,
            password: password
        }
    });
}

// Using Django ORM (Python)
user = User.objects.get(username=username, password=password)

3. Validate and Sanitize Input

Always check and clean data before using it in queries:

// Simple input validation
function validateUsername(username) {
    // Only allow letters, numbers, and underscores
    return /^[a-zA-Z0-9_]+$/.test(username);
}

// Using a validation library
const { body, validationResult } = require('express-validator');

app.post('/login', [
    body('username').isAlphanumeric().withMessage('Invalid username'),
    body('password').isLength({ min: 8 }).withMessage('Password too short')
], (req, res) => {
    const errors = validationResult(req);
    if (!errors.isEmpty()) {
        return res.status(400).json({ errors: errors.array() });
    }
    // Safe to proceed with login
});

Real-World Examples

Safe User Registration

Here’s how to safely register new users:

// Using Node.js with Express and pg
app.post('/register', async (req, res) => {
    const { username, email, password } = req.body;
    
    // Validate input
    if (!validateUsername(username)) {
        return res.status(400).json({ error: 'Invalid username' });
    }
    
    try {
        // Hash password
        const hashedPassword = await bcrypt.hash(password, 10);
        
        // Safe database query
        const result = await pool.query(
            'INSERT INTO users (username, email, password) VALUES ($1, $2, $3) RETURNING id',

[username, email, hashedPassword]

); res.json({ id: result.rows[0].id }); } catch (error) { res.status(500).json({ error: ‘Registration failed’ }); } });

Safe Search Function

How to safely search your database:

// Safe search with pagination
app.get('/products', async (req, res) => {
    const { search, page = 1, limit = 10 } = req.query;
    
    try {
        const offset = (page - 1) * limit;
        
        const result = await pool.query(
            'SELECT * FROM products WHERE name ILIKE $1 ORDER BY name LIMIT $2 OFFSET $3',
            [`%${search}%`, limit, offset]
        );
        
        res.json(result.rows);
    } catch (error) {
        res.status(500).json({ error: 'Search failed' });
    }
});

Common Mistakes to Avoid

❌ Don’t Concatenate SQL Strings

Never build SQL queries by joining strings together:

// ❌ Bad
const query = `SELECT * FROM users WHERE username = '${username}'`;

// ✅ Good
const query = 'SELECT * FROM users WHERE username = $1';
const values = [username];

❌ Don’t Trust User Input

Always validate and sanitize data before using it:

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

// ✅ Good
const id = parseInt(req.params.id);
if (isNaN(id)) {
    return res.status(400).json({ error: 'Invalid ID' });
}
const query = 'DELETE FROM users WHERE id = $1';
const values = [id];

Testing Your Website

Here’s how to test if your website is safe from SQL Injection:

// Try these in your forms
const testInputs = [
    "' OR '1'='1",
    "admin' --",
    "'; DROP TABLE users; --",
    "1' OR '1'='1"
];

If any of these inputs work, your website needs more protection!