SQL -injection attacks: what every website designer must be know:expanded version including resources

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?

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.
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.

This is only an elementary exposure to the technique of SQL injection. There are many specific articles dealing with the problem for different databases. Some interesting links are given below.
General
For a brief introduction to SQL-injection and general methods to overcome it, please read the following article. The comments after the article are also interesting.
http://lwn.net/Articles/177037/

SQL server
An advanced treatment of different methods of sql injection ,like different methods, prevention can be found in the following article . Many examples using Sql server are also given, I recommend this as a must read article for any web programmer using SQL-server.
http://www.ngssoftware.com/papers/advanced_sql_injection.pdf

The article http://www.sitepoint.com/article/sql-injection-attacks-safe also gives you very good reading..

The following paper looks at the sql injwection from a different perspective.

MySQL
http://www.wellho.net/resources/S161.html provides many articles on mysql security, including sql injection. I strongly recommend that you read these before going to code a website in mysql.


http://www.devarticles.com/c/a/MySQL/SQL-Injection-Attacks-Are-You-Safe/
is also a mustread article.

http://dev.mysql.com/doc/refman/5.0/en/security-guidelines.html
Gives you security guidelines any mysql programmer should know. It can be read after the previous article.


http://shiflett.org/articles/security-corner-apr2004
Gives you php code which will help. The comments and discussion that follows are also enlighting.

DB2
http://www.db2mag.com/story/showArticle.jhtml?articleID=17602334
Contains an article about the topic and related. This discusses DB2 security in detail.
Also I recommend the following article to be read by people who use DB2 databases.
http://www.db2mag.com/shared/printableArticle.jhtml?articleID=18901175

Ted J. Wasserman of IBM( the creators of DB2) explains DB2 security in the following article.This is detailed article and includes sql injection.
DB2 security, Part 8: Twelve DB2 security best practices

ORACLE

SQL Injection and Oracle, Part One is a simple article on sql injection in oracle. Be sure to read that.Advanced SQL Injection in Oracle databases
Presents a paper on the topics and related ones.



I will try to add as many new things as and when I am free. You are also welcome to add links by posting comments