PHP sanitize input for MySQL

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

Data Sanitization is a vital piece of web improvement, particularly while working with a form where the client first enters their own information and afterward sends that to the database. The code injection is one of the most oldest code infusion strategy which attacker generally used 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 becomes the common problem for exploiting web applications. By utilizing this the attacker can disregard transactions, they can turn into an administrator of database, or they can likewise impact our bank balance. Before prevention techniques, first, let's know how the attacker attempts on database.

SQL Injection 1=1

Suppose, there is a table in database name 'company' and 'cmp_name' is one of it's field. In the front end, there is some search module 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 front end and instead of company name, he has given the below code in 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 always evaluates to true and executed and fetch all the data from 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 the some examples of data sanitize 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 !#$%&'*+-=?^_`{|}~@.[].

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 
if (!filter_var($email , FILTER_VALIDATE_EMAIL) === false) { 
    echo("$email is valid"); 
} else { 
    echo("$email is invalid"); 
} ?>

PHP Sanitize String

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

<?php

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

?>

PHP Sanitize URL

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

<?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"); 
} 
?>

PHP Sanitize Input

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

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

PHP Sanitize Number Input

The PHP FILTER_SANITIZE_NUMBER_INT constant remove all characters except digits, plus and minus sign.

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

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

Related Articles

JavaScript display PDF in the browser using Ajax call
How to display PDF file in PHP from database
How to read CSV file in PHP and store in MySQL
Create And Download Word Document in PHP
PHP SplFileObject Standard Library
Simple File Upload Script in PHP
Sending form data to an email using PHP
Recover forgot password using PHP and MySQL
Php file based authentication
Simple PHP File Cache
How to get current directory, filename and code line number in PHP