Wednesday, July 6, 2011

VBA code for copying range of cells in excel

I helped someone make a copy of some cells in an excel spreadsheet using VBA. Thy were adding a recipe to another worksheet.

'move to the AddRecipes tab
Sheets("AddRecipes").Select
'select the range of recipe ingredients and then copy the selection.
Range("C11:C30").Select
Selection.Copy
'select the dessert sheet
Sheets("DessertIngredients").Select
'select the range and then paste there using the dynamic variables in the for loop
Range(Cells(Row1 + 1, Column1), Cells(Row1 + 21, Column1)).Select
ActiveSheet.Paste

Friday, July 1, 2011

Making php control sessions

PHP can be insecure using session variables. One reason is because if you are on a shared host like Dreamhost or godaddy you have other people on your server and the default place to store session files with your session_id and session data is /tmp which anyone can read and use that information for anything they want. I just looked in the tmp folder on a shared host and there were 14000 session files from the last 2 days. That's not good at all. First thing you should do is move the place it is stored to a place in your home directory so only you can read them.
Fix this with the following change to your php.ini file. Make sure to make this new folder writable too.
change this:
session.save_path = "/tmp"
to this:
session.save_path = "/home/username/tmp"

Also change your default session name so anyone looking at your website be not recognize the session_id when they see it:
change this:
session.name = PHPSESSID
to something like this:
session.name = fh4hd4kjddj5fhk2jdkjfh


I made a demo of how to make this work completely with MySQL using this code:
http://www.josephcrawford.com/php-articles/going-deep-inside-php-sessions/
There were some problems with his code so I am putting a version here that works including a demo page for running it.

I made a session like this:
$_SESSION['name'] = 'mike';
and it serializes it and sticks it in the file like this:
s:16:"name|s:4:"mike";";
We will be putting this in the database instead of the file system.


first make a db.php file with this in it:
<?php
include "helper_mysql.php";
if($_SERVER['HTTP_HOST'] == 'localhost') $con_server = "localhost";
else {echo "not localhost";exit();}

$con_username = "username";
$con_password = "password";

$con = @mysql_connect($con_server,$con_username,$con_password);
if (!$con){
die('Could not connect to Database');
}


Make the table for mysql with this SQL:
CREATE TABLE sessions
(session_id INT PRIMARY KEY AUTO_INCREMENT
, ses_id VARCHAR(100)
, last_access INT
, ses_start INT
, ses_value VARCHAR(5000)
);


Next is the mysql_helper function I have used with a few changes to add this variable to keep track of the number of results as a global variable: $number_results
<?php
$number_results = 0;
function mysql_magic()
{
global $con, $con_server, $con_username, $con_password, $con_database, $number_results;
$number_results = 0;
$narg = func_num_args();
$args = func_get_args();

if (!$con)
{
$con = mysql_connect( $con_server, $con_username, $con_password );
@mysql_select_db( $con_database, $con );
}

$req_sql = array_shift($args);
$req_args = $args;

$req_query = mysql_bind($req_sql, $req_args);
$req_result = mysql_query($req_query);

if (!$req_result)
{
trigger_error(mysql_error());
return false;
}

if (startsWith($req_sql, 'delete') || startsWith($req_sql, 'update') || startsWith($req_sql, 'truncate'))
{
return mysql_affected_rows(); // -1 || N
}
else if (startsWith($req_sql, 'insert'))
{
return mysql_insert_id(); // ID || 0 || FALSE
}
else if (endsWith($req_sql, 'limit 1'))
{
$number_results = mysql_num_rows($req_result);
return mysql_fetch_assoc($req_result); // [] || FALSE
}
else if (startsWith($req_sql, 'select count(*)'))
{
$line = mysql_fetch_row($req_result);
return $line[0]; // N
}
else
{
$number_results = mysql_num_rows($req_result);
return mysql_fetch_all($req_result); // [][]
}
}

function mysql_bind($sql, $values=array())
{
foreach ($values as &$value) $value = mysql_real_escape_string($value);
$sql = vsprintf( str_replace('?', "'%s'", $sql), $values);
return $sql;
}

function mysql_fetch_all($result)
{
$resultArray = array();
while(($resultArray[] = mysql_fetch_assoc($result)) || array_pop($resultArray));
return $resultArray;
}

function startsWith($haystack,$needle,$case=false) {
if($case){return (strcmp(substr($haystack, 0, strlen($needle)),$needle)===0);}
return (strcasecmp(substr($haystack, 0, strlen($needle)),$needle)===0);
}

function endsWith($haystack,$needle,$case=false) {
if($case){return (strcmp(substr($haystack, strlen($haystack) - strlen($needle)),$needle)===0);}
return (strcasecmp(substr($haystack, strlen($haystack) - strlen($needle)),$needle)===0);
}


Next is the session class which will be used to run opening, closing, reading, writing, destroying, and garbage collecting the session.

