Sanitize data and prevent SQL injections in PHP

Why Data Sanitization Matters: Prevent SQL Injections and XSS Attacks in PHP

The reason for adding this post is that sometimes, when I code in PHP, I forget that data has to be sanitized before execution to prevent SQL or XSS attacks. The simplest way to make SQL injection difficult is to use either MySQLi or PDO prepared statements, as they keep the SQL queries and data inputs wholly separated. You will find an example of each under SQL statements below.

Prepared statements and sanitizing functions I use

However, if you need to use data sanitization in regular PHP statements, here are some effective methods that I use:

1. Prepared Statements with MySQLi: Use MySQLi prepared statements to ensure that SQL queries and data are processed separately, reducing the risk of SQL injection attacks.
2. Prepared Statements with PDO: Similar to MySQLi, PDO (PHP Data Objects) also supports prepared statements, providing a secure way to handle SQL queries.
3. Sanitizing Data in PHP:
   • Use functions such as htmlspecialchars() and strip_tags() to sanitize user inputs and prevent XSS attacks.
   • Validate and sanitize data before using it in SQL queries or displaying it on a web page.

By following these practices, you can significantly reduce the risk of SQL and XSS attacks in your PHP applications, ensuring that your data is handled securely.

Methods

Integer values:

// To SANITIZE Integer value use
$var=(filter_var($var, FILTER_SANITIZE_NUMBER_INT));

//example:
$theNumber="983928/2ddo@3233'0 or 1 '%^33)_23@''''$9123!@~#";
$theNumber=(filter_var($theNumber, FILTER_SANITIZE_NUMBER_INT));
echo $theNumber; 
//cleaned out put will be: 983928232330133239123

Email values:

//To SANITIZE email query value use
$var=(filter_var($var,  FILTER_SANITIZE_EMAIL));

//example:
$theEmail="warith@d\igi7/7.com";
$theEmail=(filter_var($theEmail,  FILTER_SANITIZE_EMAIL));
echo $theEmail;
//cleaned out put will be: warith@digi77.com;

String values:

//To SANITIZE String value use
function StringInputCleaner($data)
{
	//remove space before and after
	$data = trim($data); 
	//remove slashes
	$data = stripslashes($data); 
	$data=(filter_var($data, FILTER_SANITIZE_STRING));
	return $data;
}	
//example:
$myString="Welcome  here"; ;
$myString=StringInputCleaner($myString);
echo $myString;

FILTER_SANITIZE_STRING is deprecated since PHP 8.1 and the stripslashes() step was only for magic quotes, so on PHP 8 use this cleaner, which escapes the text for HTML output:

// FILTER_SANITIZE_STRING is deprecated since PHP 8.1: escape text when you output it instead
function StringInputCleaner($data)
{
	return htmlspecialchars(trim($data), ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8');
}
//example:
$myString = "Welcome <b>here</b>";
echo StringInputCleaner($myString); // Welcome &lt;b&gt;here&lt;/b&gt;

SQL statements:

//To SANITIZE Sql query value use
function mysqlCleaner($data)
{
	$data= mysql_real_escape_string($data);
	$data= stripslashes($data);
	return $data;
	//or in one line code 
	//return(stripslashes(mysql_real_escape_string($data)));
}	

//example:
$insert="delete from vbtube_tubes WHERE tubeid =$row5[0]";
$insert= mysqlCleaner($insert);
mysql_query($insert);

The mysql_* functions above were removed in PHP 7.0, and escaping a whole query then calling stripslashes() undoes the escaping, so use a prepared statement instead (PDO or MySQLi):

// The mysql_* functions were removed in PHP 7.0: bind the value with a prepared statement instead

// PDO
$pdo = new PDO('mysql:host=localhost;dbname=vbtube;charset=utf8mb4', $dbUser, $dbPass, [
	PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);
$stmt = $pdo->prepare('DELETE FROM vbtube_tubes WHERE tubeid = ?');
$stmt->execute([(int) $row5[0]]);

// MySQLi
$db = new mysqli('localhost', $dbUser, $dbPass, 'vbtube');
$stmt = $db->prepare('DELETE FROM vbtube_tubes WHERE tubeid = ?');
$tubeId = (int) $row5[0];
$stmt->bind_param('i', $tubeId);
$stmt->execute();

Quick reference

mysql_real_escape_string //---> used when inserting into database.

htmlentities() //---> used when outputing data into web page.

htmlspecialchars() //---> used if u want to display the html tags and not execute them.

strip_tags() //---> used when remove html tags.

addslashes() //---> used when u need to add extra front slash for every back slash.

stripslashes() //---> remove slashes.

More sanitizing functions

FILTER_SANITIZE_NUMBER_FLOAT

FILTER_SANITIZE_SPECIAL_CHARS

FILTER_SANITIZE_STRING

FILTER_SANITIZE_URL

FILTER_SANITIZE_ENCODED