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.