Recently when going through the student projects on web design I came across codes similar to the following many times.

$Result =Select * from members where username=’$x’ and password= ‘$y’;

This is typically a code used for user authentication, in which username and password are collected into variables $x and $y .The students and many web designers assume that such queries are safe and the system is well protected.

But such queries give raise to a kind of attack popularly known as SQL injection attack.

The user may give admin as the user name and the string nothing ' OR '1'='1 as the password. So what happens? The query becomes

Select * from members where username=’admin’ and password= ‘nothing ' OR '1'='1’

This returns a positive number of rows since the condition ‘1’=’1’ always holds. The attacker coolly gets into an admin account. Also he may enter more dangerous commands like insert, Drop etc. into SQL and cause havoc into your database. Also this is not special to any programming language. Almost all server/client side programming is prone to this. Also an SQL can be injected to user registration, searches, and similar things.

Another common type of SQL injection attack is by injecting the SQL into the URL directly. How to prevent this?

1.Database level:

A user must have only the bare necessary privileges to the database. This is called “the principle of least privileges”.Don’t give the connecting user privileges such as drop, delete etc on databases unless it is absolutely needed. This will ensure that damage to the database is minimized.

2.Programming level:

Do not pass the query string generated by the user directly onto the database. First pass it through a security layer which checks for unwanted characters, replaces a spurious commands etc. and blocks the query if it is suspicious. For example the security layer may find that in the above login script there are unnecessary Quotes and block it. You can design an abstract security layer, which works for all types of databases and stop attacks.