Apache, basic auth and mysql

Posted 1 year, 5 months ago at 11:17 pm. 3 comments

Today i had some issues with mysql dbd module for apache.

I had two servers: One located in Slovenia and second located in US.
Imagine how rocket-fast this thing was, when i got 50 requests per page and all of them were authenticated via dbd mysql module.

So i decided to wrote my own script which will fetch all the users from database, convert their passwords with proper encryption and then put them into an .htpasswd file. Script will do this every few minutes.

Copy this code into your favorite nix editor, edit variables and save it on chosen location.

# Variables

define("DB_HOST", "localhost");
define("DB_USER", "htpasswd");
define("DB_PASS", "somepasswd");
define("DB_NAME", "htpasswd");
# data dir - for .htpasswd file
define("ROOTDIR", "/usr/local/www/htpasswd/");
# .htpasswd file - we will call this file from .htaccess
define("HTPASSWDFILE", ROOTDIR . ".htpasswd");
# tmp file for dumping data
define("HTPASSWDTMP", ROOTDIR . ".htpasswd.tmp");
# backup of previous .htpasswd file (if something's foo) 
define("HTPASSWDBAK", ROOTDIR . ".htpasswd.bak");
# maybe you would want to add some static users, 
# which will be here even if database is empty or there are no "valid" users in database.
$static_users = array(
        'staticuser' => 'hispass'
);

# FUNCTIONS

# htpasswd
function htpass($pass) {
        return crypt($pass, base64_encode($pass));
}
# database
function connect_to_db() {
        if(!$link = @mysql_connect(DB_HOST,DB_USER,DB_PASS)){
            die("MYSQL ERROR: " .mysql_error(). "n");
        }else{
            if(!@mysql_select_db(DB_NAME)){
                die ("MYSQL ERROR:".mysql_error());
            }else{
                return $link;
            }
        }
}

# init
$link = connect_to_db();

$query = "select username, password from protected_users where DATE(NOW()) < expires";
if(!$result = @mysql_query($query)) {
        die(mysql_error(). "n". $query);
} else {
        # create backup file if .htpasswd already exists
        if(file_exists(HTPASSWDFILE)) {
                copy(HTPASSWDFILE, HTPASSWDBAK);
        }

        # dump DB users into file
        $file = fopen(HTPASSWDTMP, 'w') or die("Can't open file");
        while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
           fwrite($file, $row[0]. ":" .htpass($row[1]). "n");
        }
# dump static users into file
        foreach($static_users as $user=>$pass) {
           fwrite($file, $user. ":" .htpass($pass). "n");
        }

        # close links / files
        mysql_close($link);
        fclose($file);

        rename(HTPASSWDTMP, HTPASSWDFILE);
}

Create the database and prepare tables:

CREATE DATABASE IF NOT EXISTS htpasswd;

CREATE TABLE IF NOT EXISTS `protected_users` (
  `id` int(11) NOT NULL auto_increment,
  `username` varchar(32) NOT NULL,
  `password` varchar(32) NOT NULL COMMENT 'plain password',
  `expires` date NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

INSERT INTO `protected_users` (`id`, `username`, `password`, `expires`) VALUES
(1, 'test', 'test', '2012-01-01');

Give some privileges to DB user “htpasswd”:

GRANT SELECT ON htpasswd.* TO 'htpasswd'@'localhost' IDENTIFIED BY 'somepassword';

Now set up your .htaccess file

AuthType Basic
     AuthName "Protected"
     AuthBasicProvider file
     AuthUserFile /usr/local/www/htpasswd/.htpasswd
     Require valid-user

and cron job:

*/5 * * * * cd /path/to/your/script/; /path/to/bin/php yourscript.php

.htpassword will be updated every 5 minutes. If you want to increase/decrease time between updates just change the value in cron job.

Have fun!

Get rid of those stupid ^M’s from your code.

Posted 1 year, 5 months ago at 4:21 pm. 5 comments

Yes i’m geek and i’m using VI. I found tons of ^M’s in wordpress files while i was coding this blog.

Here’s how to get rid of them:

Open your file in vi

# vi file.php

then press “:” and type this:

:%s/[ctrl+v][ctrl+m]//g

[ctrl+v] … hold control and press v
[ctrl+m] … hold control and press m

Voila, there’s no more ^M’s and your code is clean in the way it should be!