RE: [xwiki-users] delete spam comments

In other words, a backdoor? A security hole? Please more details on this 'code without programming rights'. Jld. -----Original Message----- From: jeremi joslin [mailto:jeremi23@gmail.com] Sent: October 17, 2006 11:45 To: xwiki-users(a)objectweb.org 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, wangwh(a)att.net <wangwh(a)att.net> 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)" <bt0008(a)att.com> > > 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

for the reader. 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

> > 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

> > > > 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 =

> > 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

mailing list. mailing list. -- 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 xwiki-users(a)objectweb.org

To unsubscribe: mailto:xwiki-users-unsubscribe@objectweb.org For general help: mailto:sympa@objectweb.org?subject=help ObjectWeb mailing lists service home page: http://www.objectweb.org/wws