Page MenuHomePhabricator
Files F183775

0001-SECURITY-Add-img_deleted-column-b.patch
Tgr (Gergő Tisza)
Actions

Authored By
Tgr
Jun 26 2015, 10:22 PM
Size
9 KB
Referenced Files
None
Subscribers
None

0001-SECURITY-Add-img_deleted-column-b.patch
View Options

From f96a1111902b0a345e34e40e8c5ff8f4f754fa2a Mon Sep 17 00:00:00 2001
From: csteipp <csteipp@wikimedia.org>
Date: Mon, 11 May 2015 15:11:23 -0700
Subject: [PATCH] SECURITY: Add img_deleted column
Add an img_deleted column to the image table, similar to oi_deleted
in oldimage and fa_deleted in filearchive, to handle hiding parts
of the latest image version (especially user suppression).
Includes a query to set the value to 12 (user deleted + suppressed)
if a block with a suppression (ipb_deleted) flag exists for the
uploader. Also updates oldimage / filearchive as the image -> oi/fa
transition could have caused incorrect values to be recorded there.
Bug: T90300
Change-Id: Ifb928a0592348f2d600ed4e290f9e309797c4c81
---
includes/installer/MssqlUpdater.php | 2 ++
includes/installer/MysqlUpdater.php | 1 +
includes/installer/OracleUpdater.php | 1 +
includes/installer/PostgresUpdater.php | 3 ++-
includes/installer/SqliteUpdater.php | 1 +
maintenance/archives/patch-img_deleted.sql | 37 ++++++++++++++++++++++++++++++
maintenance/mssql/tables.sql | 3 +++
maintenance/oracle/tables.sql | 3 ++-
maintenance/postgres/tables.sql | 3 ++-
maintenance/tables.sql | 5 +++-
10 files changed, 55 insertions(+), 4 deletions(-)
create mode 100644 maintenance/archives/patch-img_deleted.sql
diff --git a/includes/installer/MssqlUpdater.php b/includes/installer/MssqlUpdater.php
index 5eef335..4a280e5 100644
--- a/includes/installer/MssqlUpdater.php
+++ b/includes/installer/MssqlUpdater.php
@@ -47,6 +47,8 @@ class MssqlUpdater extends DatabaseUpdater {
array( 'dropTable', 'hitcounter' ),
array( 'dropField', 'site_stats', 'ss_total_views', 'patch-drop-ss_total_views.sql' ),
array( 'dropField', 'page', 'page_counter', 'patch-drop-page_counter.sql' ),
+ array( 'addField', 'image', 'img_deleted', 'patch-img_deleted.sql' ),
+
// Constraint updates
array( 'updateConstraints', 'category_types', 'categorylinks', 'cl_type' ),
array( 'updateConstraints', 'major_mime', 'filearchive', 'fa_major_mime' ),
diff --git a/includes/installer/MysqlUpdater.php b/includes/installer/MysqlUpdater.php
index 36d2c1d..9438718 100644
--- a/includes/installer/MysqlUpdater.php
+++ b/includes/installer/MysqlUpdater.php
@@ -271,6 +271,7 @@ class MysqlUpdater extends DatabaseUpdater {
array( 'dropField', 'site_stats', 'ss_total_views', 'patch-drop-ss_total_views.sql' ),
array( 'dropField', 'page', 'page_counter', 'patch-drop-page_counter.sql' ),
array( 'doUserNewTalkUseridUnsigned' ),
+ array( 'addField', 'image', 'img_deleted', 'patch-img_deleted.sql' ),
// note this patch covers other _comment and _description fields too
array( 'modifyField', 'recentchanges', 'rc_comment', 'patch-editsummary-length.sql' ),
);
diff --git a/includes/installer/OracleUpdater.php b/includes/installer/OracleUpdater.php
index 03dbd1c..327e503 100644
--- a/includes/installer/OracleUpdater.php
+++ b/includes/installer/OracleUpdater.php
@@ -107,6 +107,7 @@ class OracleUpdater extends DatabaseUpdater {
array( 'dropTable', 'hitcounter' ),
array( 'dropField', 'site_stats', 'ss_total_views', 'patch-drop-ss_total_views.sql' ),
array( 'dropField', 'page', 'page_counter', 'patch-drop-page_counter.sql' ),
+ array( 'addField', 'image', 'img_deleted', 'patch-img_deleted.sql' ),
// KEEP THIS AT THE BOTTOM!!
array( 'doRebuildDuplicateFunction' ),
diff --git a/includes/installer/PostgresUpdater.php b/includes/installer/PostgresUpdater.php
index 6ac5436..6d66794 100644
--- a/includes/installer/PostgresUpdater.php
+++ b/includes/installer/PostgresUpdater.php
@@ -421,7 +421,8 @@ class PostgresUpdater extends DatabaseUpdater {
array( 'dropTable', 'hitcounter' ),
array( 'dropField', 'site_stats', 'ss_total_views', 'patch-drop-ss_total_views.sql' ),
array( 'dropField', 'page', 'page_counter', 'patch-drop-page_counter.sql' ),
- array( 'dropFkey', 'recentchanges', 'rc_cur_id' )
+ array( 'dropFkey', 'recentchanges', 'rc_cur_id' ),
+ array( 'addField', 'image', 'img_deleted', 'patch-img_deleted.sql' ),
);
}
diff --git a/includes/installer/SqliteUpdater.php b/includes/installer/SqliteUpdater.php
index 2693be0..ac976ea 100644
--- a/includes/installer/SqliteUpdater.php
+++ b/includes/installer/SqliteUpdater.php
@@ -142,6 +142,7 @@ class SqliteUpdater extends DatabaseUpdater {
array( 'dropTable', 'hitcounter' ),
array( 'dropField', 'site_stats', 'ss_total_views', 'patch-drop-ss_total_views.sql' ),
array( 'dropField', 'page', 'page_counter', 'patch-drop-page_counter.sql' ),
+ array( 'addField', 'image', 'img_deleted', 'patch-img_deleted.sql' ),
array( 'modifyField', 'filearchive', 'fa_deleted_reason', 'patch-editsummary-length.sql' ),
);
}
diff --git a/maintenance/archives/patch-img_deleted.sql b/maintenance/archives/patch-img_deleted.sql
new file mode 100644
index 0000000..ec41ddd
--- /dev/null
+++ b/maintenance/archives/patch-img_deleted.sql
@@ -0,0 +1,37 @@
+-- Add img_deleted - a RevisionDelete field for the current version of the file
+ALTER TABLE /*_*/image ADD img_deleted tinyint unsigned NOT NULL default 0;
+
+-- Mark image table entries as suppressed if they belong to a suppressed user.
+--
+-- This can be slow (converted to a select it ran for 10m for Commons on the
+-- research DB, even though the result set was <500) as neither ipb_deleted
+-- not img_user is indexed. Joining on the username would be much faster but
+-- would probably miss users who have been renamed.
+UPDATE /*_*/image
+SET img_deleted = 12 -- Revision::DELETED_USER + Revision::DELETED_RESTRICTED
+WHERE img_user_text IN (
+ SELECT ipb_address
+ FROM /*_*/ipblocks
+ WHERE ipb_deleted
+);
+
+-- Make sure user suppression did not get lost on oldimage and filearchive
+-- tables. Since image did not have suppression support until now, if a user
+-- uploaded an image, was suppressed, and then someone else uploaded a new
+-- version or deleted the image, the image revision was moved to oi/fa with
+-- no suppression bit set.
+UPDATE /*_*/oldimage
+SET oi_deleted = 12 | oi_deleted
+WHERE oi_user_text IN (
+ SELECT ipb_address
+ FROM /*_*/ipblocks
+ WHERE ipb_deleted
+);
+UPDATE /*_*/filearchive
+SET fa_deleted = 12 | fa_deleted
+WHERE fa_user_text IN (
+ SELECT ipb_address
+ FROM /*_*/ipblocks
+ WHERE ipb_deleted
+);
+
diff --git a/maintenance/mssql/tables.sql b/maintenance/mssql/tables.sql
index 5b09ffd..c4b49a6 100644
--- a/maintenance/mssql/tables.sql
+++ b/maintenance/mssql/tables.sql
@@ -578,6 +578,9 @@ CREATE TABLE /*_*/image (
-- SHA-1 content hash in base-36
img_sha1 nvarchar(32) NOT NULL default '',
+ -- Visibility of deleted revisions, bitfield
+ img_deleted tinyint NOT NULL default 0,
+
CONSTRAINT img_major_mime_ckc check (img_major_mime IN('unknown', 'application', 'audio', 'image', 'text', 'video', 'message', 'model', 'multipart', 'chemical')),
CONSTRAINT img_media_type_ckc check (img_media_type in('UNKNOWN', 'BITMAP', 'DRAWING', 'AUDIO', 'VIDEO', 'MULTIMEDIA', 'OFFICE', 'TEXT', 'EXECUTABLE', 'ARCHIVE'))
);
diff --git a/maintenance/oracle/tables.sql b/maintenance/oracle/tables.sql
index 12f6518..c25e826 100644
--- a/maintenance/oracle/tables.sql
+++ b/maintenance/oracle/tables.sql
@@ -300,7 +300,8 @@ CREATE TABLE &mw_prefix.image (
img_user NUMBER DEFAULT 0 NOT NULL,
img_user_text VARCHAR2(255) NOT NULL,
img_timestamp TIMESTAMP(6) WITH TIME ZONE,
- img_sha1 VARCHAR2(32)
+ img_sha1 VARCHAR2(32),
+ img_deleted NUMBER DEFAULT 0 NOT NULL
);
ALTER TABLE &mw_prefix.image ADD CONSTRAINT &mw_prefix.image_pk PRIMARY KEY (img_name);
ALTER TABLE &mw_prefix.image ADD CONSTRAINT &mw_prefix.image_fk1 FOREIGN KEY (img_user) REFERENCES &mw_prefix.mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED;
diff --git a/maintenance/postgres/tables.sql b/maintenance/postgres/tables.sql
index 6076206..4b12a89 100644
--- a/maintenance/postgres/tables.sql
+++ b/maintenance/postgres/tables.sql
@@ -313,7 +313,8 @@ CREATE TABLE image (
img_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
img_user_text TEXT NOT NULL,
img_timestamp TIMESTAMPTZ,
- img_sha1 TEXT NOT NULL DEFAULT ''
+ img_sha1 TEXT NOT NULL DEFAULT '',
+ img_deleted SMALLINT NOT NULL DEFAULT 0
);
CREATE INDEX img_size_idx ON image (img_size);
CREATE INDEX img_timestamp_idx ON image (img_timestamp);
diff --git a/maintenance/tables.sql b/maintenance/tables.sql
index de36d26..67888e6 100644
--- a/maintenance/tables.sql
+++ b/maintenance/tables.sql
@@ -856,7 +856,10 @@ CREATE TABLE /*_*/image (
img_timestamp varbinary(14) NOT NULL default '',
-- SHA-1 content hash in base-36
- img_sha1 varbinary(32) NOT NULL default ''
+ img_sha1 varbinary(32) NOT NULL default '',
+
+ -- Visibility of deleted revisions, bitfield
+ img_deleted tinyint unsigned NOT NULL default 0
) /*$wgDBTableOptions*/;
CREATE INDEX /*i*/img_usertext_timestamp ON /*_*/image (img_user_text,img_timestamp);
--
1.8.4.5

File Metadata

Mime Type
text/x-diff
Storage Engine
blob
Storage Format
Raw Data
Storage Handle
176458
Default Alt Text
0001-SECURITY-Add-img_deleted-column-b.patch (9 KB)

Event Timeline

Content licensed under Creative Commons Attribution-ShareAlike (CC BY-SA) 4.0 unless otherwise noted; code licensed under GNU General Public License (GPL) 2.0 or later and other open source licenses. By using this site, you agree to the Terms of Use, Privacy Policy, and Code of Conduct. · Wikimedia Foundation · Privacy Policy · Code of Conduct · Terms of Use · Disclaimer · CC-BY-SA · GPL