SQL Injection Prevention

[axilant]

Code:

if (get_magic_quotes_gpc())
{
   $_POST = array_map('stripslashes_deep', $_POST);
   $_GET = array_map('stripslashes_deep', $_GET);
   $_COOKIE = array_map('stripslashes_deep', $_COOKIE);
}
$_POST = array_map('addslashes_deep', $_POST);
$_GET = array_map('addslashes_deep', $_GET);
$_COOKIE = array_map('addslashes_deep', $_COOKIE);

function stripslashes_deep($value)
{
	$value = is_array($value) ? array_map('stripslashes_deep', $value) : stripslashes($value);  return $value;
}

function addslashes_deep($value) 
{
	$value = is_array($value) ? array_map('addslashes_deep', $value) : stripslashes($value); return $value;
}

How to use this code:
Basicly this code is probably good to be put in a config file. Reason for this code is so you dont have to worry about SQL Injection prevention what so ever in your coding, cause its already done in your config file. When you pull records from mysql or something, you will need to do something like this:

$sql = mysql_query("SELECT * FROM table");
$row = mysql_fetch_array($sql);
$row = array_map('stripslashes_deep', $row); //strip the slashes that were added before.

That way you remove the \" \' ect....

To learn more about php scecurity look at this PHP Security Magazine:
http://www.insecuremagazine.com/INSECURE-Mag-2.pdf
This has a GREAT article about PHP security and keeping your programming secure.

My reason for not using mysql_real_escape_string(), well a lot of people dont even know this function exists, and addslashes/stripslashes is more common. But yet, they do almost the same exact thing. Just a new name, and this is how most big websites do it, you can even take a look at most of the popular forum software, they use addslashes/stripslashes.


What is SQL injection:

SQL injection is a security vulnerability that occurs in the database layer of an application. Its source is the incorrect escaping of variables embedded in SQL statements.

Assuming the following code is embedded in the application, and a parameter "userName" that contains the user's name is given, SQL Injection is possible:

statement := "SELECT * FROM users WHERE name = '" + userName + "';"

If supplied with "a'; DROP TABLE users; SELECT * FROM data WHERE name LIKE '%" as "userName", the following SQL statement would be generated:

SELECT * FROM users WHERE name = 'a'; DROP TABLE users; SELECT * FROM data WHERE name LIKE '%';

The database would execute the statement in order, selecting data, dropping (deleting) the "users" table and selecting data that maybe was not meant to be displayed to web users. In essence, any data in the database available to the user connecting to the database could be read and/or modified.

I got this from Wikipedia.

Cody

 

[Porte]

Thanks a lot. I was coding from scratch, so I think I can use something like this now.

 

[unclekyky]

why not just use mysql_real_escape_string() on any data sent to MySQL?

 

[axilant]

My reason is so i dont have to rewrite around 500 queries. Unless your willing to do it for free :S

 

[unclekyky]

i guess thats the beauty of database abstraction.

 

[Porte]

mysql_real_escape_string() works very well. And its suggested by the php manual.

 

[axilant]

mysql_real_escape_string() works very well. And its suggested by the php manual.

I have code with over 1200 queries :S So i rather not go back and change em! :D

Alright.

Heres my NEW mysql class, i decided to go back and rewrite it using my "escape" function

<?PHP
/*
$hostname = "localhost";
$database = "db";
$username = "root";
$password = "";
$db = new db("$hostname:$database:$username:$password");
 */
class db 
{
	var $_version = "1.0";
	var $_lastupdate = "7/28/2005";
	var $_author = "Cody Selzer <[email protected]>";
	function db($args)
	{
		$arg = explode(":", $args);
		$this->host = $arg[0];
		$this->database = $arg[1];
		$this->username = $arg[2];
		$this->password = $arg[3];
		$this->connect();
		$this->select_db();
		$this->close();
	}

	function connect() {
	global $cls;

	$this->connect = @mysql_connect("$this->host", "$this->username", "$this->password");
		if(!$this->connect) {
		$this->error("Mysql Connection Error","Failed connecting to database server\r\n\r\n" . mysql_error() . "");
		}

	return $this->connect;
	}

	function select_db() {
	global $cls;

	$this->select_db = @mysql_select_db($this->database, $this->connect);
		if(!$this->select_db) {
		$this->error("Mysql Connection Error","Failed selecting to database\r\n\r\n" . mysql_error() . "");
		}

	return $this->select_db;
	}

