Thursday, June 26, 2014

Disable new user signups (registration spam) on a wordpress blog

The lovely Carol has a wordpress blog. She's been getting lots of "new user registrations," and she spends too much time deleting these new "users". These are not real people; they're more likely "spammers wanting to take control of the system through multiple user registrations" according to this site.

I did a web search on "wordpress new users" or something like this (no quotation marks) and found an article titled “How to Disable New User Registration on WordPress Using PhpMyAdmin,” which was useful, except I don't have PhpMyAdmin. And I don't have cPanel either. And that was the most promising of the links that came up. Bummer.

What's a geek to do? well, this:

$ ls
dec24.html                 wp-config-sample.php
fire-safe-box/             wp-content/
INDEX.HTML.oneclickbackup  wp-cron.php
index.php                  wp-includes/
license.txt                wp-links-opml.php
md5sums.txt                wp-load.php
readme.html                wp-login.php
wp-activate.php            wp-mail.php
wp-admin/                  wp-settings.php
wp-blog-header.php         wp-signup.php
wp-comments-post.php       wp-trackback.php
wp-config.php              xmlrpc.php
$ 
H'm, wp-signup.php you say? That looked promising. I noticed this:
 30 if ( !is_multisite() ) {
 31         wp_redirect( site_url('wp-login.php?action=register') );
 32         die();
 33 }
"wp-signup.php" [readonly]
THAT looked promising. I looked in wp-login.php and searched for "register"; I found this:
646         if ( !get_option('users_can_register') ) {
647                 wp_redirect( site_url('wp-login.php?registration=disabled') );
648                 exit();
649         }
"wp-login.php" [readonly] 921 lines
O-kay! So we want to kill off the option 'users_can_register'.

By looking around, I discovered that get_option() gets things from the cache. I searched wordpress.org for "get_option" and landed at http://wordpress.org/search/get_option, which refers to the options database table. Somehow I got the idea of looking in wp-config.php where I discovered this:

// ** MySQL settings ** //
define('DB_NAME', 'bedrockdb');    // The name of the database
define('DB_USER', 'fred_flint');     // Your MySQL username
define('DB_PASSWORD', 'yabbadabbadoo'); // ...and password
define('DB_HOST', 'hanna.bar.be.ra');    // 99% chance you won't need to change this value
(Of course the names are changed to protect… well, myself.) In another window, I typed:
$ mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (111)
D'oh! I had to fill in the hostname and the username and…
$ mysql -h 'hanna.bar.be.ra' -u 'fred_flint' -p
Enter password: <here I typed “yabbadabbadoo”>
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 24209555 to server version: 5.5.37-35.1-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use bedrockdb
Database changed
mysql> show tables;
+-----------------------+
| Tables_in_bedrockdb   |
+-----------------------+
| wp_commentmeta        |
| wp_comments           |
| wp_links              |
| wp_options            |
| wp_postmeta           |
| wp_posts              |
| wp_subscribe2         |
| wp_term_relationships |
| wp_term_taxonomy      |
| wp_terms              |
| wp_usermeta           |
| wp_users              |
+-----------------------+
12 rows in set (0.02 sec) 
Okay, that's good. Now which table? wp_options? Yes! And according to the above article, I want wp_options. So I said:
mysql> describe wp_options;
+--------------+---------------------+------+-----+---------+----------------+
| Field        | Type                | Null | Key | Default | Extra          |
+--------------+---------------------+------+-----+---------+----------------+
| option_id    | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| option_name  | varchar(64)         | NO   | UNI |         |                |
| option_value | longtext            | NO   |     | NULL    |                |
| autoload     | varchar(20)         | NO   |     | yes     |                |
+--------------+---------------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

mysql> select * from wp_options where option_name = 'users_can_register'; 
+-----------+--------------------+--------------+----------+
| option_id | option_name        | option_value | autoload |
+-----------+--------------------+--------------+----------+
|         6 | users_can_register | 1            | yes      |
+-----------+--------------------+--------------+----------+
1 row in set (0.02 sec)
It's been a while since I updated a table in MySQL. Naturally I went to a web search to find a list of SQL commands. This one told me what I needed, down in "Data Commands."

Let's see, I wanted to change the option_value from 1 to 0 where… OK, I tried this.

