How to configure SSL(Secure Sockets Layer) in Apache?

SSL (Secure Sockets Layer) is a protocol which is used for communicating securely over the network. SSL provides both, encryption as well as authentication. For example, in client-server communication it encrypts the data, which browser(client) sends to the server or server sends to the client(browser), as well as authenticates the server to the client. Here authentication means that the client is confirmed that the server to which it is talking is genuine.

SSL works on public/private key cryptography. In SSL, certificates are used to prove the identity to other user.

What is a certificate and how does it work?

Let's say there are three parties. Party 1 is client which uses the services of Party 2. Party2 proves its identity by providing a certificate, signed by a third party, to party 1. Both the parties, party 1 as well as party 2, trust party 3. A certificate contains some information about the party and its public key, it is signed by a CA(Certificate Authority). A certificate authority is the third party which is trusted by both, owner of the certificate as well as the party relying on the certificate. Most of the popular browsers contain information about all the CAs, i.e. they know the genuine CAs, and so these browsers trust the certificates signed by them. If the certificate is signed by a party which is not trusted by the browser then it gives warning that the certificate owner may not be the one who it claims to be. In this case although your channel is secure but the other party is not authenticated, so you might be giving your secret information to a scrupulous person over an encrypted channel which is as good as having no security.

Working:

1) When SSL is configured on server(apache), the client(browser) is presented with the server certificate signed by a known CA. Now Client knows the public key of the server.

2) For authentication, server creates a message and computes its hash value. Server encrypts the hash with its private key. Server sends both, message as well as its encrypted hash, to the client.

3) Client decrypts the encrypted hash using public key of the server. It computes hash value of the message it gets from the server. It compares both the hashes to check the authenticity of the server. If both the hashes are same then the client is talking to a genuine server.

4) As authentication is complete, both client and server need some way to pass the data securely. To achieve this, client selects a synchronous key, encrypts it with the server's public key and sends the encrypted key to the server.

5) Server, after getting the encrypted synchronous key, decrypts it. Now both, client and server, have the synchronous key. They encrypt the data to be passed over the network using this synchronous key. So, security is also achieved in this step.

 Let's see how to configure SSL in apache.

We assume that apache is installed with SSL module(mod_ssl), and open SSL is also installed on the machine.

Make apache listen for HTTPS requests on port 443 by executing the following command.

a2enmod ssl

The above command enables SSL module of apache

Restart apache

/etc/init.d/apache2 restart

Apache has started listening on port 443.

Use the following command to generate a certificate request and a private key.

openssl req -new -newkey rsa:2048 -nodes -keyout server.key -out server.csr

It will ask for some basic information which you have to answer to get the certificate request generated.

Once your private key and certificate request is generated, you will have to get it signed. You can get your certificate signed by a commercial CA or you can sign it yourself. The only problem in signing it yourself is that the browser will give warning since it does not know the certificate signing authority.

Let's see how to self sign a certificate request. Use the following command to sign

openssl x509 -req -days 100 -in server.csr -signkey server.key -out server.crt

The above command creates a certificate which is valid for 100 days. Now you have got two files generated.

server.key private key

server.crt certificate

Let's configure apache to use these.

Supposing that there is virtual hosting on this server.

Inside directory /etc/apache2/sites-enabled / create a copy of default-ssl and rename it. Let's say the new file is abc-ssl

cat default-ssl > abc-ssl

Now we have to tweak at some places in abc-ssl

<VirtualHost <Your-IP-Address>>

ServerName <your-site-name>

DocumentRoot <site-directory-path>

<Directory <site-directory-path>>

SSLCertificateFile <File-path-certificate>

SSLCertificateKeyFile <File-path-key>

With all these changes done, run the following command to enable the ssl configurarion for this website

a2ensite abc-ssl

Reload apache with changed configuration

/etc/init.d/apache2 reload

Now the server will listen and forward any https request for this site.

You can also tweak .htaccess to configure whether you want to access whole website or only some of the pages of the website with https.

For more info visit.

Dynamic queries with an unknown number of inputs...

Sometimes there is a need to pass dynamic number of inputs to a query, based on conditions satisfied or data from some other source/query, as the program executes. Here, we will see how to pass dynamic/unknown number of inputs to a query.

