# SensFRX Database Helper - Usage Guide

## Overview

The `SensFRX_DB_Helper` class provides secure database operations throughout the plugin. All methods use prepared statements and validate table names against a whitelist to prevent SQL injection attacks.

## Quick Start Examples

### Example 1: Replace Unsafe SELECT Query

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

**AFTER (Safe):**
```php
$results = SensFRX_DB_Helper::safe_select(
    'sensfrx_custome_filed',
    'id, sensfrx_custom_field',
    '',
    array(),
    '',
    ARRAY_A
);
```

### Example 2: SELECT with WHERE Clause

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

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

### Example 3: SELECT with WHERE and ORDER BY

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

**AFTER (Safe):**
```php
$results = SensFRX_DB_Helper::safe_select(
    'sensfrx_real_activity',
    '*',
    'cid = %d',
    array($user_id),
    'id DESC',
    ARRAY_A
);
```

### Example 4: Get Single Row

**BEFORE (Unsafe):**
```php
global $wpdb;
$table_name = $wpdb->prefix . 'sensfrx_email_templates';
$template = $wpdb->get_row("SELECT * FROM $table_name WHERE name1 = '$template_name'", ARRAY_A);
```

**AFTER (Safe):**
```php
$template = SensFRX_DB_Helper::safe_get_row(
    'sensfrx_email_templates',
    'name1 = %s',
    array($template_name),
    ARRAY_A
);
```

### Example 5: Get Single Variable

**BEFORE (Unsafe):**
```php
global $wpdb;
$table_name = $wpdb->prefix . 'sensfrx_shadow_activity';
$status = $wpdb->get_var("SELECT sensfrx_shadow_status FROM $table_name WHERE id = 1");
```

**AFTER (Safe):**
```php
$status = SensFRX_DB_Helper::safe_get_var(
    'sensfrx_shadow_activity',
    'sensfrx_shadow_status',
    'id = %d',
    array(1)
);
```

### Example 6: INSERT (Already Safe, But Use Helper for Consistency)

**BEFORE:**
```php
global $wpdb;
$table_name = $wpdb->prefix . 'sensfrx_custome_filed';
$data = array('sensfrx_custom_field' => $slugs);
$results = $wpdb->insert($table_name, $data);
```

**AFTER (Using Helper):**
```php
$data = array('sensfrx_custom_field' => $slugs);
$results = SensFRX_DB_Helper::safe_insert('sensfrx_custome_filed', $data);
```

### Example 7: UPDATE

**BEFORE:**
```php
global $wpdb;
$table_name = $wpdb->prefix . 'sensfrx_shadow_activity';
$data = array('sensfrx_shadow_status' => $new_status);
$where = array('id' => $id);
$wpdb->update($table_name, $data, $where);
```

**AFTER (Using Helper):**
```php
$data = array('sensfrx_shadow_status' => $new_status);
$where = array('id' => $id);
SensFRX_DB_Helper::safe_update('sensfrx_shadow_activity', $data, $where);
```

### Example 8: DELETE

**BEFORE:**
```php
global $wpdb;
$table_name = $wpdb->prefix . 'sensfrx_webhook_logout';
$wpdb->delete($table_name, array('client_id' => $userId), array('%d'));
```

**AFTER (Using Helper):**
```php
SensFRX_DB_Helper::safe_delete('sensfrx_webhook_logout', array('client_id' => $userId), array('%d'));
```

### Example 9: Check Table Exists

**BEFORE:**
```php
global $wpdb;
$table_name = $wpdb->prefix . 'sensfrx_webhook_logout';
$table_exists = $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $table_name)) === $table_name;
```

**AFTER (Using Helper):**
```php
$table_exists = SensFRX_DB_Helper::table_exists('sensfrx_webhook_logout');
```

## Complete API Reference

### safe_select()
Safely execute SELECT queries.

**Parameters:**
- `$table_suffix` (string, required) - Table name without prefix (e.g., 'sensfrx_custome_filed')
- `$columns` (string, optional) - Columns to select (default: '*')
- `$where` (string, optional) - WHERE clause without 'WHERE' keyword
- `$where_values` (array, optional) - Values for WHERE placeholders
- `$order_by` (string, optional) - ORDER BY clause without 'ORDER BY' keyword
- `$output_type` (constant, optional) - ARRAY_A, ARRAY_N, or OBJECT (default: ARRAY_A)

**Returns:** Array of results or null on error

### safe_get_row()
Get a single row from a table.

**Parameters:**
- `$table_suffix` (string, required) - Table name without prefix
- `$where` (string, optional) - WHERE clause without 'WHERE' keyword
- `$where_values` (array, optional) - Values for WHERE placeholders
- `$output_type` (constant, optional) - ARRAY_A, ARRAY_N, or OBJECT (default: ARRAY_A)

