Why Format function for safe formatting in PostgreSQL? - Purpose & Use Cases

The Big Idea

What if your database queries could never break or be hacked because of user input?

The Scenario

Imagine you need to build a query by putting user input directly into a text string to search your database.

You try to write the query by hand, mixing text and variables, hoping it works every time.

The Problem

This manual way is risky and slow because if you forget to add quotes or escape characters, your query breaks or worse, opens security holes.

You might accidentally allow someone to run harmful commands by typing special characters.

The Solution

The format function helps you build queries safely by automatically handling quotes and special characters.

It makes sure your inputs fit perfectly into the query without breaking it or causing security problems.

Before vs After

✗ Before

query := 'SELECT * FROM users WHERE name = ''' || user_input || '''';

✓ After

query := format('SELECT * FROM users WHERE name = %L', user_input);

What It Enables

It enables you to create dynamic, safe SQL queries easily without worrying about syntax errors or injections.

Real Life Example

When building a search feature where users type names, format ensures their input is safely included in the query, preventing errors and attacks.

Key Takeaways

Manual string building is error-prone and unsafe.

Format function safely inserts variables into SQL queries.

It prevents syntax mistakes and security risks like SQL injection.

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