Prevent SQL Injection with Parameterized Queries (Node.js)

const { Pool } = require('pg');

// Create a new pool instance for PostgreSQL
const pool = new Pool({
    user: 'your_db_user',
    host: 'localhost',
    database: 'your_db_name',
    password: 'your_db_password',
    port: 5432,
});

async function getUserById(userId) {
    try {
        // UNSAFE: Direct string concatenation (Vulnerable to SQL Injection)
        // const unsafeQuery = `SELECT * FROM users WHERE id = ${userId}`;
        // const unsafeResult = await pool.query(unsafeQuery);
        // console.log('Unsafe result (DO NOT USE IN PRODUCTION):', unsafeResult.rows);

        // SAFE: Using parameterized queries
        const safeQuery = 'SELECT * FROM users WHERE id = $1'; // $1 is a placeholder for the first parameter
        const safeResult = await pool.query(safeQuery, [userId]); // Pass parameters as an array

        console.log('Safe result:', safeResult.rows);
        return safeResult.rows;

    } catch (error) {
        console.error('Database query error:', error);
        throw error;
    }
}

async function createUser(username, email) {
    try {
        const query = 'INSERT INTO users(username, email) VALUES($1, $2) RETURNING *';
        const values = [username, email];
        const result = await pool.query(query, values);
        console.log('User created:', result.rows[0]);
        return result.rows[0];
    } catch (error) {
        console.error('Error creating user:', error);
        throw error;
    }
}

// Example Usage:
(async () => {
    // Simulating malicious input
    const maliciousUserId = "1 OR 1=1 --"; // This would return all users in an unsafe query
    const safeUserId = 1;

    console.log('--- Attempting safe query for ID 1 ---');
    await getUserById(safeUserId);

    // This will treat "1 OR 1=1 --" as a string, not SQL code, due to parametrization
    console.log('
--- Attempting safe query with malicious-looking input ---');
    await getUserById(maliciousUserId);

    console.log('
--- Creating a new user securely ---');
    await createUser('johndoe', '[email protected]');

    await pool.end(); // Close the database connection pool
})();