Prepared Queries and Parameterized Statements

When your web application accepts user input and uses it in database queries, you face a critical security challenge: how do you safely incorporate that input without exposing your database to attacks? Prepared queries, also known as parameterized queries or prepared statements, solve this problem by separating SQL code from user data.

In this reading, you will learn what prepared queries are, why they are essential for database security, and how to implement them in your Node.js applications using PostgreSQL. Understanding this concept is crucial for the contact form, registration, and login assignments you will complete next.

The Problem: SQL Injection Attacks

Consider a simple login system where you need to verify a user's credentials. You might be tempted to build a query like this:

        // DANGER: Never do this!
        const email = req.body.email;
        const password = req.body.password;
        const query = `SELECT * FROM users WHERE email = '${email}' AND password = '${password}'`;
        const result = await db.query(query);
    

This approach seems straightforward, but it creates a severe security vulnerability called SQL injection. An attacker could enter a malicious email value like admin@example.com' OR '1'='1 which would transform your query into:

        SELECT * FROM users WHERE email = 'admin@example.com' OR '1'='1' AND password = 'anything'
    

Since '1'='1' is always true, this query returns all users regardless of the password. The attacker just bypassed your authentication system entirely. More sophisticated attacks can delete data, expose sensitive information, or even take control of your database server.

The fundamental problem is that you are mixing SQL code with user data. When you concatenate user input directly into SQL strings, the database cannot distinguish between your intended SQL structure and malicious code hidden in the user's input.

The Solution: Prepared Queries

Prepared queries solve the SQL injection problem by separating the SQL structure from the data values. Instead of concatenating user input into your SQL string, you use placeholders that tell the database where data should be inserted. The database then handles the data separately, treating it purely as data rather than executable code.

Here is how you would write the previous query safely using a prepared statement:

        // SAFE: Using parameterized query
        const email = req.body.email;
        const password = req.body.password;
        const query = 'SELECT * FROM users WHERE email = $1 AND password = $2';
        const result = await db.query(query, [email, password]);
    

The $1 and $2 are placeholders that PostgreSQL recognizes as parameter positions. When you pass the values in a separate array, PostgreSQL knows that these are data values, not SQL code. No matter what the user enters, it will be treated as a string value rather than executable SQL.

If an attacker tries the same admin@example.com' OR '1'='1 input, PostgreSQL will search for a user whose email literally equals that entire string, including the single quote and SQL code. The attack fails because the malicious SQL is never executed as code.

PostgreSQL Parameter Syntax

Different database systems use different syntax for parameterized queries. PostgreSQL uses numbered placeholders ($1, $2, $3, etc.) that correspond to positions in the values array. This explicit numbering makes the relationship between placeholders and values clear and allows you to reuse the same parameter multiple times if needed.

Here are examples of common query patterns using PostgreSQL's parameter syntax:

Simple SELECT Query

        const getUserByEmail = async (email) => {
            const query = 'SELECT * FROM users WHERE email = $1';
            const result = await db.query(query, [email]);
            return result.rows[0];
        };
    

INSERT Query

        const createUser = async (name, email, password) => {
            const query = 'INSERT INTO users (name, email, password) VALUES ($1, $2, $3) RETURNING *';
            const result = await db.query(query, [name, email, password]);
            return result.rows[0];
        };
    

UPDATE Query

        const updateUserEmail = async (userId, newEmail) => {
            const query = 'UPDATE users SET email = $1, updated_at = CURRENT_TIMESTAMP WHERE id = $2';
            const result = await db.query(query, [newEmail, userId]);
            return result.rowCount > 0;
        };
    

DELETE Query

        const deleteUser = async (userId) => {
            const query = 'DELETE FROM users WHERE id = $1';
            const result = await db.query(query, [userId]);
            return result.rowCount > 0;
        };
    

Prepared Queries in MVC Architecture

In the Model-View-Controller pattern you will use throughout this course, database queries belong in the model layer. Your models contain functions that encapsulate database operations, and these functions should always use prepared queries when incorporating any external data.

