Archive

Archive for June, 2011

Convert MySQL Query Result To Excel

June 24th, 2011 No comments

Using PHP to convert MySQL query result to Excel format is also common especially in web based finance applications. The finance data stored in database are downloaded as Excel file for easy viewing. There is no special functions in PHP to do the job. But you can do it easily by formatting the query result as tab separated values or put the value in an HTML table. After that set the content type to application/vnd.ms-excel

<?php
include ‘library/config.php’;
include ‘library/opendb.php’;

$query  = “SELECT fname, lname FROM students”;
$result = mysql_query($query) or die(‘Error, query failed’);

$tsv  = array();
$html = array();
while($row = mysql_fetch_array($result, MYSQL_NUM))
{
$tsv[]  = implode(“\t”, $row);
$html[] = “<tr><td>” .implode(“</td><td>”, $row) .              ”</td></tr>”;
}

$tsv = implode(“\r\n”, $tsv);
$html = “<table>” . implode(“\r\n”, $html) . “</table>”;

$fileName = ‘mysql-to-excel.xls’;
header(“Content-type: application/vnd.ms-excel”);
header(“Content-Disposition: attachment; filename=$fileName”);

echo $tsv;
//echo $html;

include ‘library/closedb.php’;
?>

Categories: PHP Tags:

SSH with authentication key instead of password

June 17th, 2011 No comments

Generate the authentication key

On the client machine, the user must generate a public / private keys pair that will identify himself on the servers. One can choose to protect it with password or not.

Letting it with no password, means that anyone with access to the key files (eg. root on the client’s machine) will have the same level of access of the user and no password will be asked when the client tries to connect to the servers.

Protecting the keys with password means that every time the user tries to connect to a server using those keys , the password for decrypting it will be asked. This is surely more secure, since anyone who can read the key files, will only see an encrypted version.

To generate the key pair do:

john@laptop:~$ ssh-keygen
Generating public/private rsa key pair.
Enter file in which to save the key (/home/john/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /home/john/.ssh/id_rsa.
Your public key has been saved in /home/john/.ssh/id_rsa.pub.
The key fingerprint is:
44:3e:ef:58:94:15:52:c2:88:ca:ab:21:43:53:3d:42 john@laptop
john@laptop:~$

Just let the default file (~/.ssh/id_rsa). Enter the password at choice, as explained before. If you need to change the password or add one, do:

john@laptop:~$ ssh-keygen -p
Enter file in which the key is (/home/john/.ssh/id_rsa):
Key has comment ‘/home/john/.ssh/id_rsa’
Enter new passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved with the new passphrase.
john@laptop:~$

In this case, a new password was added. Note that this operation does not change the public / private key pair. It only changes its encryption.

Install the public key on the servers

Once the public key is installed on the server, access will be granted with no password question. SSH usually comes with an utility called ssh-copy-id that simply adds the contents of client’s ~/.ssh/id_rsa.pub to the server’s ~/.ssh/authorized_keys:

john@laptop:~$ ssh-copy-id -i .ssh/id_rsa.pub root@192.168.0.1
15
john@192.168.0.1′s password:
Now try logging into the machine, with “ssh ‘john@192.168.0.1′”, and check in:

.ssh/authorized_keys

to make sure we haven’t added extra keys that you weren’t expecting.

john@laptop:~$

Note that at this point password access is needed. This procedure can be done by any other way you wish. For example, the server’s administrator himself can add the public key to allow a user access, instead of giving him a password.

Access

At this point, user’s account on the server can be locked for password authentication. On Linux systems, one can make:

root@192.168.0.1:~# passwd -l john

to lock john’s account. Key authentication will still be possible.

Now, try to access the server:

john@laptop:~$ ssh john@192.168.0.1
Enter passphrase for key ‘/home/john/.ssh/id_rsa’:
john@192.168.0.1:~$

On this case, the client’s key was encrypted and its password was asked. If it had no password, nothing would have been asked, and access would be direct:

john@laptop:~$ ssh john@192.168.0.1
john@192.168.0.1:~$
Categories: Tutorials Tags: