PHP sanitize input for MySQL

In this article, you will learn how to sanitize user input for MySQL using the PHP programming language.

Data Sanitization is a vital piece of web improvement, particularly while working with a form where the client first enters their own information and afterwards sends that to the database. Code injection is one of the oldest code infusion strategies which attackers generally use to abuse web applications. Like, if an attacker is able to embed some vulnerable query as input, then that input may get some significant information from your database or erase some data or might be able to erase the entire database. This has become a common problem for exploiting web applications. By utilizing this the attacker can disregard transactions, they can turn into an administrator of the database, or they can likewise impact our bank balance. Before preventing techniques, let's know how the attacker attempts on the database.

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 = "This email address is being protected from spambots. You need JavaScript enabled to view it."; 
  
// 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-

This email address is being protected from spambots. You need JavaScript enabled to view it. 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)

How to create search filter in PHP
PHP Server Side Form Validation
PHP File Upload MIME Type Validation
Complete HTML Form Validation in PHP
File Upload Validation in PHP
PHP SplFileObject Standard Library
Simple File Upload Script in PHP
Sending form data to an email using PHP
PHP secure random password generator
Php file based authentication
Simple PHP File Cache
How to get current directory, filename and code line number in PHP
PHP program to reverse a string
Insert in database without page refresh PHP
PHP remove last character from string
PHP String Contains
PHP Fix: invalid argument supplied for foreach
Ajax live data search using jQuery PHP MySQL