Tags
PHP
Asked 7 years ago
6 Oct 2016
Views 1008
sqltreat

sqltreat posted

how to avoid sql injection in php ?

some user entered username with '(single quote) or "(double quote ) like sam'skitchen and it break my code and people can see my query on the registration page . i got that it s called sql injection . i want to avoid it better way
ravi

ravi
answered Nov 30 '-1 00:00

to clean input to avoid sql injection.

$clean_username=new sqlEscape($_REQUEST['username'],$dbconn);//$dbconn here is connection from database which return from mysql_connection function
$cleane_query="update users set usename='$clean_username' where ...here condition .. ";

you can use sqlEscape class for it .

 class sqlEscape {
	function safeEscape($source, & $connection)
	{
	 	if (is_array($source))
		{
			foreach ($source as $key => $value)
			{
		 
				if (is_string($value))
				{
					$source[$key] = $this->quoteSmart($this->decode($value), $connection);
				}
			}
			return $source;
			 
		} else
			if (is_string($source))
			{
				 
				if (is_string($source))
				{
					return $this->quoteSmart($this->decode($source), $connection);
				}
				 
			} else
			{
				return $source;
			}
	}

	 
	function quoteSmart($source, & $connection)
	{
		 
		if (get_magic_quotes_gpc())
		{
			$source = stripslashes($source);
		}

		 
		$source = $this->escapeString($source, $connection);
		return $source;
	}

	 
	function escapeString($string, & $connection) {
		 
		if (version_compare(phpversion(), '4.3.0', '<')) {
			$string = mysql_escape_string($string);
		} else 	{
			$string = mysql_real_escape_string($string);
		}

		return $string;
	}
}
shyam

shyam
answered Nov 30 '-1 00:00

there are many way to deal with MySQL / SQL Injection

1. Filter : sanitize input or other word we can say make clean input from the bad word and blacklisted text , and make it safe.

2. Escaping :Escape the special character( like "(double quote) '(single Quote) ) which actually responsible for breaking SQL statement , special character Escaping technique depend on the SQL server Type.

3. Encoding : Encode the input value to some other safe Format.
Suppose if you do base64 encoding for all input . never you see SQL injection (it just example not preferred way )

4. Prepared Statement : in place of passing value direct to the SQL statement . pass it through by Api or function which take input value as argument and append it to the SQL safely.
Suppose . PDO - PHP Data Objects or Mysqli ((MySQL Improved Extension) is wrapper which can help to pass input value by Prepared Statement.
Mysqli Code :

$db = new mysqli($db_host, $db_user, $db_pass, $db_name, $db_port);
$stmt = $db->prepare('insert into Product(name, description) values (?, ?)');
$stmt->bind_param('%s', $_REQUEST['product_name'], $_REQUEST['product_description']);
$stmt->execute();
$stmt->close();
$db->close();

PDO code ::

try {
    $db= new PDO($dsn, $db_user, $db_pass);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}
$sth = $db->prepare(('insert into Product(name, description) values (?, ?)');
$sth->execute(array($_REQUEST['product_name'], $_REQUEST['product_description']));
Post Answer