SQL injection prevention in Wordpress - Step-by-Step Execution

Concept Flow - SQL injection prevention

User Input Received

↓

Sanitize Input

↓

Prepare SQL Statement

↓

Bind Parameters

↓

Execute Query

↓

Return Safe Results

↓

END

The flow shows how user input is cleaned, safely added to SQL commands, and executed to avoid injection.

Execution Sample

Wordpress

<?php
$user_id = $_GET['id'];
$stmt = $wpdb->prepare("SELECT * FROM wp_users WHERE ID = %d", $user_id);
$results = $wpdb->get_results($stmt);
?>

This code safely queries the database for a user by ID using prepared statements to prevent SQL injection.

Execution Table

Step	Action	Input/Variable	Result/Output	Notes
1	Receive user input	$_GET['id'] = '5 OR 1=1'	Raw input: '5 OR 1=1'	User tries to inject SQL code
2	Prepare SQL statement	SQL: "SELECT * FROM wp_users WHERE ID = %d", param: '5 OR 1=1'	Prepared SQL with placeholder	Input not directly inserted
3	Bind parameter	Bind '5 OR 1=1' as integer	Parameter converted to integer 5	Non-numeric part ignored, safe binding
4	Execute query	Final SQL: "SELECT * FROM wp_users WHERE ID = 5"	Query runs safely	Injection attempt neutralized
5	Return results	Query result for user ID 5	User data for ID 5	Safe data returned
6	End	No further action	Process complete	SQL injection prevented

💡 Execution stops after query safely runs with sanitized input, preventing injection.

Variable Tracker

Variable	Start	After Step 1	After Step 3	Final
$user_id	undefined	'5 OR 1=1'	5	5
$stmt	undefined	undefined	"SELECT * FROM wp_users WHERE ID = %d"	"SELECT * FROM wp_users WHERE ID = 5"
$results	undefined	undefined	undefined	User data for ID 5

Key Moments - 2 Insights

Why does binding the parameter as an integer prevent SQL injection?

Because the binding converts the input to a safe type (integer), ignoring malicious parts like 'OR 1=1', as shown in step 3 of the execution_table.

What happens if we insert user input directly into the SQL without prepare()?

The raw input would be part of the SQL string, allowing injection. The execution_table step 2 shows how prepare() avoids this by using placeholders.

Visual Quiz - 3 Questions

Look at the execution_table, what is the value of $user_id after binding in step 3?

A '5 OR 1=1'

B 5

C NULL

D 1=1

💡 Hint

Check the 'Bind parameter' row in execution_table where input is converted to integer.

At which step does the SQL injection attempt get neutralized?

A Step 3

B Step 1

C Step 2

D Step 5

💡 Hint

Look for where the parameter is safely bound and converted in execution_table.

If we skip using $wpdb->prepare(), what risk increases?

A Faster query execution

B More accurate results

C SQL injection vulnerability

D No risk at all

💡 Hint

Refer to key_moments explaining the importance of prepare() in preventing injection.

Concept Snapshot

SQL injection prevention in WordPress:
- Always use $wpdb->prepare() for queries with user input.
- Use placeholders (%d, %s) to safely bind parameters.
- Binding converts inputs to safe types, blocking injection.
- Never insert raw user input directly into SQL.
- This keeps your database safe and your site secure.

Full Transcript

This visual execution shows how WordPress prevents SQL injection by using prepared statements. First, user input is received, which might contain malicious code. Then, the input is not directly added to the SQL query. Instead, $wpdb->prepare() creates a safe SQL statement with placeholders. The user input is bound as a parameter, converting it to a safe type like integer. This stops any injection attempts. The query runs safely and returns correct results. Key moments highlight why binding is crucial and the risks of skipping prepare(). The quiz tests understanding of these steps and variable changes.

Practice

1. What is the main purpose of using $wpdb->prepare() in WordPress?

easy

A. To speed up database queries by caching results

B. To safely insert user input into SQL queries and prevent SQL injection

C. To format dates and times in SQL queries

D. To automatically backup the database before running queries

Solution

1. Step 1: Understand the role of $wpdb->prepare()

   $wpdb->prepare() is designed to safely insert user data into SQL queries by escaping dangerous characters.

2. Step 2: Identify the security benefit

   This prevents attackers from injecting harmful SQL commands that could damage or expose the database.

3. Final Answer:

   To safely insert user input into SQL queries and prevent SQL injection -> Option B

4. Quick Check:

   SQL injection prevention = B [OK]

Hint: Remember: prepare() protects queries from harmful input [OK]

Common Mistakes:

• Thinking prepare() speeds up queries
• Confusing prepare() with formatting dates
• Assuming prepare() backs up the database

2. Which of the following is the correct syntax to prepare a SQL query with one string placeholder in WordPress?

