Hello peeps, haven't been around in a while but i just sat down today and decided to help out a friend by posting this thread on "Sql Injection"

I would like fellow contributors to just enlighten us on how to prevent attacks like "Sql Injection" on php and mysql.

Please note that the readers here are expected to be on the advanced side.

Dhtml, Dual Core, Pc guru, Luzoma and fellow webmasters in the house, over to you (please make una no vex if i no mention una name, you all sabi pass me oh!)

There are tons of ways to avoid SQL Injection

1.PHP FILTERS can be used
2.The Strongest and Safest but Hard due to it's Syntax ("Regular Expression"
3.Mysql_real_escape_string but remember magic_quotes are now deprecated in php.ini
4.strip_tags removes jscript and html
5.Create a custom strip tag to remove sql commands like "LIKE" & "SELECT" "WHERE"
6.get rid of nasty characters ',$,%,* u might never know what they can do like for instance # in mysql means comment
7.Validate on Client and Server
8.Meet DHTML for more tips(Trust Me), 

Note: i will like to hint that the ultra best way i not to let user know the kind of backend u run

e.g home/view rather than home/view.php

u might need to know Apache Directives and .htaccess for URL-Rewriting Engine and also download php manual i use php 5.3 and there are so many deprecated functions that we might have used without knowing it take care,

hmmmmm

oh i forgot and ask Donpuzo too

pc guru:

There are tons of ways to avoid SQL Injection

1.PHP FILTERS can be used
2.The Strongest and Safest but Hard due to it's Syntax ("Regular Expression"
3.Mysql_real_escape_string but remember magic_quotes are now deprecated in php.ini
4.strip_tags removes jscript and html
5.Create a custom strip tag to remove sql commands like "LIKE" & "SELECT" "WHERE"
6.get rid of nasty characters ',$,%,* u might never know what they can do like for instance # in mysql means comment
7.Validate on Client and Server
8.Meet DHTML for more tips(Trust Me), 

Note: i will like to hint that the ultra best way i not to let user know the kind of backend u run

e.g home/view rather than home/view.php

u might need to know Apache Directives and .htaccess for URL-Rewriting Engine and also download php manual i use php 5.3 and there are so many deprecated functions that we might have used without knowing it take care,

Wonderful! I must confess that i've lost touch with server-side scripting but i' seriously revisiting steps 4 and 7. I actually started my IT sojourn on the Linux server, but it's amazing how GUI can sweep you of your feet. But then those working on the CLI's still have the best heads when it comes to security. Thank you sir, i'l quickly inform my friend about your repy.

Donpuzo:

hmmmmm

*dhtml:

*injects himself into the thread*

^^^^ oya make una add una own too oh! No knowledge is invaluable, fire on , we are learning.

ehmmm sorry do u mean Command Line Interface or Common Language Infrastructure anyways thanks even out of all these Apache Redirect to an error page is still the best, that way no ones knows the backhood

If u are not using a framework, i would advice you to create a database connection/query class where input validation is done before sending to mysql.
Explanation: Procedurally connecting to mysql on every page and validating on that page might cause you tons of trouble if u dont validate properly on one page.

Also, using a class makes it easier to improve on ur (backend)validation if there is need to, because u wont av to start doing that on every single page.

U might also wanna set your error level because u definitely don't want ur visitors seeing ur php/mysql errors. That'll be an open invitation to a hack party.

Meanwhile, if you know how to use ob_start and its friends well enough, you can capture your php/mysql errors inside an error file or into a database without letting your web viewers see anything that can help them hack down your stuff.

Be current with PHP, loads of deprecated functions exist, and you could think code you write is perfect, just try running your applications regularly, don't think your program is forever bug free!

As for Database security PC GURU has said the most of it! Though logics idea of making use of Object Oriented Method, may not be so true, As for his last point 

And most of all pray to God regularly, as God help bypass hacks too, Most times i see some  Nairalanders trying their hack scheme on my site,  all i do is pickup my car key, drive to tantalizer, with my E90 come online and see how the hack drama goes,

They have never succeeded and will never! 

it's more sane to have a Database Class to wrap all calls and every that way rather than multiple calls and multiple codes u can change one line and affect all related calls, though my advise is to create several Custom Exception e.g SQLException extends Exception and in catch remove mysql_error or best u can try PEAR::DB though make i no lie i don't use the later, Donpuzo is right pray to God u don't get hacked but am planning to learn hacking to hack for free porn movies lol

pc guru:

ehmmm sorry do u mean Command Line Interface or Common Language Infrastructure anyways thanks even out of all these Apache Redirect to an error page is still the best, that way no ones knows the backhood

Command Line Interface is what i meant,

lojik:

If u are not using a framework, i would advice you to create a database connection/query class where input validation is done before sending to mysql.
Explanation: Procedurally connecting to mysql on every page and validating on that page might cause you tons of trouble if u dont validate properly on one page.

Also, using a class makes it easier to improve on your (backend)validation if there is need to, because u wont av to start doing that on every single page.

U might also wanna set your error level because u definitely don't want your visitors seeing your php/mysql errors. That'll be an open invitation to a hack party.

@ logik, don't you think the input validation technique is a little to easy to bypass especially if the user makes special character entries which may otherwise cause dis-functionality to your code and probably may not even establish a connection with the mysql database, in other-words they might be making input in the front-end while at the back end cannot output the results of the query if at all it stores any input variables, just a tot?,

*dhtml:

Meanwhile, if you know how to use ob_start and its friends well enough, you can capture your php/mysql errors inside an error file or into a database without letting your web viewers see anything that can help them hack down your stuff.

Please expansiate further,

pc guru:

it's more sane to have a Database Class to wrap all calls and every that way rather than multiple calls and multiple codes u can change one line and affect all related calls, though my advise is to create several Custom Exception e.g SQLException extends Exception and in catch remove mysql_error or best u can try PEAR::DB though make i no lie i don't use the later, Donpuzo is right pray to God u don't get hacked but am planning to learn hacking to hack for free indecency movies lol

could you make a post showing an example of how custom exceptions can be applied, Thanks in anticipation.

Donpuzo:

Be current with PHP, loads of deprecated functions exist, and you could think code you write is perfect, just try running your applications regularly, don't think your program is forever bug free!

As for Database security PC GURU has said the most of it! Though logics idea of making use of Object Oriented Method, may not be so true, As for his last point 

And most of all pray to God regularly, as God help bypass hacks too, Most times i see some  Nairalanders trying their hack scheme on my site,  all i do is pickup my car key, drive to tantalizer, with my E90 come online and see how the hack drama goes,

They have never succeeded and will never! 

AMEN! Nice one bros , we go like know some of those your jackie chan protection techniques oh, LOL!

@PC, Almost all my programs are Object Oriented so i make use of single class (function) for my connection,

I mostly use mysql_error, If the Database is there, at least you should have made it available at the time of hosting off the local end. There is more or less no problem with that!

Though this days, i have a way to also checking off mysql_error, at least that will not fail, cause it uses variables and some other regularly used function.

@Jencejyde, No be only Jackie Chan, na Jet li or Bruce lee, Security is more or less a delicate but easy to handle matter.

Hunger dey beat me as i dey reply and i had a stress filled day, so when i don rest codes may come!

This is What i use If there any wrong practice let me know thanks

e.g insert into $stmt=bla (bla,bla) values('mysql_real_escape(bla)','mysql_real_escape(bla)');

Database::QuerySet($stmt);

