WP Security Audit Log - Version 2.6.4

Version Description

(2017-06-01) =

  • New Features
    • Added a number of queries in the plugin to support the new version of the Reports Add-On
Download this release

Release Info

Developer WPWhiteSecurity
Plugin Icon 128x128 WP Security Audit Log
Version 2.6.4
Comparing to
See all releases

Code changes from version 2.6.3 to 2.6.4

Files changed (8) hide show
  1. classes/Adapters/MySQL/ActiveRecordAdapter.php +55 -10
  2. classes/Adapters/MySQL/QueryAdapter.php +18 -7
  3. classes/Adapters/MySQL/TmpUserAdapter.php +34 -0
  4. classes/Connector/AbstractConnector.php +1 -0
  5. classes/Connector/MySQLDB.php +4 -0
  6. classes/Models/TmpUser.php +9 -0
  7. readme.txt +7 -2
  8. wp-security-audit-log.php +2 -1
classes/Adapters/MySQL/ActiveRecordAdapter.php CHANGED
@@ -229,7 +229,6 @@ class WSAL_Adapters_MySQL_ActiveRecord implements WSAL_Adapters_ActiveRecordInte
229
  $result[] = $this->getModel()->LoadData($data);
230
  }
231
  return $result;
232
-
233
  }
234
 
235
  /**
@@ -385,7 +384,8 @@ class WSAL_Adapters_MySQL_ActiveRecord implements WSAL_Adapters_ActiveRecordInte
385
  public function GetReporting($_siteId, $_userId, $_roleName, $_alertCode, $_startTimestamp, $_endTimestamp, $_nextDate = null, $_limit = 0)
386
  {
387
  global $wpdb;
388
-
 
389
  $_wpdb = $this->connection;
390
  $_wpdb->set_charset($_wpdb->dbh, 'utf8mb4', 'utf8mb4_general_ci');
391
  // tables
@@ -393,8 +393,6 @@ class WSAL_Adapters_MySQL_ActiveRecord implements WSAL_Adapters_ActiveRecordInte
393
  $tableMeta = $meta->GetTable(); // metadata
394
  $occurrence = new WSAL_Adapters_MySQL_Occurrence($this->connection);
395
  $tableOcc = $occurrence->GetTable(); // occurrences
396
-
397
- $user_names = $this->GetUserNames($_userId);
398
 
399
  $conditionDate = !empty($_nextDate) ? ' AND occ.created_on < '.$_nextDate : '';
400
 
@@ -513,10 +511,11 @@ class WSAL_Adapters_MySQL_ActiveRecord implements WSAL_Adapters_ActiveRecordInte
513
  /**
514
  * List of unique IP addresses used by the same user
515
  */
