Saturday, December 29, 2007

phpBB3 Allowed Attachments List MOD

A quick MOD for the phpBB3 posting form to display the allowed file extensions & filesizes for attachments.

MOD should take less than 5 minutes to do manually.
Files to edit are
posting.php
styles/prosilver/template/posting_attach_body.html

If a filesize has been explicitly set for a file type through the ACP it will use that value. If that value has been left at zero to denote no limit, it will use the servers' uploaded file size limit instead.



NOTE: Don't forget to Purge Cache via the ACP after making these file edits.

----[ OPEN ]----
styles/prosilver/template/posting_attach_body.html

----[ FIND ]----
<p>{L_ADD_ATTACHMENT_EXPLAIN}</p>
----[ AFTER ADD ]----

<p>{L_ALLOWED} {L_EXTENSION}:<!-- BEGIN allowed_extension --><!-- IF not allowed_extension.FIRST -->,<!-- ENDIF --> <acronym style="cursor:help;" title="{L_ALLOWED} {L_FILESIZE}: {allowed_extension.FILESIZE} {L_KB}">{allowed_extension.EXTENSION}</acronym><!-- END allowed_extension --></p>

----[ OPEN ]----
posting.php

----[ FIND ]----
// Attachment entry
----[ BEFORE ADD ]----
// Allowed extension list
$allowed_extensions = $cache->obtain_attach_extensions($forum_id);
unset($allowed_extensions['_allowed_']);
ksort($allowed_extensions);
$first_extension = true;
foreach($allowed_extensions as $ext => $vals)
{
if($vals['max_filesize'] == 0)
{
$vals['max_filesize'] = min(
eval('return ' . str_replace(array('k','m','g'), array('*1024','*1048576','*1073741824'), strtolower(trim(ini_get('upload_max_filesize')))) . ';'),
eval('return ' . str_replace(array('k','m','g'), array('*1024','*1048576','*1073741824'), strtolower(trim(ini_get('post_max_size')))) . ';')
);
}
$template->assign_block_vars('allowed_extension', array(
'FILESIZE' => number_format($vals['max_filesize'] / 1024, 2),
'EXTENSION' => $ext,
'FIRST' => $first_extension)
);
$first_extension = false;
}

----[ OPEN ]----
includes/ucp/ucp_pm.php

----[ FIND ]----
global $user, $template, $phpbb_root_path, $auth, $phpEx, $db, $config

----[ AFTER ADD ]----
, $cache

----[ FIND ]----
compose_pm($id, $mode, $action);

----[ AFTER ADD]----

// Allowed extension list
$allowed_extensions = $cache->obtain_attach_extensions(false);
unset($allowed_extensions['_allowed_']);
ksort($allowed_extensions);
$first_extension = true;
foreach($allowed_extensions as $ext => $vals)
{
if($vals['max_filesize'] == 0)
{
$vals['max_filesize'] = min(
eval('return ' . str_replace(array('k','m','g'), array('*1024','*1048576','*1073741824'), strtolower(trim(ini_get('upload_max_filesize')))) . ';'),
eval('return ' . str_replace(array('k','m','g'), array('*1024','*1048576','*1073741824'), strtolower(trim(ini_get('post_max_size')))) . ';')
);
}
$template->assign_block_vars('allowed_extension', array(
'FILESIZE' => number_format($vals['max_filesize'] / 1024, 2),
'EXTENSION' => $ext,
'FIRST' => $first_extension)
);
$first_extension = false;
}

----[ SAVE/CLOSE ALL FILES ]----
EoM

Wednesday, December 26, 2007

Database Storage of Apache Virtual Hosts

I came up with a workaround for storing Apache Virtual Hosts in a database. I figure it could be adapted to work on Windows using batch files, but I'm only going to go over doing it on a UNIX-like system here. Specificly Ubuntu, or Debian.

At least basic knowledge of the following is assumed.


The first thing that needs to be done is creating a database schema for storing virtual host containers. I'll stick with a simple schema for now so focus stays on Apache to Database connection.

This schema is for MySQL.
CREATE TABLE `vhosts` (
`active` BOOL NOT NULL DEFAULT '0',
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`container` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
INDEX ( `active` )
);


`container` will store the actual vhost container. For instance somthing like this.
NameVirtualHost *
<VirtualHost *>
ServerAdmin webmaster@domain.com
DocumentRoot /home/me/public_html/
</VirtualHost>