<?php
require("constants.php";
require("class_SQLException.php";

/**
 * @author Okeowo Aderemi
 * This Manages Databases Calls and Retrieves from MySQL
 * This Class Extends SQLException so as to not give away the type of server end.
 * Version 0.1
 * NOTE:UPDATE TO STATIC CLASS TO AVOID UNNECCESSARY OBJECT
 * CREATION
 * USAGE 
 * @deprecated $dn=new Database();
 * 				$dn->connectDB();
 *				$query=" SELECT * FROM political_parties WHERE pid = \"1\" LIMIT 1";
 *				$result=$dn->query($query);
 *				print $result['psite'];
 *This allows Object making too many calls
 *
 */
#USAGE EXAMPLE################################################################
##############################################################################
/*Database::connectDB();######################################################
 $query=" SELECT * FROM political_parties WHERE pid = \"1\" LIMIT 1";#########
 $result=Database::query($query);#############################################
 print $result['psite'];######################################################
*/############################################################################
##############################################################################
class Database {
	
static  $username=DB_NAME;
static  $database=DB_BASE;
static  $password=DB_PASS;
static  $localhost=DB_SERVER;
static $db_connection;
static $count_rows;

public static function connectDB()	
{
	try{
$db_connection=mysql_connect(self::$localhost,self::$username,self::$password);
 
if(!is_resource($db_connection) || !($db_connection))
{ 
  throw new SQLException("SQLException Encountered Error Connecting to the Database";
}
	}
catch(SQLException $e)
{
	print $e->getMessage() ." at Line [".$e->getLine()."] ";
}
mysql_select_db(Database::$database,$db_connection);//? print "y": print "n";

return $db_connection;
}

public static function  queryGet($query)
{
	$result=mysql_query($query,Database::connectDB());
	try
		{
			if(!is_resource($result))
			{
		throw new SQLException("SQLException Caught this error result as a result of bad query ".mysql_error());
	
			}
			else
			{
				$data=mysql_fetch_array($result,MYSQL_BOTH);
				
				try
				{
				 if(!$data)
				 {
				 	throw new SQLException("SQLException Caught this error result as a result of Empty Set from Database";
				 }
				 else
				 {
				 	self::$count_rows=self::CountRows($query);
				 	return $data;
				 }
				}
				catch(SQLException $t)
				{
				print $t->getMessage() ." at Line [".$t->getLine()."]";		
				}
			
			}
		}
			catch(Exception $e)
			{
	print $e->getMessage() ." at Line [".$e->getLine()."]";	
			}
		
		
}
public static function EnchanceQueryGet($query)
{

$result=mysql_query($query,Database::connectDB());
	try
		{
			$return=array();
			if(!is_resource($result))
			{
		throw new SQLException("SQLException Caught this error result as a result of bad query ".mysql_error());
	
			}
			else
			{
				while($row=mysql_fetch_array($result, MYSQL_BOTH))
					{
					$return[]=$row;
					} 
			if(empty($return))
			{
				return false;
			}
			else
			{
				return $return;
			}
			}
}
catch(SQLException $sql)
{
	print $sql->getMessage();
}
}


public static function  querySet($query)
{
	
	try
		{
			$result=mysql_query($query,Database::connectDB());
			if(!$result)
			{
				
		return false;
	
			}
			else
			{
				return 1;
			}
		}
			
		catch(Exception $e)
		{
	print $e->getMessage() ." thrown at Line [".$e->getLine()."]";	
		}
		
		
}
//////////////////////////////////////////////////////////////////////
public static function  CountRows($query)
{
	
	try
		{
			$result=mysql_query($query,Database::connectDB());
			$rows=mysql_num_rows($result);
			if(!$result)
			{
		throw new SQLException("SQLException Caught this error result as a result of bad query ".mysql_error());
	
			}
			else
			{
				return $rows;
			}
		}
			
		catch(Exception $e)
		{
	print $e->getMessage() ." thrown at Line [".$e->getLine()."]";	
		}
		
		
}

static public function close()
{
	mysql_close(self::$db_connection);
}
}
 

?>

but when upload online i will advise to remove Mysql_error in those catch and try to make use of error_log or best if you are advanced like dhtml u can create callback for Output buffering to remove error and send to u, me i don't know how to use ob for that

PC GURU, Your PHP is fine,

Shey na Java dey bring out the best in your PHP, It did the same to me, after i was billed 100,000 to learn PHP OOP by one Naija Programmer, He knows himself, I vex and learnt Java instead b4 i know, OOP PHP became easy for me.

i remeber like a time where i swore Procedural was Enough for me after doing java classes at Aptech i just forgot how to do Procedural, Omo Scopes ;Di dey use procedural just kidding.Donpuzo wetin happen u no dey follow me chat again and dhtml Big boys

Ha, No be like so, no big boy anywhere jor!, [size=1pt]though i be big man shaaa[/size]

The fact is that i have many personal projects on ground, Should release them before December,

Though they are open source , Just wanna document some facts on the web, before i commot!

As for OOP matter, No mind Onyinbo, them wahala too much, i swear soon na OPC go be new design pattern!,

Remember when i was taking a guy PHP, the guy no gree learn OOP, he turn am to OPC!

One common mistake that is used in random hacking via google advanced search (Also coded: Google master hacking) is to use non-php extensions for included files which at times may contain mysql connection parameters.

Such as putting a file like "db_conn.inc" or "config.php.inc" in an accessible folder. If u understand google hacking, u'll be suprised at how many website's sql username and password you can see for free because of this security flaw.

Also, if u are downloading an open source script or extension, be sure of the security before u implement. i've read through so many open source scripts that use ".inc" as extension for important files and put them where they can be reached.

Stop hacking around security threats that you know someone will always break into, even the best sites have been hacked by the list expected guys, You need not pray that you don't get hacked cos you will when the time comes,Follow the trend in developments, SQL injection in PHP has brought about PDO (PHP Data Object) use that and be rest assure that your database will not have anything called SQL Injection,Though it is not that easy and if i can get around using it anyone else can.The idea behind it is to abstract database object and make it independent of a specific database engine yet it offers same interface to all the underlying engine you might think of using,MySQL,Oracle,SQLite,postgreSQL etc,most interesting is that it comes with preparation of sql statements,during which it automatically filters you inputs and you can bind known variables to place placeholders in your statements the thing to get around is thnking differently from the way run your sql query, this is totally dfferent yet it is the same old sql yuo have known all your coding life.
When i have the i will post a tutorial on using the PDO ,but you can read about it on php community site ,there are a lot of developers examples there

Yeah you can use PDO like PEAR::DB or if thats considered too much mysqli has OOP Interface which is also good too,

cdeveloper:

Stop hacking around security threats that you know someone will always break into, even the best sites have been hacked by the list expected guys, You need not pray that you don't get hacked cos you will when the time comes,Follow the trend in developments.

iLike this!

Pc guru!, well done, simply proved your worth,

Donpuzo:

@PC, Almost all my programs are Object Oriented so i make use of single class (function) for my connection,

I mostly use mysql_error, If the Database is there, at least you should have made it available at the time of hosting off the local end. There is more or less no problem with that!

Though this days, i have a way to also checking off mysql_error, at least that will not fail, cause it uses variables and some other regularly used function.

@Jencejyde, No be only Jackie Chan, na Jet li or Bruce lee, Security is more or less a delicate but easy to handle matter.

Hunger dey beat me as i dey reply and i had a stress filled day, so when i don rest codes may come!

hahahh i dey feel you sir, shaky no dey.

cdeveloper:

Stop hacking around security threats that you know someone will always break into, even the best sites have been hacked by the list expected guys, You need not pray that you don't get hacked cos you will when the time comes,Follow the trend in developments, SQL injection in PHP has brought about PDO (PHP Data Object) use that and be rest assure that your database will not have anything called SQL Injection,Though it is not that easy and if i can get around using it anyone else can.The idea behind it is to abstract database object and make it independent of a specific database engine yet it offers same interface to all the underlying engine you might think of using,MySQL,Oracle,SQLite,postgreSQL etc,most interesting is that it comes with preparation of sql statements,during which it automatically filters you inputs and you can bind known variables to place placeholders in your statements the thing to get around is thnking differently from the way run your sql query, this is totally dfferent yet it is the same old sql yuo have known all your coding life.
When i have the i will post a tutorial on using the PDO ,but you can read about it on php community site ,there are a lot of developers examples there

wel said, but don't you think that any form of security measure taken is worth the effort?

Thanks dude nothing pleases me to see we all getting along, even thinking of starting a blog on Web Development but no motivation and not sure if anyone needs another blog,

Nice and lovely thread. . .

@DHTML, Bros contribute na, you know you have seen many of this hack and injection treats, and have worked on large systems, of which Security is of utmost priority!

And where is Dual and Co, Shey na Bullion Van, their sites and application dey inside>?