# Database Security Migration - Specific Examples from Codebase

This document shows the **exact patterns found in the SensFRX codebase** and how to migrate them to use the secure `SensFRX_DB_Helper` class.

---

## Pattern 1: Custom Fields Query (Found in 6+ files)

### Files Affected:
- `admin/class-sensfrx-payment.php` (lines 43, 1660)
- `admin/class-sensfrx-payment-attempt.php` (lines 39, 1604)
- `admin/class-sensfrx-webhook.php` (line 541)
- `admin/class-sensfrx-add-dashboard-notification.php` (line 519)

### BEFORE (UNSAFE):
```php
global $wpdb;
$table_name_custom = $wpdb->prefix . 'sensfrx_custome_filed';
$sensfrx_fetch_results_custom = $wpdb->get_results("SELECT id, sensfrx_custom_field FROM $table_name_custom", ARRAY_A);
```

### AFTER (SAFE):
```php
$sensfrx_fetch_results_custom = SensFRX_DB_Helper::safe_select(
    'sensfrx_custome_filed',
    'id, sensfrx_custom_field',
    '',
    array(),
    '',
    ARRAY_A
);
```

### Benefits:
- ✅ No need for `global $wpdb`
- ✅ Table name validated against whitelist
- ✅ Cleaner, more readable code
- ✅ Consistent error handling

---

## Pattern 2: Shadow Status Query (Found in 10+ files)

### Files Affected:
- `admin/class-sensfrx-payment.php` (lines 699, 2240)
- `admin/class-sensfrx-payment-attempt.php` (lines 683, 2113)
- `admin/class-sensfrx-registration-function.php` (line 174)
- `admin/class-sensfrx-registration-wp.php` (line 163)
- `admin/class-sensfrx-wp-login-track.php` (line 202)
- `admin/class-sensfrx-webhook.php` (lines 167, 964)
- `admin/class-sesnfrx-profile-update.php` (line 306)
- `admin/class-sensfrx-api-check-comment.php` (line 307)
- `admin/class-sensfrx-check-order-update.php` (line 170)
- `admin/class-sensfrx-api-callback-handling.php` (line 1701)

### BEFORE (UNSAFE):
```php
global $wpdb;
$table_name_shadow_table = $wpdb->prefix . 'sensfrx_shadow_activity';
$res_shadow = $wpdb->get_results("SELECT sensfrx_shadow_status FROM $table_name_shadow_table", ARRAY_A);
```

### AFTER (SAFE):
```php
$res_shadow = SensFRX_DB_Helper::safe_select(
    'sensfrx_shadow_activity',
    'sensfrx_shadow_status',
    '',
    array(),
    '',
    ARRAY_A
);
```

---

## Pattern 3: Email Template Query with WHERE (Found in payment files)

### Files Affected:
- `admin/class-sensfrx-payment.php` (lines 797, 983, 1171)
- `admin/class-sensfrx-payment-attempt.php` (lines 763, 939, 1145)

### BEFORE (UNSAFE):
```php
global $wpdb;
$table_name_email_templates = $wpdb->prefix . 'sensfrx_email_templates';
$template_medium_after_trans = $wpdb->get_row(
    $wpdb->prepare(
        "SELECT * FROM $table_name_email_templates WHERE name1 = %s",
        'medium_after_transaction_template'
    ),
    ARRAY_A
);
```

### AFTER (SAFE):
```php
$template_medium_after_trans = SensFRX_DB_Helper::safe_get_row(
    'sensfrx_email_templates',
    'name1 = %s',
    array('medium_after_transaction_template'),
    ARRAY_A
);
```

### Key Points:
- ✅ WHERE clause is already using `$wpdb->prepare()` - good!
- ✅ But table name was not prepared - now fixed
- ✅ Cleaner syntax with the helper

---

## Pattern 4: Check Existing Record (Found in payment-attempt files)

### Files Affected:
- `admin/class-sensfrx-payment-attempt.php` (lines 1049, 2481)

### BEFORE (UNSAFE):
```php
global $wpdb;
$table_name_shadow_table = $wpdb->prefix . 'sensfrx_shadow_activity';
$existing_record = $wpdb->get_var(
    $wpdb->prepare(
        "SELECT COUNT(*) FROM $table_name_shadow_table WHERE sensfrx_shadow_status = %s",
        'active'
    )
);
```

### AFTER (SAFE):
```php
$existing_record = SensFRX_DB_Helper::safe_get_var(
    'sensfrx_shadow_activity',
    'COUNT(*)',
    'sensfrx_shadow_status = %s',
    array('active')
);
```

