Format function for safe formatting in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

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

The format() function safely creates formatted strings by inserting values into a template, helping avoid SQL injection and syntax errors.

intermediate

How does format() help prevent SQL injection?

It automatically escapes values and inserts them safely into the string, so user input cannot break the query or run harmful commands.

beginner

What placeholder would you use in format() to insert a string safely?

Use %L to insert a string as a literal, which adds quotes and escapes special characters.

intermediate

Write a simple example of format() inserting a table name and a value safely.

Example: format('SELECT * FROM %I WHERE id = %L', 'users', 123) returns SELECT * FROM "users" WHERE id = 123.

intermediate

What is the difference between %I and %L in format()?

%I is for identifiers like table or column names (adds quotes if needed). %L is for literal values like strings or numbers (adds quotes and escapes).

Which format() placeholder is used to safely insert a table name?

A %s

B %I

C %L

D %d

%I is used for identifiers like table or column names.

What does format('SELECT * FROM %I WHERE name = %L', 'users', 'Alice') return?

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'

The table name is quoted as an identifier and the string value is quoted and escaped as a literal.

Why is using format() safer than simple string concatenation in SQL queries?

A It formats numbers with commas

B It runs queries faster

C It automatically escapes values to prevent SQL injection

D It converts all text to uppercase

It escapes values properly, preventing malicious input from breaking the query.

Which placeholder should you use to insert a number safely in format()?

A %L

B %I

C %s

D %d

%L safely inserts literal values including numbers by quoting and escaping if needed.

What will happen if you use %s in format()?

A It throws an error

B It escapes the value as a literal

C It quotes the value as an identifier

D It inserts the value as is, without escaping

%s inserts the value directly without escaping, which can be unsafe.

Explain how the format() function in PostgreSQL helps write safe SQL queries.

Think about how user input can be dangerous and how format() protects against that.

Describe the difference between the %I and %L placeholders in the format() function.

One is for names, the other for values.

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