`id` is for relation to other tables. Pretty standard stuff.

`active` is an on/off switch for each virtual host. Apache will only include a virtual host from the database if it is active.


With a database table ready to be read from we can move to the part where Apache uses the data from that table.
In httpd.conf, apache#.conf, or whatever your system uses for Apaches' main configuration file there's a directive available called Include. If you edit your configuration file and look at the end of the file you may see an example of Include being used already.
If you're on a Debian system you're likely going to find somthing similar to the following.
# Include the virtual host configurations:
Include /etc/apache2/sites-enabled/


If you're not on a Debian system, you're going to need to create a directory in your Apache directory similar to that. It should be owned by root. You will also need to add an Include directive like the one shown above to your main conf file pointing to the directory you create so Apache knows to load any configuration files found in that directory.

If you are on a Debian system you're eventually going to be emptying that directory so there's no conflicts with the database method. So now would probably be a good time to import the sites from sites-available into the database & mark the `active` field according to which sites are also in `sites-enabled`, as well as backing up that directory.


Now that we have a database to store and retrieve virtual host containers from, & a place where Apache can load configuration files from it's time to write a command to build a configuration file full of virtual host containers from the database for Apache to load.

Here's an example of such a command. Note that you'll have to sort out permissions on your own, the --user & --pass are used here for example sake, though MySQL documentation warns against doing it that way because it leaves the password visible in the pid file for a short time.

Some options are on a new line for ease of reading. The command ends with output redirection to a file in the sites-enabled directory for Apache to load.
I'm using a UNIX domain socket here, but you could use a TCP connection if need be. You might also want to alter the file output redirection is beind aimed at.
mysql --user=root --pass=pass --batch --raw --silent
--database=Websites
--execute="SELECT container FROM vhosts WHERE active = 1"
--skip-column-names
--protocol=socket
--socket=/var/run/mysqld/mysqld.sock
>/etc/apache2/sites-enabled/vhosts.conf


Of course instead of using that directory as an include directory in Apaches' configuration file, you could comment out that particular include & point an include directly at the file you have the above command outputting to.

Now we have a method to connect Apache with a Database for obtaining virtual hosts, but there's still manual labor involved in needing to run that mysql command when Apache is [re]started or reloaded.

To help with this we will have to edit the shell script used to stop/start/restart Apache so that every time Apache is [re]started, or reloaded, this file is refreshed.
On Debian this script is /etc/init.d/apache2, since I'm using Apache2.

Basicly anywhere there is a log_daemon_msg that [re]starts Apache in this script, we add our own logging & our database command right before it. So we end up with some sections looking somthing like this.
log_daemon_msg "Extracting vhosts from database" "apache2"
mysql --user=root --pass=pass --batch --raw --silent --database=Websites --execute="SELECT container FROM vhosts WHERE active = 1" --skip-column-names --protocol=socket --socket=/var/run/mysqld/mysqld.sock >/etc/apache2/vhosts.conf
log_daemon_msg "Starting web server" "apache2"


Now whenever Apache is [re]started, or reloaded, it will be using the vhost containers for active sites stored in the database.
sudo /etc/init.d/apache2 restart

Saturday, December 22, 2007

phpbb3, postgresql, & Connection Failure

While installing phpBB3 & attempting to use a PostgreSQL-8.2 database on PHP5 and Apache2 on top of Ubuntu I kept getting the following error while testing the database connection.

Could not connect to the database, see error message below.
No error message given.



I was able to connect just fine using phpPgAdmin, I was also able to connect just fine using the pgsql example code on php.net.

In phpBB3s' DB code for PostgreSQL,
(phpBB3/includes/db/postgres.php)
on lines 55-57 was the following code.
   if ($sqlserver !== 'localhost')
{
$connect_string .= "host=$sqlserver ";
}


As soon as I commented out the if-condition like this,
   //if ($sqlserver !== 'localhost')
//{
$connect_string .= "host=$sqlserver ";
//}


entered localhost, & entered my port number in the connection details,



the connection test passed.

Update 12/23/2007
This appears to have somthing to do with the way pg_connect attempts to connect to the PostgreSQL server.
More details in my bug report at phpbb.com

Attempts should probably be made to allow PHP to connect using a UNIX domain socket since that method is supposed to be faster than the TCP method specifying localhost defaults to.
But to just git r done, commenting out the if-condition as I did should get things up and running.

