script to modify DB values filtering by column and rows

One of my clients recently needed to implement an application-level encryption for some existing applications.

Unfortunately, the design pattern used for the database layer obliged me to write an independent script to browse the database and encrypt the data for each user using a user-specific two-way encryption key.

So, I ended up writing a simple utility class that applies a user-defined function (modifies) to the defined columns and records (via a filter query) of each database table.

It can be used for any relational database server supported by PDO (MySQL, SQL Server, MySQL, Oracle). I’ve only used it with MySQL but it should work smoothly with all the others.

Repository:
Db modifier https://github.com/elvisciotti/DbModifier

An example of usage is in the README, hope it’s clear enough.

You can basically use this to mass editing Db records with a function that is NOT easily implementable with a simple MySQL Query, e.g. encrypt the data with a custom PHP function.

Useful options for PHP Command line interface

I find PHP command line interface very useful, for CRON jobs, testing and whenever apache is not necessary. Basic commands here: CLI php.net manual. In this article I’m writing some CL flags and few lines of related functions / PHP code I find useful

  • To make script interactive and read the line from the console
    $var = readline("text");
  • Prints reflection of a function (params and required values). Can be used as a guide
    php -rf json_encode
  • save highlited code into a html file
    php -s file.php > fileWithCodeHighlited.phtml
  • Display a ini setting containing “log_” (e.g. error_log)
    php -i | grep "log_"
  • Run one command (without entering in interactive mode with php -a)
    php -r "echo time();";
  • Set a INI option before executing
    php -d max_execution_time=20 ...
  • Read from STDIN (when piped)
    $handle = fopen('php://stind', 'r');
    while (!feof($handle)) {
    	$line = trim(fgets($handle));
    	if(strlen($line) > 0){
    	echo strrev($line).PHP_EOL;
    	}
    }
    fclose($handle);
  • Get options using getopt
    $arg = getopt('ab:c::') // "a" as flag "b" required, "c" optional

Using PHP Closures to get cached object with one call

Getting caching objects if a fairly frequent task when dealing with performative web applications.

Standard approach

if (cache object is valid)  {
return cached object
} else {
get fresh object (*)
save object into cache
return object
}

I don’t like that approach as there are a lots of LOC that wraps the only meaningful code (to get the fresh object).
Continue reading

sessions vs cookies with load balancer

It’s a well known fact that HTTP is a stateless protocol and cookies are needed to keep the communication session.

When dealing with sessions with PHP, we have two main solutions:

– set manual cookies
One approach is using directly the PHP functions to set the cookies. When possible I don’t use manual cookies(except than keeping an hash string for the user identity or keep the session after the browser is closed) as there are lots of issues (datas kept in the user browser so they have to be validated at each request, difficulty to store complex datas, browser compatibility or partial cookie blocking etc…).

– session functions
A much easier approach is using the session functions. The session is automatically managed by PHP and the superglobal array $_SESSION is available with persistent user datas, thanks to the session cookie automatically managed.
Advantages: data stored in the server, easy to save arrays and custom objects, superglobal array immediately available and semplicity in writing the code.
Disadvantages: not possible to keep it after the browser closes (except a custom save handler on disk or db) , not possible to specify URL path and domain for the session.

– server balancer issues -> cookies !
Today we had some issue with our server load balancer. We realize that it didn’t support sticky sessions, so at every redirect (also to the same page because of some rules) the session datas (kept through session functions) were deleted. In order to meet a deadline, we set a manual cookie to keep the data needed.

PHP for file moving / managing

PHP is a technology damned cool for web application but the language doesn’t provide an advanced syntax as Python in itself. However, by writing readable and maintenable code (for instance by smartly separating features in function and classes), PHP is satisfying also to make file managing / system scripts, IMHO of course 🙂

Example
Here is a simple script I’m using to copy the latest mp3 files from a directory to another one (ipod shuffle) skipping the already existing files. Basically it’s a kind of one-way-sync script for files filtered by mask and creation date. (To run it from the command line, launch it from the destination directory. e.g.: “php -f script.php” in the root of the ipod, or any USB MP3 player)


define('_SOURCE_PATH_', 'd:/documents/Music/incoming/');
define('_DEST_FOLDER_' , 'Music/'); #ipod shuffle

foreach (new DirectoryIterator(_SOURCE_PATH_) as $f) {

if ( Utils::is_recent_mp3( $f->getPathname() ) &&
Utils::copy_if_not_exists( $f->getPathname(), _DEST_FOLDER_.$f->getFilename() )
)
echo "[COPIED][{$f->getFilename()}]n";

}




class Utils {

public static function is_recent_mp3($path, $daysOld = 7) {
return ( substr($path,-1)=='3' && filemtime($path) > ( time()-$daysOld*24*3600) );
}

public static function copy_if_not_exists($from, $to){
return ( file_exists($from) && !file_exists($to) ) ? copy($from, $to) : false;
}

}

PHP Arrays sort

There are lots of PHP defined function to order arrays (the core of PHP, technically are hashmaps), ordering by value or keys, preserving key order or not, using an user-defined function, normal or reverse order, etc…

Following, some clear example of the main ones, and some tips about how to remember them

ORDER BY VALUE

sort(): order by VALUE (keys destroyed and renumberted starting from zero)
$a = array(‘z’=>’A’, ‘y’=>’C’,’k’=>’B’,’x’=>’B’);
sort( $a ); # Array ( A ,B ,B ,C )