---

## Pattern 5: Get All Records with ORDER BY

### Files Affected:
- `admin/class-sensfrx-api-callback-handling.php` (line 4907)

### BEFORE (UNSAFE):
```php
global $wpdb;
$table_name = $wpdb->prefix . 'sensfrx_real_activity';
$results = $wpdb->get_results("SELECT * FROM $table_name ORDER BY id DESC", ARRAY_A);
```

### AFTER (SAFE):
```php
$results = SensFRX_DB_Helper::safe_select(
    'sensfrx_real_activity',
    '*',
    '',
    array(),
    'id DESC',
    ARRAY_A
);
```

---

## Pattern 6: Webhook Logout Query with WHERE (Already Prepared)

### Files Affected:
- `admin/class-sensfrx-webhook.php` (lines 125, 133)
- `admin/sql-query/sensfrx_where_query.php` (lines 17, 21)

### BEFORE (ALREADY SAFE - but can be improved):
```php
global $wpdb;
$results = $wpdb->get_results(
    $wpdb->prepare(
        'SELECT * FROM wp_sensfrx_webhook_logout WHERE client_id = %d',
        $user_id
    )
);
```

### AFTER (IMPROVED - More Flexible):
```php
$results = SensFRX_DB_Helper::safe_select(
    'sensfrx_webhook_logout',
    '*',
    'client_id = %d',
    array($user_id),
    '',
    ARRAY_A
);
```

### Note:
This query was already using `$wpdb->prepare()` correctly for the WHERE clause, but it was hardcoding the table name as `wp_sensfrx_webhook_logout` instead of using `$wpdb->prefix`. The helper fixes both issues.

---

## Pattern 7: Table Existence Check (Found in helper classes)

### Files Affected:
- `admin/sql-query/sensfrx_table_exist.php` (lines 17, 21)

### BEFORE (ALREADY SAFE):
```php
global $wpdb;
$table_name_exist = $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $tb));
```

### AFTER (CLEANER):
```php
// Assuming $tb contains just the suffix
$table_name_exist = SensFRX_DB_Helper::table_exists($tb);
```

### Returns:
- Boolean `true` if table exists
- Boolean `false` if table doesn't exist

---

## Pattern 8: INSERT Operations (Already Safe, But Standardize)

### Files Affected:
- Multiple files (60+ instances)

### BEFORE (ALREADY SAFE):
```php
global $wpdb;
$sensfrx_ato_table_name = $wpdb->prefix . 'sensfrx_real_activity';
$data = array(
    'sensfrx_log_type' => $log_type,
    'sensfrx_log1' => $log_data,
);
$insert_result = $wpdb->insert($sensfrx_ato_table_name, $data);
```

### AFTER (STANDARDIZED):
```php
$data = array(
    'sensfrx_log_type' => $log_type,
    'sensfrx_log1' => $log_data,
);
$insert_result = SensFRX_DB_Helper::safe_insert('sensfrx_real_activity', $data);
```

### Note:
`$wpdb->insert()` already uses prepared statements internally, so these are technically safe. However, using the helper provides:
- Table name validation
- Consistent error handling
- Easier testing and maintenance

---

## Pattern 9: DELETE Operations

### Files Affected:
- `admin/class-sensfrx-api-callback-handling.php` (lines 623, 647)

### BEFORE (ALREADY SAFE):
```php
global $wpdb;
$tableName = $wpdb->prefix . 'sensfrx_webhook_logout';
$result = $wpdb->delete(
    $tableName,
    array('client_id' => $userId),
    array('%d')
);
```

### AFTER (STANDARDIZED):
```php
$result = SensFRX_DB_Helper::safe_delete(
    'sensfrx_webhook_logout',
    array('client_id' => $userId),
    array('%d')
);
```

---

## Pattern 10: INSERT with Custom Fields Loop

### Files Affected:
- `admin/class-sensfrx-add-dashboard-notification.php` (lines 561, 571)

### BEFORE:
```php
global $wpdb;
$table_name_custom = $wpdb->prefix . 'sensfrx_custome_filed';

foreach ($sensfrx_slugs as $slugs) {
    // ... checking logic ...
    if (true === $check_if_exist) {
        $results = wp_cache_get('sensfrx_custom_data_using', 'sensfrx_group');
        if ($results !== false) {
            $data = array('sensfrx_custom_field' => $slugs);
            $results = $wpdb->insert($table_name_custom, $data);
        } else {
            $data = array('sensfrx_custom_field' => $slugs);
            $results = $wpdb->insert($table_name_custom, $data);
        }
    }
}
```

