SQL injection prevention in Wordpress - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding SQL Injection Risks in WordPress

Which of the following best describes why using raw SQL queries with user input in WordPress is risky?

A Because user input can contain malicious SQL code that changes the query behavior.

B Because WordPress does not support SQL queries at all.

C Because SQL queries are always slow and cause performance issues.

D Because user input is automatically encrypted by WordPress.

💡 Hint

Think about what happens if a user types something unexpected into a search box.

Raw SQL queries with user input can be manipulated to run harmful commands if the input is not handled safely. This is called SQL injection.

❓ component_behavior

intermediate

Behavior of $wpdb->prepare() in WordPress

What does the $wpdb->prepare() function do when used with user input in WordPress?

A It removes all user input from the query.

B It executes the SQL query immediately without any changes.

C It encrypts the user input before adding it to the query.

D It safely escapes user input to prevent SQL injection by preparing the query with placeholders.

💡 Hint

Think about how placeholders work in SQL queries.

The $wpdb->prepare() function replaces placeholders with safely escaped user input, preventing attackers from injecting harmful SQL.

📝 Syntax

advanced

Correct Usage of $wpdb->prepare()

Which of the following code snippets correctly uses $wpdb->prepare() to safely query posts by title?

A $wpdb->prepare("SELECT * FROM wp_posts WHERE post_title = ?", $title);

B $wpdb->prepare("SELECT * FROM wp_posts WHERE post_title = '$title'");

C $wpdb->prepare("SELECT * FROM wp_posts WHERE post_title = %s", $title);

D $wpdb->prepare("SELECT * FROM wp_posts WHERE post_title = %d", $title);

💡 Hint

Remember the placeholder types: %s for strings, %d for numbers.

Option C correctly uses %s as a placeholder for a string and passes the variable separately. Option C is unsafe because it injects the variable directly. Option C uses an invalid placeholder '?'. Option C uses %d which is for integers, not strings.

🔧 Debug

advanced

Identifying SQL Injection Vulnerability

Given the following WordPress code, what is the main security issue?

$title = $_GET['title'];
$query = "SELECT * FROM wp_posts WHERE post_title = '$title'";
$results = $wpdb->get_results($query);

Wordpress

$title = $_GET['title'];
$query = "SELECT * FROM wp_posts WHERE post_title = '$title'";
$results = $wpdb->get_results($query);

A The code uses $wpdb->get_results() which is deprecated.

B The code directly inserts user input into the SQL query without escaping, allowing SQL injection.

C The variable $title is not defined before use.

D The query uses double quotes which causes a syntax error.

💡 Hint

Look at how the variable $title is used inside the query string.

Directly inserting user input into SQL queries without escaping or preparing allows attackers to inject malicious SQL code.

❓ state_output

expert

Output of a Prepared Query with Malicious Input

What will be the output of the following WordPress code if the user inputs "anything' OR '1'='1" as $title?

$title = "anything' OR '1'='1";
$query = $wpdb->prepare("SELECT * FROM wp_posts WHERE post_title = %s", $title);
echo $query;

Wordpress

$title = "anything' OR '1'='1";
$query = $wpdb->prepare("SELECT * FROM wp_posts WHERE post_title = %s", $title);
echo $query;

A SELECT * FROM wp_posts WHERE post_title = 'anything\' OR \'1\'=\'1'

B SELECT * FROM wp_posts WHERE post_title = 'anything' OR '1'='1'

C SyntaxError due to unescaped quotes

D SELECT * FROM wp_posts WHERE post_title = %s

💡 Hint

Think about how $wpdb->prepare() escapes quotes inside strings.

The $wpdb->prepare() function escapes single quotes inside the input by adding backslashes, so the malicious input is treated as a string literal, not SQL code.

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