SQL injection prevention in Wordpress

Introduction

SQL injection prevention helps keep your website safe by stopping bad people from changing your database with harmful commands.

When you accept user input like search terms or form data that goes into the database.

When you build custom database queries in WordPress plugins or themes.

When you want to protect login or registration forms from attacks.

When you handle data that affects your website content or user information.

When you want to keep your site secure and avoid data leaks or damage.

Syntax

Wordpress

$wpdb->prepare( $query, $value1, $value2, ... )

Use $wpdb->prepare() to safely insert user data into SQL queries.

It replaces placeholders like %s for strings and %d for numbers.

Examples

This safely adds the username into the query to find a user.

Wordpress

$wpdb->prepare( "SELECT * FROM wp_users WHERE user_login = %s", $username )

This safely adds a post ID number to get a specific post.

Wordpress

$wpdb->prepare( "SELECT * FROM wp_posts WHERE ID = %d", $post_id )

This safely checks comments by email and approved status.

Wordpress

$wpdb->prepare( "SELECT * FROM wp_comments WHERE comment_author_email = %s AND comment_approved = %d", $email, 1 )

Sample Program

This code safely gets a user from the database by username using $wpdb->prepare(). It stops SQL injection by escaping the username.

Wordpress

<?php
// Example: Safe query to get user info by username
global $wpdb;
$username = 'alice'; // Imagine this comes from user input

// Prepare the SQL query safely
$query = $wpdb->prepare( "SELECT * FROM wp_users WHERE user_login = %s", $username );

// Run the query
$user = $wpdb->get_row( $query );

// Show user info
if ( $user ) {
    echo "User ID: " . $user->ID . "\n";
    echo "User Login: " . $user->user_login . "\n";
} else {
    echo "User not found.\n";
}
?>

Important Notes

Never put user input directly into SQL queries without $wpdb->prepare().

Always use the right placeholder: %s for strings, %d for integers.

Using $wpdb->prepare() helps WordPress escape data safely to prevent attacks.

Summary

SQL injection prevention keeps your site safe from harmful database commands.

Use $wpdb->prepare() to safely add user data into SQL queries.

Always match placeholders to the data type to avoid errors and security risks.

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