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.