What is SQL Injection?
SQL injection is the act of injecting some arbitrary SQL code into a query, usually by using unescaped characters like quotes, to trick the application into doing something it was not meant to do. For example, this might mean logging in with administrator privileges without a password, or deleting records.

It's easier to understand if you have an example. Pretend you have a login form that takes a username and password. The SQL query that authenticates the user, by looking it up in the database, might look like this:

SELECT * FROM user WHERE username='$user' AND password='$pass'

If the user input is not properly checked and sanitized, then an attacker can inject SQL into the query and gain access without requiring a password (red text would be user inputted text):

SELECT * FORM user WHERE username='admin' AND password='blah' OR 1='1'

By inputting a single quote after blah, the attacker could inject any code he wanted.

How do I Prevent SQL Injection?
You have to check and sanitize any user input you use. Rule number one for programmers is never trust the user.

If you've got a select box with numbers from 1-100, make sure the form data the user sent is actually a number! (Note: is_numeric checks for any numerical value, including floats etc. You will still want to cast it to the target type)

PHP Code:

if(!is_numeric($_POST['fav_num'])) die('Please choose a number.');

// Or simply cast it into a number
$fav_num = (int)$_POST['fav_num']; 


Sanitizing the data before inserting it into the database means escaping any characters that could change the query. A lot of programmers think this means using addslashes() to escape quotes -- but this may not be enough. Databases that use different character sets (for example) will still be vulnerable if you only escape quotes.

Quoting the correct characters differs from DBMS to DBMS, so be careful. For MySQL, you can use the mysql_real_escape_string function to make user data 100% safe.

If you use the DB PEAR package, then use the quoteSmart() method. If you use another database abstraction class, be sure to check its documentation for a similar method.

PHP Code:

$user = mysql_real_escape_string($_POST['user']);
$pass  = mysql_real_escape_string($_POST['pass']);
$sql = "SELECT * FROM user WHERE username='$user' AND password='$pass'";

// Or use the quoteSmart() method from PEAR::DB
$user = $db->quoteSmart($_POST['user']);
$pass = $db->quoteSmart($_POST['pass']);
$sql = "SELECT * FROM user WHERE username=$user AND password=$pass";

/* Note that the quoteSmart() method automatically
   adds quotes around the value when it is needed,
   so you do not need to put them directory into your
   query. */ 


Nice tutorial, thanks! Ill keep these in mind

So the injection can only be used to modifying the single query, not adding one more? I mean if a user input for $password will be "blah\'; drop table user" would it work? I guess not, since mysql_query() doesn't allow multiple queries in a call.

They could still inject SQL to run subqueries for example.

Very interesting and informative. A lot of people been getting hit, from what I have read in some forums. I think there are some automated scripts scowering for certain programs too.

Thanks for the information!

EDIT: Sorry i thought this had been sticked (got confused between sticky with link to topic and the actual topic). Did not meant to bump.

It would have been better if you went over the effects of magic quotes, i.e. checking its status, reversing its effects and then manipulating (escaping) the string.

This is something i wrote to help someone else the other day:
------------------------------------------------------------------------------------



First defense is escaping all quotes in a string.

- How do we do this?
On some setups depending on configuration php will try to do this for us, this is called 'magic quotes'. Most developers prefer this to be off so we can manipulate the string ourself and also so the script does not depend on a variable (magic quotes being on or off).

So before you do anything to a string you strip the existing quotes to counteract the potential effects of magic quotes.

$string = stripslashes( mixed value );

So now the string is as it should be, exactly what was submitted. Now back to point #1, escaping the string. To do this there are 3 functions, either one of the 3 (mysql_real_escape_string, mysql_escape_string, addslashes) will do but for readability's sake i will use addslashes.

$string = addslashes( stripslashes( mixed value ) );

So you are now removing the escape charactor (\) and then reading it, seems crazy i know but it's the best way. If you do not strip them first, running an escape function again will double escape the string which becomes a hassle when pulling it from the database. remember you should not have to use stripslashes on pulled data.

This gets a little tedious to write so many people will use a function like i do:

PHP Code:

<?php
function remove_mq( $array ) 
{
     foreach($array as $key => $value) // loop array elements
     {
          if(is_array($value) // if element is an array (multidimensional array)
          {
               $array[$key] = remove_mq($value); // run function again
          }
          else // if normal string
          {
               $array[$key] = stripslashes($value); // strip slashes
          }
     }
     
     return $array; // return the array
}
?>

Now that's all great but how do we use it?

PHP Code:

<?php
// run at the top of every page, including previous code.
if(get_magic_quotes_gpc() == 1) // if magic_quotes is on (1).
{
    if (!empty($_GET))    { $_GET    = remove_mq($_GET);    } // if $_GET superglobal is nto empty run function
    if (!empty($_POST))   { $_POST   = remove_mq($_POST);   } // if $_POST superglobal is nto empty run function
}
?>

So now all you need to do when using a string is

$string = addslashes( mixed value );

...as if magic quotes is on then its effects have already been reversed.

Hope that helps a bit.

Things to read: http://uk2.php.net/magic_quotes

Rule number one for programmers is never trust the user.

This phrease is great!

doesn't mysql_real_escape_string($query); help more?

Originally Posted by TheGeapy

doesn't mysql_real_escape_string($query); help more?

No need to use mysql_real_escape_string on the whole query, use it on the data you're sending to the mysql server, like this:

 $query = sprintf("SELECT * 
                   FROM sample_table
                   WHERE field1 = '%s' and field2 = '%s'",
                  mysql_real_escape($var1),
                  mysql_real_escape_string($var2));

Best way to prevent sql injection is to delete your database duh?

Or...store it offline...duh duh?

Ways to delete a DB:

1 - Drop command
2 - Remove drive and hammer it
3 - Burn drive
4 - Cook Drive
5 - Launch missile to server building

A great little tip on preventing from SQL Injection is to always use an ELSE statement if your executing an SQL depending on a if statement. It gives you a little, but extremely nice little security boost.

Nice tutorial on the basic of SQL Injection

another step that i use is to break up my DB access permissions.
so i have a read only account that is used 99% of the time.
I have a write account that has insert, update, 'read' permissions
and lastly i have a delete account that is hardly ever used and is the only account that can delete things out of the database

so even if someone injects a write or delete comand into a query they will fail due to lack of permissions