Skip to main content

Sanitize Text for SQL

Published on
Cross Site Scripting (XSS) and SQL Injections are some of the biggest security threats to a PHP application.  Every developer has different ways of preventing SQL Injections or malicious JavaScript from being inserted into the database. Here are some easy solutions to help safeguard your PHP applications.


mysql_real_escape_string() should always be used on data being placed into your SQL database.  The function places backslashes before certain characters. That means if someone tried to insert an apostrophe ( ' ) into your SQL a backslash would be added ( \' )  so the apostrophe would be recognized as a character, not part of the script.
$input = 'I'll be darned, this would have broke it.';
$data = mysql_real_escape_string($input);


htmlentities() is one of my best friends. I learned about XSS early when I was a developer when a hacker posted JavaScript into a field and was able to snatch my admin cookie and deface my site. See, mysql_real_escape_string() may prevent SQL Injections but not XSS. htmlentities() will convert characters to their html entities. This will turn < or > into &lt; or &gt; preventing JavaScript from being inserted and executed on viewing.
$input = '<script>javascript:alert("Alert");</script>'
$data = htmlentities($input);
//Returns &lt;script&gt;


At first I did not use htmlentities() but made my own function using str_replace() to replace characters to their HTML entities. I had one array containing the specific characters to be converted and the other array contained the HTML entities.  This can be safer as htmlentities() does not convert all characters to HTML entities.
$html = array("'", '"', '<', '>');
$entities = array(''', '"', '&lt;', '&gt;');
$data = str_replace($html, $entities, $input);


strtr() is essentially the same as str_replace() but is faster. Also, instead using two arrays you can use just one array that uses pairs .
$html = array('<' => '&lt;', '>' => '&gt;');
$data = strtr($input, $html);