Generating sequential numbers in a database

You are creating an application that allows organisations to manage employees. One of the tasks that it has to do is generate an employee ID when a new employee is being entered. One way of doing this is through this query:

SELECT max(empl_id)+1 FROM employee;

By James Cridland
By James Cridland

However, this query presents a problem in a multiuser environment: if more than one user is entering employee details at the same time, they will both get the same empl_id. To tide over this problem, one way to go is to look at the auto numbering solution provided by the database – however I personally find that solution limiting and have never used it.

The other approach is to create a single row table for global settings (in all probability your application will already have this) and maintain a field in that table as the last number used. Thereafter, the code can be written as below:

UPDATE settings_tbl SET lastnumber=lastnumber+1;

SELECT lastnumber FROM settings_tbl;

Remember that the order of the queries is important in a multiuser environment. Placing SELECT before the UPDATE can cause problems (locking has to happen first).

This piece of code should be executed at the time of saving the employee and not when a request for the blank employee form is generated. This is necessary so that one user of the application doesn’t have to wait for another to be finished. Note that the UPDATE lock is released only when you do the COMMIT or ROLLBACK.

Another way of doing the same thing in Oracle, one that I prefer myself and have used in a number of tight situations is the FOR UPDATE clause. This one allows you to do the SELECT first:

SELECT lastnumber FROM settings_tbl FOR UPDATE;

UPDATE settings_tbl SET lastnumber=lastnumber+1;

Share

Safe Browsing Guide – II

Continued from Safe Browsing Guide – I

You might get some ‘warnings’ while opening secure pages, like below:

Warnings

The first one means that the certificate could have been signed by anyone, including by someone you do not trust. If all I need is encryption: for example if I am sending email, and not sending any corporate presentations, or password, or credit card numbers – I should be fine. Many websites use such certificates – but if it’s a bank website using it – I would not login. To give you an idea of the relative security impact of many of the warnings discussed during this guide: this particular warning has a severity of 40 out of 100. It means even on seeing this warning, I am 60% likely to use the website.

The second one means that the certificate has expired. In this case, I would check what is the expiry date (by clicking on the lock) and if it expired a couple of days back I would allow it. In addition, if its sensitive information, I would not use the site until the issue is fixed. This one has a severity of 30.

The last one says that the website names do not match between the certificate and the actual website you are visiting. This could be a case of phishing, and could be serious. What I do in such a case is I find out what name the certificate is issued to (clicking on the lock icon) and check it against the actual website in the URL. If the website visited is server.icicibank.com and the certificate is for icicibank.com – I continue to use the site. If the two are very different, I don’t use the site. Severity is 80.

Mozilla issues one message for each of the issues as noted above while Internet Explorer (shown above) issues just one message with error icons:

Warnings

This corresponds to the second warning from Internet Explorer.

Warnings

This one corresponds to the first warning from Internet Explorer, same actions apply.

Another warning that you might see is this one:

Warnings

Correspondingly for Mozilla:

Warnings

This warning means that there is some content on the page which is not encrypted: this could be images or something else. Severity is 50: on GMail it may mean that the emails have such content so it’s ok to continue to do your stuff. However, if on one of the emails you are sending a password – you may want to be careful.

Lastly I would encourage visitors to read this advise on safe browsing from a security expert. This essay from 2004 is still good.

Disclaimer: This is just a guide and is not meant to replace professional advise. No measures can guarantee 100% security. There are a lot of threat vectors outside the scope of this tutorial: such as key loggers on your computer. In addition, the severities explained for warnings are just guides and have no scientific basis.

Share

Safe Browsing Guide – I

When you browse over the Internet, or Chat, or send/receive email – you are not doing that in private. It is important to understand exactly what is private, and what steps you need to follow to maintain the privacy.

When accessing any website such as Yahoo.com, you get connected to the web site’s server which provides you the information you seek – search results, or email. This connection is not direct: you are connected through a series of nodes. Each node can view/alter the information that is flowing through it.

A protocol – ‘https’ provides privacy to your interaction by adding a ‘Secure Socket Layer‘ on top of the normal HTTP protocol. Enough of jargon, back to English!

So when you use this particular protocol you are secure subject to some caveats. Use of this protocol can be confirmed through the ‘https’ at the beginning of the URL, and through the ‘lock’ icon at the bottom right: Lock.

A lot of online websites support HTTPS for logging in. You have to select ‘secure’ at the login screen where you enter username/password. This means that your password is protected during the communication. However, these sites move back to normal mode after the login: your data (for example the email content) is not protected. Gmail supports secure connection even after login but you have to enable it in the settings – this makes sense and you should do it. However, even after doing this it does not mean that all your content is ‘protected’ – more on this later.

Please understand that if its emails in question: just your using a secure connection is not enough. The recipient should also use it for the information to remain inaccessible at the nodes.

Proceed to the next part of the guide.

Disclaimer: This is just a guide and is not meant to replace professional advise. No measures can guarantee 100% security. There are a lot of threat vectors outside the scope of this tutorial: such as key loggers on your computer. In addition, the severities explained for warnings are just guides and have no scientific basis.

Share