easy

A. $wpdb->prepare('SELECT * FROM wp_users WHERE user_login = %s', $username);

B. $wpdb->prepare('SELECT * FROM wp_users WHERE user_login = ?', $username);

C. $wpdb->prepare('SELECT * FROM wp_users WHERE user_login = :username', $username);

D. $wpdb->prepare('SELECT * FROM wp_users WHERE user_login = $username');

Solution

1. Step 1: Identify correct placeholder syntax

   WordPress uses %s for string placeholders in $wpdb->prepare(), not ? or named placeholders.

2. Step 2: Match the placeholder with the variable

   The variable $username is passed as the second argument to safely replace %s.

3. Final Answer:

   $wpdb->prepare('SELECT * FROM wp_users WHERE user_login = %s', $username); -> Option A

4. Quick Check:

   Use %s for strings in prepare() = A [OK]

Hint: Use %s for strings, %d for numbers in prepare() [OK]

Common Mistakes:

• Using ? instead of %s as placeholder
• Using named placeholders like :username
• Not passing variables as arguments to prepare()

3. What will be the output of the following code snippet?

$user_id = 5;
$query = $wpdb->prepare('SELECT * FROM wp_users WHERE ID = %d', $user_id);
echo $query;

medium

A. SELECT * FROM wp_users WHERE ID = 5

B. SELECT * FROM wp_users WHERE ID = %d

C. SELECT * FROM wp_users WHERE ID = '5'

D. Error: Missing argument for prepare()

Solution

1. Step 1: Understand %d placeholder usage

   %d is used for integers and will be replaced by the integer value without quotes.

2. Step 2: Check the variable substitution

   $user_id = 5 replaces %d, so the query becomes SELECT * FROM wp_users WHERE ID = 5.

3. Final Answer:

   SELECT * FROM wp_users WHERE ID = 5 -> Option A

4. Quick Check:

   Integer placeholder %d replaced by 5 = A [OK]

Hint: Integer placeholders %d insert numbers without quotes [OK]

Common Mistakes:

• Expecting %d to be replaced with quoted '5'
• Thinking prepare() returns an error without extra arguments
• Confusing %d with %s for strings

4. Identify the error in this WordPress SQL preparation code:

$email = $_POST['email'];
$query = $wpdb->prepare('SELECT * FROM wp_users WHERE user_email = %d', $email);

medium

A. Missing the global $wpdb declaration

B. Not sanitizing the $_POST input before prepare()

C. Using %d placeholder for an email string instead of %s

D. prepare() cannot be used with SELECT queries

Solution

1. Step 1: Check placeholder type vs variable type

   %d is for integers, but $email is a string, so %s should be used.

2. Step 2: Understand prepare() placeholder matching

   Using wrong placeholder causes SQL errors or unexpected behavior.

3. Final Answer:

   Using %d placeholder for an email string instead of %s -> Option C

4. Quick Check:

   Placeholder type must match variable type = C [OK]

Hint: Match %s for strings, %d for numbers in prepare() [OK]

Common Mistakes:

• Using %d for strings like emails
• Assuming prepare() sanitizes input fully
• Thinking prepare() can't be used with SELECT

5. You want to safely query posts by author ID and post status using $wpdb->prepare(). Which of the following is the best way to write this query?

hard

A. $wpdb->prepare('SELECT * FROM wp_posts WHERE post_author = ? AND post_status = ?', $author_id, $status);

B. $wpdb->prepare('SELECT * FROM wp_posts WHERE post_author = %s AND post_status = %d', $author_id, $status);

C. $wpdb->prepare('SELECT * FROM wp_posts WHERE post_author = $author_id AND post_status = '$status'');

D. $wpdb->prepare('SELECT * FROM wp_posts WHERE post_author = %d AND post_status = %s', $author_id, $status);

Solution

1. Step 1: Match placeholders to variable types

   $author_id is a number, so use %d; $status is a string, so use %s.

2. Step 2: Verify correct usage of prepare()

   $wpdb->prepare('SELECT * FROM wp_posts WHERE post_author = %d AND post_status = %s', $author_id, $status); correctly uses %d and %s placeholders with variables passed as arguments.

3. Step 3: Identify errors in other options

   Using ? placeholders is unsupported; using %s for post_author (integer) and %d for post_status (string) mismatches types; directly inserting variables into the query string is unsafe.

4. Final Answer:

   $wpdb->prepare('SELECT * FROM wp_posts WHERE post_author = %d AND post_status = %s', $author_id, $status); -> Option D

5. Quick Check:

   %d for author_id, %s for status = D [OK]

Hint: Use %d for numbers, %s for strings in prepare() [OK]

Common Mistakes:

• Swapping %d and %s placeholders
• Using ? instead of % placeholders
• Injecting variables directly into query string