mybb_convert_ips.py

# Req: MySQLdb, Python2.7

import MySQLdb, socket, string, sys, collections

try: 
  socket.inet_pton(socket.AF_INET, '80.80.80.80')
except: # Windows is being a dick like usual

  import ctypes

  class sockaddr(ctypes.Structure):
      _fields_ = [("sa_family", ctypes.c_short),
                  ("__pad1", ctypes.c_ushort),
                  ("ipv4_addr", ctypes.c_byte * 4),
                  ("ipv6_addr", ctypes.c_byte * 16),
                  ("__pad2", ctypes.c_ulong)]
   
  WSAStringToAddressA = ctypes.windll.ws2_32.WSAStringToAddressA

  def inet_pton(address_family, ip_string):
    addr = sockaddr()
    addr.sa_family = address_family
    addr_size = ctypes.c_int(ctypes.sizeof(addr))

    if WSAStringToAddressA(ip_string, address_family, None, ctypes.byref(addr), ctypes.byref(addr_size)) != 0:
        raise socket.error(ctypes.FormatError())

    return ctypes.string_at(addr.ipv4_addr, 4)

  socket.inet_pton = inet_pton

def log(msg, *args):
  print '[+]', msg % args

LIMIT = 1000

Update = collections.namedtuple('Update', ['table', 'primary', 'column'])

UPDATES = [Update(table   = 'mybb_users',         
                  primary = 'uid', 
                  column  = 'regip'),

           Update(table   = 'mybb_users',         
                  primary = 'uid', 
                  column  = 'lastip'),

           Update(table   = 'mybb_threadratings', 
                  primary = 'rid', 
                  column  = 'ipaddress'),

           Update(table   = 'mybb_posts',  
                  primary = 'pid', 
                  column  = 'ipaddress'),

           Update(table   = 'mybb_maillogs',      
                  primary = 'mid', 
                  column  = 'ipaddress'),
 
           Update(table   = 'mybb_adminlog',      
                  primary = 'id',  
                  column  = 'ipaddress'), # You might not have the ID column

           Update(table   = 'mybb_moderatorlog', 
                  primary = 'id',  
                  column  = 'ipaddress')] # You might not have the ID column
 
db = MySQLdb.connect(host='127.0.0.1', user='root', passwd='root', db='forum')

cur = db.cursor()

for update in UPDATES:
  log('UPDATING "%s" IN "%s"', update.column, update.table)

  cur.execute('SELECT COUNT(%s) FROM %s' % (update.column, update.table))
  AMOUNT = int(cur.fetchone()[0])

  log('%d ROWS TO UPDATE\n', AMOUNT)

  for CURR in range(0, AMOUNT, LIMIT):
    LEFT  = (AMOUNT - CURR) / LIMIT + 1
    
    uids  = []
    cases = []

    log('LEFT: %d run%s (%d/%d)', LEFT, '' if LEFT is 1 else 's', CURR, AMOUNT)

    cur.execute('SELECT %s, %s FROM %s limit %d, %d' % (update.primary, update.column, update.table, CURR, LIMIT))

    for uid, ip in cur.fetchall():
      if ip.find('.') != -1:
        try:
          cases.append((ip, socket.inet_pton(socket.AF_INET, ip).encode('hex')))
          uids.append(str(uid))
        except:
          print 'INVALID:', (uid, ip)

    if cases and uids:
      cur.execute('UPDATE %s SET %s = (CASE %s END) WHERE %s IN (%s);' % (update.table, update.column, ' '.join(['WHEN %s = "%s" THEN "%s"' % (update.column, c[0], c[1]) for c in cases]), update.primary, ','.join(uids)))
    else:
      print 'Fucking damn it:', update, CURR

  db.commit()

cur.close()
db.close()