Update 12/28/2007
I've got a UNIX domain socket listening for PostgreSQL now. The socket began registering itself after a system update required a reboot.
So if you've recently installed PostgreSQL & have an issue like this, you should try rebooting the system and see if the domain socket registers itself.

An interesting note: I have another installation of phpBB3 on the same server, the default install used about 350KB of space in the MySQL install & 8.5MB for the PostgreSQL install.

Saturday, December 15, 2007

MySQL & Microsoft Access Databases (mdb)

Update 12/17: There's a Perl script controller here that will export as an SQL file. It handles converting between each databases datatypes.

After a quick search for a way to import an MDB into MySQL didn't return anything I liked. I remembered I have a Ubuntu computer now & started looking for the pieces instead.

What I ended up doing boils down to this.
  1. Install mdbtools
  2. Determine table names
  3. Export table to CSV
  4. Determine column names & required data types
  5. Create tables in MySQL
  6. LOAD DATA INFILE
  7. PROCEDURE ANALYSE()
  8. Tweak data types


The first thing I found was a package called mdbtools. The package includes some MDB command line utilities. So far this has been usefull for extracting database tables from MDB files in CSV format.

As usual the package was easy to install from the command line.
sudo apt-get install mdbtools

I needed to use two of the utilities to extract my tables. The first one is mdb-tables which lists the names of the tables in the MDB.
me@box:/mdb$ mdb-tables database.mdb
table_one table_two


Now that I knew the exact table names I was able to use the second utility, mdb-export, to export the tables.
me@box:/mdb$ mdb-export
Usage: mdb-export [options] <file> <table>
where options are:
-H supress header row
-Q don't wrap text-like fields in quotes
-d <delimiter> specify a column delimiter
-R <delimiter> specify a row delimiter
-I INSERT statements (instead of CSV)
-D <format> set the date format (see strftime(3) for details)
-S Sanitize names (replace spaces etc. with underscore)
-Q <char> Use <char> to wrap text-like fields. Default is ".
-X <char> Use <char> to escape quoted characters within a field. Default is doubling.
me@box:/mdb$ mdb-export -X \ -H database.mdb table_one > table_one.csv


Now it's worth noteing the first time I didn't think to tell the output where to go and I ended up with hundreds of table rows scrolling across my screen until I pressed CTRL+C to hault it.

From there I opened the CSV file in Notepad2 and found out the column names by looking at the first line in the file.
I created a new database table with the largest possible data types in MySQL using phpMyAdmin.

Once I had my database table created, I used a LOAD DATA INFILE to import the CSV file into the table.
LOAD DATA
INFILE '/mdb/table_one.csv'
INTO TABLE database.table_one
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
IGNORE 1 LINES


After a few seconds I had a table full of data. I used phpMyAdmins' Propose table structure, which runs the following query.
SELECT * 
FROM `table_one`
PROCEDURE ANALYSE()


Using the info provided by that, and a little planning for what will happen with the database later, I was able to tweak the table a little so that it doesn't need to use the largest possible data types for columns. I just wanted to make sure I didn't truncate anything during the import.

It might be worth rigging this to work with the phpMyAdmin import controll panel, but I'll save that for another day.

Tuesday, December 11, 2007

Easier .htaccess On Windows

Now and then you'll see someone wondering how to create an .htaccess file for Apache on Windows. This usually appears to be people who have a private server setup on their local intranet for testing, then uploading to a remotely hosted server.
Windows apparently complains with an error message similar to the following when you attempt to create or rename a file to a name starting with a dot. Such as .htaccess.

You must type a filename !



What alot of people aren't aware of is that Apache actually has a setting you can alter to change the name of the file used for .htaccess.
The name of that setting is AccessFileName.

Now at first glance it would seem that altering the AccessFileName directive could cause major problems, and it can. You probably shouldn't alter this setting on anything other than a system in an intranet enviroment. You should also be prepared to rename existing .htaccess files when doing this.

That said, here's an example of what can be done with the AccessFileName directive. Note that this needs to be done in the Server Config or a Virtual Host container.
AccessFileName ht.htaccess

<Files ~ "^(ht)?\.ht">
Order allow,deny
Deny from all
</Files>


