SQL Injection


What is SQL Injection

SQL injection refers to the act of someone inserting a MySQL statement to be run on your database without your knowledge. Injection usually occurs when you ask a user for input, like their name, and instead of a name they give you a MySQL
statement that you will unknowingly run on your database.

SQL Injection Example:

Below is a sample string that has been gathered from a normal user and a bad
user trying to use SQL Injection. We asked the users for their login, which will
be used to run a SELECT statement to get their information.

MySQL & PHP Code:

// a good user's name
$name = "avi"; 
$query = "SELECT * FROM customers WHERE username = '$name'";
echo "Normal: " . $query . "<br />";

// user input that uses SQL Injection
$name_bad = "' OR 1'"; 

// our MySQL query builder, however, not a very safe one
$query_bad = "SELECT * FROM customers WHERE username = '$name_bad'";

// display what the new query will look like, with injection
echo "Injection: " . $query_bad;

Display:

Normal: SELECT * FROM customers WHERE username = 'avi'

Injection: SELECT * FROM customers WHERE username = '' OR 1''

The normal query is no problem, as our MySQL statement will just select
everything from customers that has a username equal to avi.
However, the injection attack has actually made our query behave differently
than we intended. By using a single quote (') they have ended the string part of
our MySQL query
username = ' '
and then added on to our WHERE statement with an OR clause of 1 (always true).
username = ' ' OR 1
This OR clause of 1 will always be true and so every single entry in the
"customers" table would be selected by this statement!

More Serious SQL Injection Attacks

Although the above example displayed a situation where an attacker could
possibly get access to a lot of information they shouldn't have, the attacks can
be a lot worse. For example an attacker could empty out a table by executing a
DELETE statement.

MySQL & PHP Code:

$name_evil = "'; DELETE FROM customers WHERE 1 or username = '"; 

// our MySQL query builder really should check for injection
$query_evil = "SELECT * FROM customers WHERE username = '$name_evil'";

// the new evil injection query would include a DELETE statement
echo "Injection: " . $query_evil;

Display:

SELECT * FROM customers WHERE username = ' '; DELETE FROM customers WHERE 1 or
username = ' '

If you were run this query, then the injected DELETE statement would completely
empty your "customers" table. Now that you know this is a problem, how can you
prevent it?

Injection Prevention methods:

1. mysql_real_escape_string()

Lucky for you, this problem has been known for a while and PHP has a
specially-made function to prevent these attacks. All you need to do is use the
mouthful of a function mysql_real_escape_string.

What mysql_real_escape_string does is take a string that is going to be used in
a MySQL query and return the same string with all SQL Injection attempts safely
escaped. Basically, it will replace those troublesome quotes(') a user might
enter with a MySQL-safe substitute, an escaped quote \'.

Lets try out this function on our two previous injection attacks and see how it
works.

MySQL & PHP Code:

//NOTE: you must be connected to the database to use this function!
// connect to MySQL

$name_bad = "' OR 1'"; 
$name_bad = mysql_real_escape_string($name_bad);
$query_bad = "SELECT * FROM customers WHERE username = '$name_bad'";
echo "Escaped Bad Injection: <br />" . $query_bad . "<br />";
$name_evil = "'; DELETE FROM customers WHERE 1 or username = '"; 
$name_evil = mysql_real_escape_string($name_evil);
$query_evil = "SELECT * FROM customers WHERE username = '$name_evil'";
echo "Escaped Evil Injection: <br />" . $query_evil;

Display:

Escaped Bad Injection:

SELECT * FROM customers WHERE username = '\' OR 1\''
Escaped Evil Injection:
SELECT * FROM customers WHERE username = '\'; DELETE FROM customers WHERE 1 or
username = \''

Notice that those evil quotes have been escaped with a backslash \, preventing
the injection attack. Now all these queries will do is try to find a username
that is just completely ridiculous:
Bad: \' OR 1\'
Evil: \'; DELETE FROM customers WHERE 1 or username = \'

And I don't think we have to worry about those silly usernames getting access to
our MySQL database. So please do use the handy mysql_real_escape_string()
function to help prevent SQL Injection attacks on your websites.

2. Parameterized statements 

A parameterized query uses placeholders for the input, and the parameter values
are supplied at execution time.

$params = array($Uname, $Pword);
$sql = 'INSERT INTO Users (UserName, Password) VALUES (?, ?)';
$query = sqlsrv_query($connection, $sql, $params); 

Advanced: In PHP version 5 and above, there are multiple choices for using
parametrized statements; the PDO database layer is one of them. There are also
vendor-specific methods; for example, MySQL 4.1 + used with the mysqli
extension.

Comments

Popular posts from this blog

SVN: File remains in conflict

HowTo: Enable extended logging for exim

Error: could not open mime types config file