	function query($query) {
	global $cls;

	$this->result = mysql_query($query);
		if(!$this->result) {
		$this->error("Mysql Query Error","Error Message: Failed executing database query\r\nDate/Time: " . date('Y-m-d H:i:s') . "\r\nQuery: $query\r\nMySQL Error: " . mysql_error() . "");
		}

	$detect1 = preg_replace('/DELETE/siU', 1, $query);
	$detect2 = preg_replace('/UPDATE/siU', 1, $query);
	$detect3 = preg_replace('/INSERT/siU', 1, $query);

		if($detect1 == 1 || $detect2 == 1 || $detect2 == 1) {
		$this->affected_rows = mysql_affected_rows();
		$this->affected_rows_total += $this->affected_rows;
		}

	$this->counter++;
	return $this->result;
	}

	function fetch_row($query) {
	$this->result = mysql_query($query);
	$this->row = $this->fetch_array($this->result);

	$this->counter++;
	return $this->row;
	}

	function fetch_array($query) {
	$this->row = @mysql_fetch_array($query);

	return $this->row;
	}

	function num_rows($query) {
	$this->result = mysql_query($query);
	$this->num_rows = mysql_num_rows($this->result);

	$this->counter++;
	return $this->num_rows;
	}
	function escape(&arr)
	{
		foreach($arr as $k => $v)
		{
			if (is_array($v))
			{
				$this->escape($arr[$k]);
			}
			else
			{
				if (function_exists('get_magic_quotes'))
				{
    				if(!get_magic_quotes_gpc())
    				{
        				$arr[$k] = stripslashes($v);
    				}
				}
				$arr[$k] = mysql_real_escape_string($v):
			}
   		}
	}
	function count_queries() {
	return $this->counter;
	}

	function affected_rows() {
		if($this->affected_rows_total == '0') {
		$this->affected_rows_total = '0';
		}

	return $this->affected_rows_total; 
	}
	function list_dbs()
	{
		return mysql_list_dbs();
    }
    function tablename($query)
    {
	    return mysql_tablename($query);
    }
    function num_fields($query)
    {
	    return mysql_num_fields($query);
    }
    function error($title,$msg)
    {
	    exit("<h3>$title</h3>$msg");
    }

	function close() {
	register_shutdown_function('mysql_close');
	}
}
?>

when you are going to use a $_POST $_GET, $_COOKIE, $_REQUESt ect...
use:

$db->escape($_POST); //(replace _POSt with your input array)

Im updating this in 5-10 minutes. Edit: Updated. (I saw an error.)

 

[Porte]

Very nice, but its very complicated to me only.
I'm not too familiar with classes, well I think I would stick with the input validation at the first place (first post).

 

[Refrozen]

why not just use mysql_real_escape_string() on any data sent to MySQL?

I wrap that in to my database classes entirely. No need to deal with security, my class does it

 

[iNod]

One of the other biggest made ways to injection is $select = "SELECT *...";
Than $select = mysql_query($select);

This opens up to a way where the user can just do
index.php?select=DELETE FROM clients WHERE user=bob

Something as easy as that can destory a business.

But yes I reccommand the script above to new programmers. More advanced ones have other ways in which to make sure no sql injections occur.

Regards,

 

[adam_uk]

One of the other biggest made ways to injection is $select = "SELECT *...";
Than $select = mysql_query($select);

This opens up to a way where the user can just do
index.php?select=DELETE FROM clients WHERE user=bob

Something as easy as that can destory a business.

But yes I reccommand the script above to new programmers. More advanced ones have other ways in which to make sure no sql injections occur.

Regards,

to be honest if your retarded enough to pass sql queries through urls you should be shot.

 

[axilant]

to be honest if your retarded enough to pass sql queries through urls you should be shot.

Rofl... yea really rofl thats about the funniest thing i ever heard ^^'

even if you encrypt them... its still not safe... theres ways to decrypt most things including md5 (takes a while though, visual basic program that will keep running till it finds a match ^^)

 

[Porte]

That's weird..I never heard about passing queries through URLs.

 

[axilant]

That's weird..I never heard about passing queries through URLs.

Keep it that way, unless you want to make me a fun place ^^'

 

[axilant]

I bring this topic back up because i have made the first post in this thread much better, to explain it a bit better than i have before. Website Security is probably the hardest thing to come by.