kaman

SQL injection is a method for attacking databases. The attacker "injects" elements into your program's SQL in order to bypass authorization or damage the database. Web sites that send SQL commands to databases are particularly vulnerable to SQL injection, because they often rely on dynamic SQL, and because it can be easy to mount millions of such attacks until one succeeds.

Here is a simple example.

A PHP page asks the user for a name and a password, then sends this to the database:

SELECT * FROM mysql.user WHERE user = '$usr' AND password = '$pwd';

Is it as harmless as it looks? Suppose a user enters something like this as a user name:

' OR 1>0; --

When your application plugs that entry into your SQL, the command becomes:

SELECT * FROM mysql.user WHERE user = '' OR 1>0; -- AND password = ''

Attacker just retrieved all rows and columns of the mysql.user table. Not exactly what you had in mind. Or attaker might supply this username:

OR 1>0; TRUNCATE customers; --

whereupon your application sends this command to the database:

SELECT * FROM mysql.user WHERE user = '' OR 1>0; TRUNCATE customers; -- ' AND password = ''

If your application's connection supports multiple SQL commands in a single query call, all your customer data just went away.

LEVEL 1 DEFENSE: NEGATIVE INPUT FILTERS

The simplest way to prevent this sort of injection is to search the SQL string for semi-colons and double dashes, and remove them before passing the statement to the database. That's easy in an adequate application language, for example in PHP:

$protectedqry = str_replace( "--", "", str_replace( ";", "", $qry ));

If $qry has offending characters, sending $protectedqry to the database raises a MySQL error. That provides one level of protection. Better still, search the string for double dashes and semi-colons, and if either is found then refuse to send the query to the database. If you want to be really thoroughgoing, you could blacklist the IP address that launched the attack. Now you are fully protected against attacks that use double dashes and semi-colons. Have you covered all possible attacks? Not a chance, human ingenuity having no practical limit. For example, a favorite trick we haven't touched on is introduction of harmful WHERE clauses.

LEVEL 2 DEFENSE: POSITIVE INPUT FILTERS

The attacker has to succeed just once. If your database is to be safe, you must succeed every time. You are on better logical ground enforcing a simple positive validation pattern than looking for a limitless number of dangerous or invalid patterns. Positive input filters improve your chances of success enormously.

For example, you could decide to accept only alphanumeric characters in user names and passwords. It is easy to enforce that rule in PHP:

if( ereg( '[^A-Za-z0-9]+', $usr.$pwd )) {
echo "<script>
alert('Alphabetic and numeric characters only, please.');
</script>";

You can formulate more stringent tests based on specific input requirements.

LEVEL 3 DEFENSE: OUTPUT FILTERS

Finally, application languages provide generic tools for cleaning up submissions to your database. Again in PHP the function to use is mysql_real_escape_string():

$qry = mysql_real_escape_string( $qry, $connection_resource );

LEVEL 4 DEFENSE: ENCAPSULATION

Enterprise RDBMS policies usually require that all such protective logic be encapsulated in stored procedures.

Summary

To stop SQL injection attacks in their tracks, apply simple positive and negative input filters, and escape possibly problematic characters in what you send to the database.

Thank You for reading.

kaman

LEVEL 5 DEFENCE: Filter search keyword while using LIKE operator

The percentage symbol is commonly used by MySQL to perform LIKE queries - this WON'T get escaped. If your application is doing LIKE comparisons, and database is large, then it's worth checking for this specifically to avoid a user entering "%" and making database grind to a halt.

Example:
$user_input = '%';
$query = "SELECT x,y,z FROM tablename WHERE user LIKE '%$user_input%';

// becomes LIKE %%% and returns all rows in tablename. In this case the search filter does not work and makes database to look all records.

Thanks

kaman

DEFENCE 6: Never connect database using as superuser (administrator). Use always customized users with very limited privileges so if any hacker injects hacking codes to SQL statement and try to execute SQL commads, the system won't allow such commands.

Thanks

sachin

Very interesting. It has improved the knowledge.

thanks for sharing

kaman

Thank you sir for your prompt response.

sharma.s.prakash

this is very usefull information you have shared
Thanx

sharma.s.prakash

have you some more information like this please share.

kaman

I'll do as soon as possible. thanks for your interest.

harry

very effective information

kaman

Thank you, Harry.