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 !#$%&'*+-=?^_`{|}~@.[] 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"); 
} 
?>