Skip to content

README.md

Latest commit

 

History

History
448 lines (340 loc) · 10.2 KB

README.md

File metadata and controls

448 lines (340 loc) · 10.2 KB

SQL Injection Prevention

The Problem

SQL injection is one of the most dangerous security vulnerabilities. It allows attackers to:

  • Read sensitive data from your database
  • Modify or delete data
  • Execute administrative operations
  • In some cases, execute commands on the operating system

One vulnerable query can compromise your entire site.

Bad Practice (bad.php)

Direct String Concatenation - CRITICAL VULNERABILITY!

function get_posts_by_author_name($author_name) {
    global $wpdb;
    
    // CRITICAL VULNERABILITY!
    $query = "SELECT * FROM {$wpdb->posts} WHERE post_author = '$author_name'";
    $results = $wpdb->get_results($query);
    
    return $results;
}

Attack Example:

// Normal use
$posts = get_posts_by_author_name('john');
// Query: SELECT * FROM wp_posts WHERE post_author = 'john'

// Malicious use
$posts = get_posts_by_author_name("' OR '1'='1");
// Query: SELECT * FROM wp_posts WHERE post_author = '' OR '1'='1'
// Returns ALL posts from ALL authors!

More Dangerous Examples

// DELETE all posts
delete_user_posts("1 OR 1=1");
// Query: DELETE FROM wp_posts WHERE post_author = 1 OR 1=1
// Deletes EVERYTHING!

// DROP tables
get_sorted_posts("post_date; DROP TABLE wp_posts; --");
// Executes: SELECT * FROM wp_posts ORDER BY post_date; DROP TABLE wp_posts; --
// Your posts table is GONE!

Good Practice (good.php)

Solution 1: Use $wpdb->prepare() (REQUIRED!)

function get_posts_by_author_id( int $author_id ): array {
    global $wpdb;
    
    // SAFE: Using prepare with placeholders
    $query = $wpdb->prepare(
        "SELECT * FROM {$wpdb->posts} WHERE post_author = %d",
        $author_id
    );
    
    return $wpdb->get_results( $query );
}

Placeholders:

  • %d - Integer
  • %s - String
  • %f - Float
  • %i - Identifier (table/column name)

How it works:

  1. prepare() escapes all special characters
  2. Treats user input as DATA, never as SQL code
  3. Prevents all SQL injection attacks

Solution 2: Use WordPress Helper Methods

// BEST: Use wpdb::insert() instead of raw INSERT
$wpdb->insert(
    $table_name,
    [ 'name' => $name, 'value' => $value ],
    [ '%s', '%s' ] // Data types
);

// BEST: Use wpdb::update() instead of raw UPDATE
$wpdb->update(
    $table_name,
    [ 'value' => $new_value ],  // Data
    [ 'id' => $id ],             // WHERE
    [ '%s' ],                    // Data format
    [ '%d' ]                     // WHERE format
);

// BEST: Use wpdb::delete() instead of raw DELETE $wpdb->delete( $table_name, ['id' => $id], ['%d'] );


### Solution 3: Use WordPress APIs When Available