Let's see how to do it. We assume a situation where we get inputs, to be passed to a dynamic query, from a different select query.  

DECLARE CURSOR c_fetch_ids IS 

SELECT ids FROM employee WHERE name LIKE 'John %'; 

v_counter NUMBER; 

d NUMBER; 

v_dyn_stmt VARCHAR2(1000); 

v_cur NUMBER; 

v_temp employee.ids%TYPE; 

v_tab_ids DBMS_SQL.NUMBER_TABLE; 

bind_names DBMS_SQL.VARCHAR2_TABLE; 

v_names DBMS_SQL.VARCHAR2_TABLE; 

v_salaries DBMS_SQL.NUMBER_TABLE; 

BEGIN v_counter := 0; 

OPEN c_fetch_ids; 

LOOP FETCH c_fetch_ids INTO v_temp; 

EXIT WHEN c_fetch_ids%NOTFOUND; 

v_counter := v_counter+1; 

v_tab_ids(v_counter) := v_temp; 

END LOOP; CLOSE c_fetch_ids; 

v_dyn_stmt := 'SELECT name, salary FROM employee WHERE ids IN ('; 

FOR v_counter IN 1 .. v_tab_ids.count 

LOOP 

bind_names(v_counter) := v_counter; 

IF v_counter=1 

THEN 

v_dyn_stmt := v_dyn_stmt||' :1'; 

ELSE v_dyn_stmt := v_dyn_stmt||' ,:'||v_counter; 

END IF; 

END LOOP; 

v_dyn_stmt := v_dyn_stmt||')'; 

v_cur := DBMS_SQL.OPEN_CURSOR; 

DBMS_SQL.PARSE(v_cur, v_dyn_stmt, DBMS_SQL.NATIVE); 

for v_counter IN 1 .. v_tab_ids.count 

LOOP 

DBMS_SQL.BIND_VARIABLE(v_cur,bind_names(v_counter),v_tab_ids(v_counter)); 

END LOOP; 

DBMS_SQL.DEFINE_ARRAY(v_cur, 1, v_names, 10, 1); 

DBMS_SQL.DEFINE_ARRAY(v_cur, 2, v_salaries, 10, 1); 

d := DBMS_SQL.EXECUTE(v_cur); 

LOOP 

d := DBMS_SQL.FETCH_ROWS(v_cur); 

DBMS_SQL.COLUMN_VALUE(v_cur, 1, v_names); 

DBMS_SQL.COLUMN_VALUE(v_cur, 2, v_salaries); 

EXIT WHEN d!=10; 

END LOOP; 

DBMS_SQL.CLOSE_CURSOR(v_cur); 

END;

The above block fetches names and salaries of employees, 10 at a time, after taking dynamic number of inputs(ids) which come from a different query with the condition that name should be starting as 'John %'.

For more info visit

Execute dynamic queries using Native Dynamic SQL(NDS) in PL/SQL.

Dynamic SQL statements are a powerful way to execute dynamic queries in PL/SQL. In this approach, queries are designed as the program proceeds or when the conditions arise. There are two tools in PL/SQL to design and execute dynamic queries.

1) Native Dynamic SQL (NDS)

2) DBMS_SQL package

NDS is relatively easy to use when compared to DBMS_SQL package. DBMS_SQL has a complex structure for making dynamic queries.

Using NDS, inputs can be passed to a query statement and outputs can be collected.

While writing dynamic queries, we can follow two approaches. Parts and inputs of a dynamic query can be attached to each other dynamically using string concatenation and then the dynamic query can be executed, or , placeholders can be used to pass parameters/inputs to a dynamic query. Using placeholder technique is a safer approach as it prevents SQL injection attacks.

Now let's see how to use NDS

CREATE OR REPLACE PROCEDURE proc(p_id IN number, p_name OUT varchar2)
 IS 
user_id number(6); 
query_text varchar2(500); 
BEGIN user_id := p_id; 
query_text := 'UPDATE employee_data SET salary=1.1*salary WHERE id = :1 RETURNING name INTO :2'; 
EXECUTE IMMEDIATE query_text USING user_id RETURNING INTO p_name;
COMMIT; 
END;

Create/replace the above procedure and call it. This dynamic query executes the update statement by taking id as input and returns the name of the employee who has got the hike.