516
- public function GetReportGrouped($_siteId, $_startTimestamp, $_endTimestamp, $_alertCode = 'null', $_limit = 0)
517
  {
518
  global $wpdb;
519
-
 
520
  $_wpdb = $this->connection;
521
  $_wpdb->set_charset($_wpdb->dbh, 'utf8mb4', 'utf8mb4_general_ci');
522
  // tables
@@ -524,7 +523,16 @@ class WSAL_Adapters_MySQL_ActiveRecord implements WSAL_Adapters_ActiveRecordInte
524
  $tableMeta = $meta->GetTable(); // metadata
525
  $occurrence = new WSAL_Adapters_MySQL_Occurrence($this->connection);
526
  $tableOcc = $occurrence->GetTable(); // occurrences
527
-
 
 
 
 
 
 
 
 
 
528
  $sql = "SELECT DISTINCT *
529
  FROM (SELECT DISTINCT
530
  occ.site_id,
@@ -534,16 +542,24 @@ class WSAL_Adapters_MySQL_ActiveRecord implements WSAL_Adapters_ActiveRecordInte
534
  JOIN $tableMeta AS meta ON meta.occurrence_id = occ.id
535
  WHERE
536
  (@siteId is NULL OR find_in_set(occ.site_id, @siteId) > 0)
 
 
 
 
 
 
 
537
  AND (@alertCode is NULL OR find_in_set(occ.alert_id, @alertCode) > 0)
538
  AND (@startTimestamp is NULL OR occ.created_on >= @startTimestamp)
539
  AND (@endTimestamp is NULL OR occ.created_on <= @endTimestamp)
 
540
  HAVING user_login IS NOT NULL
541
  UNION ALL
542
  SELECT DISTINCT
543
  occ.site_id,
544
  CONVERT((SELECT u.user_login
545
  FROM $tableMeta as t2
546
- JOIN {$wpdb->prefix}users AS u ON u.ID = replace(t2.value, '\"', '')
547
  WHERE t2.name = 'CurrentUserID'
548
  AND t2.occurrence_id = occ.id
549
  GROUP BY u.ID
@@ -553,18 +569,28 @@ class WSAL_Adapters_MySQL_ActiveRecord implements WSAL_Adapters_ActiveRecordInte
553
  JOIN $tableMeta AS meta ON meta.occurrence_id = occ.id
554
  WHERE
555
  (@siteId is NULL OR find_in_set(occ.site_id, @siteId) > 0)
 
 
 
 
 
 
 
556
  AND (@alertCode is NULL OR find_in_set(occ.alert_id, @alertCode) > 0)
557
  AND (@startTimestamp is NULL OR occ.created_on >= @startTimestamp)
558
  AND (@endTimestamp is NULL OR occ.created_on <= @endTimestamp)
 
559
  HAVING user_login IS NOT NULL) ip_logins
560
- WHERE user_login != 'Website Visitor'
561
  ORDER BY user_login ASC
562
  ";
563
  $_wpdb->query("SET @siteId = $_siteId");
 
 
564
  $_wpdb->query("SET @alertCode = $_alertCode");
565
  $_wpdb->query("SET @startTimestamp = $_startTimestamp");
566
  $_wpdb->query("SET @endTimestamp = $_endTimestamp");
567
-
568
  if (!empty($_limit)) {
569
  $sql .= " LIMIT {$_limit}";
570
  }
@@ -595,4 +621,23 @@ class WSAL_Adapters_MySQL_ActiveRecord implements WSAL_Adapters_ActiveRecordInte
595
 
596
  return $grouped_types;
597
  }
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
598
  }
229
  $result[] = $this->getModel()->LoadData($data);
230
  }
231
  return $result;
 
232
  }
233
 
234
  /**
384
  public function GetReporting($_siteId, $_userId, $_roleName, $_alertCode, $_startTimestamp, $_endTimestamp, $_nextDate = null, $_limit = 0)
385
  {
386
  global $wpdb;
387
+ $user_names = $this->GetUserNames($_userId);
388
+
389
  $_wpdb = $this->connection;
390
  $_wpdb->set_charset($_wpdb->dbh, 'utf8mb4', 'utf8mb4_general_ci');
391
  // tables
393
  $tableMeta = $meta->GetTable(); // metadata
394
  $occurrence = new WSAL_Adapters_MySQL_Occurrence($this->connection);
395
  $tableOcc = $occurrence->GetTable(); // occurrences
 
 
396
 
397
  $conditionDate = !empty($_nextDate) ? ' AND occ.created_on < '.$_nextDate : '';
398
 
511
  /**
512
  * List of unique IP addresses used by the same user
513
  */
