Synch vBulletin Mailing List with 1-2-All

OK, every now and then I post something technical on this site, and this is one of those posts. The problem? You have a mailing list of forum members which reside in the vBulletin database. You use 1-2-All, from ActiveCampaign, to manage your email lists. vBulletin’s email system sucks in comparison because it is dirt slow and you have to baby it while it sends out emails. The solution? Use the Database Synch Add-on for 1-2-All to bring all the forum members from vBulletin into the 1-2-All software, thereby giving you a mailing list of your forum members. The database sync utility used to be an extra charge, but now they are bundling it with all 1-2-All sales.

First off, the reason this issue justifies a blog post (in my eyes) is because its anything but straightforward. For two reasons:

1. vBulletin uses a strange integer format to store user permissions, so its not a simple matter of running a query on an obvious field. The email address is a separate database field, however vBulletin has an option for members to choose whether they want to allow the admins (you) to email them. And the vBulletin database is anything but straight-forward on how to extract which users are opting into the list and which are not.
2. The Database Sync utility with 1-2-All allows filters, but you have to essentially trick it into doing what you want in terms of vBulletin. In essence, it seems like you have to take advantage of a potential security problem with the add-on to make it work. More on this in a bit.

So, here is how to to it. Lets get right to the point.

1. Set up a new synchronization profile in the 1-2-All add-on. Enter a name, the database information for your vBulletin database, the list you want to add the emails to, etc. I would set it up to add to a new, blank list. Not an existing one. Just in case.
2. Next, you will map out those database fields from vBulletin to the fields in 1-2-All. Take the username field in the “user” tabkle of Vbulletin and map it to the “Subscriber’s Name” field in 1-2-All. Take the “email” field from the “user” table in VB and map it to the email field in 1-2-All. Then save.
3. Next, you need to click on “Rules” for the profile you just set up. This is the tricky part. By design, the synching utility only displays a field dropdown, a logical operator (like less than, greater than, etc), and a text box to enter your condition. However, the thing to realize is that anything that is typed into that text box is surrounded by single quotes in the final query. The query that needs to be run here is:

   WHERE userid > ’0′ AND (options & 16) AND email!=”

   By design, the sync utility does not allow a query like that. The “(options & 16)” part is not really possible (at least obviously) with the utility, however that is what is necessary to decipher from the vBulletin database which emails are allowed to be emailed and which don’t want to be part of your list. So, to get around the built-in single quote problem, you essentially need to enter single quotes into your filter so as to fool this thing into running the query you want. So, to get it done, choose “userid” in the field dropdown list, choose “greater than” in the logic list, then enter the following in the text box:

   0′ AND (options & 16) AND email!=’

   With this, the query will work, and you’re using your own single quotes to work in conjunction with the ones that are built-in to make a valid query on multiple fields at the same time.

Not exactly easy. And, essentially, you’re taking advantage of an unfiltered text input into that query to get it done. In effect, you’re taking advantage of a security hole in the add-on, it seems to me. But, hell, I don’t know. If it is, I just hope they don’t “fix” it. Either that, or provide some alternative means to include fancier logic into the GUI for setting up the SQL query.

Anyway, for some of you, this will be boring. For those using the same software as I was, this will probably save you a boatload of time.

Like what you read?

If so, please join over 12,000 people who receive exclusive online business and blogging tips, and get a FREE COPY of my eBook, Six Figure Blogger Blueprint (PDF and MP3)! Just enter your name and email below: