..a dose of zero-day know-hows ..

4/24/2008

How to automatically set session.save_path for Clients (For WHC(Webhost CPanel) Users who Resell and Host a Shared Environment).

This article is for WHC("Webhost CPanel" NOT "CPanel") Users who Resell and Host, please spare your clients by manually setting their PHP session.save_path values via .htaccess. These clients deserve a writable session.save_path of their own.

Scenario: A client opens an account from a webhost, account is activated. The client does not have a writable session.save_path directory (usually and by default points to /tmp). As a recourse, the client uses .htaccess to declare a session.save_path of their own under their account directory.

This is both bad to the client and the host because anything you put in the .htaccess adds to the server load, having a session.save_path php directive in .htaccess is not much but is not efficient since this can(SHOULD) be handled in the server level automatically upon Client Account creation.

Note: If you are on a shared hosting, and your session.save_path points to /tmp and you can write to it, inform the host immediatly as this means everyone on their cluster and possibly someone from the outside can write on the /tmp directory which is a compromise to the security of the whole hosting server and sites it hosts.

Resolution: Alter the following lines of the file: /var/cpanel/templates/apache2/vhost.default
From:

To:

Then run
[shell command]
/usr/local/cpanel/bin/build_apache_conf

The above changes will make all your clients under your shared environment to have their own session.save_path pointed to their own /tmp directory under their space upon account creation. You may make the creation of the /home/{USER}/tmp directory automatic or if not advice your client to create one so they may have a writable session.save_path.

Depending if your server has phpSuExec/or not, you need to make sure that httpd/apache can write to the /home/{USER}/tmp directory per client.

How to Change/Convert your MySQL Database Collation to utf8 (a verbose sample)

Scenario: You have a very big database(schema)(more than 500 mb), and you wish to convert it say from latin1 to utf8(for reasons you can keep :) probably because you want your DB to be able to accommodate an app that can be fully localized (like Mambo or Vbulletin for Example), then this article might help.

You might find many conversion scripts around, but ever wondered what happens in the background? then read on.

Tools:
1.) mysqldump and mysql -or- phpmyadmin
2.) a text editor (vi perhaps?)

Steps:

Step 1: Create a new Database
This will be the placeholder for the database you are about to convert:

[SQL Query]

CREATE DATABASE `new_database` DEFAULT CHARSET=utf8


Step 2: Generate a "Structure-only sql dump"
Export an SQL file that should contain only the table structure:

[SHELL COMMAND]

mysqldump -u {USER} -p{PASSWORD} --no-data --no-create-db --opt --quote-names old_db > old_db_structure_only.sql

where old_db is the name of the database you wish to convert. Replace {USER} and {PASSWORD} with appropriate values


Step 3. Modify the "Structure-only sql dump"
Open the "old_db_structure_only.sql" file, use a text editor-> "Replace All" feature to change all instance of "DEFAULT CHARSET={CURRENT_CHARSET}" to "DEFAULT CHARSET=utf8". If you are using vi, you can probably get away with it by using the following command