The line AccessFileName ht.htaccess should be pretty self explainitory. It makes the filename used ht.htaccess instead of just .htaccess
This way it's simple to create an ht.htaccess file on the Windows machine, then simply rename it to .htaccess once it gets uploaded to the server.

The second part of that example code isn't required, but it's done just in case you ever have other people with access to the machine. There's a similar section in your actual Apache configuration file already. That section doesn't include the (ht)? part that prevents the new ht.htaccess from being viewed as currently happens with the default .htaccess files.
It also will not conflict with anything if you later decide to revert to the default setting for AccessFileName & forget to remove it.

The Apache Manuals' htaccess page goes into much more detail about htaccess files.

Thursday, December 6, 2007

BOM Squad

Cartoony Bomb
Since posting about phpBB3 and UTF-8 BOM/Signature in October, I discovered there seems to be alot of people tripping over UTF-8 encoded files that have a BOM, otherwise known as a Signature embeded.
Most people seem to know what the BOM is and what it does, but don't know how to get rid of it or how it got there.

Well, now seems like a good time to go over how the BOM gets in files, how to find out if an editor is silently prepending a BOM to UTF-8 encoded files, and how it can be removed if needed.

A major source of these BOM headaches seems to be Notepad on Windows. When saving a file with UTF-8 encoding using Notepad on Windows, it automaticly prepends the BOM to the file but doesn't inform the user about this.

I think this is partly due to quite a few people suggesting that Notepad can be used to easily and quickly convert files to UTF-8 in order to solve issues with strange characters showing up in files.

utf-8 Windows Notepad
Here's a screenshot of the choices you get when saving a file using Notepad. You can see there's no mention of BOM, or Signature there.
I can't help but wonder why Microsoft decided to diferentiate between big & little endian for Unicode but not leave a choice for the UTF-8 BOM.

From what I understand Windows text editors are the main contributers of UTF-8 BOM in files. UNIX type system applications generally don't include the BOM because it can cause problems with configuration files, which are primarily text files.

The first thing to do is check your editors' settings, particularly areas having to do with encoding, very well for UTF-8, BOM, and or Signature. You may have controll over what your editor does and it's good to be aware if you do.

One method to determine if your editor is secretly prepending the BOM to your files is to save an empty file using UTF-8 encoding and look at the filesize. If what should be an empty file has a filesize of three(3), your editor is prepending a BOM. You might want to consider leaving a single character in the file & making sure it's not four(4) bytes instead, some editors may default to non-UTF encoding for empty files.

I use a Notepad replacement called Notepad2 for quick editing. That application gives me an option to save UTF-8 encoded files with or without the BOM, though it calls it a Signature. Notepad2 displays which encoding is used in the file on the applications status bar which comes in handy from time to time. The application is self-contained and compressed is only about 250KB, easily carried around on a USB memory stick.

Another option for getting rid of the BOM is using the Perl script in this forum thread. I tried it myself (as seen in that thread), it works exactly as expected. This is probably the one of the easiest options if the files are on a Ubuntu or Linux system.

I'm sure there's plenty of people looking for a PHP solution to removing the BOM, so here's function designed for that situation called debom_utf8. It should work in either PHP4, PHP5. The methods used are generic enough that it should continue to work in PHP6.

<?php
/*
@author: http://develobert.blogspot.com/
@description : PHP Function to remove UTF-8 BOM/Signature from the beginning of a file.
@param $filename: Name of the file a BOM should be looked for and removed from.
@returns (bool): Returns true if the file didn't, or no longer contain(s) a BOM, false on error.

@example usage: echo debom_utf8('BOM.txt') ? 'free of BOM' : 'error';
*/
function debom_utf8($filename = '')
{
if($size = filesize($filename) && $size < 3)
{// BOM not possible
return true;
}
if($fh = fopen($filename, 'r+b'))
{
if(bin2hex(fread($fh, 3)) == 'efbbbf')
{
if($size == 3 && ftruncate($fh, 0))
{// Empty other than BOM
fclose($fh);
return true;
}
else if($buffer = fread($fh, $size))
{// Shift file contents to beginning of file
if(ftruncate($fh, strlen($buffer)) && rewind($fh))
{
if(fwrite($fh, $buffer))
{
fclose($fh);
return true;
}
}
}
}
else
{// No BOM found
fclose($fh);
return true;
}
}
return false;
}
?>


Comments on other methods to deal with BOM disposal are more than welcome.