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 = "timmy"; 
$query = "SELECT * FROM customers WHERE username = '$name'";
echo "Normal: " . $query . "
";

// 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 = 'timmy'
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 timmy.

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 aDELETE 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 - 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: 
" . $query_bad . "
";


$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: 
" . $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. You have no excuse not to use it after reading this lesson!

function cleanQuery($string)
{
  if(get_magic_quotes_gpc())  // prevents duplicate backslashes
  {
    $string = stripslashes($string);
  }
  if (phpversion() >= '4.3.0')
  {
    $string = mysql_real_escape_string($string);
  }
  else
  {
    $string = mysql_escape_string($string);
  }
  return $string;
}

// if you are using form data, use the function like this:
if (isset($_POST['itemID'])) $itemID = cleanQuery($_POST['itemID']);

// you can also filter the data as part of your query:
SELECT * FROM items WHERE itemID = '".  cleanQuery($itemID)."' "

PHP Code:



$username = mysql_real_escape_string( $username );$password = mysql_real_escape_string( $password );

?>

This is intended as a brief guide to protecting your MySQL database from SQL injection attacks. Unfortunately, a large amount of the code that I've seen written by people on forums, and in countless crappy PHP tutorials lurking around on the net, and in the many websites that display the "magic breeding slashed-quote" show that many people just do not understand what's going on and how to protect themselves against SQL injection attacks.

This is intended as a brief guide to protecting your MySQL database from SQL injection attacks. Unfortunately, a large amount of the code that I've seen written by people on forums, and in countless crappy PHP tutorials lurking around on the net, and in the many websites that display the magic breeding slashed-quote (\' - see below) show that many people just do not understand what's going on and how to protect themselves against SQL injection attacks.

In fact, the only reason that many websites are "protected" is due to magic quotes, and given that this is due to be disabled in the forthcoming PHP6, then there's going to be some major problems cropping up.

I'll talk about the problem of SQL injection, the half-hearted attempt to fix it with these "magic quotes", and what you should really be doing EVERY TIME you send user inputted data to your database.

The Problem - What is SQL Injection:

As the name suggests, SQL Injection is quite simply, when the user injects SQL into your application. How does this happen? Say we have a nice simple login form that takes a username and password, and checks if that's in the database. If it is, the user is logged into an admin section or something. The code for this could look something like this:

Monday, May 30, 2011

Preventing SQL Injection with MySQL and PHP

what is sql injection

sql injection example

MySQL & PHP Code:

Display:

more serious sql injection attacks

MySQL & PHP Code:

Display:

injection prevention - mysql_real_escape_string()

MySQL & PHP Code:

Display:

The Problem - What is SQL Injection:

The crap attempt to fix it ( magic quotes ):

But do not rely on this!

(Aside) The Magic Breeding Slashed-Quote:

Fixing it.

Using better slashes - mysql_real_escape_string( ) (PHP4, mysql_* )

Warning:

Fixing it with better technique - bound parameters ( PHP5, MySQLi ):

Another thing to keep in mind:

The Percentage Sign (%)

3 comments: