Preventing SQL Injection with Parameterized Queries in Node.js (PostgreSQL)

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

// Database connection pool configuration
const pool = new Pool({
    user: 'dbuser',
    host: 'localhost',
    database: 'mydatabase',
    password: 'dbpassword',
    port: 5432,
});

// Example function to fetch user by ID safely
async function getUserById(userId) {
    const client = await pool.connect();
    try {
        // Use a parameterized query ($1 is a placeholder for the first parameter)
        const queryText = 'SELECT id, username, email FROM users WHERE id = $1';
        const values = [userId]; // Pass parameters as an array

        const result = await client.query(queryText, values);
        if (result.rows.length > 0) {
            console.log('User found:', result.rows[0]);
            return result.rows[0];
        } else {
            console.log('User not found.');
            return null;
        }
    } catch (err) {
        console.error('Error executing query', err.stack);
        throw err;
    } finally {
        client.release(); // Release the client back to the pool
    }
}

// Example function to insert data safely
async function createUser(username, email) {
    const client = await pool.connect();
    try {
        const queryText = 'INSERT INTO users(username, email) VALUES($1, $2) RETURNING id';
        const values = [username, email];
        const result = await client.query(queryText, values);
        console.log('User created with ID:', result.rows[0].id);
        return result.rows[0].id;
    } catch (err) {
        console.error('Error creating user', err.stack);
        throw err;
    } finally {
        client.release();
    }
}

// --- Example Usage ---
(async () => {
    console.log('Fetching user with ID 1:');
    await getUserById(1);

    console.log('
Attempting SQL injection (will fail safely):');
    // If we used string concatenation like `WHERE id = ${userId}`,
    // this input would cause an error or data leakage.
    // With parameterized queries, it's treated as a literal string.
    await getUserById("1 OR 1=1 --"); 

    console.log('
Creating a new user:');
    await createUser('john_doe', '[email protected]');

    // Close the pool when done (in a real app, this might be handled by application shutdown)
    await pool.end();
})();