# Dynamic Data (Pro Overlay)

How Pro contributes to dynamic data sources in Elementor — Pro's DynamicTags subsystem (registered via Elementor's tag system) and the Pro-only data integrations Pro injects into Lite's Advanced Data Table widget.

## Context

"Dynamic data" in Essential Addons covers two distinct mechanisms:

1. **Elementor Dynamic Tags** — Elementor's native dynamic-content system. Any control marked dynamic-enabled can pull from a registered tag (e.g. post title, ACF field, custom field). Pro adds 5 tag classes under a single tag group.
2. **Widget-level data sources** — patterns where a widget reads from an external data store (DB, Google Sheets, TablePress) rather than its own Repeater. Pro adds 4 such data sources to Lite's `Advanced Data Table` widget via the Extender trait.

Lite's `docs/architecture/dynamic-data/` folder covers the broader subsystem (WP_Query construction, load-more, Login & Registration, WooCommerce integration). This Pro doc captures **only the Pro-specific surface**.

## Verified facts

### Pro DynamicTags — group + 5 tag classes

Registered via the `Advanced_Dynamic_Tags` extension class (`includes/Extensions/Advanced_Dynamic_Tags.php`):

```php
public function __construct() {
    add_action( 'elementor/dynamic_tags/register', [ $this, 'register_dynamic_widgets' ] );
}

public function register_dynamic_widgets( $dynamic_tags_manager ) {
    $dynamic_tags_manager->register_group(
        'eael-advanced-dynamic-tags',
        [ 'title' => __( 'EA Dynamic Tags', 'essential-addons-elementor' ) ]
    );

    $dynamic_tags_manager->register( new Posts() );
    $dynamic_tags_manager->register( new Woo_Products() );
    $dynamic_tags_manager->register( new Terms() );
    $dynamic_tags_manager->register( new Custom_Post_Types() );
}
```

**Note:** `Acf_Relationship` (which extends `Posts`) is **not** in the explicit `register()` list above. It's only instantiated when ACF is present and the `Posts` tag delegates — verify the exact registration path in code when editing.

### Tag class inventory

| Class                | Tag name                                  | Group                              | Extends                                              | What it returns                                                       |
| -------------------- | ----------------------------------------- | ---------------------------------- | ---------------------------------------------------- | --------------------------------------------------------------------- |
| `Posts`              | `eael-dynamic-tags-posts`                 | `eael-advanced-dynamic-tags`       | `\Elementor\Core\DynamicTags\Tag`                    | HTML string — looped posts with format / separator controls          |
| `Custom_Post_Types`  | `eael-dynamic-tags-custom-post-types`     | `eael-advanced-dynamic-tags`       | (verify in code; likely extends `Tag`)               | HTML string — looped CPT entries                                      |
| `Terms`              | `eael-dynamic-tags-terms`                 | `eael-advanced-dynamic-tags`       | (verify)                                             | HTML string — taxonomy terms                                          |
| `Woo_Products`       | `eael-dynamic-tags-woo-products`          | `eael-advanced-dynamic-tags`       | (verify)                                             | HTML string — WooCommerce products                                    |
| `Acf_Relationship`   | `eael-dynamic-tags-acf-relationship`      | `eael-advanced-dynamic-tags`       | `Posts`                                              | HTML string — ACF relationship-field linked posts                     |

### Tag render pattern — `Posts`

`Extensions/DynamicTags/Posts.php::render()`:

```php
public function render() {
    $settings = $this->get_settings_for_display();
    if (empty($settings)) { return; }

    $settings['post_type'] = ! empty( $settings['post_type'] )
        ? ClassesHelper::validate_post_types( $settings['post_type'] )
        : '';

    $args = Helper::get_query_args( $settings );          // ← Lite's Helper builds WP_Query args
    $extra_args = $this->get_extra_args();
    $args = array_merge( $args, $extra_args );

    if (empty($args)) { return; }

    $wp_query = new \WP_Query( $args );
    if ( $wp_query->have_posts() ) {
        while ( $wp_query->have_posts() ) {
            $wp_query->the_post();

            if ( 'none' === $settings['separator'] ) {
                echo $this->get_post_by_format();
            } else {
                echo '<a href=' . get_the_permalink() . '>' . $this->get_post_by_format() . '</a>';
            }
            // ...separator between items
        }
    }
}
```

Three observations:

1. **Pro delegates to Lite's `Helper::get_query_args()`** for the `WP_Query` args build — same code path Lite widgets use. This is the right pattern; do not duplicate.
2. **Output is unescaped** (`echo $this->get_post_by_format()`). The PHPCS annotations `phpcs:ignore WordPress.Security.EscapeOutput.OutputNotEscaped` are present. The `get_post_by_format()` helper is expected to return pre-escaped HTML — verify this contract is honoured when editing the helper.
3. **`get_permalink()` is concatenated into an `<a href=>` without `esc_url()`**. Real-world risk is low (permalinks are server-generated) but PHPCS would normally complain. The ignore annotation likely suppresses the warning.

### Acf_Relationship — extends `Posts`