[vi's non-visual mode command (press caps lock, then escape, then 'q' to switch to this mode)]

:%s/CHARSET=latin1/CHARSET=utf8/

assuming you are converting a latin1 collated DB


Step 4. Generate a Data-only sql dump
Next is generate a data-only dump, if your DB is more than 50mb, i advise that you use mysqldump command to execute this. Generating a big data-only dump from a big DB might lead to problems if done over http (timeouts, corrupted output etc)

[SHELL COMMAND]

mysqldump -u {USER} -p{PASSWORD} --no-create-db --no-create-info --opt --quote-names old_db > old_db_data_only.sql


Step 5. Restore the Structure-only sql dump (.sql) into the new Database

[SHELL COMMAND]

mysql -u {USER} -p{PASSWORD} new_db < old_db_structure_only.sql


Step 6. Restore the Data-only sql dump (.sql) into the new Database

[SHELL COMMAND]

mysql -u {USER} -p{PASSWORD} new_db <>Step 7. Drop the old Database (Make sure you are keeping your backups)

[SQL Query]

DROP DATABASE `old_db`

Step 8. Point your app from `old_db` to `new_db`
This would depend with your php application. There is a file usually named config.php or configuration.php which you will have to change the db_name values.


your app nows use utf8.. done

4/17/2008

Using MySQL Triggers to Audit Field Changes from a Database Table

Original Source: http://www.phpugph.com/blog/2008/03/21/using-mysql-triggers-to-audit-field-changes-from-a-database-table/

Author: Arpee Ong


A MySQL trigger is an object that is associated to a table. It is a set of tasks that is ran/activated when a particular operation is imposed on a table. A couple of operations that can invoke a MySQL trigger is an Insert and Update.

This article will discuss how this feature can be used to audit field changes that will natively be handled via the MySQL DB engine. This is useful for the following scenarios.

1.) When there is a need to return multiple previous versions of a given data
2.) Track recent changes with a possibility to rollback a field to previous states
3.) When field data should not be lost upon update
4.) When there is a need to do all of the above without the intervention of webserver scripting methods

We will use two tables for this article as an example as follows:

Table 1. `content` table - will be the table whose content field will be audited:

Code:

CREATE TABLE  `content` (
`id` int(11) unsigned NOT NULL auto_increment,
`title` varchar(100),
`content` varchar(100),
PRIMARY KEY (`id`)
)

Prefill the `content` table with sample Data

Code:

INSERT INTO content VALUES
(1, "1st Article", "1st Content"),
(2, "2nd Article", "2nd Content"),
(3, "3rd Article", "2nd Content");

SELECT * from `content`; now will return the following:

Code:

+----+-------------+-------------+
| id | title | content |
+----+-------------+-------------+
| 1 | 1st Article | 1st Content |
| 2 | 2nd Article | 2nd Content |
| 3 | 3rd Article | 2nd Content |
+----+-------------+-------------+
3 rows in set (0.00 sec)

Table 2. `content_audit` table - will audit the `content.content` field.

Code:

CREATE TABLE  `tester2`.`content_audit` (
`id` int(11) unsigned NOT NULL auto_increment,
`content_id` int(11) NOT NULL default '0',
`before_value` mediumtext,
`after_value` mediumtext,
`date_created` timestamp NOT NULL default CURRENT_TIMESTAMP
on update CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
)

The Goal: Keep track of the changes made to `content.content` field utilizing `content_audit` table.

Step 1: Create the Trigger by executing the following SQL Query

Code:

DELIMITER |
CREATE TRIGGER audit_content BEFORE UPDATE ON content
FOR EACH ROW BEGIN
INSERT INTO content_audit SET content_id = OLD.id;
UPDATE content_audit SET before_value = OLD.content
WHERE id = last_insert_id();
UPDATE content_audit SET after_value = NEW.content
WHERE id = last_insert_id();
END;
|
DELIMITER ;

Explanation:

Code:

DELIMITER |

Tells MySQL to use “|” as the delimiter instead of the default “;”. This makes it possible to execute the whole create trigger statement without the mysql cli having to halt on “;”.

Code:

CREATE TRIGGER audit_content BEFORE UPDATE ON content

CREATE TRIGGER invokes the creation of the trigger, in the case above, it accepted several parameters as follows:

audit_content = the name of the trigger
BEFORE = when to execute the statement {BEFORE|AFTER} the event
UPDATE = the operation that should activate the trigger
ON = specifier of the table where to associate the trigger
content = the name of the table to associate the trigger

Code:

FOR EACH ROW BEGIN

For every row UPDATE imposed on the `content` table, the succeeding statements will be executed

Code:

  INSERT INTO content_audit SET content_id = OLD.id;