Let's see one more example with select statement.

CREATE OR REPLACE PROCEDURE proc(p_id IN number, p_name OUT varchar2) 
IS
user_id number(6); 
query_text varchar2(500); 
BEGIN user_id := p_id; query_text := 'SELECT name FROM employee_data WHERE id = :1'; 
EXECUTE IMMEDIATE query_text INTO p_name USING user_id;
END;

This select statement saves the name of the employee in p_name variable for a given id.

For more info visit

How to emulate a remote Linux machine on Mac using X11 port forwarding in ssh ?

Remote Linux server/machine can be accessed from Mac using X11 port forwarding feature in ssh.

X11, also known as X window system, is a combination of server and client programs which can be used to emulate desktop environment of remote Unix like machine on a local machine. The server program of X window system runs on local machine and the client program runs on remote machine.

The best feature of X11 window system is that it is separated into client and server program, which makes it suitable to use both the programs either on same machine or on different machines. For ex, when you are using a Linux desktop with GUI, both client and server programs are running on the same machine and when you access your remote unix like machine from local machine then the server program of X window system runs on local machine and the client program runs on remote machine.

Separation of client and server programs makes X window system really fast over networked connections as most of the drawing work is handled on the local machine.

X window system can be really complex to use over networked connection as server is on the local machine, which makes it difficult for the client, running at remote machine, to see the server.

ssh provides a feature to handle this complexity, which is known as X11 port forwarding.

X11 port forwarding feature of ssh makes a secure tunnel between client and server programs of X window system, so that they can communicate .

Now let's see how to use this feature of ssh to connect to a remote Linux machine from a Mac.

You need to have an X11 server program on your mac to connect to the remote Linux machine.

X11 app comes default with the Operating system for Snow Leopard users, but it has to be downloaded and installed explicitly on Mountain Lion.

Mountain Lion users can download X11 app from here.

Once app is installed, you will have to perform some configuration changes in ssh server running on remote Linux machine.

Add the following lines to the file /etc/ssh/ssh_config

ForwardAgent yes

ForwardX11 yes

ForwardX11Trusted yes

After this uncomment/add the following line in /etc/ssh/sshd_config file.

X11Forwarding yes

Restart the ssh server on remote machine for changes to take effect.

Now on local machine

Open terminal and execute the following command

ssh -X user@host.com

This will log you in to the remote machine if the keys are set, else, it will ask for password.

After you are logged in

Assuming that the remote machine's desktop environment is GNOME, execute the following 

command.

gnome-session

If everything works perfectly, you will be able to see your remote Linux machine GUI on your Mac.

For more info visit.

How to use rsync over ssh for a secure and fast transfer?

rsync is a free utility for unix based systems which can be used to transfer files between remote machine and local machine. It does same work as rcp, but it's much faster than rcp. The logic behind its agility is that it doesn't transfer the whole file or directory, it uses checksum-search algorithm to transfer only the differences.

rsync in itself doesn't offer any security, but when used over ssh, it is best way to transfer files securely and that also faster than other copying utilities.

rsync can be used over ssh or it can be directly connected to rsync daemon running over the remote machine.

NOTE: rsync cannot be used for transfer between two remote hosts.

Now, we will see how to use rsync over ssh to synchronize files or directories between remote and local machine.

To synchronize directory on local machine with that of remote machine

rsync -aervz "ssh -l USER" --delete HOST:REMOTE_DIR LOCAL_DIR

In the same way way, to synchronize a remote directory with a local directory

rsync -aervz "ssh -l USER" --delete LOCAL_DIR HOST:REMOTE_DIR

 

Let's see what all these options are

a : stands for sync in archive mode, i.e. it offers the functionality that archive offers.

e: specifies the shell program used for communication, default is ssh.

r: transfer recursively from directories

v: stands for verbose mode

z: used for compressing the data before transmitting

delete: deletes the extraneous file at receiving side not present in sending side.

For more info visit

How to connect to a remote SSH server using public/private key cryptography?

Generally we use username and password to connect to a remote SSH server. Connecting using password is a cumbersome and less secure approach. Here are some of the drawbacks of connecting using password.

1) If you use more than one account then you need to remember password for all the accounts.

2) Changing password is an annoying task and you need to communicate password change to everyone who is using shared account.

