RE: [xwiki-users] delete spam comments
Hi, Brian, Thanks a lot. My site is on Xwiki.com, can I do all these steps? Is there a way just to stop showing comments right now? Wei-hsing -------------- Original message ---------------------- From: "THOMAS, BRIAN M (SBCSI)" <[email protected]>
Direct database time...
First, BACK UP your database.
No. First, if you are not an SQL hack, get someone who is.
Now, BACK UP your database.
Next, you must delete all rows connected with spam comments.
I'm going to derive the process whereby you do this, because I'm a little rusty on the XWiki database schemas, and because I'm an inveterate pedant. You can skip some of these steps that exist for information only if you wish, especially if you already know how to do them.
First, it's necessary to know which rows constitute comments. XWiki's dynamic typing is a great boon to users and an equally great bane to administrators, as you will soon see. The xwikiclasses table describes user-defined classes (well, actually it doesn't; it names them, and describes server-defined custom classes if there are any, which fortunately there aren't, because I couldn't tell you what to do if there were). The class that defines XWiki comments is named XWiki.XWikiComments. The xwikiclassesprop table contains the list of fields for a given class, which is identified not by its name but by the unique ID from the xwikiclasses table. So you need to get the ID of the row in the xwikiclasses table whose name field is 'XWiki.XWikiComments', and retrieve the field name and type from all rows of the xwikiclassesprop table that have that ID. The following SQL does this less verbosely:
select xwp_name, xwp_classtype from xwikiclassesprop p, xwikiclasses c where xwo_name = 'XWiki.XWikiComments' and p.xwp_id = c.xwo_id
The results of this should be: +-----------+---------------------------------------------+ | xwp_name | xwp_classtype | +-----------+---------------------------------------------+ | author | com.xpn.xwiki.objects.classes.StringClass | | comment | com.xpn.xwiki.objects.classes.TextAreaClass | | date | com.xpn.xwiki.objects.classes.DateClass | | highlight | com.xpn.xwiki.objects.classes.TextAreaClass | | replyto | com.xpn.xwiki.objects.classes.NumberClass | +-----------+---------------------------------------------+ 5 rows in set (0.00 sec)
You'd have to go into the XWiki source (more specifically, the Hibernate configuration, I believe) to see how the xwp_classtype field above links to what I'm doing below; I leave that as an exercise for the reader. All that's really needed is to know the types of the fields and which tables they're stored in.
I've actually misled you somewhat: this is not the actual schema of existing comment objects but the prototype for the creation of new comments. I've done this ostensibly to give you a fuller understanding of how XWiki allows one to change the definition of a user-defined class without breaking existing instances, but it's really because I got tripped up on it myself, and I wanted to share my misery with you.
To get the actual schema for existing comments you have to look at the xwikiobjects table, which defines actual XWiki object instances in terms of their class names and the document to which they belong. The xwo_id field is the field that ties all of the tables together.
So the following query:
select distinct(xwp_name), xwp_classtype from xwikiobjects o, xwikiproperties p where xwo_classname='XWiki.XWikiComments' and xwo_id = xwp_id
yields a similar-looking result (in my database, anyway), because the XWiki.XWikiComments class hasn't changed since these comments were added: +-----------+-------------------------------------------+ | xwp_name | xwp_classtype | +-----------+-------------------------------------------+ | author | com.xpn.xwiki.objects.StringProperty | | date | com.xpn.xwiki.objects.DateProperty | | comment | com.xpn.xwiki.objects.LargeStringProperty | | replyto | com.xpn.xwiki.objects.IntegerProperty | | highlight | com.xpn.xwiki.objects.LargeStringProperty | +-----------+-------------------------------------------+ 5 rows in set (0.05 sec)
So now we know (without explaining how) that (for example) the contents of the "author" field of an instance of the class named XWiki.XWikiComments is in the xwikistrings table, in a row whose xws_id field matches the xwo_id field of an xwikiobjects row whose xwo_classname file is "XWiki.XWikiComments". All the field values can be found thus:
field name table name field value author xwikistrings xws_value date xwikidates xws_value comment xwikilargestrings xwl_value replyto xwikiintegers xwi_value highlight xwikilargestrings xwl_value
So all the fields of a comment would be retrieved, given its ID, by the following query:
select s.xws_value, /* author name */ d.xws_value, /* comment date */ l.xws_value, /* comment text */ i.xwi_value, /* reply-to field */ h.xwl_value /* highlight field */ from xwikistrings s, xwikidates d, xwikilargestrings l, xwikiintegers i, xwikilargestrings h where s.xws_id = <comment ID> and s.xws_name = 'author' and d.xws_id = s.xws_id and d.xws_name = 'date' and l.xws_id = s.xws_id and l.xws_name = 'comment' and i.xwi_value = s.xws_id and i.xwi_name = 'replyto' and h.xwl_value = s.xws_id and h.xwl_name = 'highlight'
Of course, the job is much simpler than this, for several reasons. First, the replyto and highlight fields are not populated by XWiki's default templates, so they're always null anyway; second, of the remaining values, each one is uniquely identified by the object's ID field anyway, so the respective name fields don't need to be specified.
To get a list of all comments in the database, with only their object ID field, author name, and date (which produces a fairly neat display) this query should do it.
select o.xwo_id, s.xws_value, d.xws_value from xwikiobjects o, xwikistrings s, xwikidates d where xwo_classname='XWiki.XWikiComments' and s.xws_id = o.xwo_id and d.xws_id = o.xwo_id order by xwo_id
With all that as background, what you need to do is:
1. Identify the offending comments (by their object IDs).
If the comments were all from bogus registrations, then the above query with an added qualifier something like:
where s.xws_value in ('XWiki.spammer1', 'XWiki.spammer2'[,...])
will give you the object IDs you need.
If you have allowed anonymous users to comment, then other criteria must be used, although of course "s.xws_value = 'XWiki.XWikiGuest'" should be in your WHERE clause. Most likely, you could probably nail most of them by saying
select o.xwo_id from xwikiobjects o, xwikilargestrings l where o.classname = 'XWiki.XWikiComments' and l.xwl_id = o.xwo_id and l.xwl_value like "%Viagra%" or l.xwl_value like "%sex" or l.xwl_value like "%stock%" or ...
you get the idea.
The best idea, of course, is to keep running and refining your query until you are sure that you've identified all of the offending comments and only the offending comments.
2. After you've built a query that identifies the set of records you want to remove, you must delete them from each table where they appear. The easiest way to do this is to modify your query to return only the xwikiobjects.xwo_id field in the SELECT clause and put it into a temporary table:
create temporary table badcomments (comment_id integer);
insert into badcomments select o.xwo_id from xwikiobjects o [... where, etc...];
Then delete every row from xwikistrings, xwikilargestrings, xwikidates, and xwikiintegers where the respective ID fields (xws_id, xwl_id, xws_id, and xwi_id, respectively) match the comment_id field from your badcomments table.
You can also do it using the $xwiki.search() method, but there you have to tie the XWiki objects together using HQL. The advantage is that you don't have to have server access; the disadvantage is that it's miserable to get it right (my opinion).
brain[sic]
-----Original Message----- From: [email protected] [mailto:[email protected]] Sent: Tuesday, October 17, 2006 12:32 AM To: [email protected] Subject: [xwiki-users] delete spam comments
Hi, My wiki site got over a thousand spam comments, anyone know how can I delete them quickly (better than edit object, then delete one by one). Wei-hsing
Hi, we have some scripts for this, but they require the programming right. Can you send me the adress of your wiki, I will install it on your wiki. It's possible with the new api of xwiki to rewrite this script to be written without programming right. jeremi On 10/17/06, [email protected] <[email protected]> wrote:
Hi, Brian, Thanks a lot. My site is on Xwiki.com, can I do all these steps? Is there a way just to stop showing comments right now? Wei-hsing
-------------- Original message ---------------------- From: "THOMAS, BRIAN M (SBCSI)" <[email protected]>
Direct database time...
First, BACK UP your database.
No. First, if you are not an SQL hack, get someone who is.
Now, BACK UP your database.
Next, you must delete all rows connected with spam comments.
I'm going to derive the process whereby you do this, because I'm a little rusty on the XWiki database schemas, and because I'm an inveterate pedant. You can skip some of these steps that exist for information only if you wish, especially if you already know how to do them.
First, it's necessary to know which rows constitute comments. XWiki's dynamic typing is a great boon to users and an equally great bane to administrators, as you will soon see. The xwikiclasses table describes user-defined classes (well, actually it doesn't; it names them, and describes server-defined custom classes if there are any, which fortunately there aren't, because I couldn't tell you what to do if there were). The class that defines XWiki comments is named XWiki.XWikiComments. The xwikiclassesprop table contains the list of fields for a given class, which is identified not by its name but by the unique ID from the xwikiclasses table. So you need to get the ID of the row in the xwikiclasses table whose name field is 'XWiki.XWikiComments', and retrieve the field name and type from all rows of the xwikiclassesprop table that have that ID. The following SQL does this less verbosely:
select xwp_name, xwp_classtype from xwikiclassesprop p, xwikiclasses c where xwo_name = 'XWiki.XWikiComments' and p.xwp_id = c.xwo_id
The results of this should be: +-----------+---------------------------------------------+ | xwp_name | xwp_classtype | +-----------+---------------------------------------------+ | author | com.xpn.xwiki.objects.classes.StringClass | | comment | com.xpn.xwiki.objects.classes.TextAreaClass | | date | com.xpn.xwiki.objects.classes.DateClass | | highlight | com.xpn.xwiki.objects.classes.TextAreaClass | | replyto | com.xpn.xwiki.objects.classes.NumberClass | +-----------+---------------------------------------------+ 5 rows in set (0.00 sec)
You'd have to go into the XWiki source (more specifically, the Hibernate configuration, I believe) to see how the xwp_classtype field above links to what I'm doing below; I leave that as an exercise for the reader. All that's really needed is to know the types of the fields and which tables they're stored in.
I've actually misled you somewhat: this is not the actual schema of existing comment objects but the prototype for the creation of new comments. I've done this ostensibly to give you a fuller understanding of how XWiki allows one to change the definition of a user-defined class without breaking existing instances, but it's really because I got tripped up on it myself, and I wanted to share my misery with you.
To get the actual schema for existing comments you have to look at the xwikiobjects table, which defines actual XWiki object instances in terms of their class names and the document to which they belong. The xwo_id field is the field that ties all of the tables together.
So the following query:
select distinct(xwp_name), xwp_classtype from xwikiobjects o, xwikiproperties p where xwo_classname='XWiki.XWikiComments' and xwo_id = xwp_id
yields a similar-looking result (in my database, anyway), because the XWiki.XWikiComments class hasn't changed since these comments were added: +-----------+-------------------------------------------+ | xwp_name | xwp_classtype | +-----------+-------------------------------------------+ | author | com.xpn.xwiki.objects.StringProperty | | date | com.xpn.xwiki.objects.DateProperty | | comment | com.xpn.xwiki.objects.LargeStringProperty | | replyto | com.xpn.xwiki.objects.IntegerProperty | | highlight | com.xpn.xwiki.objects.LargeStringProperty | +-----------+-------------------------------------------+ 5 rows in set (0.05 sec)
So now we know (without explaining how) that (for example) the contents of the "author" field of an instance of the class named XWiki.XWikiComments is in the xwikistrings table, in a row whose xws_id field matches the xwo_id field of an xwikiobjects row whose xwo_classname file is "XWiki.XWikiComments". All the field values can be found thus:
field name table name field value author xwikistrings xws_value date xwikidates xws_value comment xwikilargestrings xwl_value replyto xwikiintegers xwi_value highlight xwikilargestrings xwl_value
So all the fields of a comment would be retrieved, given its ID, by the following query:
select s.xws_value, /* author name */ d.xws_value, /* comment date */ l.xws_value, /* comment text */ i.xwi_value, /* reply-to field */ h.xwl_value /* highlight field */ from xwikistrings s, xwikidates d, xwikilargestrings l, xwikiintegers i, xwikilargestrings h where s.xws_id = <comment ID> and s.xws_name = 'author' and d.xws_id = s.xws_id and d.xws_name = 'date' and l.xws_id = s.xws_id and l.xws_name = 'comment' and i.xwi_value = s.xws_id and i.xwi_name = 'replyto' and h.xwl_value = s.xws_id and h.xwl_name = 'highlight'
Of course, the job is much simpler than this, for several reasons. First, the replyto and highlight fields are not populated by XWiki's default templates, so they're always null anyway; second, of the remaining values, each one is uniquely identified by the object's ID field anyway, so the respective name fields don't need to be specified.
To get a list of all comments in the database, with only their object ID field, author name, and date (which produces a fairly neat display) this query should do it.
select o.xwo_id, s.xws_value, d.xws_value from xwikiobjects o, xwikistrings s, xwikidates d where xwo_classname='XWiki.XWikiComments' and s.xws_id = o.xwo_id and d.xws_id = o.xwo_id order by xwo_id
With all that as background, what you need to do is:
1. Identify the offending comments (by their object IDs).
If the comments were all from bogus registrations, then the above query with an added qualifier something like:
where s.xws_value in ('XWiki.spammer1', 'XWiki.spammer2'[,...])
will give you the object IDs you need.
If you have allowed anonymous users to comment, then other criteria must be used, although of course "s.xws_value = 'XWiki.XWikiGuest'" should be in your WHERE clause. Most likely, you could probably nail most of them by saying
select o.xwo_id from xwikiobjects o, xwikilargestrings l where o.classname = 'XWiki.XWikiComments' and l.xwl_id = o.xwo_id and l.xwl_value like "%Viagra%" or l.xwl_value like "%sex" or l.xwl_value like "%stock%" or ...
you get the idea.
The best idea, of course, is to keep running and refining your query until you are sure that you've identified all of the offending comments and only the offending comments.
2. After you've built a query that identifies the set of records you want to remove, you must delete them from each table where they appear. The easiest way to do this is to modify your query to return only the xwikiobjects.xwo_id field in the SELECT clause and put it into a temporary table:
create temporary table badcomments (comment_id integer);
insert into badcomments select o.xwo_id from xwikiobjects o [... where, etc...];
Then delete every row from xwikistrings, xwikilargestrings, xwikidates, and xwikiintegers where the respective ID fields (xws_id, xwl_id, xws_id, and xwi_id, respectively) match the comment_id field from your badcomments table.
You can also do it using the $xwiki.search() method, but there you have to tie the XWiki objects together using HQL. The advantage is that you don't have to have server access; the disadvantage is that it's miserable to get it right (my opinion).
brain[sic]
-----Original Message----- From: [email protected] [mailto:[email protected]] Sent: Tuesday, October 17, 2006 12:32 AM To: [email protected] Subject: [xwiki-users] delete spam comments
Hi, My wiki site got over a thousand spam comments, anyone know how can I delete them quickly (better than edit object, then delete one by one). Wei-hsing
---------- Forwarded message ---------- From: "THOMAS, BRIAN M (SBCSI)" <[email protected]> To: <[email protected]> Date: Tue, 17 Oct 2006 15:11:00 +0000 Subject: RE: [xwiki-users] delete spam comments
-- You receive this message as a subscriber of the [email protected] mailing list. To unsubscribe: mailto:[email protected] For general help: mailto:[email protected]?subject=help ObjectWeb mailing lists service home page: http://www.objectweb.org/wws
-- You receive this message as a subscriber of the [email protected] mailing list. To unsubscribe: mailto:[email protected] For general help: mailto:[email protected]?subject=help ObjectWeb mailing lists service home page: http://www.objectweb.org/wws
-- jeremi
In other words, a backdoor? A security hole? Please more details on this 'code without programming rights'. Jld. -----Original Message----- From: jeremi joslin [mailto:[email protected]] Sent: October 17, 2006 11:45 To: [email protected] Subject: Re: [xwiki-users] delete spam comments Hi, we have some scripts for this, but they require the programming right. Can you send me the adress of your wiki, I will install it on your wiki. It's possible with the new api of xwiki to rewrite this script to be written without programming right. jeremi On 10/17/06, [email protected] <[email protected]> wrote:
Hi, Brian, Thanks a lot. My site is on Xwiki.com, can I do all these steps? Is there a way just to stop showing comments right now? Wei-hsing
-------------- Original message ---------------------- From: "THOMAS, BRIAN M (SBCSI)" <[email protected]>
Direct database time...
First, BACK UP your database.
No. First, if you are not an SQL hack, get someone who is.
Now, BACK UP your database.
Next, you must delete all rows connected with spam comments.
I'm going to derive the process whereby you do this, because I'm a little rusty on the XWiki database schemas, and because I'm an inveterate pedant. You can skip some of these steps that exist for information only if you wish, especially if you already know how to do them.
First, it's necessary to know which rows constitute comments. XWiki's dynamic typing is a great boon to users and an equally great bane to administrators, as you will soon see. The xwikiclasses table describes user-defined classes (well, actually it doesn't; it names them, and describes server-defined custom classes if there are any, which fortunately there aren't, because I couldn't tell you what to do if there were). The class that defines XWiki comments is named XWiki.XWikiComments. The xwikiclassesprop table contains the list of fields for a given class, which is identified not by its name but by the unique ID from the xwikiclasses table. So you need to get the ID of the row in the xwikiclasses table whose name field is 'XWiki.XWikiComments', and retrieve the field name and type from all rows of the xwikiclassesprop table that have that ID. The following SQL does this less verbosely:
select xwp_name, xwp_classtype from xwikiclassesprop p, xwikiclasses c where xwo_name = 'XWiki.XWikiComments' and p.xwp_id = c.xwo_id
The results of this should be: +-----------+---------------------------------------------+ | xwp_name | xwp_classtype | +-----------+---------------------------------------------+ | author | com.xpn.xwiki.objects.classes.StringClass | | comment | com.xpn.xwiki.objects.classes.TextAreaClass | | date | com.xpn.xwiki.objects.classes.DateClass | | highlight | com.xpn.xwiki.objects.classes.TextAreaClass | | replyto | com.xpn.xwiki.objects.classes.NumberClass | +-----------+---------------------------------------------+ 5 rows in set (0.00 sec)
You'd have to go into the XWiki source (more specifically, the Hibernate configuration, I believe) to see how the xwp_classtype field above links to what I'm doing below; I leave that as an exercise for the reader. All that's really needed is to know the types of the fields and which tables they're stored in.
I've actually misled you somewhat: this is not the actual schema of existing comment objects but the prototype for the creation of new comments. I've done this ostensibly to give you a fuller understanding of how XWiki allows one to change the definition of a user-defined class without breaking existing instances, but it's really because I got tripped up on it myself, and I wanted to share my misery with you.
To get the actual schema for existing comments you have to look at the xwikiobjects table, which defines actual XWiki object instances in terms of their class names and the document to which they belong. The xwo_id field is the field that ties all of the tables together.
So the following query:
select distinct(xwp_name), xwp_classtype from xwikiobjects o, xwikiproperties p where xwo_classname='XWiki.XWikiComments' and xwo_id = xwp_id
yields a similar-looking result (in my database, anyway), because the XWiki.XWikiComments class hasn't changed since these comments were added: +-----------+-------------------------------------------+ | xwp_name | xwp_classtype | +-----------+-------------------------------------------+ | author | com.xpn.xwiki.objects.StringProperty | | date | com.xpn.xwiki.objects.DateProperty | | comment | com.xpn.xwiki.objects.LargeStringProperty | | replyto | com.xpn.xwiki.objects.IntegerProperty | | highlight | com.xpn.xwiki.objects.LargeStringProperty | +-----------+-------------------------------------------+ 5 rows in set (0.05 sec)
So now we know (without explaining how) that (for example) the contents of the "author" field of an instance of the class named XWiki.XWikiComments is in the xwikistrings table, in a row whose xws_id field matches the xwo_id field of an xwikiobjects row whose xwo_classname file is "XWiki.XWikiComments". All the field values can be found thus:
field name table name field value author xwikistrings xws_value date xwikidates xws_value comment xwikilargestrings xwl_value replyto xwikiintegers xwi_value highlight xwikilargestrings xwl_value
So all the fields of a comment would be retrieved, given its ID, by the following query:
select s.xws_value, /* author name */ d.xws_value, /* comment date */ l.xws_value, /* comment text */ i.xwi_value, /* reply-to field */ h.xwl_value /* highlight field */ from xwikistrings s, xwikidates d, xwikilargestrings l, xwikiintegers i, xwikilargestrings h where s.xws_id = <comment ID> and s.xws_name = 'author' and d.xws_id = s.xws_id and d.xws_name = 'date' and l.xws_id = s.xws_id and l.xws_name = 'comment' and i.xwi_value = s.xws_id and i.xwi_name = 'replyto' and h.xwl_value = s.xws_id and h.xwl_name = 'highlight'
Of course, the job is much simpler than this, for several reasons. First, the replyto and highlight fields are not populated by XWiki's default templates, so they're always null anyway; second, of the remaining values, each one is uniquely identified by the object's ID field anyway, so the respective name fields don't need to be specified.
To get a list of all comments in the database, with only their object ID field, author name, and date (which produces a fairly neat display) this query should do it.
select o.xwo_id, s.xws_value, d.xws_value from xwikiobjects o, xwikistrings s, xwikidates d where xwo_classname='XWiki.XWikiComments' and s.xws_id = o.xwo_id and d.xws_id = o.xwo_id order by xwo_id
With all that as background, what you need to do is:
1. Identify the offending comments (by their object IDs).
If the comments were all from bogus registrations, then the above query with an added qualifier something like:
where s.xws_value in ('XWiki.spammer1', 'XWiki.spammer2'[,...])
will give you the object IDs you need.
If you have allowed anonymous users to comment, then other criteria must be used, although of course "s.xws_value = 'XWiki.XWikiGuest'" should be in your WHERE clause. Most likely, you could probably nail most of them by saying
select o.xwo_id from xwikiobjects o, xwikilargestrings l where o.classname = 'XWiki.XWikiComments' and l.xwl_id = o.xwo_id and l.xwl_value like "%Viagra%" or l.xwl_value like "%sex" or l.xwl_value like "%stock%" or ...
you get the idea.
The best idea, of course, is to keep running and refining your query until you are sure that you've identified all of the offending comments and only the offending comments.
2. After you've built a query that identifies the set of records you want to remove, you must delete them from each table where they appear. The easiest way to do this is to modify your query to return only the xwikiobjects.xwo_id field in the SELECT clause and put it into a temporary table:
create temporary table badcomments (comment_id integer);
insert into badcomments select o.xwo_id from xwikiobjects o [... where, etc...];
Then delete every row from xwikistrings, xwikilargestrings, xwikidates, and xwikiintegers where the respective ID fields (xws_id, xwl_id, xws_id, and xwi_id, respectively) match the comment_id field from your badcomments table.
You can also do it using the $xwiki.search() method, but there you have to tie the XWiki objects together using HQL. The advantage is that you don't have to have server access; the disadvantage is that it's miserable to get it right (my opinion).
brain[sic]
-----Original Message----- From: [email protected] [mailto:[email protected]] Sent: Tuesday, October 17, 2006 12:32 AM To: [email protected] Subject: [xwiki-users] delete spam comments
Hi, My wiki site got over a thousand spam comments, anyone know how can I delete them quickly (better than edit object, then delete one by one). Wei-hsing
---------- Forwarded message ---------- From: "THOMAS, BRIAN M (SBCSI)" <[email protected]> To: <[email protected]> Date: Tue, 17 Oct 2006 15:11:00 +0000 Subject: RE: [xwiki-users] delete spam comments
-- You receive this message as a subscriber of the [email protected] mailing list. To unsubscribe: mailto:[email protected] For general help: mailto:[email protected]?subject=help ObjectWeb mailing lists service home page: http://www.objectweb.org/wws
-- You receive this message as a subscriber of the [email protected] mailing list. To unsubscribe: mailto:[email protected] For general help: mailto:[email protected]?subject=help ObjectWeb mailing lists service home page: http://www.objectweb.org/wws
-- jeremi -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.408 / Virus Database: 268.13.4/478 - Release Date: 17/10/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.408 / Virus Database: 268.13.4/478 - Release Date: 17/10/2006
Hi, no it's not a backdoor. we just publish more functions in the api. now, for exemple, if you have the right to edit a page, you can save a document with a velocity script. jeremi On 10/17/06, Jean-Lou Dupont <[email protected]> wrote:
In other words, a backdoor? A security hole? Please more details on this 'code without programming rights'. Jld.
-----Original Message----- From: jeremi joslin [mailto:[email protected]] Sent: October 17, 2006 11:45 To: [email protected] Subject: Re: [xwiki-users] delete spam comments
Hi, we have some scripts for this, but they require the programming right. Can you send me the adress of your wiki, I will install it on your wiki.
It's possible with the new api of xwiki to rewrite this script to be written without programming right.
jeremi
On 10/17/06, [email protected] <[email protected]> wrote:
Hi, Brian, Thanks a lot. My site is on Xwiki.com, can I do all these steps? Is there a way just to stop showing comments right now? Wei-hsing
-------------- Original message ---------------------- From: "THOMAS, BRIAN M (SBCSI)" <[email protected]>
Direct database time...
First, BACK UP your database.
No. First, if you are not an SQL hack, get someone who is.
Now, BACK UP your database.
Next, you must delete all rows connected with spam comments.
I'm going to derive the process whereby you do this, because I'm a little rusty on the XWiki database schemas, and because I'm an inveterate pedant. You can skip some of these steps that exist for information only if you wish, especially if you already know how to do them.
First, it's necessary to know which rows constitute comments. XWiki's dynamic typing is a great boon to users and an equally great bane to administrators, as you will soon see. The xwikiclasses table describes user-defined classes (well, actually it doesn't; it names them, and describes server-defined custom classes if there are any, which fortunately there aren't, because I couldn't tell you what to do if there were). The class that defines XWiki comments is named XWiki.XWikiComments. The xwikiclassesprop table contains the list of fields for a given class, which is identified not by its name but by the unique ID from the xwikiclasses table. So you need to get the ID of the row in the xwikiclasses table whose name field is 'XWiki.XWikiComments', and retrieve the field name and type from all rows of the xwikiclassesprop table that have that ID. The following SQL does this less verbosely:
select xwp_name, xwp_classtype from xwikiclassesprop p, xwikiclasses c where xwo_name = 'XWiki.XWikiComments' and p.xwp_id = c.xwo_id
The results of this should be: +-----------+---------------------------------------------+ | xwp_name | xwp_classtype | +-----------+---------------------------------------------+ | author | com.xpn.xwiki.objects.classes.StringClass | | comment | com.xpn.xwiki.objects.classes.TextAreaClass | | date | com.xpn.xwiki.objects.classes.DateClass | | highlight | com.xpn.xwiki.objects.classes.TextAreaClass | | replyto | com.xpn.xwiki.objects.classes.NumberClass | +-----------+---------------------------------------------+ 5 rows in set (0.00 sec)
You'd have to go into the XWiki source (more specifically, the Hibernate configuration, I believe) to see how the xwp_classtype field above links to what I'm doing below; I leave that as an exercise for the reader. All that's really needed is to know the types of the fields and which tables they're stored in.
I've actually misled you somewhat: this is not the actual schema of existing comment objects but the prototype for the creation of new comments. I've done this ostensibly to give you a fuller understanding of how XWiki allows one to change the definition of a user-defined class without breaking existing instances, but it's really because I got tripped up on it myself, and I wanted to share my misery with you.
To get the actual schema for existing comments you have to look at the xwikiobjects table, which defines actual XWiki object instances in terms of their class names and the document to which they belong. The xwo_id field is the field that ties all of the tables together.
So the following query:
select distinct(xwp_name), xwp_classtype from xwikiobjects o, xwikiproperties p where xwo_classname='XWiki.XWikiComments' and xwo_id = xwp_id
yields a similar-looking result (in my database, anyway), because the XWiki.XWikiComments class hasn't changed since these comments were added: +-----------+-------------------------------------------+ | xwp_name | xwp_classtype | +-----------+-------------------------------------------+ | author | com.xpn.xwiki.objects.StringProperty | | date | com.xpn.xwiki.objects.DateProperty | | comment | com.xpn.xwiki.objects.LargeStringProperty | | replyto | com.xpn.xwiki.objects.IntegerProperty | | highlight | com.xpn.xwiki.objects.LargeStringProperty | +-----------+-------------------------------------------+ 5 rows in set (0.05 sec)
So now we know (without explaining how) that (for example) the contents of the "author" field of an instance of the class named XWiki.XWikiComments is in the xwikistrings table, in a row whose xws_id field matches the xwo_id field of an xwikiobjects row whose xwo_classname file is "XWiki.XWikiComments". All the field values can be found thus:
field name table name field value author xwikistrings xws_value date xwikidates xws_value comment xwikilargestrings xwl_value replyto xwikiintegers xwi_value highlight xwikilargestrings xwl_value
So all the fields of a comment would be retrieved, given its ID, by the following query:
select s.xws_value, /* author name */ d.xws_value, /* comment date */ l.xws_value, /* comment text */ i.xwi_value, /* reply-to field */ h.xwl_value /* highlight field */ from xwikistrings s, xwikidates d, xwikilargestrings l, xwikiintegers i, xwikilargestrings h where s.xws_id = <comment ID> and s.xws_name = 'author' and d.xws_id = s.xws_id and d.xws_name = 'date' and l.xws_id = s.xws_id and l.xws_name = 'comment' and i.xwi_value = s.xws_id and i.xwi_name = 'replyto' and h.xwl_value = s.xws_id and h.xwl_name = 'highlight'
Of course, the job is much simpler than this, for several reasons. First, the replyto and highlight fields are not populated by XWiki's default templates, so they're always null anyway; second, of the remaining values, each one is uniquely identified by the object's ID field anyway, so the respective name fields don't need to be specified.
To get a list of all comments in the database, with only their object ID field, author name, and date (which produces a fairly neat display) this query should do it.
select o.xwo_id, s.xws_value, d.xws_value from xwikiobjects o, xwikistrings s, xwikidates d where xwo_classname='XWiki.XWikiComments' and s.xws_id = o.xwo_id and d.xws_id = o.xwo_id order by xwo_id
With all that as background, what you need to do is:
1. Identify the offending comments (by their object IDs).
If the comments were all from bogus registrations, then the above query with an added qualifier something like:
where s.xws_value in ('XWiki.spammer1', 'XWiki.spammer2'[,...])
will give you the object IDs you need.
If you have allowed anonymous users to comment, then other criteria must be used, although of course "s.xws_value = 'XWiki.XWikiGuest'" should be in your WHERE clause. Most likely, you could probably nail most of them by saying
select o.xwo_id from xwikiobjects o, xwikilargestrings l where o.classname = 'XWiki.XWikiComments' and l.xwl_id = o.xwo_id and l.xwl_value like "%Viagra%" or l.xwl_value like "%sex" or l.xwl_value like "%stock%" or ...
you get the idea.
The best idea, of course, is to keep running and refining your query until you are sure that you've identified all of the offending comments and only the offending comments.
2. After you've built a query that identifies the set of records you want to remove, you must delete them from each table where they appear. The easiest way to do this is to modify your query to return only the xwikiobjects.xwo_id field in the SELECT clause and put it into a temporary table:
create temporary table badcomments (comment_id integer);
insert into badcomments select o.xwo_id from xwikiobjects o [... where, etc...];
Then delete every row from xwikistrings, xwikilargestrings, xwikidates, and xwikiintegers where the respective ID fields (xws_id, xwl_id, xws_id, and xwi_id, respectively) match the comment_id field from your badcomments table.
You can also do it using the $xwiki.search() method, but there you have to tie the XWiki objects together using HQL. The advantage is that you don't have to have server access; the disadvantage is that it's miserable to get it right (my opinion).
brain[sic]
-----Original Message----- From: [email protected] [mailto:[email protected]] Sent: Tuesday, October 17, 2006 12:32 AM To: [email protected] Subject: [xwiki-users] delete spam comments
Hi, My wiki site got over a thousand spam comments, anyone know how can I delete them quickly (better than edit object, then delete one by one). Wei-hsing
---------- Forwarded message ---------- From: "THOMAS, BRIAN M (SBCSI)" <[email protected]> To: <[email protected]> Date: Tue, 17 Oct 2006 15:11:00 +0000 Subject: RE: [xwiki-users] delete spam comments
-- You receive this message as a subscriber of the [email protected] mailing list. To unsubscribe: mailto:[email protected] For general help: mailto:[email protected]?subject=help ObjectWeb mailing lists service home page: http://www.objectweb.org/wws
-- You receive this message as a subscriber of the [email protected] mailing list. To unsubscribe: mailto:[email protected] For general help: mailto:[email protected]?subject=help ObjectWeb mailing lists service home page: http://www.objectweb.org/wws
-- jeremi
-- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.408 / Virus Database: 268.13.4/478 - Release Date: 17/10/2006
-- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.408 / Virus Database: 268.13.4/478 - Release Date: 17/10/2006
-- You receive this message as a subscriber of the [email protected] mailing list. To unsubscribe: mailto:[email protected] For general help: mailto:[email protected]?subject=help ObjectWeb mailing lists service home page: http://www.objectweb.org/wws
-- jeremi
There are obviously subtle points I am missing -- in my mind, the ability to control server side resources (storage, computation, communication) constitutes a potential for security problems. Now, if this capability is widely available, then the said potential materializes. What rules I am missing? Jld. -----Original Message----- From: jeremi joslin [mailto:[email protected]] Sent: October 17, 2006 12:03 To: [email protected] Subject: Re: [xwiki-users] delete spam comments Hi, no it's not a backdoor. we just publish more functions in the api. now, for exemple, if you have the right to edit a page, you can save a document with a velocity script. jeremi On 10/17/06, Jean-Lou Dupont <[email protected]> wrote:
In other words, a backdoor? A security hole? Please more details on this 'code without programming rights'. Jld.
-----Original Message----- From: jeremi joslin [mailto:[email protected]] Sent: October 17, 2006 11:45 To: [email protected] Subject: Re: [xwiki-users] delete spam comments
Hi, we have some scripts for this, but they require the programming right. Can you send me the adress of your wiki, I will install it on your wiki.
It's possible with the new api of xwiki to rewrite this script to be written without programming right.
jeremi
On 10/17/06, [email protected] <[email protected]> wrote:
Hi, Brian, Thanks a lot. My site is on Xwiki.com, can I do all these steps? Is there a way just to stop showing comments right now? Wei-hsing
-------------- Original message ---------------------- From: "THOMAS, BRIAN M (SBCSI)" <[email protected]>
Direct database time...
First, BACK UP your database.
No. First, if you are not an SQL hack, get someone who is.
Now, BACK UP your database.
Next, you must delete all rows connected with spam comments.
I'm going to derive the process whereby you do this, because I'm a little rusty on the XWiki database schemas, and because I'm an inveterate pedant. You can skip some of these steps that exist for information only if you wish, especially if you already know how to do them.
First, it's necessary to know which rows constitute comments. XWiki's dynamic typing is a great boon to users and an equally great bane to administrators, as you will soon see. The xwikiclasses table describes user-defined classes (well, actually it doesn't; it names them, and describes server-defined custom classes if there are any, which fortunately there aren't, because I couldn't tell you what to do if there were). The class that defines XWiki comments is named XWiki.XWikiComments. The xwikiclassesprop table contains the list of fields for a given class, which is identified not by its name but by the unique ID from the xwikiclasses table. So you need to get the ID of the row in the xwikiclasses table whose name field is 'XWiki.XWikiComments', and retrieve the field name and type from all rows of the xwikiclassesprop table that have that ID. The following SQL does this less verbosely:
select xwp_name, xwp_classtype from xwikiclassesprop p, xwikiclasses c where xwo_name = 'XWiki.XWikiComments' and p.xwp_id = c.xwo_id
The results of this should be: +-----------+---------------------------------------------+ | xwp_name | xwp_classtype | +-----------+---------------------------------------------+ | author | com.xpn.xwiki.objects.classes.StringClass | | comment | com.xpn.xwiki.objects.classes.TextAreaClass | | date | com.xpn.xwiki.objects.classes.DateClass | | highlight | com.xpn.xwiki.objects.classes.TextAreaClass | | replyto | com.xpn.xwiki.objects.classes.NumberClass | +-----------+---------------------------------------------+ 5 rows in set (0.00 sec)
You'd have to go into the XWiki source (more specifically, the Hibernate configuration, I believe) to see how the xwp_classtype field above links to what I'm doing below; I leave that as an exercise for the reader. All that's really needed is to know the types of the fields and which tables they're stored in.
I've actually misled you somewhat: this is not the actual schema of existing comment objects but the prototype for the creation of new comments. I've done this ostensibly to give you a fuller understanding of how XWiki allows one to change the definition of a user-defined class without breaking existing instances, but it's really because I got tripped up on it myself, and I wanted to share my misery with you.
To get the actual schema for existing comments you have to look at the xwikiobjects table, which defines actual XWiki object instances in terms of their class names and the document to which they belong. The xwo_id field is the field that ties all of the tables together.
So the following query:
select distinct(xwp_name), xwp_classtype from xwikiobjects o, xwikiproperties p where xwo_classname='XWiki.XWikiComments' and xwo_id = xwp_id
yields a similar-looking result (in my database, anyway), because the XWiki.XWikiComments class hasn't changed since these comments were added: +-----------+-------------------------------------------+ | xwp_name | xwp_classtype | +-----------+-------------------------------------------+ | author | com.xpn.xwiki.objects.StringProperty | | date | com.xpn.xwiki.objects.DateProperty | | comment | com.xpn.xwiki.objects.LargeStringProperty | | replyto | com.xpn.xwiki.objects.IntegerProperty | | highlight | com.xpn.xwiki.objects.LargeStringProperty | +-----------+-------------------------------------------+ 5 rows in set (0.05 sec)
So now we know (without explaining how) that (for example) the contents of the "author" field of an instance of the class named XWiki.XWikiComments is in the xwikistrings table, in a row whose xws_id field matches the xwo_id field of an xwikiobjects row whose xwo_classname file is "XWiki.XWikiComments". All the field values can be found thus:
field name table name field value author xwikistrings xws_value date xwikidates xws_value comment xwikilargestrings xwl_value replyto xwikiintegers xwi_value highlight xwikilargestrings xwl_value
So all the fields of a comment would be retrieved, given its ID, by the following query:
select s.xws_value, /* author name */ d.xws_value, /* comment date */ l.xws_value, /* comment text */ i.xwi_value, /* reply-to field */ h.xwl_value /* highlight field */ from xwikistrings s, xwikidates d, xwikilargestrings l, xwikiintegers i, xwikilargestrings h where s.xws_id = <comment ID> and s.xws_name = 'author' and d.xws_id = s.xws_id and d.xws_name = 'date' and l.xws_id = s.xws_id and l.xws_name = 'comment' and i.xwi_value = s.xws_id and i.xwi_name = 'replyto' and h.xwl_value = s.xws_id and h.xwl_name = 'highlight'
Of course, the job is much simpler than this, for several reasons. First, the replyto and highlight fields are not populated by XWiki's default templates, so they're always null anyway; second, of the remaining values, each one is uniquely identified by the object's ID field anyway, so the respective name fields don't need to be specified.
To get a list of all comments in the database, with only their object ID field, author name, and date (which produces a fairly neat display) this query should do it.
select o.xwo_id, s.xws_value, d.xws_value from xwikiobjects o, xwikistrings s, xwikidates d where xwo_classname='XWiki.XWikiComments' and s.xws_id = o.xwo_id and d.xws_id = o.xwo_id order by xwo_id
With all that as background, what you need to do is:
1. Identify the offending comments (by their object IDs).
If the comments were all from bogus registrations, then the above query with an added qualifier something like:
where s.xws_value in ('XWiki.spammer1', 'XWiki.spammer2'[,...])
will give you the object IDs you need.
If you have allowed anonymous users to comment, then other criteria must be used, although of course "s.xws_value = 'XWiki.XWikiGuest'" should be in your WHERE clause. Most likely, you could probably nail most of them by saying
select o.xwo_id from xwikiobjects o, xwikilargestrings l where o.classname = 'XWiki.XWikiComments' and l.xwl_id = o.xwo_id and l.xwl_value like "%Viagra%" or l.xwl_value like "%sex" or l.xwl_value like "%stock%" or ...
you get the idea.
The best idea, of course, is to keep running and refining your query until you are sure that you've identified all of the offending comments and only the offending comments.
2. After you've built a query that identifies the set of records you want to remove, you must delete them from each table where they appear. The easiest way to do this is to modify your query to return only the xwikiobjects.xwo_id field in the SELECT clause and put it into a temporary table:
create temporary table badcomments (comment_id integer);
insert into badcomments select o.xwo_id from xwikiobjects o [... where, etc...];
Then delete every row from xwikistrings, xwikilargestrings, xwikidates, and xwikiintegers where the respective ID fields (xws_id, xwl_id, xws_id, and xwi_id, respectively) match the comment_id field from your badcomments table.
You can also do it using the $xwiki.search() method, but there you have to tie the XWiki objects together using HQL. The advantage is that you don't have to have server access; the disadvantage is that it's miserable to get it right (my opinion).
brain[sic]
-----Original Message----- From: [email protected] [mailto:[email protected]] Sent: Tuesday, October 17, 2006 12:32 AM To: [email protected] Subject: [xwiki-users] delete spam comments
Hi, My wiki site got over a thousand spam comments, anyone know how can I delete them quickly (better than edit object, then delete one by one). Wei-hsing
---------- Forwarded message ---------- From: "THOMAS, BRIAN M (SBCSI)" <[email protected]> To: <[email protected]> Date: Tue, 17 Oct 2006 15:11:00 +0000 Subject: RE: [xwiki-users] delete spam comments
-- You receive this message as a subscriber of the [email protected] mailing list. To unsubscribe: mailto:[email protected] For general help: mailto:[email protected]?subject=help ObjectWeb mailing lists service home page: http://www.objectweb.org/wws
-- You receive this message as a subscriber of the [email protected] mailing list. To unsubscribe: mailto:[email protected] For general help: mailto:[email protected]?subject=help ObjectWeb mailing lists service home page: http://www.objectweb.org/wws
-- jeremi
-- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.408 / Virus Database: 268.13.4/478 - Release Date: 17/10/2006
-- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.408 / Virus Database: 268.13.4/478 - Release Date: 17/10/2006
-- You receive this message as a subscriber of the [email protected] mailing list. To unsubscribe: mailto:[email protected] For general help: mailto:[email protected]?subject=help ObjectWeb mailing lists service home page: http://www.objectweb.org/wws
-- jeremi -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.408 / Virus Database: 268.13.4/478 - Release Date: 17/10/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.408 / Virus Database: 268.13.4/478 - Release Date: 17/10/2006
Hi, You don't control the severside. You can do what you are authorized to.if you give the right to edit a page, so it's possible to use a velocity script to edit it. You can only do what you can do by the interface, but using velocity scripts. jeremi On 10/17/06, Jean-Lou Dupont <[email protected]> wrote:
There are obviously subtle points I am missing -- in my mind, the ability to control server side resources (storage, computation, communication) constitutes a potential for security problems. Now, if this capability is widely available, then the said potential materializes.
What rules I am missing?
Jld.
-----Original Message----- From: jeremi joslin [mailto:[email protected]] Sent: October 17, 2006 12:03 To: [email protected] Subject: Re: [xwiki-users] delete spam comments
Hi, no it's not a backdoor. we just publish more functions in the api. now, for exemple, if you have the right to edit a page, you can save a document with a velocity script.
jeremi
On 10/17/06, Jean-Lou Dupont <[email protected]> wrote:
In other words, a backdoor? A security hole? Please more details on this 'code without programming rights'. Jld.
-----Original Message----- From: jeremi joslin [mailto:[email protected]] Sent: October 17, 2006 11:45 To: [email protected] Subject: Re: [xwiki-users] delete spam comments
Hi, we have some scripts for this, but they require the programming right. Can you send me the adress of your wiki, I will install it on your wiki.
It's possible with the new api of xwiki to rewrite this script to be written without programming right.
jeremi
On 10/17/06, [email protected] <[email protected]> wrote:
Hi, Brian, Thanks a lot. My site is on Xwiki.com, can I do all these steps? Is there a way just to stop showing comments right now? Wei-hsing
-------------- Original message ---------------------- From: "THOMAS, BRIAN M (SBCSI)" <[email protected]>
Direct database time...
First, BACK UP your database.
No. First, if you are not an SQL hack, get someone who is.
Now, BACK UP your database.
Next, you must delete all rows connected with spam comments.
I'm going to derive the process whereby you do this, because I'm a little rusty on the XWiki database schemas, and because I'm an inveterate pedant. You can skip some of these steps that exist for information only if you wish, especially if you already know how to do them.
First, it's necessary to know which rows constitute comments. XWiki's dynamic typing is a great boon to users and an equally great bane to administrators, as you will soon see. The xwikiclasses table describes user-defined classes (well, actually it doesn't; it names them, and describes server-defined custom classes if there are any, which fortunately there aren't, because I couldn't tell you what to do if there were). The class that defines XWiki comments is named XWiki.XWikiComments. The xwikiclassesprop table contains the list of fields for a given class, which is identified not by its name but by the unique ID from the xwikiclasses table. So you need to get the ID of the row in the xwikiclasses table whose name field is 'XWiki.XWikiComments', and retrieve the field name and type from all rows of the xwikiclassesprop table that have that ID. The following SQL does this less verbosely:
select xwp_name, xwp_classtype from xwikiclassesprop p, xwikiclasses c where xwo_name = 'XWiki.XWikiComments' and p.xwp_id = c.xwo_id
The results of this should be: +-----------+---------------------------------------------+ | xwp_name | xwp_classtype | +-----------+---------------------------------------------+ | author | com.xpn.xwiki.objects.classes.StringClass | | comment | com.xpn.xwiki.objects.classes.TextAreaClass | | date | com.xpn.xwiki.objects.classes.DateClass | | highlight | com.xpn.xwiki.objects.classes.TextAreaClass | | replyto | com.xpn.xwiki.objects.classes.NumberClass | +-----------+---------------------------------------------+ 5 rows in set (0.00 sec)
You'd have to go into the XWiki source (more specifically, the Hibernate configuration, I believe) to see how the xwp_classtype field above links to what I'm doing below; I leave that as an exercise for the reader. All that's really needed is to know the types of the fields and which tables they're stored in.
I've actually misled you somewhat: this is not the actual schema of existing comment objects but the prototype for the creation of new comments. I've done this ostensibly to give you a fuller understanding of how XWiki allows one to change the definition of a user-defined class without breaking existing instances, but it's really because I got tripped up on it myself, and I wanted to share my misery with you.
To get the actual schema for existing comments you have to look at the xwikiobjects table, which defines actual XWiki object instances in terms of their class names and the document to which they belong. The xwo_id field is the field that ties all of the tables together.
So the following query:
select distinct(xwp_name), xwp_classtype from xwikiobjects o, xwikiproperties p where xwo_classname='XWiki.XWikiComments' and xwo_id = xwp_id
yields a similar-looking result (in my database, anyway), because the XWiki.XWikiComments class hasn't changed since these comments were added: +-----------+-------------------------------------------+ | xwp_name | xwp_classtype | +-----------+-------------------------------------------+ | author | com.xpn.xwiki.objects.StringProperty | | date | com.xpn.xwiki.objects.DateProperty | | comment | com.xpn.xwiki.objects.LargeStringProperty | | replyto | com.xpn.xwiki.objects.IntegerProperty | | highlight | com.xpn.xwiki.objects.LargeStringProperty | +-----------+-------------------------------------------+ 5 rows in set (0.05 sec)
So now we know (without explaining how) that (for example) the contents of the "author" field of an instance of the class named XWiki.XWikiComments is in the xwikistrings table, in a row whose xws_id field matches the xwo_id field of an xwikiobjects row whose xwo_classname file is "XWiki.XWikiComments". All the field values can be found thus:
field name table name field value author xwikistrings xws_value date xwikidates xws_value comment xwikilargestrings xwl_value replyto xwikiintegers xwi_value highlight xwikilargestrings xwl_value
So all the fields of a comment would be retrieved, given its ID, by the following query:
select s.xws_value, /* author name */ d.xws_value, /* comment date */ l.xws_value, /* comment text */ i.xwi_value, /* reply-to field */ h.xwl_value /* highlight field */ from xwikistrings s, xwikidates d, xwikilargestrings l, xwikiintegers i, xwikilargestrings h where s.xws_id = <comment ID> and s.xws_name = 'author' and d.xws_id = s.xws_id and d.xws_name = 'date' and l.xws_id = s.xws_id and l.xws_name = 'comment' and i.xwi_value = s.xws_id and i.xwi_name = 'replyto' and h.xwl_value = s.xws_id and h.xwl_name = 'highlight'
Of course, the job is much simpler than this, for several reasons. First, the replyto and highlight fields are not populated by XWiki's default templates, so they're always null anyway; second, of the remaining values, each one is uniquely identified by the object's ID field anyway, so the respective name fields don't need to be specified.
To get a list of all comments in the database, with only their object ID field, author name, and date (which produces a fairly neat display) this query should do it.
select o.xwo_id, s.xws_value, d.xws_value from xwikiobjects o, xwikistrings s, xwikidates d where xwo_classname='XWiki.XWikiComments' and s.xws_id = o.xwo_id and d.xws_id = o.xwo_id order by xwo_id
With all that as background, what you need to do is:
1. Identify the offending comments (by their object IDs).
If the comments were all from bogus registrations, then the above query with an added qualifier something like:
where s.xws_value in ('XWiki.spammer1', 'XWiki.spammer2'[,...])
will give you the object IDs you need.
If you have allowed anonymous users to comment, then other criteria must be used, although of course "s.xws_value = 'XWiki.XWikiGuest'" should be in your WHERE clause. Most likely, you could probably nail most of them by saying
select o.xwo_id from xwikiobjects o, xwikilargestrings l where o.classname = 'XWiki.XWikiComments' and l.xwl_id = o.xwo_id and l.xwl_value like "%Viagra%" or l.xwl_value like "%sex" or l.xwl_value like "%stock%" or ...
you get the idea.
The best idea, of course, is to keep running and refining your query until you are sure that you've identified all of the offending comments and only the offending comments.
2. After you've built a query that identifies the set of records you want to remove, you must delete them from each table where they appear. The easiest way to do this is to modify your query to return only the xwikiobjects.xwo_id field in the SELECT clause and put it into a temporary table:
create temporary table badcomments (comment_id integer);
insert into badcomments select o.xwo_id from xwikiobjects o [... where, etc...];
Then delete every row from xwikistrings, xwikilargestrings, xwikidates, and xwikiintegers where the respective ID fields (xws_id, xwl_id, xws_id, and xwi_id, respectively) match the comment_id field from your badcomments table.
You can also do it using the $xwiki.search() method, but there you have to tie the XWiki objects together using HQL. The advantage is that you don't have to have server access; the disadvantage is that it's miserable to get it right (my opinion).
brain[sic]
-----Original Message----- From: [email protected] [mailto:[email protected]] Sent: Tuesday, October 17, 2006 12:32 AM To: [email protected] Subject: [xwiki-users] delete spam comments
Hi, My wiki site got over a thousand spam comments, anyone know how can I delete them quickly (better than edit object, then delete one by one). Wei-hsing
---------- Forwarded message ---------- From: "THOMAS, BRIAN M (SBCSI)" <[email protected]> To: <[email protected]> Date: Tue, 17 Oct 2006 15:11:00 +0000 Subject: RE: [xwiki-users] delete spam comments
-- You receive this message as a subscriber of the [email protected] mailing list. To unsubscribe: mailto:[email protected] For general help: mailto:[email protected]?subject=help ObjectWeb mailing lists service home page: http://www.objectweb.org/wws
-- You receive this message as a subscriber of the [email protected] mailing list. To unsubscribe: mailto:[email protected] For general help: mailto:[email protected]?subject=help ObjectWeb mailing lists service home page: http://www.objectweb.org/wws
-- jeremi
-- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.408 / Virus Database: 268.13.4/478 - Release Date: 17/10/2006
-- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.408 / Virus Database: 268.13.4/478 - Release Date: 17/10/2006
-- You receive this message as a subscriber of the [email protected] mailing list. To unsubscribe: mailto:[email protected] For general help: mailto:[email protected]?subject=help ObjectWeb mailing lists service home page: http://www.objectweb.org/wws
-- jeremi
-- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.408 / Virus Database: 268.13.4/478 - Release Date: 17/10/2006
-- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.408 / Virus Database: 268.13.4/478 - Release Date: 17/10/2006
-- You receive this message as a subscriber of the [email protected] mailing list. To unsubscribe: mailto:[email protected] For general help: mailto:[email protected]?subject=help ObjectWeb mailing lists service home page: http://www.objectweb.org/wws
-- jeremi
With some help from Robin on the Xwiki IRC channel, I understand what is being proposed. What I am getting at is the potential for DOS (Denial Of Service) increases significantly with the addition of this feature. If you thought you had SPAM problems, wait till some punk get a hold of your Velocity based interface... I believe Xwiki needs another level of Rights Management... Jld. -----Original Message----- From: jeremi joslin [mailto:[email protected]] Sent: October 17, 2006 12:20 To: [email protected] Subject: Re: [xwiki-users] delete spam comments Hi, You don't control the severside. You can do what you are authorized to.if you give the right to edit a page, so it's possible to use a velocity script to edit it. You can only do what you can do by the interface, but using velocity scripts. jeremi On 10/17/06, Jean-Lou Dupont <[email protected]> wrote:
There are obviously subtle points I am missing -- in my mind, the ability to control server side resources (storage, computation, communication) constitutes a potential for security problems. Now, if this capability is widely available, then the said potential materializes.
What rules I am missing?
Jld.
-----Original Message----- From: jeremi joslin [mailto:[email protected]] Sent: October 17, 2006 12:03 To: [email protected] Subject: Re: [xwiki-users] delete spam comments
Hi, no it's not a backdoor. we just publish more functions in the api. now, for exemple, if you have the right to edit a page, you can save a document with a velocity script.
jeremi
On 10/17/06, Jean-Lou Dupont <[email protected]> wrote:
In other words, a backdoor? A security hole? Please more details on this 'code without programming rights'. Jld.
-----Original Message----- From: jeremi joslin [mailto:[email protected]] Sent: October 17, 2006 11:45 To: [email protected] Subject: Re: [xwiki-users] delete spam comments
Hi, we have some scripts for this, but they require the programming right. Can you send me the adress of your wiki, I will install it on your wiki.
It's possible with the new api of xwiki to rewrite this script to be written without programming right.
jeremi
On 10/17/06, [email protected] <[email protected]> wrote:
Hi, Brian, Thanks a lot. My site is on Xwiki.com, can I do all these steps? Is there a way just to stop showing comments right now? Wei-hsing
-------------- Original message ---------------------- From: "THOMAS, BRIAN M (SBCSI)" <[email protected]>
Direct database time...
First, BACK UP your database.
No. First, if you are not an SQL hack, get someone who is.
Now, BACK UP your database.
Next, you must delete all rows connected with spam comments.
I'm going to derive the process whereby you do this, because I'm a little rusty on the XWiki database schemas, and because I'm an inveterate pedant. You can skip some of these steps that exist for information only if you wish, especially if you already know how to do them.
First, it's necessary to know which rows constitute comments. XWiki's dynamic typing is a great boon to users and an equally great bane to administrators, as you will soon see. The xwikiclasses table describes user-defined classes (well, actually it doesn't; it names them, and describes server-defined custom classes if there are any, which fortunately there aren't, because I couldn't tell you what to do if there were). The class that defines XWiki comments is named XWiki.XWikiComments. The xwikiclassesprop table contains the list of fields for a given class, which is identified not by its name but by the unique ID from the xwikiclasses table. So you need to get the ID of the row in the xwikiclasses table whose name field is 'XWiki.XWikiComments', and retrieve the field name and type from all rows of the xwikiclassesprop table that have that ID. The following SQL does this less verbosely:
select xwp_name, xwp_classtype from xwikiclassesprop p, xwikiclasses c where xwo_name = 'XWiki.XWikiComments' and p.xwp_id = c.xwo_id
The results of this should be: +-----------+---------------------------------------------+ | xwp_name | xwp_classtype | +-----------+---------------------------------------------+ | author | com.xpn.xwiki.objects.classes.StringClass | | comment | com.xpn.xwiki.objects.classes.TextAreaClass | | date | com.xpn.xwiki.objects.classes.DateClass | | highlight | com.xpn.xwiki.objects.classes.TextAreaClass | | replyto | com.xpn.xwiki.objects.classes.NumberClass | +-----------+---------------------------------------------+ 5 rows in set (0.00 sec)
You'd have to go into the XWiki source (more specifically, the Hibernate configuration, I believe) to see how the xwp_classtype field above links to what I'm doing below; I leave that as an exercise for the reader. All that's really needed is to know the types of the fields and which tables they're stored in.
I've actually misled you somewhat: this is not the actual schema of existing comment objects but the prototype for the creation of new comments. I've done this ostensibly to give you a fuller understanding of how XWiki allows one to change the definition of a user-defined class without breaking existing instances, but it's really because I got tripped up on it myself, and I wanted to share my misery with you.
To get the actual schema for existing comments you have to look at the xwikiobjects table, which defines actual XWiki object instances in terms of their class names and the document to which they belong. The xwo_id field is the field that ties all of the tables together.
So the following query:
select distinct(xwp_name), xwp_classtype from xwikiobjects o, xwikiproperties p where xwo_classname='XWiki.XWikiComments' and xwo_id = xwp_id
yields a similar-looking result (in my database, anyway), because the XWiki.XWikiComments class hasn't changed since these comments were added: +-----------+-------------------------------------------+ | xwp_name | xwp_classtype | +-----------+-------------------------------------------+ | author | com.xpn.xwiki.objects.StringProperty | | date | com.xpn.xwiki.objects.DateProperty | | comment | com.xpn.xwiki.objects.LargeStringProperty | | replyto | com.xpn.xwiki.objects.IntegerProperty | | highlight | com.xpn.xwiki.objects.LargeStringProperty | +-----------+-------------------------------------------+ 5 rows in set (0.05 sec)
So now we know (without explaining how) that (for example) the contents of the "author" field of an instance of the class named XWiki.XWikiComments is in the xwikistrings table, in a row whose xws_id field matches the xwo_id field of an xwikiobjects row whose xwo_classname file is "XWiki.XWikiComments". All the field values can be found thus:
field name table name field value author xwikistrings xws_value date xwikidates xws_value comment xwikilargestrings xwl_value replyto xwikiintegers xwi_value highlight xwikilargestrings xwl_value
So all the fields of a comment would be retrieved, given its ID, by the following query:
select s.xws_value, /* author name */ d.xws_value, /* comment date */ l.xws_value, /* comment text */ i.xwi_value, /* reply-to field */ h.xwl_value /* highlight field */ from xwikistrings s, xwikidates d, xwikilargestrings l, xwikiintegers i, xwikilargestrings h where s.xws_id = <comment ID> and s.xws_name = 'author' and d.xws_id = s.xws_id and d.xws_name = 'date' and l.xws_id = s.xws_id and l.xws_name = 'comment' and i.xwi_value = s.xws_id and i.xwi_name = 'replyto' and h.xwl_value = s.xws_id and h.xwl_name = 'highlight'
Of course, the job is much simpler than this, for several reasons. First, the replyto and highlight fields are not populated by XWiki's default templates, so they're always null anyway; second, of the remaining values, each one is uniquely identified by the object's ID field anyway, so the respective name fields don't need to be specified.
To get a list of all comments in the database, with only their object ID field, author name, and date (which produces a fairly neat display) this query should do it.
select o.xwo_id, s.xws_value, d.xws_value from xwikiobjects o, xwikistrings s, xwikidates d where xwo_classname='XWiki.XWikiComments' and s.xws_id = o.xwo_id and d.xws_id = o.xwo_id order by xwo_id
With all that as background, what you need to do is:
1. Identify the offending comments (by their object IDs).
If the comments were all from bogus registrations, then the above query with an added qualifier something like:
where s.xws_value in ('XWiki.spammer1', 'XWiki.spammer2'[,...])
will give you the object IDs you need.
If you have allowed anonymous users to comment, then other criteria must be used, although of course "s.xws_value = 'XWiki.XWikiGuest'" should be in your WHERE clause. Most likely, you could probably nail most of them by saying
select o.xwo_id from xwikiobjects o, xwikilargestrings l where o.classname = 'XWiki.XWikiComments' and l.xwl_id = o.xwo_id and l.xwl_value like "%Viagra%" or l.xwl_value like "%sex" or l.xwl_value like "%stock%" or ...
you get the idea.
The best idea, of course, is to keep running and refining your query until you are sure that you've identified all of the offending comments and only the offending comments.
2. After you've built a query that identifies the set of records you want to remove, you must delete them from each table where they appear. The easiest way to do this is to modify your query to return only the xwikiobjects.xwo_id field in the SELECT clause and put it into a temporary table:
create temporary table badcomments (comment_id integer);
insert into badcomments select o.xwo_id from xwikiobjects o [... where, etc...];
Then delete every row from xwikistrings, xwikilargestrings, xwikidates, and xwikiintegers where the respective ID fields (xws_id, xwl_id, xws_id, and xwi_id, respectively) match the comment_id field from your badcomments table.
You can also do it using the $xwiki.search() method, but there you have to tie the XWiki objects together using HQL. The advantage is that you don't have to have server access; the disadvantage is that it's miserable to get it right (my opinion).
brain[sic]
-----Original Message----- From: [email protected] [mailto:[email protected]] Sent: Tuesday, October 17, 2006 12:32 AM To: [email protected] Subject: [xwiki-users] delete spam comments
Hi, My wiki site got over a thousand spam comments, anyone know how can I delete them quickly (better than edit object, then delete one by one). Wei-hsing
---------- Forwarded message ---------- From: "THOMAS, BRIAN M (SBCSI)" <[email protected]> To: <[email protected]> Date: Tue, 17 Oct 2006 15:11:00 +0000 Subject: RE: [xwiki-users] delete spam comments
-- You receive this message as a subscriber of the [email protected] mailing list. To unsubscribe: mailto:[email protected] For general help: mailto:[email protected]?subject=help ObjectWeb mailing lists service home page: http://www.objectweb.org/wws
-- You receive this message as a subscriber of the [email protected] mailing list. To unsubscribe: mailto:[email protected] For general help: mailto:[email protected]?subject=help ObjectWeb mailing lists service home page: http://www.objectweb.org/wws
-- jeremi
-- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.408 / Virus Database: 268.13.4/478 - Release Date: 17/10/2006
-- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.408 / Virus Database: 268.13.4/478 - Release Date: 17/10/2006
-- You receive this message as a subscriber of the [email protected] mailing list. To unsubscribe: mailto:[email protected] For general help: mailto:[email protected]?subject=help ObjectWeb mailing lists service home page: http://www.objectweb.org/wws
-- jeremi
-- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.408 / Virus Database: 268.13.4/478 - Release Date: 17/10/2006
-- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.408 / Virus Database: 268.13.4/478 - Release Date: 17/10/2006
-- You receive this message as a subscriber of the [email protected] mailing list. To unsubscribe: mailto:[email protected] For general help: mailto:[email protected]?subject=help ObjectWeb mailing lists service home page: http://www.objectweb.org/wws
-- jeremi -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.408 / Virus Database: 268.13.4/478 - Release Date: 17/10/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.408 / Virus Database: 268.13.4/478 - Release Date: 17/10/2006
What Jeremi is saying is that right now, anyone who can post anything on an XWiki page can access the Velocity context (or XWiki wouldn't be much use at all, he didn't say). The problem was that the rights system didn't allocate certain rights where they needed to be. Generally, the boundary was at the com.xpn.xwiki.api package layer (btw, standard Java security can express rights at the level of a Java package, obviating the checks on every method call that tries to go beyond it). Some things, such as the ability to save a document or delete objects from a document, required the programming right, when properly they should belong to anyone with edit rights on a document. In fact, this strategy avoids a far greater risk, which is the biggest reason that MS Windows is such an infosec nightmare: making it necessary to give users far more privileges than they need. You may be right about needing another level of rights management, although it may be already provided by the Java permissions system, which can prevent users fiddling with sensitive classes in the server's space. brain[sic]
-----Original Message----- From: Jean-Lou Dupont [mailto:[email protected]] Sent: Tuesday, October 17, 2006 11:54 AM To: [email protected] Subject: RE: [xwiki-users] delete spam comments
With some help from Robin on the Xwiki IRC channel, I understand what is being proposed.
What I am getting at is the potential for DOS (Denial Of Service) increases significantly with the addition of this feature. If you thought you had SPAM problems, wait till some punk get a hold of your Velocity based interface...
I believe Xwiki needs another level of Rights Management...
Jld.
-----Original Message----- From: jeremi joslin [mailto:[email protected]] Sent: October 17, 2006 12:20 To: [email protected] Subject: Re: [xwiki-users] delete spam comments
Hi, You don't control the severside. You can do what you are authorized to.if you give the right to edit a page, so it's possible to use a velocity script to edit it. You can only do what you can do by the interface, but using velocity scripts.
jeremi
On 10/17/06, Jean-Lou Dupont <[email protected]> wrote:
There are obviously subtle points I am missing -- in my mind, the ability to control server side resources (storage, computation, communication) constitutes a potential for security problems. Now, if this capability is widely available, then the said potential materializes.
What rules I am missing?
Jld.
-----Original Message----- From: jeremi joslin [mailto:[email protected]] Sent: October 17, 2006 12:03 To: [email protected] Subject: Re: [xwiki-users] delete spam comments
Hi, no it's not a backdoor. we just publish more functions in the api. now, for exemple, if you have the right to edit a page, you can save a document with a velocity script.
jeremi
On 10/17/06, Jean-Lou Dupont <[email protected]> wrote:
In other words, a backdoor? A security hole? Please more details on this 'code without programming rights'. Jld.
-----Original Message----- From: jeremi joslin [mailto:[email protected]] Sent: October 17, 2006 11:45 To: [email protected] Subject: Re: [xwiki-users] delete spam comments
Hi, we have some scripts for this, but they require the programming right. Can you send me the adress of your wiki, I will install it on your wiki.
It's possible with the new api of xwiki to rewrite this script to be written without programming right.
jeremi
On 10/17/06, [email protected] <[email protected]> wrote:
Hi, Brian, Thanks a lot. My site is on Xwiki.com, can I do all these steps? Is there a way just to stop showing comments right now? Wei-hsing
-------------- Original message ---------------------- From: "THOMAS, BRIAN M (SBCSI)" <[email protected]>
Direct database time...
First, BACK UP your database.
No. First, if you are not an SQL hack, get someone who is.
Now, BACK UP your database.
Next, you must delete all rows connected with spam comments.
I'm going to derive the process whereby you do this, because I'm a little rusty on the XWiki database schemas, and because I'm an inveterate pedant. You can skip some of these steps that exist for information only if you wish, especially if you already know how to do them.
First, it's necessary to know which rows constitute comments. XWiki's dynamic typing is a great boon to users and an equally great bane to administrators, as you will soon see. The xwikiclasses table describes user-defined classes (well, actually it doesn't; it names them, and describes server-defined custom classes if there are any, which fortunately there aren't, because I couldn't tell you what to do if there were). The class that defines XWiki comments is named XWiki.XWikiComments. The xwikiclassesprop table contains the list of fields for a given class, which is identified not by its name but by the unique ID from the xwikiclasses table. So you need to get the ID of the row in the xwikiclasses table whose name field is 'XWiki.XWikiComments', and retrieve the field name and type from all rows of the xwikiclassesprop table that have that ID. The following SQL does this less verbosely:
select xwp_name, xwp_classtype from xwikiclassesprop p, xwikiclasses c where xwo_name = 'XWiki.XWikiComments' and p.xwp_id = c.xwo_id
The results of this should be: +-----------+---------------------------------------------+ | xwp_name | xwp_classtype | +-----------+---------------------------------------------+ | author | com.xpn.xwiki.objects.classes.StringClass | | comment | com.xpn.xwiki.objects.classes.TextAreaClass | | date | com.xpn.xwiki.objects.classes.DateClass | | highlight | com.xpn.xwiki.objects.classes.TextAreaClass | | replyto | com.xpn.xwiki.objects.classes.NumberClass | +-----------+---------------------------------------------+ 5 rows in set (0.00 sec)
You'd have to go into the XWiki source (more specifically, the Hibernate configuration, I believe) to see how the xwp_classtype field above links to what I'm doing below; I leave that as an exercise for the reader. All that's really needed is to know the types of the fields and which tables they're stored in.
I've actually misled you somewhat: this is not the actual schema of existing comment objects but the prototype for the creation of new comments. I've done this ostensibly to give you a fuller understanding of how XWiki allows one to change the definition of a user-defined class without breaking existing instances, but it's really because I got tripped up on it myself, and I wanted to share my misery with you.
To get the actual schema for existing comments you have to look at the xwikiobjects table, which defines actual XWiki object instances in terms of their class names and the document to which they belong. The xwo_id field is the field that ties all of the tables together.
So the following query:
select distinct(xwp_name), xwp_classtype from xwikiobjects o, xwikiproperties p where xwo_classname='XWiki.XWikiComments' and xwo_id = xwp_id
yields a similar-looking result (in my database, anyway), because the XWiki.XWikiComments class hasn't changed since these comments were added: +-----------+-------------------------------------------+ | xwp_name | xwp_classtype | +-----------+-------------------------------------------+ | author | com.xpn.xwiki.objects.StringProperty | | date | com.xpn.xwiki.objects.DateProperty | | comment | com.xpn.xwiki.objects.LargeStringProperty | | replyto | com.xpn.xwiki.objects.IntegerProperty | | highlight | com.xpn.xwiki.objects.LargeStringProperty | +-----------+-------------------------------------------+ 5 rows in set (0.05 sec)
So now we know (without explaining how) that (for example) the contents of the "author" field of an instance of the class named XWiki.XWikiComments is in the xwikistrings table, in a row whose xws_id field matches the xwo_id field of an xwikiobjects row whose xwo_classname file is "XWiki.XWikiComments". All the field values can be found thus:
field name table name field value author xwikistrings xws_value date xwikidates xws_value comment xwikilargestrings xwl_value replyto xwikiintegers xwi_value highlight xwikilargestrings xwl_value
So all the fields of a comment would be retrieved, given its ID, by the following query:
select s.xws_value, /* author name */ d.xws_value, /* comment date */ l.xws_value, /* comment text */ i.xwi_value, /* reply-to field */ h.xwl_value /* highlight field */ from xwikistrings s, xwikidates d, xwikilargestrings l, xwikiintegers i, xwikilargestrings h where s.xws_id = <comment ID> and s.xws_name = 'author' and d.xws_id = s.xws_id and d.xws_name = 'date' and l.xws_id = s.xws_id and l.xws_name = 'comment' and i.xwi_value = s.xws_id and i.xwi_name = 'replyto' and h.xwl_value = s.xws_id and h.xwl_name = 'highlight'
Of course, the job is much simpler than this, for several reasons. First, the replyto and highlight fields are not populated by XWiki's default templates, so they're always null anyway; second, of the remaining values, each one is uniquely identified by the object's ID field anyway, so the respective name fields don't need to be specified.
To get a list of all comments in the database, with only their object ID field, author name, and date (which produces a fairly neat display) this query should do it.
select o.xwo_id, s.xws_value, d.xws_value from xwikiobjects o, xwikistrings s, xwikidates d where xwo_classname='XWiki.XWikiComments' and s.xws_id = o.xwo_id and d.xws_id = o.xwo_id order by xwo_id
With all that as background, what you need to do is:
1. Identify the offending comments (by their object IDs).
If the comments were all from bogus registrations, then the above query with an added qualifier something like:
where s.xws_value in ('XWiki.spammer1', 'XWiki.spammer2'[,...])
will give you the object IDs you need.
If you have allowed anonymous users to comment, then other criteria must be used, although of course "s.xws_value = 'XWiki.XWikiGuest'" should be in your WHERE clause. Most likely, you could probably nail most of them by saying
select o.xwo_id from xwikiobjects o, xwikilargestrings l where o.classname = 'XWiki.XWikiComments' and l.xwl_id = o.xwo_id and l.xwl_value like "%Viagra%" or l.xwl_value like "%sex" or l.xwl_value like "%stock%" or ...
you get the idea.
The best idea, of course, is to keep running and refining your query until you are sure that you've identified all of the offending comments and only the offending comments.
2. After you've built a query that identifies the set of records you want to remove, you must delete them from each table where they appear. The easiest way to do this is to modify your query to return only the xwikiobjects.xwo_id field in the SELECT clause and put it into a temporary table:
create temporary table badcomments (comment_id integer);
insert into badcomments select o.xwo_id from xwikiobjects o [... where, etc...];
Then delete every row from xwikistrings, xwikilargestrings, xwikidates, and xwikiintegers where the respective ID fields (xws_id, xwl_id, xws_id, and xwi_id, respectively) match the comment_id field from your badcomments table.
You can also do it using the $xwiki.search() method, but there you have to tie the XWiki objects together using HQL. The advantage is that you don't have to have server access; the disadvantage is that it's miserable to get it right (my opinion).
brain[sic]
-----Original Message----- From: [email protected] [mailto:[email protected]] Sent: Tuesday, October 17, 2006 12:32 AM To: [email protected] Subject: [xwiki-users] delete spam comments
Hi, My wiki site got over a thousand spam comments, anyone know how can I delete them quickly (better than edit object, then delete one by one). Wei-hsing
---------- Forwarded message ---------- From: "THOMAS, BRIAN M (SBCSI)" <[email protected]> To: <[email protected]> Date: Tue, 17 Oct 2006 15:11:00 +0000 Subject: RE: [xwiki-users] delete spam comments
-- You receive this message as a subscriber of the [email protected] mailing list. To unsubscribe: mailto:[email protected] For general help: mailto:[email protected]?subject=help ObjectWeb mailing lists service home page: http://www.objectweb.org/wws
-- You receive this message as a subscriber of the [email protected] mailing list. To unsubscribe: mailto:[email protected] For general help: mailto:[email protected]?subject=help ObjectWeb mailing lists service home page: http://www.objectweb.org/wws
-- jeremi
-- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.408 / Virus Database: 268.13.4/478 - Release Date: 17/10/2006
-- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.408 / Virus Database: 268.13.4/478 - Release Date: 17/10/2006
-- You receive this message as a subscriber of the [email protected] mailing list. To unsubscribe: mailto:[email protected] For general help: mailto:[email protected]?subject=help ObjectWeb mailing lists service home page: http://www.objectweb.org/wws
-- jeremi
-- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.408 / Virus Database: 268.13.4/478 - Release Date: 17/10/2006
-- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.408 / Virus Database: 268.13.4/478 - Release Date: 17/10/2006
-- You receive this message as a subscriber of the [email protected] mailing list. To unsubscribe: mailto:[email protected] For general help: mailto:[email protected]?subject=help ObjectWeb mailing lists service home page: http://www.objectweb.org/wws
-- jeremi
-- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.408 / Virus Database: 268.13.4/478 - Release Date: 17/10/2006
-- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.408 / Virus Database: 268.13.4/478 - Release Date: 17/10/2006
Whoops. Forgot to ask about that. Then, you're stuck with the $xwiki.search() method. Looks something like this: #set($hql = "from BaseObject as obj, LargeStringProperty as s where obj.className = 'XWiki.XWikiComments' and obj.id = s.id.id") #foreach($comment in $xwiki.search($hql)) #foreach($field in $comment) #if($velocityCount == 1) $field.name #else $field #end #end #end The class names from the xwp_classtype queries I gave you are the ones that you need to use in HQL, which is an SQL-like syntax on the object hierarchy persisted by Hibernate. I think the results are easier to deal with using groovy, but haven't done that lately. There's a better way, I think, than simply counting the fields and working on the values, but I've forgotten it if I ever knew. Of course, once you've found them, you have to remove them from their respective documents, which is something I haven't done, but I think it's XWikiDocument.addObjectsToRemove(Object) and then Document.saveDocument(). brain[sic]
-----Original Message----- From: [email protected] [mailto:[email protected]] Sent: Tuesday, October 17, 2006 10:22 AM To: [email protected] Subject: RE: [xwiki-users] delete spam comments
Hi, Brian, Thanks a lot. My site is on Xwiki.com, can I do all these steps? Is there a way just to stop showing comments right now? Wei-hsing
Then, you're stuck with the $xwiki.search() method. Afraid that's out of bounds - $xwiki.search() has been restricted so you need programming rights for that too on xwiki.com. :(
The best solution is to let Jeremy add his scripts - and don't edit&save them else you will lose the ability to execute them. Jeremy, by the way, how much do we have to bribe you to get programming rights on xwiki.com? :D Regards, Robin. On 17/10/06, THOMAS, BRIAN M (SBCSI) <[email protected]> wrote:
Whoops. Forgot to ask about that.
Then, you're stuck with the $xwiki.search() method. Looks something like this:
#set($hql = "from BaseObject as obj, LargeStringProperty as s where obj.className = 'XWiki.XWikiComments' and obj.id = s.id.id")
#foreach($comment in $xwiki.search($hql)) #foreach($field in $comment) #if($velocityCount == 1) $field.name #else $field #end #end #end
The class names from the xwp_classtype queries I gave you are the ones that you need to use in HQL, which is an SQL-like syntax on the object hierarchy persisted by Hibernate.
I think the results are easier to deal with using groovy, but haven't done that lately. There's a better way, I think, than simply counting the fields and working on the values, but I've forgotten it if I ever knew.
Of course, once you've found them, you have to remove them from their respective documents, which is something I haven't done, but I think it's XWikiDocument.addObjectsToRemove(Object) and then Document.saveDocument().
brain[sic]
-----Original Message----- From: [email protected] [mailto:[email protected]] Sent: Tuesday, October 17, 2006 10:22 AM To: [email protected] Subject: RE: [xwiki-users] delete spam comments
Hi, Brian, Thanks a lot. My site is on Xwiki.com, can I do all these steps? Is there a way just to stop showing comments right now? Wei-hsing
-- You receive this message as a subscriber of the [email protected] mailing list. To unsubscribe: mailto:[email protected] For general help: mailto:[email protected]?subject=help ObjectWeb mailing lists service home page: http://www.objectweb.org/wws
On 10/17/06, Robin Fernandes <[email protected]> wrote:
Then, you're stuck with the $xwiki.search() method. Afraid that's out of bounds - $xwiki.search() has been restricted so you need programming rights for that too on xwiki.com. :(
The best solution is to let Jeremy add his scripts - and don't edit&save them else you will lose the ability to execute them.
Jeremy, by the way, how much do we have to bribe you to get programming rights on xwiki.com? :D
hi, It will be very expensive :-) I'm sorry but it's not really possible. What function do you need? We can try to do something for you because your are helping XWiki. jeremi -- jeremi
QED to my previous message, i.e.: not giving users enough privileges to do their jobs means you have to give them too much - or run around doing things for them that they should be able to do for themselves. brain[sic]
-----Original Message----- From: jeremi joslin [mailto:[email protected]] Sent: Tuesday, October 17, 2006 12:12 PM To: [email protected] Cc: [email protected] Subject: Re: [xwiki-users] delete spam comments
On 10/17/06, Robin Fernandes <[email protected]> wrote:
Then, you're stuck with the $xwiki.search() method. Afraid that's out of bounds - $xwiki.search() has been restricted so you need programming rights for that too on xwiki.com. :(
The best solution is to let Jeremy add his scripts - and don't edit&save them else you will lose the ability to execute them.
Jeremy, by the way, how much do we have to bribe you to get programming rights on xwiki.com? :D
hi, It will be very expensive :-) I'm sorry but it's not really possible. What function do you need? We can try to do something for you because your are helping XWiki.
jeremi
-- jeremi
participants (5)
-
Jean-Lou Dupont -
jeremi joslin -
Robin Fernandes -
THOMAS, BRIAN M (SBCSI) -
wangwh＠att.net