514
+ public function GetReportGrouped($_siteId, $_startTimestamp, $_endTimestamp, $_userId = 'null', $_roleName = 'null', $_ipAddress = 'null', $_alertCode = 'null', $_limit = 0)
515
  {
516
  global $wpdb;
517
+ $user_names = $this->GetUserNames($_userId);
518
+
519
  $_wpdb = $this->connection;
520
  $_wpdb->set_charset($_wpdb->dbh, 'utf8mb4', 'utf8mb4_general_ci');
521
  // tables
523
  $tableMeta = $meta->GetTable(); // metadata
524
  $occurrence = new WSAL_Adapters_MySQL_Occurrence($this->connection);
525
  $tableOcc = $occurrence->GetTable(); // occurrences
526
+ // Get temp table `wsal_tmp_users`
527
+ $tmp_users = new WSAL_Adapters_MySQL_TmpUser($this->connection);
528
+ // if the table exist
529
+ if ($tmp_users->IsInstalled()) {
530
+ $tableUsers = $tmp_users->GetTable(); // tmp_users
531
+ $this->TempUsers($tableUsers);
532
+ } else {
533
+ $tableUsers = $wpdb->users;
534
+ }
535
+
536
  $sql = "SELECT DISTINCT *
537
  FROM (SELECT DISTINCT
538
  occ.site_id,
542
  JOIN $tableMeta AS meta ON meta.occurrence_id = occ.id
543
  WHERE
544
  (@siteId is NULL OR find_in_set(occ.site_id, @siteId) > 0)
545
+ AND (@userId is NULL OR (
546
+ (meta.name = 'CurrentUserID' AND find_in_set(meta.value, @userId) > 0)
547
+ OR (meta.name = 'Username' AND replace(meta.value, '\"', '') IN ($user_names))
548
+ ))
549
+ AND (@roleName is NULL OR (meta.name = 'CurrentUserRoles'
550
+ AND replace(replace(replace(meta.value, ']', ''), '[', ''), '\\'', '') REGEXP @roleName
551
+ ))
552
  AND (@alertCode is NULL OR find_in_set(occ.alert_id, @alertCode) > 0)
553
  AND (@startTimestamp is NULL OR occ.created_on >= @startTimestamp)
554
  AND (@endTimestamp is NULL OR occ.created_on <= @endTimestamp)
555
+ AND (@ipAddress is NULL OR (meta.name = 'ClientIP' AND find_in_set(meta.value, @ipAddress) > 0))
556
  HAVING user_login IS NOT NULL
557
  UNION ALL
558
  SELECT DISTINCT
559
  occ.site_id,
560
  CONVERT((SELECT u.user_login
561
  FROM $tableMeta as t2
562
+ JOIN $tableUsers AS u ON u.ID = replace(t2.value, '\"', '')
563
  WHERE t2.name = 'CurrentUserID'
564
  AND t2.occurrence_id = occ.id
565
  GROUP BY u.ID
569
  JOIN $tableMeta AS meta ON meta.occurrence_id = occ.id
570
  WHERE
571
  (@siteId is NULL OR find_in_set(occ.site_id, @siteId) > 0)
572
+ AND (@userId is NULL OR (
573
+ (meta.name = 'CurrentUserID' AND find_in_set(meta.value, @userId) > 0)
574
+ OR (meta.name = 'Username' AND replace(meta.value, '\"', '') IN ($user_names))
575
+ ))
576
+ AND (@roleName is NULL OR (meta.name = 'CurrentUserRoles'
577
+ AND replace(replace(replace(meta.value, ']', ''), '[', ''), '\\'', '') REGEXP @roleName
578
+ ))
579
  AND (@alertCode is NULL OR find_in_set(occ.alert_id, @alertCode) > 0)
580
  AND (@startTimestamp is NULL OR occ.created_on >= @startTimestamp)
581
  AND (@endTimestamp is NULL OR occ.created_on <= @endTimestamp)
582
+ AND (@ipAddress is NULL OR (meta.name = 'ClientIP' AND find_in_set(meta.value, @ipAddress) > 0))
583
  HAVING user_login IS NOT NULL) ip_logins
584
+ WHERE user_login NOT IN ('Website Visitor', 'Plugins', 'Plugin')
585
  ORDER BY user_login ASC
586
  ";
587
  $_wpdb->query("SET @siteId = $_siteId");
588
+ $_wpdb->query("SET @userId = $_userId");
589
+ $_wpdb->query("SET @roleName = $_roleName");
590
  $_wpdb->query("SET @alertCode = $_alertCode");
591
  $_wpdb->query("SET @startTimestamp = $_startTimestamp");
592
  $_wpdb->query("SET @endTimestamp = $_endTimestamp");
593
+ $_wpdb->query("SET @ipAddress = $_ipAddress");
594
  if (!empty($_limit)) {
595
  $sql .= " LIMIT {$_limit}";
596
  }
621
 
622
  return $grouped_types;
623
  }
624
+
625
+ /**
626
+ * TRUNCATE temp table `tmp_users` and populate with users
627
+ * It is used in the query of the above function
628
+ */
629
+ private function TempUsers($tableUsers)
630
+ {
631
+ $_wpdb = $this->connection;
632
+ $sql = "DELETE FROM $tableUsers";
633
+ $_wpdb->query($sql);
634
+
635
+ $sql = "INSERT INTO $tableUsers (ID, user_login) VALUES " ;
636
+ $users = get_users(array('fields' => array('ID', 'user_login')));
637
+ foreach ($users as $user) {
638
+ $sql .= '('. $user->ID .', \''. $user->user_login .'\'), ';
639
+ }
640
+ $sql = rtrim($sql, ", ");
641
+ $_wpdb->query($sql);
642
+ }
643
  }
classes/Adapters/MySQL/QueryAdapter.php CHANGED
@@ -16,7 +16,6 @@ class WSAL_Adapters_MySQL_Query implements WSAL_Adapters_QueryInterface
16
  {
17
  $conditions = $query->getConditions();
18
  $searchCondition = $this->SearchCondition($query);
19
-
20
  $sWhereClause = "";
21
  foreach ($conditions as $fieldName => $fieldValue) {
22
  if (empty($sWhereClause)) {
@@ -27,12 +26,22 @@ class WSAL_Adapters_MySQL_Query implements WSAL_Adapters_QueryInterface
27
 
28
  if (is_array($fieldValue)) {
29
  $subWhereClause = "(";
30
- foreach($fieldValue as $orFieldName => $orFieldValue) {
31
- if ($subWhereClause != '(') {
32
- $subWhereClause .= " OR ";
 
 
 
 
 
 
 
 
 
 
 
 
33
  }
34
- $subWhereClause .= $orFieldName;
35
- $args[] = $orFieldValue;
36
  }
37
  $subWhereClause .= ")";
38
  $sWhereClause .= $subWhereClause;
@@ -64,7 +73,8 @@ class WSAL_Adapters_MySQL_Query implements WSAL_Adapters_QueryInterface
64
  if (!empty($searchCondition)) {
65
  $args[] = $searchCondition['args'];
66
  }
67
- return 'SELECT ' . $fields
 
68
  . ' FROM ' . implode(',', $fromDataSets)
69
  . $joinClause
70
  . $sWhereClause
@@ -72,6 +82,7 @@ class WSAL_Adapters_MySQL_Query implements WSAL_Adapters_QueryInterface
72
  // @todo GROUP BY goes here
73
  . (!empty($orderBys) ? (' ORDER BY ' . implode(', ', array_keys($orderBys)) . ' ' . implode(', ', array_values($orderBys))) : '')
74
  . $sLimitClause;
 
75
  }
76
 
77
  protected function getActiveRecordAdapter()
16
  {
17
  $conditions = $query->getConditions();
18
  $searchCondition = $this->SearchCondition($query);
 
19
  $sWhereClause = "";
20
  foreach ($conditions as $fieldName => $fieldValue) {
21
  if (empty($sWhereClause)) {
26
 
27
  if (is_array($fieldValue)) {
28
  $subWhereClause = "(";
29
+ foreach ($fieldValue as $orFieldName => $orFieldValue) {
30
+ if (is_array($orFieldValue)) {
31
+ foreach ($orFieldValue as $value) {
32
+ if ($subWhereClause != '(') {
33
+ $subWhereClause .= " OR ";
34
+ }
35
+ $subWhereClause .= $orFieldName;
36
+ $args[] = $value;
37
+ }
38
+ } else {
39
+ if ($subWhereClause != '(') {
40
+ $subWhereClause .= " OR ";
41
+ }
42
+ $subWhereClause .= $orFieldName;
43
+ $args[] = $orFieldValue;
44
  }
 
 
45
  }
46
  $subWhereClause .= ")";
47
  $sWhereClause .= $subWhereClause;
73
  if (!empty($searchCondition)) {
74
  $args[] = $searchCondition['args'];
75
  }
76
+
77
+ $sql = 'SELECT ' . $fields
78
  . ' FROM ' . implode(',', $fromDataSets)
79
  . $joinClause
80
  . $sWhereClause
82
  // @todo GROUP BY goes here
83
  . (!empty($orderBys) ? (' ORDER BY ' . implode(', ', array_keys($orderBys)) . ' ' . implode(', ', array_values($orderBys))) : '')
84
  . $sLimitClause;
85
+ return $sql;
86
  }
87
 
88
  protected function getActiveRecordAdapter()
classes/Adapters/MySQL/TmpUserAdapter.php ADDED
@@ -0,0 +1,34 @@
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1
+ <?php
2
+
3
+ class WSAL_Adapters_MySQL_TmpUser extends WSAL_Adapters_MySQL_ActiveRecord {
4
+
5
+ protected $_table = 'wsal_tmp_users';
6
+
7
+ public function GetModel()
8
+ {
9
+ return new WSAL_Models_Meta();
10
+ }
11
+
12
+ public function __construct($conn)
13
+ {
14
+ parent::__construct($conn);
15
+ }
16
+
17
+ /**
18
+ * @return string Must return SQL for creating table.
19
+ */
20
+ protected function _GetInstallQuery($prefix = false)
21
+ {
22
+ $_wpdb = $this->connection;
23
+ $table_name = ($prefix) ? $this->GetWPTable() : $this->GetTable();
24
+ $sql = 'CREATE TABLE IF NOT EXISTS ' . $table_name . ' (' . PHP_EOL;
25
+ $sql .= 'ID BIGINT NOT NULL,' . PHP_EOL;
26
+ $sql .= 'user_login VARCHAR(60) NOT NULL,' . PHP_EOL;
27
+ $sql .= 'INDEX (ID)' . PHP_EOL;
28
+ $sql .= ')';
29
+ if (!empty($_wpdb->charset)) {
30
+ $sql .= ' DEFAULT CHARACTER SET ' . $_wpdb->charset;
31
+ }
32
+ return $sql;
33
+ }
34
+ }
classes/Connector/AbstractConnector.php CHANGED
@@ -23,6 +23,7 @@ abstract class WSAL_Connector_AbstractConnector
23
  require_once($this->getAdaptersDirectory() . DIRECTORY_SEPARATOR . 'MetaAdapter.php');
24
  require_once($this->getAdaptersDirectory() . DIRECTORY_SEPARATOR . 'OccurrenceAdapter.php');
25
  require_once($this->getAdaptersDirectory() . DIRECTORY_SEPARATOR . 'QueryAdapter.php');
 
26
  }
27
  }
28
 
23
  require_once($this->getAdaptersDirectory() . DIRECTORY_SEPARATOR . 'MetaAdapter.php');
24
  require_once($this->getAdaptersDirectory() . DIRECTORY_SEPARATOR . 'OccurrenceAdapter.php');
25
  require_once($this->getAdaptersDirectory() . DIRECTORY_SEPARATOR . 'QueryAdapter.php');
26
+ require_once($this->getAdaptersDirectory() . DIRECTORY_SEPARATOR . 'TmpUserAdapter.php');
27
  }
28
  }
29
 
classes/Connector/MySQLDB.php CHANGED
@@ -114,6 +114,10 @@ class WSAL_Connector_MySQLDB extends WSAL_Connector_AbstractConnector implements
114
  if ($excludeOptions && $class instanceof WSAL_Adapters_MySQL_Option) {
115
  continue;
116
  }
 
 
 
 
117
 
118
  if (is_subclass_of($class, "WSAL_Adapters_MySQL_ActiveRecord")) {
119
  $class->Install();
114
  if ($excludeOptions && $class instanceof WSAL_Adapters_MySQL_Option) {
115
  continue;
116
  }
117
+ // exclude the tmp_users table
118
+ if (!$excludeOptions && $class instanceof WSAL_Adapters_MySQL_TmpUser) {
119
+ continue;
120
+ }
121
 
122
  if (is_subclass_of($class, "WSAL_Adapters_MySQL_ActiveRecord")) {
123
  $class->Install();
classes/Models/TmpUser.php ADDED
@@ -0,0 +1,9 @@
 
 
 
 
 
 
 
 
 
1
+ <?php
2
+
3
+ class WSAL_Models_TmpUser extends WSAL_Models_ActiveRecord {
4
+
5
+ protected $adapterName = "TmpUser";
6
+
7
+ public $id = 0;
8
+ public $user_login = '';
9
+ }
readme.txt CHANGED
@@ -6,8 +6,8 @@ License: GPLv3
6
  License URI: http://www.gnu.org/licenses/gpl.html
7
  Tags: wordpress security plugin, wordpress security audit log, audit log, wordpress log, event log wordpress, wordpress user tracking, wordpress activity log, wordpress audit, security event log, audit trail, security audit trail, wordpress security alerts, wordpress monitor, wordpress security monitor, wordpress admin, wordpress admin monitoring, analytics, activity, admin, multisite, wordpress multisite, actions, dashboard, log, notification, wordpress monitoring, email notification, wordpress email alerts, tracking, user tracking, user activity report, wordpress audit trail
8
  Requires at least: 3.6
9
- Tested up to: 4.7.4
10
- Stable tag: 2.6.3
11
 
12
  Keep an audit trail of all changes and under the hood WordPress activity to ensure productivity and thwart possible WordPress hacker attacks.
13
 
@@ -186,6 +186,11 @@ Please refer to the [FAQs page](https://www.wpsecurityauditlog.com/documentation
186
 
187
  == Changelog ==
188
 
 
 
 
 
 
189
  = 2.6.3 (2017-05-03) =
190
 
191
  * **Security Update**
6
  License URI: http://www.gnu.org/licenses/gpl.html
7
  Tags: wordpress security plugin, wordpress security audit log, audit log, wordpress log, event log wordpress, wordpress user tracking, wordpress activity log, wordpress audit, security event log, audit trail, security audit trail, wordpress security alerts, wordpress monitor, wordpress security monitor, wordpress admin, wordpress admin monitoring, analytics, activity, admin, multisite, wordpress multisite, actions, dashboard, log, notification, wordpress monitoring, email notification, wordpress email alerts, tracking, user tracking, user activity report, wordpress audit trail
8
  Requires at least: 3.6
9
+ Tested up to: 4.8
10
+ Stable tag: 2.6.4
11
 
12
  Keep an audit trail of all changes and under the hood WordPress activity to ensure productivity and thwart possible WordPress hacker attacks.
13
 
186
 
187
  == Changelog ==
188
 
189
+ = 2.6.4 (2017-06-01) =
190
+
191
+ * **New Features**
192
+ * Added a number of queries in the plugin to support the new version of the [Reports Add-On](https://www.wpsecurityauditlog.com/wordpress-user-monitoring-plugin-releases/statistics-scheduled-wordpress-reports/)
193
+
194
  = 2.6.3 (2017-05-03) =
195
 
196
  * **Security Update**
wp-security-audit-log.php CHANGED
@@ -4,7 +4,7 @@ Plugin Name: WP Security Audit Log
4
  Plugin URI: http://www.wpsecurityauditlog.com/
5
  Description: Identify WordPress security issues before they become a problem. Keep track of everything happening on your WordPress including WordPress users activity. Similar to Windows Event Log and Linux Syslog, WP Security Audit Log generates a security alert for everything that happens on your WordPress blogs and websites. Use the Audit Log Viewer included in the plugin to see all the security alerts.
6
  Author: WP White Security
7
- Version: 2.6.3
8
  Text Domain: wp-security-audit-log
9
  Author URI: http://www.wpsecurityauditlog.com/
10
  License: GPL2
@@ -125,6 +125,7 @@ class WpSecurityAuditLog {
125
  require_once('classes/Models/Query.php');
126
  require_once('classes/Models/OccurrenceQuery.php');
127
  require_once('classes/Models/Option.php');
 
128
 
129
  // Use WP_Session (default)
130
  if (!defined('WP_SESSION_COOKIE')) {
4
  Plugin URI: http://www.wpsecurityauditlog.com/
5
  Description: Identify WordPress security issues before they become a problem. Keep track of everything happening on your WordPress including WordPress users activity. Similar to Windows Event Log and Linux Syslog, WP Security Audit Log generates a security alert for everything that happens on your WordPress blogs and websites. Use the Audit Log Viewer included in the plugin to see all the security alerts.
6
  Author: WP White Security
7
+ Version: 2.6.4
8
  Text Domain: wp-security-audit-log
9
  Author URI: http://www.wpsecurityauditlog.com/
10
  License: GPL2
125
  require_once('classes/Models/Query.php');
126
  require_once('classes/Models/OccurrenceQuery.php');
127
  require_once('classes/Models/Option.php');
128
+ require_once('classes/Models/TmpUser.php');
129
 
130
  // Use WP_Session (default)
131
  if (!defined('WP_SESSION_COOKIE')) {