### AFTER:
```php
foreach ($sensfrx_slugs as $slugs) {
    // ... checking logic ...
    if (true === $check_if_exist) {
        $data = array('sensfrx_custom_field' => $slugs);
        $results = SensFRX_DB_Helper::safe_insert('sensfrx_custome_filed', $data);
        
        // Update cache if needed
        $cache_results = wp_cache_get('sensfrx_custom_data_using', 'sensfrx_group');
        if ($cache_results !== false) {
            wp_cache_replace('sensfrx_custom_data_using', $results, 'sensfrx_group', 86400);
        } else {
            wp_cache_set('sensfrx_custom_data_using', $results, 'sensfrx_group', 86400);
        }
    }
}
```

### Benefits:
- ✅ Removed duplicate code
- ✅ Cleaner cache handling
- ✅ Table name validation

---

## Migration Priority Order

Based on vulnerability analysis, fix files in this order:

### HIGH PRIORITY (Unsafe SELECT queries):
1. ✅ `admin/class-sensfrx-payment.php` - 4 unsafe queries
2. ✅ `admin/class-sensfrx-payment-attempt.php` - 4 unsafe queries
3. ✅ `admin/class-sensfrx-webhook.php` - 4 unsafe queries
4. ✅ `admin/class-sensfrx-add-dashboard-notification.php` - 2 unsafe queries
5. ✅ `admin/class-sensfrx-api-callback-handling.php` - 2 unsafe queries

### MEDIUM PRIORITY (Unsafe SELECT, less frequently executed):
6. ✅ `admin/class-sensfrx-registration-function.php` - 1 unsafe query
7. ✅ `admin/class-sensfrx-registration-wp.php` - 1 unsafe query
8. ✅ `admin/class-sensfrx-wp-login-track.php` - 1 unsafe query
9. ✅ `admin/class-sesnfrx-profile-update.php` - 1 unsafe query
10. ✅ `admin/class-sensfrx-api-check-comment.php` - 1 unsafe query
11. ✅ `admin/class-sensfrx-check-order-update.php` - 1 unsafe query

### LOW PRIORITY (Already safe, standardize for consistency):
12. ⚪ Helper classes that already use `$wpdb->prepare()`
13. ⚪ All INSERT operations (already safe)
14. ⚪ All UPDATE operations (already safe)
15. ⚪ All DELETE operations (already safe)

---

## Testing Checklist

After migrating each file, test:

- [ ] ✅ Page/functionality loads without errors
- [ ] ✅ Data is retrieved correctly
- [ ] ✅ No SQL errors in debug.log
- [ ] ✅ Functionality works the same as before
- [ ] ✅ Check browser console for any JS errors
- [ ] ✅ Check Network tab for failed API calls

---

## Common Mistakes to Avoid

❌ **Don't forget to remove `global $wpdb;` if no longer needed**

❌ **Don't include 'WHERE' in the WHERE clause parameter**
```php
// WRONG:
'WHERE client_id = %d'

// RIGHT:
'client_id = %d'
```

❌ **Don't include 'ORDER BY' in the ORDER BY parameter**
```php
// WRONG:
'ORDER BY id DESC'

// RIGHT:
'id DESC'
```

❌ **Don't forget to pass values as array**
```php
// WRONG:
safe_select('table', '*', 'id = 5')

// RIGHT:
safe_select('table', '*', 'id = %d', array(5))
```

❌ **Don't use $wpdb->prefix in table suffix parameter**
```php
// WRONG:
safe_select($wpdb->prefix . 'sensfrx_custome_filed', '*')

// RIGHT:
safe_select('sensfrx_custome_filed', '*')
```

---

## Quick Reference

| Old Method | New Helper Method |
|------------|-------------------|
| `$wpdb->get_results()` | `SensFRX_DB_Helper::safe_select()` |
| `$wpdb->get_row()` | `SensFRX_DB_Helper::safe_get_row()` |
| `$wpdb->get_var()` | `SensFRX_DB_Helper::safe_get_var()` |
| `$wpdb->insert()` | `SensFRX_DB_Helper::safe_insert()` |
| `$wpdb->update()` | `SensFRX_DB_Helper::safe_update()` |
| `$wpdb->delete()` | `SensFRX_DB_Helper::safe_delete()` |
| `SHOW TABLES LIKE` | `SensFRX_DB_Helper::table_exists()` |

---

## Need More Help?

Refer to `DB-HELPER-USAGE-GUIDE.md` for complete API documentation and additional examples.