asort(): as the previous, but it maintains index association
$a = array(‘z’=>’A’, ‘y’=>’C’,’k’=>’B’,’x’=>’B’);
asort( $a ); # Array ( [z] => A , [k] => B , [x] => B , [y] => C )

natsort(): use “natural” sorting
$a = array(‘z’=>’5p’, ‘y’=>’10p’,’20p’);
sort( $a ); # Array ( 10p ,20p ,5p )
natsort( $a ); # Array ( [2] => 5p , [0] => 10p , [1] => 20
p )

ORDER BY KEY

ksort(): order by key (maintaining associations)
$a = array(‘z’=>’A’, ‘y’=>’C’,’k’=>’B’,’x’=>’B’);
ksort( $a ); # Array ( [k] => B , [x] => B , [y] => C , [z] => A )

USER-DEFINED COMPARISON FUNCTION

usort(): compare using an user-defined function. (keys destroyed and renumbered starting from zero)
$a = array( ‘0’=>array(‘id’=>4,’n’=>’aaa’) , ‘1’=>array(‘id’=>2,’n’=>’bbb’), ‘2’=>array(‘id’=>1,’n’=>’ccc’), );
usort($a, function ($a, $b) { #order by ‘[id]’ contained in the elements #closure (php 5.3)
return ($a[‘id’] < $b['id']) ? -1 : 1; }); #Array ( Array ( [id] => 1 [n] => ccc ) ,Array ( [id] => 2 [n] => bbb ) ,Array ( [id] => 4 [n] => aaa ) )

uasort(): as usort but MAINTAINS the KEYS

HOW TO REMEMBER FUNCTION NAMES

sort = default sort behaviour: order by value and destroy keys
If the function name contains:
u = sort using [U]ser-defined function (Usort, Uasort, Uksort)
a = [A]ssociative: maintains key order (Asort, Arsort, uAsort)
k = order by [K]ey (Ksort, Krsort)
r = [R]everse order (aRsort, kRsort, Rsort)

OTHER ARRAY USEFUL FUNCTIONS

shuffle(): scramble array s contents. (keys destroyed and renumberted starting from 0)

array_rand($array, $number): return $number random elements from $array

array_multisort()

array_shift( array &$array ) ; return the array shifted by 1element off the beginning of array (remove the 1st)

array_slice( array $array , 3, 2, $preserve_keys); RETURNS 2 elements starting from the 4th. if $preserve_keys is true, the keys in the return array are the same in the original one
$a = array(0=>1, 1=>2, 2=>3, 4=>4, 3=>5, 5=>6);
$a = array_slice($a,3,2); #Array ( 4 ,5 )

array_splice(): remove the slice (REFERENCE) and return the removed part
$a = array(1, 2, 3, 4, 5, 6);
$returned = array_splice($a,3,2); #remove 2 elements from 4th elem. return the removed slice
# $returned = Array ( 4 ,5 )
# $a = Array ( 1 ,2 , 3 , 6 )

PHP 5.3

I’ve just read this pdf from I.A.’s blog about PHP 5.3 performances.
My comments:

Performances
What I really consider good is the performance increasing (5/10%) that include a smarter behaviour with require/inclusion, smaller binary size and better stack performance.

Features
– Namespaces.
Late static binding. It was the only big lack of the PHP OO.
– Closures: useful to write a clearer code. I’ve tested their performances (*) and it seems there are no decreasing using them, that’s cool.
(*) array_map(function ($n){return($n * $n * $n);}, array(1, 2, 3, 4, 5)); #
– “goto”: its utility (especially in a OO language!) doesn’t make any sense to me. Very bad code readability with it.
– MySQLInd sounds interesting to have better performances with MySQL but I don’t think make sense to replace for so small performance increasing and maybe worse reliability. Client side query cache, written for PHP (not C/C++), performance statistics for bottle-neck analysis [read here]
– Hundreds of bug fixing and improvements, including Directory iterator and date functions => well done PHP community !

UTF8 in LAMP applications: overview and how to solve the common issues

The problem
In a LAMP application the text is frequently saved/retrieved from/to a database and files. We must consider all the different encoding (mapping characher-byte value): latin1 iso8859-1, latin9, UTF-8 (utf8) etc…
Lots of applications use ISO8859 encoding and some PHP functions to convert the characters (htmlentities, htmlspecialchars etc…)
The solution
Converting all the text from an encoding to another using PHP functions is unsafe, difficult and annoying.
Ad example the character “é” (encoded as iso8859) will printed as “é” if it’s supposed to be encoded as utf8.
The solution is to use only one charset for files, Content-Type of the pages and the db. UTF-8 [wiki] is the best choice: a variable-lenght char encoding for the standard Unicode. If you use this charset in the HTML file, it won’t need to convert the characters to the respective entities.
How To use UTF-8
  • set your IDE to save and open source files using utf8 encoding
  • set the content-type of your application to utf-8 (better a apache/htaccess rule instead of the meta tag).
  • set the database server to use utf8 encoding (also tables must be converted). If the db is utf8 but client encoding is latin1, execute first of all the query “SET NAMES utf8”
  • if the application was using latin1 and PHP convert functions, remove all the existing function to encode/decode special characters/entities.