3) Passwords are not as secure way of authentication as using keys. Each time you use password, it is passed over the network for authentication.

Now, let's see what is a key?. When we use keys to authenticate over the network, we actually use public/private key cryptography for authentication.

How does a public/private key cryptography work?

In this method, we generate two keys, a public key and a private key. Public key is known to everyone, we can also transfer it over the network. Private key is known only to us, we do not transmit it over the network, nor do we tell it to anyone. Private key is present only on your local machine and that also in a way, that only the authorized account/user can access it.

Now, when both the keys are generated, we install public key on remote machine and keep private key with us.

Following steps are followed when we try to authenticate to the server using keys.

1) Local machine requests the server for connection.

2) Server sends some data known as challenge, encrypted by public key, to the local machine.

3) Local machine/account uses its private key to decrypt the data and sends it back to the server.

4) If the server finds that both the data(sent and received) match then it allows the connection, otherwise it refuses the connection.

Let's see, how we can actually set key based authentication

1) Generate keys

Run the following program on shell

ssh-keygen

This will generate both, public as well as private key. When this program is run, it asks for the file name in which you want to save the keys and a passphrase for private key. I will discuss passphrase after sometime. For the time being, if you want, you can enter it, or leave it and press enter. Supposing, you gave file name for the keys to be my_secret_key then two files will be generated.

my_secret_key will have the private key.

my_secret_key.pub will have the public key.

2) After the keys are generated, you will have to install public key on the remote server. For this, secure copy public key from your local machine to remote machine.

scp /home/XYZ/my_secret_key.pub remote_user@host.com:/home/remote_user/

Now the public key is copied to the home directory of remote_user.

3) After this, log in to the remote host using ssh with the account for which you want to install the public key, in this case it is remote_user. This is the last time you will be logging using your password.

Make .ssh directory inside your home directory, only if it is not present. Assuming that you are in home directory.

mkdir ./.ssh (if not present)

Note: This directory is hidden, so use ls -a to check for its presence.

If .ssh directory is present then check for the file authorized_keys. If present, then append the content of my_secret_key.pub into it as

cat my_secret_key.pub >> /home/remote_user/.ssh/authorized_keys

or else, make the file.

Anyways, append will make the file if not present, so you need not make it explicitly. You only have to take care that you might not delete someone else's public key present for the same account.

Your public key is installed on the remote machine.

Come back to your local machine. Remember you entered a passphrase(if entered) while generating keys. This passphrase will be used to encrypt your private key. The private key will be stored in an encrypted form, if passphrase is used, on the local machine. So, even in the case your encrypted private key becomes vulnerable, nobody will be able to decrypt it and use it to decrypt challenge(which the server sends), unless they know the passphrase. Now you get the importance of passphrase.

Everything set, you can try connecting to remote SSH server using keys.

ssh -i my_secret_key remote_user@host.com

It will ask for your passphrase(if set).

After you enter the passphrase, you are connected to the server.

The best part of connecting by this approach is that your password is not passed over the network, not even your passphrase.

If you don't want to enter your passphrase again and again, you can set up your passphrase in an agent. An agent is a program which remembers the passphrase for you, and whenever you try to use SSH client to connect to the host, it enters the passphrase on your behalf.

For more info visit

How to use FTP in a shell script?

FTP is a standard protocol for file transfer. With FTP, files can be transferred to and from a remote host. Remote host has a FTP server installed on it which listens to FTP clients. FTP client is the program which communicates with FTP server.

Suppose that a user has an account on FTP server with username name and password pass.

Now, let's write a script to transfer a file from a remote host to local machine.

#! /bin/sh 
ftp -in host/ip<<END 
user name pass 
cd /var/myremotedir 
lcd /var/mylocaldir 
get file 
close 
bye 
END 
echo 'transfer completed' 

 In the above script, we are transferring file from remote directory /var/myremotedir to local directory /var/mylocaldir. cd is used to change directory at remote server. lcd is used to change directory at local machine. get is used to transfer file from remote directory to local directory. Similarly, we can use put to transfer file from local directory to remote directory. Note: Here cd, lcd, get are ftp commands and not unix commands. FTP server should be installed and listening on remote machine for FTP client to communicate. The username and password used by FTP client should be registered with FTP server.

For more info visit