| 1 |
<?php |
| 2 |
|
| 3 |
/** |
| 4 |
* Extension Factory |
| 5 |
* |
| 6 |
* @package NotificationX\Extensions |
| 7 |
*/ |
| 8 |
|
| 9 |
namespace NotificationX\Core; |
| 10 |
|
| 11 |
use NotificationX\GetInstance; |
| 12 |
use WPDeveloper\QueryBuilder\Query as QueryBuilder; |
| 13 |
|
| 14 |
/** |
| 15 |
* Database Class |
| 16 |
* @method static Database get_instance($args = null) |
| 17 |
*/ |
| 18 |
class Database { |
| 19 |
/** |
| 20 |
* Instance of Database |
| 21 |
* |
| 22 |
* @var Database |
| 23 |
*/ |
| 24 |
use GetInstance; |
| 25 |
|
| 26 |
/** |
| 27 |
* WordPress database abstraction object. |
| 28 |
* |
| 29 |
* @var \wpdb |
| 30 |
*/ |
| 31 |
protected $wpdb; |
| 32 |
public static $version = '2.1'; |
| 33 |
public static $table_entries; |
| 34 |
public static $table_posts; |
| 35 |
public static $table_stats; |
| 36 |
protected static $query; |
| 37 |
|
| 38 |
/** |
| 39 |
* Initially Invoked when initialized. |
| 40 |
*/ |
| 41 |
public function __construct() { |
| 42 |
global $wpdb; |
| 43 |
$this->wpdb = $wpdb; |
| 44 |
self::$table_entries = $wpdb->prefix . 'nx_entries'; |
| 45 |
self::$table_posts = $wpdb->prefix . 'nx_posts'; |
| 46 |
self::$table_stats = $wpdb->prefix . 'nx_stats'; |
| 47 |
} |
| 48 |
|
| 49 |
public static function query() { |
| 50 |
if (!isset(self::$query)) { |
| 51 |
self::$query = QueryBuilder::init(); |
| 52 |
} |
| 53 |
return self::$query; |
| 54 |
} |
| 55 |
|
| 56 |
public function Create_DB() { |
| 57 |
$charset_collate = $this->wpdb->get_charset_collate(); |
| 58 |
$table_posts = self::$table_posts; |
| 59 |
$table_entries = self::$table_entries; |
| 60 |
$table_stats = self::$table_stats; |
| 61 |
|
| 62 |
$sql = "CREATE TABLE {$table_entries} ( |
| 63 |
entry_id bigint(20) unsigned NOT NULL auto_increment, |
| 64 |
nx_id bigint(20) unsigned NULL, |
| 65 |
source varchar(55) default NULL, |
| 66 |
entry_key varchar(255) default NULL, |
| 67 |
data longtext, |
| 68 |
created_at TIMESTAMP NOT NULL, |
| 69 |
updated_at TIMESTAMP NOT NULL, |
| 70 |
PRIMARY KEY (entry_id), |
| 71 |
KEY source (source), |
| 72 |
KEY nx_id (nx_id) |
| 73 |
) $charset_collate ;"; |
| 74 |
|
| 75 |
require_once ABSPATH . 'wp-admin/includes/upgrade.php'; |
| 76 |
$entries_db = dbDelta( $sql ); |
| 77 |
|
| 78 |
$sql = "CREATE TABLE {$table_posts} ( |
| 79 |
nx_id bigint(20) unsigned NOT NULL auto_increment, |
| 80 |
title text default NULL, |
| 81 |
type varchar(55) default NULL, |
| 82 |
source varchar(55) default NULL, |
| 83 |
theme varchar(55) default NULL, |
| 84 |
is_inline varchar(255) default NULL, |
| 85 |
global_queue BOOLEAN default false, |
| 86 |
enabled BOOLEAN default false, |
| 87 |
data longtext, |
| 88 |
created_at TIMESTAMP NOT NULL, |
| 89 |
updated_at TIMESTAMP NOT NULL, |
| 90 |
PRIMARY KEY (nx_id), |
| 91 |
KEY type (type), |
| 92 |
KEY source (source), |
| 93 |
KEY theme (theme) |
| 94 |
) $charset_collate ;"; |
| 95 |
$post_db = dbDelta( $sql ); |
| 96 |
|
| 97 |
$sql = "CREATE TABLE {$table_stats} ( |
| 98 |
stat_id bigint(20) unsigned NOT NULL auto_increment, |
| 99 |
nx_id bigint(20) unsigned default NULL, |
| 100 |
views varchar(55) default 0, |
| 101 |
clicks varchar(55) default 0, |
| 102 |
created_at DATE NOT NULL, |
| 103 |
PRIMARY KEY (stat_id), |
| 104 |
KEY nx_id (nx_id) |
| 105 |
) $charset_collate ;"; |
| 106 |
$stats_db = dbDelta( $sql ); |
| 107 |
|
| 108 |
} |
| 109 |
|
| 110 |
public function update_analytics( $col, $id, $date, $data = null ) { |
| 111 |
$table_name = self::$table_stats; |
| 112 |
$_data = is_null( $data ) ? 1 : intval( $data ); |
| 113 |
|
| 114 |
// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnquotedComplexPlaceholder -- False positive: the query is prepared via $this->wpdb->prepare(), which this sniff does not recognise, and only $wpdb->prefix table names are interpolated. Audited 2026-07-16. |
| 115 |
return $this->wpdb->query( $this->wpdb->prepare( ' |
| 116 |
UPDATE %1$s |
| 117 |
SET `%2$s` = `%3$s` + %4$s |
| 118 |
WHERE nx_id = "%5$s" |
| 119 |
AND created_at = "%6$s"', |
| 120 |
// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- False positive: the query is prepared via $this->wpdb->prepare(), which this sniff does not recognise, and only $wpdb->prefix table names are interpolated. Audited 2026-07-16. |
| 121 |
$table_name, esc_sql( $col ), esc_sql( $col ), $_data, intval( $id ), $date |
| 122 |
) |
| 123 |
); |
| 124 |
// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnquotedComplexPlaceholder |
| 125 |
} |
| 126 |
|
| 127 |
public function insert_post( $table_name, $post, $format = null ) { |
| 128 |
$post = $this->serialize_data( $post ); |
| 129 |
$this->wpdb->insert( $table_name, $post, $this->get_format($format, $post) ); |
| 130 |
return $this->wpdb->insert_id; |
| 131 |
} |
| 132 |
|
| 133 |
public function insert_posts( $table_name, $posts, $format = null ) { |
| 134 |
if ( ! empty( $posts[0] ) ) { |
| 135 |
$values = array(); |
| 136 |
$place_holders = array(); |
| 137 |
$_column = array_keys( $posts[0] ); |
| 138 |
/* |
| 139 |
* Column names are interpolated into the statement, so prepare() below |
| 140 |
* cannot protect them - it only binds the values. Callers such as the |
| 141 |
* /import REST route take these keys straight from a user-supplied JSON |
| 142 |
* payload, so accept identifiers only and quote them. |
| 143 |
*/ |
| 144 |
$_column = array_values( |
| 145 |
array_filter( |
| 146 |
$_column, |
| 147 |
function ( $col ) { |
| 148 |
return is_string( $col ) && preg_match( '/^[A-Za-z0-9_]+$/', $col ); |
| 149 |
} |
| 150 |
) |
| 151 |
); |
| 152 |
if ( empty( $_column ) ) { |
| 153 |
return; |
| 154 |
} |
| 155 |
$columns = '`' . implode( '`, `', $_column ) . '`'; |
| 156 |
$query = "INSERT INTO $table_name ($columns) VALUES "; |
| 157 |
foreach ( $posts as $key => $entry ) { |
| 158 |
$entry = $this->serialize_data( $entry ); |
| 159 |
reset( $_column ); |
| 160 |
$_place_holders = []; |
| 161 |
foreach ( $_column as $col ) { |
| 162 |
$values[] = isset( $entry[ $col ] ) ? $entry[ $col ] : ''; |
| 163 |
if(!empty($format) && isset($format[$col])){ |
| 164 |
$_place_holders[] = $format[$col]; |
| 165 |
} else { |
| 166 |
$_place_holders[] = '%s'; |
| 167 |
} |
| 168 |
} |
| 169 |
// $values = array_merge($values, array_values($entry)); |
| 170 |
$place_holders[] = '(' . implode( ', ', $_place_holders ) . ')'; /* In my case, i know they will always be integers */ |
| 171 |
} |
| 172 |
$query .= implode( ', ', $place_holders ); |
| 173 |
// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- Values are bound by prepare() below; the column list is whitelisted to /^[A-Za-z0-9_]+$/ identifiers above, which is what stops the injection this used to allow. |
| 174 |
$query = $this->wpdb->prepare( "$query", $values ); |
| 175 |
// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.NotPrepared -- $query is the prepare()d statement built above, with the column list whitelisted to identifiers. |
| 176 |
$this->wpdb->query( $query ); |
| 177 |
} |
| 178 |
} |
| 179 |
|
| 180 |
public function update_post( $table_name, $post, $where__or_pid, $format = null ) { |
| 181 |
$post = $this->serialize_data( $post ); |
| 182 |
if ( ! is_array( $where__or_pid ) ) { |
| 183 |
$id = $this->get_primary_col( $table_name ); |
| 184 |
$where__or_pid = [ $id => $where__or_pid ]; |
| 185 |
} |
| 186 |
return $this->wpdb->update( $table_name, $post, $where__or_pid, $this->get_format($format, $post) ); |
| 187 |
} |
| 188 |
|
| 189 |
public function get_post( $table_name, $where__or_pid, $select = '*' ) { |
| 190 |
if ( ! is_array( $where__or_pid ) ) { |
| 191 |
$id = $this->get_primary_col( $table_name ); |
| 192 |
$where__or_pid = [ $id => absint( $where__or_pid ) ]; |
| 193 |
} |
| 194 |
$posts = $this->get_posts( $table_name, $select, $where__or_pid ); |
| 195 |
return ! empty( $posts[0] ) ? $posts[0] : null; |
| 196 |
} |
| 197 |
|
| 198 |
public function get_posts( $table_name, $select = '*', $wheres = [], $join_table = '', $group_by_col = '', $join_type = 'LEFT JOIN', $extra_query = '' ) { |
| 199 |
$query = "SELECT $select FROM $table_name"; |
| 200 |
if ( ! empty( $join_table ) ) { |
| 201 |
$query .= " AS a $join_type `$join_table` AS b ON a.nx_id = b.nx_id"; |
| 202 |
} |
| 203 |
$query .= $this->get_where_query( $wheres ); |
| 204 |
if ( ! empty( $group_by_col ) ) { |
| 205 |
$query .= " GROUP BY $group_by_col"; |
| 206 |
} |
| 207 |
// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- False positive: the query is prepared via $this->wpdb->prepare(), which this sniff does not recognise, and only $wpdb->prefix table names are interpolated. Audited 2026-07-16. |
| 208 |
$posts = $this->wpdb->get_results( "$query $extra_query", ARRAY_A ); |
| 209 |
$posts = array_map( [ $this, 'unserialize_data' ], $posts ); |
| 210 |
return $posts; |
| 211 |
} |
| 212 |
|
| 213 |
public function get_col( $table_name, $col, $wheres, $distinct = 'DISTINCT' ) { |
| 214 |
$col = esc_sql( $col ); |
| 215 |
$posts = $this->get_posts( $table_name, "$distinct `$col`", $wheres ); |
| 216 |
return array_column( $posts, $col ); |
| 217 |
} |
| 218 |
|
| 219 |
// public function get_count($table_name, $col, $wheres) { |
| 220 |
// $posts = $this->get_posts($table_name, "$col", $wheres); |
| 221 |
// return array_column($posts, $col, 0); |
| 222 |
// } |
| 223 |
|
| 224 |
public function get_source_count( $table_name, $col, $wheres = [] ) { |
| 225 |
$results = []; |
| 226 |
$col = esc_sql( $col ); |
| 227 |
$posts = $this->get_posts( $table_name, "`$col`, count(*)", $wheres, '', "`$col`" ); |
| 228 |
foreach ( $posts as $key => $value ) { |
| 229 |
$results[ $value[ $col ] ] = $value['count(*)']; |
| 230 |
} |
| 231 |
return $results; |
| 232 |
} |
| 233 |
|
| 234 |
public function delete_post( $table_name, $post_id ) { |
| 235 |
$id = $this->get_primary_col( $table_name ); |
| 236 |
return $this->delete_posts( $table_name, [ $id => $post_id ] ); |
| 237 |
} |
| 238 |
|
| 239 |
public function delete_posts( $table_name, $wheres, $limit = 0 ) { |
| 240 |
if ( $limit ) { |
| 241 |
return $this->delete_posts_limit( $table_name, $wheres, $limit ); |
| 242 |
} |
| 243 |
return $this->wpdb->delete( $table_name, $wheres ); |
| 244 |
} |
| 245 |
|
| 246 |
public function delete_posts_limit( $table_name, $wheres, $limit ) { |
| 247 |
$limit = absint( $limit ); |
| 248 |
$query = "DELETE FROM `$table_name` "; |
| 249 |
$query .= $this->get_where_query( $wheres ); |
| 250 |
$query .= " LIMIT $limit"; |
| 251 |
// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.NotPrepared -- False positive: the query is prepared via $this->wpdb->prepare(), which this sniff does not recognise, and only $wpdb->prefix table names are interpolated. Audited 2026-07-16. |
| 252 |
return $this->wpdb->query( $query ); |
| 253 |
} |
| 254 |
|
| 255 |
public function serialize_data( $post ) { |
| 256 |
if ( ! empty( $post['data'] ) ) { |
| 257 |
$post['data'] = maybe_serialize( $post['data'] ); |
| 258 |
} |
| 259 |
// created_at and updated_at if not empty convert to mysql date |
| 260 |
if ( ! empty( $post['created_at'] ) ) { |
| 261 |
$post['created_at'] = gmdate( 'Y-m-d H:i:s', strtotime( $post['created_at'] ) ); |
| 262 |
} |
| 263 |
if ( ! empty( $post['updated_at'] ) ) { |
| 264 |
$post['updated_at'] = gmdate( 'Y-m-d H:i:s', strtotime( $post['updated_at'] ) ); |
| 265 |
} |
| 266 |
return $post; |
| 267 |
} |
| 268 |
|
| 269 |
public function unserialize_data( $post ) { |
| 270 |
if ( ! empty( $post['data'] ) ) { |
| 271 |
$post['data'] = maybe_unserialize( $post['data'] ); |
| 272 |
} |
| 273 |
return $post; |
| 274 |
} |
| 275 |
|
| 276 |
public function get_primary_col( $table_name ) { |
| 277 |
if ( $table_name == self::$table_posts ) { |
| 278 |
return 'nx_id'; |
| 279 |
} elseif ( $table_name == self::$table_entries ) { |
| 280 |
return 'entry_id'; |
| 281 |
} elseif ( $table_name == self::$table_stats ) { |
| 282 |
return 'stat_id'; |
| 283 |
} |
| 284 |
} |
| 285 |
|
| 286 |
public function get_where_query( $wheres ) { |
| 287 |
$query = ''; |
| 288 |
if ( ! empty( $wheres ) && is_string( $wheres ) ) { |
| 289 |
return " $wheres "; |
| 290 |
} elseif ( ! empty( $wheres ) ) { |
| 291 |
$query .= ' WHERE true=true'; |
| 292 |
foreach ( $wheres as $key => $value ) { |
| 293 |
$compare = '='; |
| 294 |
if ( is_array( $value ) ) { |
| 295 |
$compare = $value[0]; |
| 296 |
// $value = $value[1]; |
| 297 |
if ( 'IN' === strtoupper( $compare ) && is_array( $value[1] ) ) { |
| 298 |
$value = array_map( 'esc_sql', $value[1] ); |
| 299 |
$value = "('" . implode( "', '", $value ) . "')"; |
| 300 |
} elseif ( 'BETWEEN' === strtoupper( $compare ) && isset( $value[1], $value[2] ) ) { |
| 301 |
$value = $this->wpdb->prepare( '%s AND %s', $value[1], $value[2] ); |
| 302 |
} elseif ( in_array( $compare, [ '<', '<=', '>', '>=' ], true ) ) { |
| 303 |
$value = "'" . esc_sql( $value[1] ) . "'"; |
| 304 |
} else { |
| 305 |
throw new \Exception( esc_html( "Unknown parameter $compare." ), 1 ); |
| 306 |
} |
| 307 |
} else { |
| 308 |
$value = "'" . esc_sql( $value ) . "'"; // is_bool($value) ? $value :. |
| 309 |
} |
| 310 |
$query .= " AND $key $compare $value"; |
| 311 |
} |
| 312 |
} |
| 313 |
return $query; |
| 314 |
} |
| 315 |
|
| 316 |
public function update_option( $key, $value, $autoload = 'no' ) { |
| 317 |
// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared -- False positive: the query is prepared via $this->wpdb->prepare(), which this sniff does not recognise, and only $wpdb->prefix table names are interpolated. Audited 2026-07-16. |
| 318 |
$is_exists = $this->wpdb->get_row( $this->wpdb->prepare( "SELECT * FROM {$this->wpdb->prefix}options WHERE option_name=%s LIMIT 1", $key ) ); |
| 319 |
if ( $is_exists ) { |
| 320 |
if ( $is_exists->option_value == $value ) { |
| 321 |
return; |
| 322 |
} |
| 323 |
$this->wpdb->update( "{$this->wpdb->options}", [ 'option_value' => $value ], [ 'option_name' => $key ] ); |
| 324 |
} else { |
| 325 |
$this->wpdb->insert( "{$this->wpdb->options}", [ |
| 326 |
'option_name' => $key, |
| 327 |
'option_value' => $value, |
| 328 |
] |
| 329 |
); |
| 330 |
} |
| 331 |
} |
| 332 |
public function get_option( $key, $default = false ) { |
| 333 |
// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared -- False positive: the query is prepared via $this->wpdb->prepare(), which this sniff does not recognise, and only $wpdb->prefix table names are interpolated. Audited 2026-07-16. |
| 334 |
$results = $this->wpdb->get_row( $this->wpdb->prepare( "SELECT * FROM {$this->wpdb->options} WHERE option_name=%s LIMIT 1", $key ) ); |
| 335 |
if ( $results ) { |
| 336 |
return ! empty( $results->option_value ) ? $results->option_value : $default; |
| 337 |
} |
| 338 |
return $default; |
| 339 |
} |
| 340 |
|
| 341 |
/** |
| 342 |
* Undocumented function |
| 343 |
* |
| 344 |
* @param array $format |
| 345 |
* @param array $data |
| 346 |
* @return array |
| 347 |
*/ |
| 348 |
public function get_format($format, $data) { |
| 349 |
if(empty($format)){ |
| 350 |
return null; |
| 351 |
} |
| 352 |
|
| 353 |
$result = array(); |
| 354 |
foreach ($data as $key => $value) { |
| 355 |
if (isset($format[$key])) { |
| 356 |
$result[] = $format[$key]; |
| 357 |
} else { |
| 358 |
$result[] = '%s'; |
| 359 |
} |
| 360 |
} |
| 361 |
return $result; |
| 362 |
} |
| 363 |
} |
| 364 |
|