```php
// SAFEST: Use WP_Query for posts
$query = new WP_Query(
  [
    'author' => $author_id,
    'posts_per_page' => 10,
  ]
);

// SAFEST: Use get_posts()
$posts = get_posts(
  [
    'author' => $author_id,
    'numberposts' => 10,
  ]
);

// SAFEST: Use get_post_meta()
$value = get_post_meta( $post_id, $meta_key, true );

// SAFEST: Use update_post_meta()
update_post_meta( $post_id, $meta_key, $meta_value );

WordPress APIs handle SQL injection prevention automatically!

Special Cases

LIKE Queries

// BAD
$query = "SELECT * FROM {$wpdb->posts} WHERE post_title LIKE '%{$search}%'";

// GOOD
$search = '%' . $wpdb->esc_like($search) . '%';
$query = $wpdb->prepare(
    "SELECT * FROM {$wpdb->posts} WHERE post_title LIKE %s",
    $search
);

Why esc_like()? It escapes % and _ wildcards that have special meaning in LIKE.

ORDER BY (Cannot Use prepare())

// BAD
$query = "SELECT * FROM {$wpdb->posts} ORDER BY {$order_by}";

// GOOD: Whitelist allowed values
$allowed = ['post_date', 'post_title', 'ID'];
if (!in_array($order_by, $allowed, true)) {
    $order_by = 'post_date';
}
// Now safe to use
$query = "SELECT * FROM {$wpdb->posts} ORDER BY {$order_by}";

Important: You cannot use prepare() for column/table names. Use a whitelist instead.

IN Clause with Multiple Values

// BAD
$ids_string = implode(',', $post_ids);
$query = "SELECT * FROM {$wpdb->posts} WHERE ID IN ({$ids_string})";

// GOOD: Create placeholders
$post_ids = array_map('intval', $post_ids); // Validate
$placeholders = implode(',', array_fill(0, count($post_ids), '%d'));

$query = $wpdb->prepare(
    "SELECT * FROM {$wpdb->posts} WHERE ID IN ({$placeholders})",
    ...$post_ids
);

Dynamic WHERE Clauses

// GOOD: Build safely
$where = ['1=1'];
$values = [];

if ($author_id) {
    $where[] = 'post_author = %d';
    $values[] = $author_id;
}

if ($category_id) {
    $where[] = 'category_id = %d';
    $values[] = $category_id;
}

$where_clause = implode(' AND ', $where);
$sql = "SELECT * FROM {$wpdb->posts} WHERE {$where_clause}";

if (!empty($values)) {
    $query = $wpdb->prepare($sql, ...$values);
} else {
    $query = $sql;
}

Common Mistakes

Mistake 1: Using sprintf() Instead of prepare()

// STILL VULNERABLE!
$query = sprintf(
    "SELECT * FROM {$wpdb->posts} WHERE post_author = '%s'",
    $author_name
);

sprintf() does NOT escape SQL! Always use $wpdb->prepare().

Mistake 2: Thinking esc_sql() is Enough

// MISLEADING - not sufficient!
$escaped = esc_sql($search);
$query = "SELECT * FROM {$wpdb->posts} WHERE post_title LIKE '%{$escaped}%'";

esc_sql() is a low-level function. Use prepare() instead!

Mistake 3: Preparing Only Part of Query

// STILL VULNERABLE!
$safe_part = $wpdb->prepare("post_author = %d", $author_id);
$query = "SELECT * FROM {$wpdb->posts} WHERE {$safe_part} AND post_status = '{$status}'";
//                                                                              ^^^^^^^^
//                                                                              UNSAFE!

The entire query must be prepared!

Mistake 4: Trusting "Safe" Input

// WRONG ASSUMPTION!
if (is_numeric($user_input)) {
    // Still need prepare()!
    $query = "SELECT * FROM {$wpdb->posts} WHERE ID = {$user_input}";
}

// CORRECT
$query = $wpdb->prepare(
    "SELECT * FROM {$wpdb->posts} WHERE ID = %d",
    $user_input
);

Never trust user input, even if it looks safe!

Security Levels (Best to Worst)

✅ Level 1: WordPress APIs (SAFEST)

get_posts(), WP_Query, get_post_meta(), update_post_meta()

✅ Level 2: wpdb Helper Methods

$wpdb->insert(), $wpdb->update(), $wpdb->delete()

✅ Level 3: wpdb::prepare()

$wpdb->prepare("SELECT * FROM table WHERE id = %d", $id)

⚠️ Level 4: Whitelisting (only for identifiers)

in_array($column, $allowed_columns, true)

❌ Level 5: Direct Queries (NEVER!)

"SELECT * FROM {$wpdb->posts} WHERE id = {$id}"

Testing for SQL Injection

Test Inputs

Try these in your forms to test for vulnerabilities:

' OR '1'='1
'; DROP TABLE wp_posts; --
1' UNION SELECT username, password FROM wp_users--
admin'--
' OR 1=1--

If any of these cause unexpected behavior, you have a vulnerability!

Automated Testing

// Test that prepare() is being used
function test_safe_query() {
    $malicious = "' OR '1'='1";
    $result = get_posts_by_author_name($malicious);
    
    // Should return 0 results, not all posts
    assert(count($result) === 0);
}

Real-World Impact

What Attackers Can Do

With SQL injection, an attacker can:

  1. Read all data

    ' UNION SELECT user_login, user_pass FROM wp_users--

  2. Delete everything

    '; DROP TABLE wp_posts; DROP TABLE wp_users;--

  3. Create admin accounts

    '; INSERT INTO wp_users (user_login, user_pass) VALUES ('hacker', MD5('password'));--

  4. Steal customer data

    ' UNION SELECT credit_card, cvv FROM wp_orders--

Real Breach Examples

  • 2019: SQL injection in WordPress plugin exposed 200,000 websites
  • 2020: WooCommerce vulnerability allowed reading order data
  • 2021: Contact Form 7 SQL injection affected millions of sites

Don't be the next headline!

Migration Strategy

Step 1: Identify Vulnerabilities

Search your codebase for:

# Find potentially unsafe queries
grep -r "wpdb->query" . --include="*.php"
grep -r "wpdb->get_results" . --include="*.php"
grep -r "\$wpdb->posts} WHERE" . --include="*.php"

Step 2: Fix High-Risk Queries First

Priority order:

  1. DELETE/UPDATE/INSERT queries (can destroy data)
  2. Login/authentication queries (can compromise accounts)
  3. SELECT queries (can leak data)

Step 3: Add prepare() Everywhere

// Before
$query = "SELECT * FROM {$wpdb->posts} WHERE ID = {$id}";

// After
$query = $wpdb->prepare(
    "SELECT * FROM {$wpdb->posts} WHERE ID = %d",
    $id
);

Step 4: Add Automated Tests

// Test all database functions with malicious input
public function test_sql_injection_protection() {
    $malicious_inputs = [
        "' OR '1'='1",
        "'; DROP TABLE wp_posts;--",
        "admin'--",
    ];
    
    foreach ($malicious_inputs as $input) {
        $result = $this->repository->find_by_name($input);
        $this->assertEmpty($result); // Should find nothing
    }
}

Key Takeaways

✅ ALWAYS use $wpdb->prepare() for user input
✅ Use WordPress APIs when available (safest)
✅ Use wpdb::insert/update/delete for DML operations
✅ Whitelist column/table names (can't use prepare())
✅ Use esc_like() for LIKE patterns
✅ Validate input types before database queries
✅ Test with malicious input to verify security

❌ NEVER concatenate user input into SQL
❌ NEVER trust user input, even if validated
❌ DON'T use sprintf() instead of prepare()
❌ DON'T use esc_sql() as primary defense
❌ DON'T assume is_numeric() makes it safe

The Bottom Line

SQL injection can destroy your entire site in seconds.

Every database query with user input MUST use prepare():

// ONE UNSAFE QUERY can compromise everything
$query = "SELECT * FROM {$wpdb->posts} WHERE id = {$_GET['id']}"; // ❌ DISASTER

// ONE LINE FIX makes it safe
$query = $wpdb->prepare(
    "SELECT * FROM {$wpdb->posts} WHERE id = %d",
    $_GET['id']
); // ✅ SAFE

There is NO excuse for SQL injection vulnerabilities.

Always use prepare(). Your users' data depends on it.