• Howdy! Welcome to our community of more than 130.000 members devoted to web hosting. This is a great place to get special offers from web hosts and post your own requests or ads. To start posting sign up here. Cheers! /Peo, FreeWebSpace.net
managed wordpress hosting

Tips for Preventing SQL Injection Attacks

specialhost

specialhost

Member
Public data breaches in one form or another are more prevalent than ever these days, so it’s important to use a variety of tactics to keep hackers at bay. While a database on your network or in your application is safe enough on its own, when you connect perfectly secure web interface to a perfectly secure database you can, unfortunately, create the perfect attack vector. Add in a little notoriety from an advertising campaign and instantly you are on the radar of script kiddies, hackers and an endless storm of botnet networks that scour the internet perpetually looking for an opportunity to steal your data.

The beauty of the SQL injection attack is that hackers are using the inherent functionality of the database against itself. Fundamentally, a successful SQL injection attack is basically the result of an unprotected or “un-sanitized” question being asked of a database. As it is the very nature of a database to allow for the querying or “asking of properly formatted questions” to retrieve data, SQL injection is little more than a database doing what it is was built to do. The problem with this method is that you can access data that is never intended to be returned, as well as access stored functionality in the database that was never intended to be used by an over-the-internet interface.

So, how do we fix it? Well, that is not so easy. If it were easy, we would just install a tool or a patch and move on, like we did with NIMDA, a quick-spreading computer worm released back in 2001. This is no NIMDA and the “fix” is multi facetted. First, it requires training and awareness of the DBA and Web team of the vulnerabilities that exist in database driven content served up over the internet. Second, it requires a professional environment where it is acceptable to ask for help and/or training so that an employee is not made to feel inferior for not having the complex issue resolved. Next, management needs to support their coders with a professional development infrastructure that fosters a solid and practical implementation of secure coding principles. If you are using third party or outsourced development teams, considerable care should be given to understanding which controls, frameworks and methodologies are in place to ensure you get functionality and security in your apps. Finally, test your applications for negative and positive results from perspectives outside of the basic, intended functionality. Peer and third party review of all “go-live” code should happen as a normal QA function, at every major and minor revision.
 
Nice, but truly the main aspect lie with dynamic of the total scenario. People should consider, updating their cart CMS and system. SQL Injection is also of varied nature and can affect system widely.
 
mod_security is fine and great, but it can be very difficult to fine tune the rules and find that happy medium between being too restrictive and too lenient.
 
Well, my favorite aspect of mod_sec is the nature of how it functions: attacks are hopefully stopped dead in their tracks before even being accepted by the web server, which keeps MySQL and inner server functions effectively firewalled. :classic2:
 
Mod_security is the best option to avoid such issues, but it also depends on the privileges set to mysql database.
 
Make sure you never trust user input. If you understand this golden rule, you will be safer by miles
 
I find using an entity model when developing works wonders. Let your language work for you, and put a wall between the user and the database;
For each "table" have an entity, such as the following;

PHP: 
class Account extends entitiy
{
	function __construct($payload = null)
	{
		$this->entity = "Account";
		if ($payload != null)
			$this->processPayload($payload);
	}

	public function AccountName($value = null) {
		if ($value == null) { return $this->getData("AccountName");
		} else { $this->makeChange("AccountName", $value, 64); }
	}

	public function Phone1($value = null) {
		if ($value == null) { return $this->getData("Phone1");
		} else { $this->makeChange("Phone1", $value, 32); }
	}
}

The base class manages the "Save", "Delete", "Update", etc;

PHP: 
class entitiy
{
	protected $_id = "";
	public function id()
	{
		return $this->_id;	
	}
	
	protected $entity = "";
	protected $values = array();
	protected $changes = array();
	protected $orig = array();
	
	protected function ProcessPayload($payload)
	{
		foreach ($payload as $key => $value )
		{
			if ($key == "id")
			{
				$this->_id = $value;
			} else {
				$this->values[$key] = $value;	
			}
		}
	}
	
	public function Delete()
	{
		// Prepare the delete
		$sql = "";
		$sql = "DELETE FROM " . $this->entity 
			. " WHERE id = '" . $this->id() . "'";
		
		// Run the delete
		db::query($sql);
	}
	
	public function Save()
	{
		$sql = "";
		if ($this->_id != "")
		{
			// insert
			$changes = array();
			$changes[] = "ModifyUser = '1'";
			$changes[] = "ModifyDate = '".time()."'";
			foreach ($this->changes as $key => $value)
			{
				$changes[] = $key . " = '" . db::escape($value->after()) . "'";
			}
			$changes = implode(", ", $changes);
			
			// SQL statement
			$sql = "UPDATE " . $this->entity . " SET " . $changes . " WHERE id = '" . $this->_id . "'";
			db::query($sql);
		}
		else
		{
			$fChanges = array();
			$vChanges = array();
			
			$fChanges[] = "id";
			$vChanges[] = "NULL";
			
			// Create/Modify Date
			$fChanges[] = "CreateDate"; $fChanges[] = "ModifyDate";
			$fChanges[] = "CreateUser"; $fChanges[] = "ModifyUser";
			
			$vChanges[] = time(); $vChanges[] = time();
			$vChanges[] = "1"; $vChanges[] = "1";
			
			foreach ($this->changes as $key => $value)
			{
				$fChanges[] = $key;
				if ($value->after() == "")
				{ $vChanges[] = 'NULL'; } 
				else { $vChanges[] = "'" . db::escape($value->after()) . "'"; }
			}
			
			$fields = implode(", ", $fChanges);
			$values = implode(", ", $vChanges);
			
			$sql = "INSERT INTO " . $this->entity . " (" . $fields . ") VALUES (" . $values . ")";
			db::query($sql);
		}
	}
	
	protected function makeChange($property, $value, $maxLength)
	{
		if (strlen($value) > $maxLength) {
			throw new Exception("String too Long");	
		}
		
		$before = "";
		if (isset($this->orig[$property])) {
			// There has been a change before...
			$before = $this->orig[$property];
		} else {
			// No change before..
			$before = $this->values[$property];
		}
		
		// Store Change State and update property
		$this->changes[$property] = new changeState($before, $value);
		$this->values[$property] = $value;
	}
	
	protected function getData($property)
	{
		return $this->values[$property];
	}
}

Then use helper classes to retrieve the entities;

PHP: 
	public static function getById($entity, $id)
	{
		$sql = "SELECT * FROM " . $entity . " WHERE id='" . $id . "'";	
		$data = db::query($sql);
		if (count($data) != 1) {
			return NULL;
		} else {
			eval('$a = new ' . $entity . '($data[0]);');
			return $a;
		}
	}

The above is of course not the entire solution - But with a little work, you can have auto-generating "entity" classes that manage every aspect of your application - Even to the point of validation on save, or events on delete, etc... A simple example of this in action;

PHP: 
$account = getById("Account", 1);
$account->AccountName("New Account Name");
$account->Save();

How simple is that? :)
 
Last edited:
You must log in or register to reply here.
Back
Top