Here is how prepared queries fit into the MVC pattern you will use in your upcoming assignments:

        // src/models/user-model.js
        import db from './db.js';

        /**
         * Check if an email already exists in the database
         */
        const emailExists = async (email) => {
            const query = 'SELECT * FROM users WHERE email = $1';
            const result = await db.query(query, [email]);
            return result.rows.length > 0;
        };

        /**
         * Save a new user to the database
         */
        const saveUser = async (name, email, hashedPassword) => {
            const query = 'INSERT INTO users (name, email, password) VALUES ($1, $2, $3) RETURNING *';
            const result = await db.query(query, [name, email, hashedPassword]);
            return result.rows[0];
        };

        /**
         * Get a user by their email address
         */
        const getUserByEmail = async (email) => {
            const query = 'SELECT * FROM users WHERE email = $1';
            const result = await db.query(query, [email]);
            return result.rows[0] || null;
        };

        export { emailExists, saveUser, getUserByEmail };
    

Your controllers will call these model functions, passing in data from request objects. The model functions handle all database interactions using prepared queries, keeping your application secure and maintaining proper separation of concerns.

        // src/controllers/registration-controller.js
        import { emailExists, saveUser } from '../models/user-model.js';

        const processRegistration = async (req, res) => {
            const { name, email, password } = req.body;
            
            // Check if email already exists
            const exists = await emailExists(email);
            if (exists) {
                return res.redirect('/register');
            }
            
            // Hash password and save user
            const hashedPassword = await bcrypt.hash(password, 10);
            const newUser = await saveUser(name, email, hashedPassword);
            
            // Redirect to success page
            res.redirect('/users');
        };
    

Working with Complex Data

Real applications often need to work with multiple parameters or complex data structures. Prepared queries handle these scenarios elegantly by accepting any number of parameters in the values array.

Multiple Parameters

When you have many fields to insert or update, simply add more placeholders and corresponding values:

        const createDetailedUser = async (name, email, password, phone, address, city, state, zip) => {
            const query = `
                INSERT INTO users (name, email, password, phone, address, city, state, zip)
                VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
                RETURNING *
            `;
            const result = await db.query(query, [name, email, password, phone, address, city, state, zip]);
            return result.rows[0];
        };
    

Destructuring Objects

Often your data arrives as an object from a form submission or API request. You can destructure the object to extract values for your prepared query:

        const createUserFromObject = async (userData) => {
            const { name, email, password, phone, address, city, state, zip } = userData;
            const query = `
                INSERT INTO users (name, email, password, phone, address, city, state, zip)
                VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
                RETURNING *
            `;
            const result = await db.query(query, [name, email, password, phone, address, city, state, zip]);
            return result.rows[0];
        };
    

Queries with Multiple WHERE Conditions

Search functionality often requires multiple optional filters. You can build prepared queries that handle complex WHERE clauses:

        const searchUsers = async (searchEmail, searchCity, minDate) => {
            const query = `
                SELECT * FROM users 
                WHERE email LIKE $1 
                AND city = $2 
                AND created_at >= $3
                ORDER BY created_at DESC
            `;
            const result = await db.query(query, [`%${searchEmail}%`, searchCity, minDate]);
            return result.rows;
        };
    

What Cannot Be Parameterized

While prepared queries protect against SQL injection for data values, you cannot use parameters for SQL keywords, table names, or column names. These parts of the SQL structure must be hardcoded in your query string:

        // This WILL work - parameterizing data values
        const query = 'SELECT * FROM users WHERE email = $1';
        await db.query(query, [email]);

        // This will NOT work - cannot parameterize table names
        const query = 'SELECT * FROM $1 WHERE email = $2';
        await db.query(query, [tableName, email]); // ERROR

        // This will NOT work - cannot parameterize column names
        const query = 'SELECT * FROM users WHERE $1 = $2';
        await db.query(query, [columnName, value]); // ERROR
    

If you need dynamic table or column names, you must validate them against a whitelist of allowed values and build your query string accordingly. Never accept table or column names directly from user input without strict validation, as this creates SQL injection vulnerabilities that prepared queries cannot prevent.

Benefits Beyond Security

While SQL injection prevention is the primary reason to use prepared queries, they provide several additional benefits that improve your application:

• Performance: The database can cache and reuse execution plans for prepared queries, reducing parsing overhead for frequently executed queries.
• Code Clarity: Separating SQL structure from data values makes your queries easier to read and understand. The SQL logic is immediately visible without being obscured by string concatenation.
• Type Safety: The database handles type conversion automatically, ensuring that integers are treated as integers, dates as dates, and strings as strings without manual conversion.
• Special Character Handling: You never need to worry about escaping quotes, backslashes, or other special characters. The database handles all escaping automatically.
• Maintainability: When you need to modify a query, you change the SQL structure in one place without worrying about breaking string concatenation logic scattered throughout your code.

Best Practices

Follow these guidelines when working with prepared queries in your applications:

• Always use prepared queries for user input: Any data that comes from users, whether through forms, URL parameters, or API requests, must be passed through prepared query parameters.
• Keep queries in model functions: Encapsulate all database operations in model layer functions that use prepared queries. Controllers should never construct SQL queries directly.
• Verify parameter order: Before testing a query, trace through your placeholders and values array to confirm they align correctly. This prevents subtle bugs where data ends up in the wrong fields.
• Use descriptive variable names: Clear variable names help you match parameters to placeholders. Instead of val1 and val2, use userEmail and hashedPassword.
• Handle null values appropriately: If a parameter might be null or undefined, consider how your query should handle that case. PostgreSQL supports IS NULL checks, but you need to structure your query accordingly.
• Test with malicious input: During development, deliberately try SQL injection attacks against your queries to verify they are properly protected.

Check Your Understanding

        /**
         * Challenge 1: Basic Parameterized Query
         * 
         * Write a model function that retrieves a contact form submission by its ID.
         * Use a prepared query with proper PostgreSQL syntax.
         * 
         * Requirements:
         * - Function Name: getContactById
         * - Database: Use `db.query` to execute the query; a reference to the pg connection
         * - Parameter: id (number)
         * - Query: SELECT all columns from contact_form table WHERE id matches
         * - Return: The contact object or null if not found
         */

        const getContactById = async (id) => {
            // TODO: Write your prepared query here
            // TODO: Execute the query with proper parameters
            // TODO: Return the first result or null
        };
    

        /**
         * Challenge 2: Multiple Parameters
         * 
         * Write a model function that saves a complete contact form submission.
         * This function has more fields than the simple examples in the reading.
         * 
         * Requirements:
         * - Function Name: saveContactForm
         * - Database: Use `db.query` to execute the query; a reference to the pg connection
         * - Parameters: subject, message, name, email, phone, category (in that order)
         * - Query: INSERT into contact_form table with all 6 fields
         * - Return: The newly created contact object using RETURNING *
         */

        const saveContactForm = async (subject, message, name, email, phone, category) => {
            // TODO: Write INSERT query with 6 placeholders
            // TODO: Pass all 6 values in correct order
            // TODO: Return the inserted row
        };
    

        /**
         * Challenge 3: Object Destructuring with Complex Query
         * 
         * Write a model function that searches users based on multiple criteria.
         * This function accepts an object and uses several properties in the query.
         * 
         * Requirements:
         * - Function Name: searchUsers
         * - Database: Use `db.query` to execute the query; a reference to the pg connection
         * - Parameter: searchCriteria (object with properties: email, city, state, minDate, maxDate)
         * - Query: SELECT all columns from users table WHERE:
         *   - email contains the search term (use LIKE with wildcards)
         *   - city matches exactly
         *   - state matches exactly  
         *   - created_at is between minDate and maxDate (use BETWEEN)
         * - Order results by created_at DESC
         * - Return: Array of matching user objects
         * 
         * Hints:
         * - Destructure the searchCriteria object first
         * - Use LIKE with % wildcards for partial email matching
         * - BETWEEN requires two parameters: BETWEEN $4 AND $5
         * - Make sure parameter order matches your placeholder numbers
         */

        const searchUsers = async (searchCriteria) => {
            // TODO: Destructure email, city, state, minDate, maxDate from searchCriteria
            // TODO: Write query with 5 parameters using LIKE and BETWEEN
            // TODO: Execute with proper parameter array
            // TODO: Return all matching rows
        };
    

Looking Ahead

In your upcoming assignments, you will apply these prepared query concepts extensively. The contact form assignment will have you writing queries to save and retrieve form submissions. The registration assignment will use prepared queries to check for existing emails and save new user accounts with hashed passwords. The login assignment will query the database to verify user credentials and manage sessions.

Every database interaction in these assignments must use prepared queries. This is not just an academic exercise but a fundamental security requirement for any production web application. The patterns you learn here will serve you throughout your career as a web developer.