How to sanitize input for MySQL using PHP?

need an explanation for this answer? contact us directly to get an explanation for this answer

SQL Injection 1=1

Suppose there is a table in the database name 'company' and 'cmp_name' one of its fields. At the front end, there are some search modules that select company information on the basis of company name. In the controller, for the most part we compose the query to fetch the searched company name as-

$query = "SELECT * FROM company WHERE cmp_name = '$cmpname' ";

Suppose the attacker goes to this search module in the front end and instead of the company name, he has given the below code in the company name variable as -

 OR '1' = '1'

At this point the select query becomes -

$query = "SELECT * FROM company WHERE cmp_name = '$cmpname' OR '1' = '1' ";

AS '1' = '1' condition is always evaluated to be true and executed, fetching all the data from the company table. By this way, the attacker can fetch all the company data. Therefore, to protect the database from attackers, it is important to filter and sanitize the client entered information prior to sending it to the database.

PHP provides different variables for sanitizing data. For example, passing in FILTER_SANITIZE_EMAIL will remove characters that are inappropriate for an email address to contain. That said, it does not validate the data. These are some examples of data sanitised variables-

PHP Sanitize Email

The PHP variable FILTER_SANITIZE_EMAIL is used to sanitize the email. It removes all illegal characters except letters, digits and !#$%&'*+-=?^_`{|}~@.[] and then checks whether the format is valid or not.

Example -

<?php

$email = "developer@domain.com"; 
  
// sanitizing the email 
$email = filter_var($email , FILTER_SANITIZE_EMAIL); 
  
// validating email 
if (!filter_var($email , FILTER_VALIDATE_EMAIL) === false) { 
    echo("$email is valid"); 
} else { 
    echo("$email is invalid"); 
} 
?>

Output of the above code - 

developer@domain.com is valid

As, you can see in the above example, email is stored in the $email variable and it is sanitized using filter_var() to remove any illegal characters. After this process, the given email is validated.

PHP Sanitize String

The PHP variable FILTER_SANITIZE_STRING is used to sanitize the string. It strips all the HTML tags detected from a string.

<?php

$str= "<h2>Welcome to ETUTORIALSPOINT</h2>"; 
$str_new= filter_var($str, FILTER_SANITIZE_STRING); 
echo $str_new; 

?>

Output of the above code - 

Welcome to ETUTORIALSPOINT

In the given example, the variable $str contains a string. This string is sanitized using the string filter FILTER_SANITIZE_STRING to strip all the HTML tags. After this process, the given string is validated.

PHP Sanitize URL

The PHP constant FILTER_SANITIZE_URL removes all characters except letters, digits and $-_.+!*'(),{}|\\^~[]`<>#%";/?:@&= from the URL string and then check whether the format is valid or not.

<?php

$url = "https://www.etutorialspoint.com"; 
  
//url sanitizer 
$url = filter_var($url, FILTER_SANITIZE_URL); 
  
//url validator 
if (!filter_var($url, FILTER_VALIDATE_URL) === false) { 
    echo("$url is valid"); 
} else { 
    echo("$url is invalid"); 
} 

?>

Output of the above code - 

https://www.etutorialspoint.com is valid

PHP Sanitize Input

The PHP FILTER_SANITIZE_ENCODED constant is used to remove or encode special characters in a URL.

<?php
   $url="www.etutorialspointÅÅ.com";
   $url = filter_var($url, FILTER_SANITIZE_ENCODED, FILTER_FLAG_STRIP_HIGH);
   echo $url;
?>

Output of the above code - 

www.etutorialspoint.com

PHP Sanitize Number Input

The PHP FILTER_SANITIZE_NUMBER_INT constant removes all characters except digits, plus and minus signs.

<?php
  $number="2-5+1qf";

  var_dump(filter_var($number, FILTER_SANITIZE_NUMBER_INT));
?>

Output of the above code - 

E:\wamp\www\test\index.php:4:string '2-5+1' (length=5)

need an explanation for this answer? contact us directly to get an explanation for this answer