`Extensions/DynamicTags/Acf_Relationship.php`:

```php
class Acf_Relationship extends Posts {
    public function get_name()  { return 'eael-dynamic-tags-acf-relationship'; }
    public function get_title() { return __('ACF Relationship', 'essential-addons-elementor'); }
    // (controls + render overridden to query ACF relationship-field linked posts)
}
```

Pattern: rather than reimplementing the post loop, ACF Relationship reuses `Posts`'s render skeleton and changes only the `WP_Query` args (probably `'post__in' => acf_get_field('relationship_field_name')`).

### Widget-level data sources for Advanced Data Table

Pro adds 4 data sources to Lite's `Advanced Data Table` widget via Extender filters registered in `Bootstrap::register_hooks`:

| Hook                                                            | Method                                                | Source             |
| --------------------------------------------------------------- | ----------------------------------------------------- | ------------------ |
| `eael/advanced-data-table/table_html/integration/database`      | `advanced_data_table_database_integration`            | Local WordPress DB |
| `eael/advanced-data-table/table_html/integration/remote`        | `advanced_data_table_remote_database_integration`     | Remote MySQL via `mysqli` |
| `eael/advanced-data-table/table_html/integration/google`        | `advanced_data_table_google_sheets_integration`       | Google Sheets API  |
| `eael/advanced-data-table/table_html/integration/tablepress`    | `advanced_data_table_tablepress_integration`          | TablePress plugin  |

### Local DB integration — `wpdb` with SELECT-only allowlist

`Extender.php` `advanced_data_table_database_integration( $settings )`:

```php
global $wpdb;
$wpdb->suppress_errors = true;

if ($settings['ea_adv_data_table_source_database_query_type'] == 'table') {
    $table = $settings["ea_adv_data_table_source_database_table"];
    // phpcs:ignore [direct DB query]
    $results = $wpdb->get_results( "SELECT * FROM $table", ARRAY_A );
} else {
    if ( empty( $settings['ea_adv_data_table_source_database_query'] ) ) { return; }
    $current_user_id = get_current_user_id();
    $current_post_id = get_the_ID();
    $query = str_replace(
        [ '[CURRENT_USER_ID]', '[CURRENT_POST_ID]' ],
        [ $current_user_id, $current_post_id ],
        $settings['ea_adv_data_table_source_database_query']
    );
    if ( ! $this->eael_valid_select_query( $query ) ) {
        return $results;
    }
    // phpcs:ignore [direct DB query]
    $results = $wpdb->get_results( $query, ARRAY_A );
}
```

**Security observations:**

- **Custom-query mode is SELECT-only** via `eael_valid_select_query` allowlist. The validator (definition further down in Extender.php; verify exact rules before editing) is the only thing between a user-supplied SQL string and `$wpdb->get_results`.
- **Table-mode interpolates the table name directly** (`SELECT * FROM $table`). The `$table` value comes from `$settings["ea_adv_data_table_source_database_table"]` — set via a control in the Elementor editor. Editor access requires `edit_posts`-level cap, so the threat model is "compromised editor can run arbitrary SELECTs on any table". That's the design intent but should be documented.
- **`[CURRENT_USER_ID]` and `[CURRENT_POST_ID]` placeholders** are integer-substituted from `get_current_user_id()` / `get_the_ID()`. Safe — these are integer functions.
- **No prepared statements.** Every query path runs raw against `$wpdb->get_results`. The SELECT allowlist is the only safety net.

### Remote DB integration — direct `mysqli`

```php
$conn = new mysqli(
    $settings['ea_adv_data_table_source_remote_host'],
    $settings['ea_adv_data_table_source_remote_username'],
    $settings['ea_adv_data_table_source_remote_password'],
    $settings['ea_adv_data_table_source_remote_database']
);
if ( $conn->connect_error ) {
    return "Failed to connect to MySQL: " . $conn->connect_error;
}
$conn->set_charset( "utf8" );
// ... runs $settings['ea_adv_data_table_source_remote_query'] via eael_valid_select_query
```

**Security observations:**

- **Credentials stored in widget settings.** A remote DB password ends up in Elementor's saved JSON for the page — `wp_postmeta` `_elementor_data` field. Anyone with access to the post meta can read it. Avoid using this for sensitive production DBs.
- **Connection error message returned as widget output.** `return "Failed to connect to MySQL: " . $conn->connect_error;` leaks DB host info to anyone viewing the page. Sanitize / replace with generic error.
- **No TLS enforcement.** `mysqli` doesn't enable TLS unless explicitly configured with `mysqli::ssl_set()`. Remote MySQL connections from a WP server happen in plaintext today.

### Google Sheets integration — transient-cached

```php
$cache_limit = ! empty( $settings['ea_adv_data_table_data_cache_limit'] )
    ? intval( $settings['ea_adv_data_table_data_cache_limit'] ) : 1;
$transient_key = 'ea_adv_data_table_source_google_sheet_' . md5( implode( '', $arg ) );
$results = get_transient( $transient_key );

if ( empty( $results ) || empty( $results['rowData'] ) ) {
    $connection = wp_remote_get(
        "https://sheets.googleapis.com/v4/spreadsheets/{$sheet_id}/?key={$api_key}&ranges={$range}&includeGridData=true",
        [ 'timeout' => 70 ]
    );
    // ...
}
```

**Observations:**

- **Transient-cached** with user-configurable TTL (`$cache_limit`, default 1 hour). Better than the Fancy Chart version which has no cache.
- **70s timeout** — same flag as `external-api-integrations.md`. Unsafe synchronous wait.
- **API key and Sheet ID concatenated into URL** with no `urlencode`. User-supplied range is also concatenated.

### TablePress integration

`advanced_data_table_tablepress_integration` (`Extender.php:1946`) — reads from TablePress's own data store. Depends on TablePress being installed; gracefully no-ops if not.

### SQL allowlist — `eael_valid_select_query`

Pro ships a SELECT-only validator (`Extender.php` — exact line above). It's the single security boundary protecting both local and remote DB integrations from non-SELECT statements. **Any edit to this function affects two integrations and is security-critical.** Treat it like the License `Manager::remote_post()` core — never weaken without explicit review.

Likely rules (verify in code):

- Statement must start with `SELECT` (case-insensitive after trim)
- No `INSERT`, `UPDATE`, `DELETE`, `DROP`, `TRUNCATE`, `ALTER`, `CREATE`, `RENAME`, `GRANT`, `REVOKE`, `EXEC`, `CALL`
- No multi-statement (no semicolons followed by another statement)
- Verify whether comments (`--`, `/* */`) are stripped before validation — if not, an attacker could comment-out the SELECT keyword

## What's missing

1. **`eael_valid_select_query` is undocumented.** It's the most security-critical function in the Pro codebase and has no inline docblock summarizing the allowlist rules. Add a docblock + a unit test fixture.
2. **Remote DB plaintext credentials in post meta.** Big security model gap. Solutions: prompt the user with a warning at control level; store credentials in a separate, capability-gated option; only support read-only DB users.
3. **TLS on `mysqli` is unconfigured.** Enable `mysqli::ssl_set()` when the user-supplied host doesn't end in `.local`/`.localhost`.
4. **Connection error leakage.** Return a generic "Failed to connect to remote database" message; log details to the WP error log only.
5. **Google Sheets timeout 70s** — already flagged in `external-api-integrations.md`. Cap at 15s for both Sheets call-sites.
6. **DynamicTags output isn't escaped at the tag level.** Render methods rely on the `get_post_by_format()` helper returning pre-escaped HTML. Verify and document this contract; failure here = stored XSS via post titles/content.
7. **`Acf_Relationship` registration is implicit.** `Advanced_Dynamic_Tags::register_dynamic_widgets` doesn't `register( new Acf_Relationship() )` explicitly. Trace whether it auto-registers via parent (`Posts`) or via a separate hook elsewhere. If it's never registered, the class is dead code.
8. **No per-tag documentation.** Each Pro DynamicTag class (Posts, CPT, Terms, Woo_Products, Acf_Relationship) needs its own control catalogue + WP_Query argument map. Future follow-up.
9. **Cross-cutting concern: Pro's DynamicTags use Lite's `Classes/Helper` for `validate_post_types` and `get_query_args`.** When Lite refactors those helpers, all 5 Pro tags break together. Add a stable adapter or test the cross-plugin compatibility.

## Acceptance

This doc accurately reflects:

- Pro DynamicTags registered via `elementor/dynamic_tags/register` action ✓
- Group name `eael-advanced-dynamic-tags`, 4 tags explicitly registered + ACF Relationship implicit ✓
- Posts tag renders via `\WP_Query` and Lite's `Helper::get_query_args` ✓
- Advanced Data Table data sources registered via Pro Extender filters ✓
- Local DB integration uses `wpdb->get_results` with SELECT-only allowlist ✓
- Remote DB uses `mysqli` directly (no `wpdb` second-DB pattern) ✓
- Remote DB credentials end up in `_elementor_data` post meta (verify in code) ✓
- Google Sheets has transient cache + 70s timeout ✓

If the SQL validator or any data-source integration changes, update this doc in the same PR.

## Pairs with

- `pro-lite-bridge.md` — Extender filter hooks `eael/advanced-data-table/table_html/integration/*` are registered via the bridge
- `external-api-integrations.md` — Google Sheets calls are subject to the API integration rules
- `extensions.md` — `Advanced_Dynamic_Tags` is the extension that registers the DynamicTags group

## Related

- Lite's `docs/architecture/dynamic-data/wp-query-construction.md` — the `Helper::get_query_args` Pro relies on
- WordPress `wpdb` security guide
- Elementor DynamicTags API documentation

## Out of scope

- Lite's own dynamic-data integrations (AJAX endpoints, login/register, WooCommerce loops) — Lite's docs cover those
- Per-DynamicTag control catalogue — future per-tag docs
- The Lite-side `Advanced Data Table` widget anatomy — Lite owns the widget; Pro only injects data sources via filter