mysql> update wp_options set option_value = 0 where option_name = 'users_can_register';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
Wow, it worked the first time! How often does that happen? Just to be sure, I typed this:
mysql> select * from wp_options where option_name = 'users_can_register';
+-----------+--------------------+--------------+----------+
| option_id | option_name        | option_value | autoload |
+-----------+--------------------+--------------+----------+
|         6 | users_can_register | 0            | yes      |
+-----------+--------------------+--------------+----------+
1 row in set (0.01 sec)

mysql> quit
Bye
Now for the acid test. I went to the sign-up page "wp-signup.php" and got: "User registration is currently not allowed."

And that's what I'll do next time.

Thursday, June 12, 2014

CSV to QIF transformation with Python3

This evening I discovered that the folks at my bank, or more probably the folks running the online banking website, have "upgraded" the user interface, and they no longer support downloading transactions to the "old" Quicken file format (i.e., "QIF"). Instead, they (mostly) support "export"ing transactions to CSV.

Fortunately, a format conversion from CSV to QIF isn't that hard. I first did a web search, which yielded

  • a website (upload your csv and we'll give you a qif file),
  • some Windows (I guess) software--free to download and try, but I guess they want money for it, and
  • maybe something else that I didn't think would work for me
so I decided to just hack something together. Of course it would be in Python, and probably python3.

I remembered that there was a module that knew how to read and write CSV files; a quick look at the library reference showed that the module is called, oddly enough, "csv"; the result is described here (produced by "pydoc3 -w csv_to_qif")

 
 
csv_to_qif
index
/mnt/home/collin/projects/csv-qif/csv_to_qif.py

transform a "csv" file on stdin to a "qif" file on stdout.

 
Modules
       
csv
os
sys

 
Functions
       
main(infile, outfile)
Read infile -- we expect valid entries to have 9 fields:
xaction#, date, descr, memo, amt debit, amt credit, bal, check#, fees
 
Write an outfile that looks like...
    !Type:Bank
    C*
    Dmm/dd/yyyy
    T<amount> (negative for withdrawal)
    N###      (## = checkNumber)
    P<descr>  (description...)
    ^
Each entry begins with "C" and ends with "^".
Here's the code. It is Copyright©2014 by me, but you are free to use, redistribute and or modify it under the terms of GPL2; see below.
#!/usr/bin/python3 -utt
# vim:et
'''transform a "csv" file on stdin to a "qif" file on stdout.'''

# This program is free software: you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation, version 2.
# NO WARRANTY express or implied.

import csv
import os
import sys

def main(infile, outfile):
    '''Read infile -- we expect valid entries to have 9 fields:
    xaction#, date, descr, memo, amt debit, amt credit, bal, check#, fees
    
    Write an outfile that looks like...
        !Type:Bank
        C*
        Dmm/dd/yyyy
        T (negative for withdrawal)
        N###      (## = checkNumber)
        P  (description...)
        ^
    Each entry begins with "C" and ends with "^".'''

    outfile.write('!Type:Bank\n')

    reader = csv.reader(infile)
        
    for arow in reader:
        if len(arow) == 1:
            continue                    # a couple of title lines
        if len(arow) != 9:
            print("I don't grok this row with len=%d:\n\t'%s'" %
                        (len(arow), "',".join(arow)), outfile=sys.stderr)
            continue
        trans_num, date, descr, memo, deb, cred, bal, cnum, fees = arow
        if date.startswith('Date'):
            continue                    # that's the header line
        outfile.write('C*\n')
        outfile.write('D%s\n' % date)
        if deb:
            outfile.write('T%s\n' % deb)
        else:
            outfile.write('T%s\n' % cred)
        if cnum:
            outfile.write('N%s\n' % cnum)
        outfile.write('P%s\n' % descr)
        outfile.write('^\n')
    sys.exit(0)

if __name__ == '__main__':
    try:
        infile = open(sys.argv[1], 'r')
        outfile = open(sys.argv[2], 'w')
    except:
        print('usage: %s INFILE OUTFILE' % os.path.basename(sys.argv[0]))
    main(infile, outfile)
UPDATE 2014-06-24: Added a "C*" at the beginning of each entry. Without this, my quicken "check register" wasn't showing the "checks" as being Cleared.