Format function for safe formatting in PostgreSQL - Mini Project: Build & Apply

Using the FORMAT Function for Safe SQL Queries in PostgreSQL

📖 Scenario: You are managing a small library database. You want to create SQL queries that safely insert and display book information without risking errors or SQL injection.

🎯 Goal: Build a PostgreSQL query using the FORMAT function to safely format strings and variables in SQL statements.

📋 What You'll Learn

Create a variable with a book title and author name

Create a variable with a book's publication year

Use the FORMAT function to safely combine these variables into a SQL INSERT statement

Use the FORMAT function to safely create a SELECT statement that filters books by author

💡 Why This Matters

🌍 Real World

Database developers often need to build SQL queries dynamically while avoiding errors and security risks like SQL injection. Using FORMAT with placeholders helps keep queries safe and clean.

💼 Career

Knowing how to safely format SQL queries is essential for backend developers, database administrators, and data engineers to maintain secure and reliable database applications.

1

Create variables for book title and author

Create two variables called book_title and author_name and assign them the exact values 'The Great Gatsby' and 'F. Scott Fitzgerald' respectively.

PostgreSQL

# Create variables book_title and author_name with given values
# Your code here

Hint

Use the := operator to assign values to variables in PostgreSQL.

2

Create a variable for publication year

Create a variable called pub_year and assign it the exact value 1925.

PostgreSQL

book_title := 'The Great Gatsby';
author_name := 'F. Scott Fitzgerald';
# Create variable pub_year with value 1925
# Your code here

Hint

Assign the integer 1925 to the variable pub_year.

3

Use FORMAT to create a safe INSERT statement

Use the FORMAT function to create a variable called insert_query that safely formats an INSERT SQL statement for a table named books with columns title, author, and year. Use %L for literal values and include the variables book_title, author_name, and pub_year in that order.

PostgreSQL

book_title := 'The Great Gatsby';
author_name := 'F. Scott Fitzgerald';
pub_year := 1925;
-- Create insert_query using FORMAT with %L placeholders
-- Your code here

Hint

Use FORMAT with %L to safely insert literal values into the SQL string.

4

Use FORMAT to create a safe SELECT statement filtering by author

Use the FORMAT function to create a variable called select_query that safely formats a SELECT SQL statement to get all columns from books where the author matches the variable author_name. Use %L for the author literal.

PostgreSQL

book_title := 'The Great Gatsby';
author_name := 'F. Scott Fitzgerald';
pub_year := 1925;
insert_query := FORMAT('INSERT INTO books (title, author, year) VALUES (%L, %L, %L);', book_title, author_name, pub_year);
-- Create select_query using FORMAT to filter by author_name
-- Your code here

Hint

Use FORMAT with %L to safely include the author name in the WHERE clause.

Practice

1. What is the main purpose of the format() function in PostgreSQL?

easy

A. To safely build text strings with placeholders for values

B. To execute SQL queries directly

C. To create new tables in the database

D. To delete rows from a table

Solution

1. Step 1: Understand the function's role

   The format() function is designed to build text strings safely by inserting values into placeholders.

2. Step 2: Compare with other options

   Options A, B, and C describe other SQL operations, not string formatting.

3. Final Answer:

   To safely build text strings with placeholders for values -> Option A

4. Quick Check:

   format() = safe string building [OK]

Hint: Remember: format() builds safe strings with placeholders [OK]

Common Mistakes:

• Thinking format() executes queries
• Confusing format() with table operations
• Ignoring the safety aspect of format()

2. Which placeholder should you use with format() to safely insert a table name (identifier)?

easy

A. %L

B. %s

C. %I

D. %d

Solution

1. Step 1: Identify placeholder types

   In format(), %I is used for identifiers like table or column names.

2. Step 2: Eliminate other placeholders

   %s is for strings, %L for literals, and %d is not a valid placeholder in PostgreSQL's format.

3. Final Answer:

   %I -> Option C

4. Quick Check:

   Identifier placeholder = %I [OK]

Hint: Use %I for identifiers like table names [OK]

Common Mistakes:

• Using %s for identifiers
• Confusing %L with %I
• Using invalid placeholders like %d

3. What is the output of this query?

SELECT format('SELECT * FROM %I WHERE name = %L', 'users', 'Alice');

medium

A. SELECT * FROM users WHERE name = Alice

B. SELECT * FROM users WHERE name = 'Alice'

C. SELECT * FROM 'users' WHERE name = 'Alice'

D. SELECT * FROM users WHERE name = Alice;

Solution

1. Step 1: Understand placeholders used

   %I inserts the identifier without quotes, so 'users' becomes users. %L inserts the literal with quotes, so 'Alice' becomes 'Alice'.

2. Step 2: Construct the output string

   The output is: SELECT * FROM users WHERE name = 'Alice'

3. Final Answer:

   SELECT * FROM users WHERE name = 'Alice' -> Option B

4. Quick Check:

   Identifier no quotes, literal with quotes = SELECT * FROM users WHERE name = 'Alice' [OK]

Hint: Remember %I no quotes, %L adds quotes around literals [OK]

Common Mistakes:

• Adding quotes around identifiers
• Missing quotes around literals
• Confusing %s with %I or %L

4. Identify the error in this usage of format():

SELECT format('INSERT INTO %I VALUES (%s)', 'products', 100);

medium

A. Using %s for a numeric value causes a syntax error

B. Missing quotes around the table name

C. No error, the query is correct

D. Should use %L for the numeric value to be safe

Solution

1. Step 1: Check placeholder for numeric value

   %s inserts the value as a string without quotes, which can cause errors or injection risks.

2. Step 2: Use %L for safe literal insertion

   Using %L safely quotes and escapes the numeric literal, preventing errors.

3. Final Answer:

   Should use %L for the numeric value to be safe -> Option D

4. Quick Check:

   Numeric literals need %L for safety [OK]

Hint: Use %L for all literal values, even numbers [OK]

Common Mistakes:

• Using %s for literals without quotes
• Assuming %s works for all types
• Ignoring injection risks with %s

5. You want to build a dynamic query to update a user's email safely. Which format() call is correct?

1) format('UPDATE %I SET email = %s WHERE id = %L', 'users', '[e-mail]', 42)
2) format('UPDATE %I SET email = %L WHERE id = %L', 'users', '[e-mail]', 42)
3) format('UPDATE %s SET email = %L WHERE id = %s', 'users', '[e-mail]', 42)
4) format('UPDATE %I SET email = %I WHERE id = %L', 'users', '[e-mail]', 42)

hard

A. Option 2

B. Option 1

C. Option 3

D. Option 4

Solution

1. Step 1: Check placeholders for each part

   Table name is an identifier, so %I is correct. Email is a string literal, so %L is needed. ID is a literal number, so %L is safe.

2. Step 2: Evaluate each option

   Option 2 uses %I for table, %L for email and id, which is correct. Option 1 uses %s for email (unsafe). Option 3 uses %s for table and id (wrong). Option 4 uses %I for email (wrong).

3. Final Answer:

   Option 2 -> Option A

4. Quick Check:

   Identifiers %I, literals %L = Option 2 [OK]

Hint: Use %I for identifiers, %L for all literal values [OK]

Common Mistakes:

• Using %s for literals
• Using %I for string literals
• Mixing placeholders incorrectly