Category Archives: MySQL

What goes around, comes around

I’m not a big believer in karma, but this week I experienced some karma-like effects. Two years ago for work, I developed code to protect wiki websites. Then I published it on my blog.

This weekend a software upgrade caused this protection code to stop working on our websites. I couldn’t find an answer. Then yesterday, some chap named Nathan left a comment describing the solution. I hadn’t asked for help. He was simply documenting his own experience. But it was just what I needed.

This is fundamental to open source software — the creation of a software commons. It’s also what happens on Wikipedia, the creation of a knowledge commons.

In Love Is the Killer App, Tim Sanders suggests freely sharing your knowledge and your network, not hoarding them.

Jon Udell talks of “narrating” one’s work from day to day. This allows everyone to share in your vast brain knowledge, and it becomes your living résumé. I’d like to do more of that.

Essential PHP Security

I recently finished reading Essential PHP Security by Chris Shiflett (O’Reilly). It was a good, quick read, and for me it was mostly a review of principles I had previously read on Chris’s blog. The main principles are filter input and escape output. Using separate arrays for each kind of data is a best practice:


// filter input and assign it to the "$clean" array
if (ctype_alnum($_POST['name']))
$clean['name'] = $_POST['name'];

// escape HTML output with htmlentities()
$html['name'] = htmlentities($clean['name'], ENT_QUOTES);
echo "You entered the name $html[name].";

// escape MySQL output with mysql_real_escape_string()
$mysql['name'] = mysql_real_escape_string($clean['name']);
mysql_query("INSERT INTO table (name) VALUES ('$mysql[name]')");

After reading the book I was only left with one question: is HTTP Authentication over SSL fairly secure? (I assumed it would be.) I emailed Chris with my question and he responded quickly in the affirmative. Thanks, Chris.

CSS Best Practices

Last month at the UPHPU meeting, Wade Shearer presented on CSS best practices. He’s one of the few programmers in the group that’s a designer first, and a programmer second, so he has unique insight into web design. Here are my notes:

  • Keep HTML free of presentational attributes
  • Write clean, semantic HTML
  • Use HTML tables semantically–for tabular data, not layout (generally)
  • Create print-friendly version of all your pages using media=print
  • For input buttons, use a 1px invisible GIF and then restyle the image with CSS:

  • <!-- HTML -->
    <input type="image" src="1px.gif" class="next_button" />

    // CSS
    input.next_button
    {
    background-image: url(next_button.gif);
    }

  • Do the same thing for image links, but for accessibility include link text overwritten by a style:

  • <!-- HTML -->
    <a href="next_page.html" class="next_button" />Next Page</a>

    // CSS
    a.next_button
    {
    display: block;
    background-image: url(next_button.gif);
    text-indent: -99999px;
    }

  • Use comments in CSS to separate typography, headers, layout, forms
  • Sometimes body styles don’t cascade into tables like they should so you need to repeat body styles on all tables
  • begin with a few default styles:

  • table, tr, td
    {
    margin:0;
    padding:0;
    border:0;
    border-collapse:collapse;
    vertical-align:top;
    }

    form
    {
    padding:0;
    margin:0;
    }

    img
    {
    border:none;
    padding:0;
    margin:0;
    }

  • Restyling the horizontal rule (<hr>) with an image can be a beautiful addition to a web page
  • Keep a library of helpful CSS classes:

  • .float_left
    {
    float:left;
    }

    .float_right
    {
    float:right;
    }

    .clear
    {
    clear:both;
    }

    .col2_left
    {
    float:left;
    width:45%;
    }

    .col2_right
    {
    float:right;
    width:45%;
    }

  • Use PHP to do browser sniffing and to include CSS files relevant to the section.
  • For more best practices, take a peak at the stylesheets for Wade’s place of employment, Doba.com

Password protecting MediaWiki with mod_auth_mysql

MediaWiki is the powerful software on which Wikipedia and many other sites are built. It does not, however, come with the option to password protect pages from being viewed. (It can password protect pages from being edited.)

If you need to setup a private, members-only wiki for internal use, here is how you can do it with MediaWiki software and the Apache server extension mod_auth_mysql:

1. Install MediaWiki as usual. Create a user account for yourself.

2. Add the following line to your LocalSettings.php file, located in the root of your MediaWiki installation. This will cause MediaWiki to use a simple MD5 hash for user passwords in the database, instead of the more complicated “salted hash hash” that it normally uses.

$wgPasswordSalt = false;

3. Activate mod_auth_mysql in Apache. This is usually done with a LoadModule line in your Apache configuration file (httpd.conf), provided the module is available. (If not, you may need to compile or download the module.)

LoadModule mysql_auth_module libexec/apache2/mod_auth_mysql.so

4. Create a new MySQL user that has SELECT access to the “user_name” and “user_password” fields in the “user” table of your MediaWiki installation. Apache will use this MySQL user for connecting to the MediaWiki database.

5. Configure mod_auth_mysql to use the MediaWiki user table for authentication by placing the follow directives in your Apache configuration file:



AuthName "This wiki is password protected (make sure the first letter of the username is Uppercase)"
AuthType Basic
require valid-user
AuthMySQLEnable On
AuthMySQLHost localhost
AuthMySQLUser unprivilegeduser
AuthMySQLPassword thesecretpassword
AuthMySQLDB mediawikidatabase
AuthMySQLUserTable user
AuthMySQLNameField user_name
AuthMySQLPasswordField user_password
AuthMySQLPwEncryption md5
AuthMySQLAuthoritative On

6. Restart Apache.

Your installation of MediaWiki should now be password-protected, but your username and password will let you in. This protects the entire wiki; no one will even know that MediaWiki is present until they login. To give other people access, you can either create user accounts for them, or you can create a guest account that they can use until they sign themselves up.

P.S. Thanks to Gary Thornock for helping me with the details of installing mod_auth_mysql on FreeBSD.

UPDATE (2008-09-11):
The latest version of MediaWiki (version 1.13) uses a new password format which is incompatible with mod_auth_mysql. It prepends “:A:” to each MD5 hash. Here is a workaround:

1. Create a MySQL view that mirrors the username and password, minus the prefix:
CREATE VIEW user_view AS SELECT user_id, user_name, substring_index(user_password, ':', -1) AS user_password FROM user;
2. Configure mod_auth_mysql to use user_view instead of user as the lookup table.