**Returns:** Single row or null on error

### safe_get_var()
Get a single variable/value from a table.

**Parameters:**
- `$table_suffix` (string, required) - Table name without prefix
- `$column` (string, required) - Column name to retrieve
- `$where` (string, optional) - WHERE clause without 'WHERE' keyword
- `$where_values` (array, optional) - Values for WHERE placeholders

**Returns:** String value or null on error

### safe_insert()
Insert data into a table.

**Parameters:**
- `$table_suffix` (string, required) - Table name without prefix
- `$data` (array, required) - Data to insert (column => value pairs)
- `$format` (array, optional) - Format for data values (%s, %d, %f)

**Returns:** Number of rows affected or false on error

### safe_update()
Update data in a table.

**Parameters:**
- `$table_suffix` (string, required) - Table name without prefix
- `$data` (array, required) - Data to update (column => value pairs)
- `$where` (array, required) - WHERE conditions (column => value pairs)
- `$format` (array, optional) - Format for data values
- `$where_format` (array, optional) - Format for WHERE values

**Returns:** Number of rows affected or false on error

### safe_delete()
Delete data from a table.

**Parameters:**
- `$table_suffix` (string, required) - Table name without prefix
- `$where` (array, required) - WHERE conditions (column => value pairs)
- `$where_format` (array, optional) - Format for WHERE values

**Returns:** Number of rows affected or false on error

### table_exists()
Check if a table exists.

**Parameters:**
- `$table_suffix` (string, required) - Table name without prefix

**Returns:** Boolean (true if exists, false otherwise)

### get_table_name()
Get a validated, prefixed table name.

**Parameters:**
- `$table_suffix` (string, required) - Table name without prefix

**Returns:** Full table name or false if invalid

### get_last_error()
Get the last database error message.

**Returns:** String error message

### get_insert_id()
Get the last insert ID.

**Returns:** Integer insert ID

### get_rows_affected()
Get the number of rows affected by the last query.

**Returns:** Integer rows affected

### gdpr_delete_user_data()
Delete all user data for GDPR compliance.

**Parameters:**
- `$user_id` (int, required) - WordPress user ID

**Returns:** Boolean success status

## Valid Table Names

Only these table suffixes are allowed (security whitelist):
- `sensfrx_sleep`
- `sensfrx_webhook_logout`
- `sensfrx_api_active_and_webhook`
- `sensfrx_tab_data`
- `sensfrx_custome_filed`
- `sensfrx_real_activity`
- `sensfrx_shadow_activity`
- `sensfrx_email_templates`
- `sensfrx_pre_payment_order`

## Placeholder Types for Prepared Statements

Use these in your WHERE clauses and format arrays:
- `%s` - String
- `%d` - Integer
- `%f` - Float

**Example:**
```php
$results = SensFRX_DB_Helper::safe_select(
    'sensfrx_real_activity',
    '*',
    'sensfrx_log_type = %s AND cid = %d',
    array('login', 123),
    'created_at DESC'
);
```

## Error Handling

The helper class logs errors only when `WP_DEBUG` is enabled. Always check return values:

```php
$results = SensFRX_DB_Helper::safe_select('sensfrx_custome_filed', '*');

if (null === $results) {
    // Handle error
    $error = SensFRX_DB_Helper::get_last_error();
    error_log('Database error: ' . $error);
    return;
}

if (empty($results)) {
    // No results found (different from error)
    echo 'No records found';
    return;
}

// Process results
foreach ($results as $row) {
    // ...
}
```

## Migration Checklist

When migrating existing code to use the helper:

1. ✅ Identify all `$wpdb->get_results()`, `$wpdb->get_row()`, `$wpdb->get_var()` calls
2. ✅ Replace with appropriate `SensFRX_DB_Helper::safe_*()` method
3. ✅ Extract table suffix (remove `$wpdb->prefix`)
4. ✅ Convert direct SQL WHERE clauses to use placeholders
5. ✅ Pass values as separate array parameter
6. ✅ Test the functionality thoroughly
7. ✅ Check error logs for any helper validation errors

## Benefits

✅ **SQL Injection Prevention** - All queries use prepared statements
✅ **Table Name Validation** - Whitelist prevents unauthorized table access
✅ **Consistent Error Handling** - Centralized logging and error management
✅ **GDPR Compliance** - Built-in data deletion methods
✅ **Easier Maintenance** - Single place to update database logic
✅ **WordPress Standards** - Follows WordPress coding best practices

## Need Help?

If you encounter any issues with the helper class:
1. Check that the table suffix is in the valid tables list
2. Verify placeholder types match your data types
3. Check error logs when WP_DEBUG is enabled
4. Use `SensFRX_DB_Helper::get_last_error()` for debugging