<?php
class Session
{
private $ses_id;
private $table;
private $ses_life;
private $ses_start;

public function __construct($table = 'sessions')
{
$this->_table = $table;
}

public function open($path, $name)
{
echo "opening<br />";
$this->_ses_life = ini_get('session.gc_maxlifetime');
}

public function close()
{
echo "closing<br />";
$this->gc();
}

public function read($ses_id)
{
global $number_results;
if(isset($this->_ses_id)) $ses_id = $this->_ses_id;
echo "reading<br />";
$session_sql = "SELECT * FROM " . $this->_table. " WHERE ses_id = '$ses_id' LIMIT 1";
$session_row = mysql_magic($session_sql);
if (!$session_row) return '';
if ($number_results> 0)
{
$ses_data = unserialize($session_row["ses_value"]);
$this->_ses_start = $session_row['ses_start'];
return $ses_data;
} else {
if(!isset($this->_ses_id)) $this->_ses_id = $ses_id;
return '';
}
}

public function write($ses_id, $data)
{
global $number_results;
if(!isset($this->_ses_id)) $this->_ses_id = $ses_id;
echo "writing<br />";
if(!isset($this->_ses_start)) $this->_ses_start = time();
$session_sql = "SELECT * FROM " . $this->_table. " WHERE ses_id = '" . $this->_ses_id . "' LIMIT 1";
mysql_magic($session_sql);

if( $number_results == 0 )
{
$session_sql = "INSERT INTO ".$this->_table." (session_id, ses_id, last_access, ses_start, ses_value) VALUES (NULL, '".$this->_ses_id."', ".time().", ".$this->_ses_start.", '".serialize($data)."')";
} else {
$session_sql = "UPDATE ".$this->_table." SET last_access=".time().", ses_value='".serialize($data)."' WHERE ses_id='".$this->_ses_id."'";
}
$session_res = mysql_magic($session_sql);
if (!$session_res) return FALSE;
else return TRUE;
}

public function destroy($ses_id)
{
echo "deletinging<br />";
//delete the session from the database
if(!isset($this->_ses_id)) $this->_ses_id = $ses_id;
$session_sql = "DELETE FROM sessions WHERE ses_id = '" . $this->_ses_id . "'";
$session_res = mysql_magic($session_sql);
if (!$session_res) return FALSE;
else return TRUE;
}

public function gc()
{
echo "gcing<br />";
$ses_life = time() - $this->_ses_life;
$session_sql = "DELETE FROM " . $this->_table. " WHERE last_access <$ses_life";
$session_res = mysql_magic($session_sql);
if (!$session_res) return FALSE;
else return TRUE;
}

}


Now the following will use the code that we have shown you so far. It will do many different kinds of things with sessions so you can see how it works.
<?php
$database = "test";
include "db.php";
mysql_select_db($database, $con);
require_once('session.php');

define( 'UA_THRESHOLD', 25 );
define( 'PW_MAX_CHECKS', 3 );
require_once('session.php');

$s = new Session();

/**
* Change the save_handler to use
* the class functions
*/
session_set_save_handler (
array(&$s, 'open'),
array(&$s, 'close'),
array(&$s, 'read'),
array(&$s, 'write'),
array(&$s, 'destroy'),
array(&$s, 'gc')
);

session_start();
//uses the destore function to delete your session.

echo "session_id: ".session_id()."<br />";

//how do you change php's current session_id to the custom string like below.
//changing the session_id but it only changes it temporarily for this page and in the database.
//If you echo session_id() again after refreshing the page you will find that the session_id is put back to the randomized session_id.
//you should make the session_id a psudo random id like: sess_id_, random 26 character string, IP address, timestamp in seconds, unique_id
//it would look like this: sess_id_4e0e4d31ba4981223439051270011309560113165

$new_session_id = "sess_id_".str_replace(".","",uniqid("",1).$_SERVER['REMOTE_ADDR']).time()."165";
//this will show something like this:
//sess_id_4e0e4d31ba4981223439051270011309560113165
//the 165 at the end would be incremented by 1 each time a session is made so its harder to duplicate
//only do this when you are loging in or if you want to reset the session to be different every time you go to another page or refresht he current page. You will have to test this to see what works best for you. The more often people get new sessions the less likly a hacker is able to get the session and use it before it is invalidated. It depends on the importance of your user data.
session_id($new_session_id);

echo "here is the new session_id: ".session_id()."<br />";
echo "<br />session values: <pre>";
print_r($_SESSION);
echo "</pre>";

$_SESSION['message'] = "setting session vars in the database: yay!!!";
$_SESSION['number'] = 1.3432;
$_SESSION['user'] = 'stokescomp';
$_SESSION['unset_me'] = 'I will be unset';
$_SESSION['list_array'] = array(1,4,6,8,'help');

//this will call the write function and remove that value from the session
unset($_SESSION['unset_me']);

echo "<br />session values: <pre>";
print_r($_SESSION);
echo "</pre>";

//this removes the session from the database by calling the destory method so they will have to relogin
//session_destroy();


That's it and this will make working with sessions much more secure.

Making php control sessions

PHP can be insecure using session variables. One reason is because if you are on a shared host like Dreamhost or godaddy you have other people on your server and the default place to store session files with your session_id and session data is /tmp which anyone can read and use that information for anything they want. I just looked in the tmp folder on a shared host and there were 14000 session files from the last 2 days. That's not good at all. First thing you should do is move the place it is stored to a place in your home directory so only you can read them.
Fix this with the following change to your php.ini file. Make sure to make this new folder writable too.
change this:
session.save_path = "/tmp"
to this:
session.save_path = "/home/username/tmp"

Also change your default session name so anyone looking at your website be not recognize the session_id when they see it:
change this:
session.name = PHPSESSID
to something like this:
session.name = fh4hd4kjddj5fhk2jdkjfh



I made a demo of how to make this work completely with MySQL using this code:
http://www.josephcrawford.com/php-articles/going-deep-inside-php-sessions/
There were some problems with his code so I am putting a version here that works including a demo page for running it.