UPDATE content_audit SET before_value = OLD.content
WHERE id = last_insert_id();
UPDATE content_audit SET after_value = NEW.content
WHERE id = last_insert_id();

The Statement above will be executed everytime an UPDATE operation is imposed on `content` table

OLD.id = the value of `content.id`.
last_insert_id() = the value of the last ID inserted on the same table, in this case, the last ID inserted into content_audit

Code:

END;

Ends the FOR EACH statement of the trigger

Code:

|

Halts the script

Code:

DELIMITER ;

Switches the DELIMITER back to the default “;”

Step 2: Update one of the `content`.`content` field

Code:

UPDATE content SET content = "2nd Content - 2nd Version" WHERE id = "2";

The above code alters the content of the “2nd Article” and should activate the Trigger `audit_content` trigger.

To check if the Trigger was activated. Run the following Query to Return all prior versions of the “2nd Article”

Code:

SELECT
c.title as "Title,
c.content as "Current Content",
ca.before_value as "Content before latest Update"
FROM content c
JOIN content_audit ca on ca.content_id = c.id;

which should output (if the trigger is working)

Code:

+-------------+---------------------------+------------------------------+
| title | Current Content | Content before latest Update |
+-------------+---------------------------+------------------------------+
| 2nd Article | 2nd Content - 2nd Version | 2nd Content |
+-------------+---------------------------+------------------------------+
1 row in set (0.00 sec)

Notice that in the above resultset, the old value of the field is not lost and we were able to return it side by side the current data.

If there were more than 1 revision of the `content`.`content`, the same query will enable you to cascade through the different prior versions and even sort them by date using `content_audit`.`date_created` field.

A discrepancy/sanity check can be done by checking if the latest `content_audit`.`after_value` is equal to the `content`.`content`.

END

4/16/2008

How to Make 'Read More' Links (A very simple sample implementation for PHP/MySQL)

Scenario: You have a php/mysql dynamically driven pages, you want a 'Read More' link displayed with your articles. This sample implementation might just give you the very basic analogy.

Method 1: For Articles stored on a single DB Field

A.) The DB Structure: (Use MySQL CLI or PHPMyAdmin to execute query)

CREATE DATABASE `test`;
CREATE TABLE `test`.`article_table` (
`id` int(11) unsigned NOT NULL auto_increment,
`title` varchar(100) NOT NULL default '',
`article` mediumtext NOT NULL,
`created` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`id`)
)

B.) The DB Sample Content: (Use MySQL CLI or PHPMyAdmin to execute query)

INSERT INTO `test`.`article_table` VALUES
(1, 'Article 1', 'Its been a while since i posted an article, busy months have passed and
im still within timeframes and deadlines.. well most of the time real life intervenes eh?
anyways heres my article about Sharing Hashes between Mambo 4.x and SugarCRM
4.5.1 Using MySQL Views', '2008-01-01 12:00:00'),
(2,'Article 2', 'The "foo: bar: not found" error message does not indicate that bar could
not be found, but rather bar exists but is calling something that could not be found.
This is the case with Perl scripts when the script cannot find where Perl is.
I have noticed that most of out "out-of-the-box" Perl scripts point to "/usr/local/bin/perl"
whereas some of the OS pre-bundled Perl is preinstalled at "/bin/perl", hence, when you
run a Perl script, the "foo: bar: not found" pops out the terminal.', '2008-01-01 12:00:00')

C.) The Script to Display the Articles in Summary and Full View Depending on the Task. Save as readmore.php


Heres whats happening above:
  • All Articles Are displayed as Summary
  • We used Substring to show only first 60 characters, of course you can adjust this to any value, just hack the code.
  • Read More Link Per article will point to "readmore.php?task=Full&id={ID OF ARTICLE}"
  • Full View is Displayed
Important Hints:
  • The Full Article View needs the ID of the Article to Display
  • This makes it necessary for ReadMore link to specify the ID of the Article it is a part of