[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


mysql_real_escape_string vs htmlspecialchars

Posted on 2009-02-12
Medium Priority
Last Modified: 2013-11-16
What is the difference between mysql_real_escape_string and htmlspecialchars?  I'm trying to make my site as secure as possible, but need someone to break that down a little for me!

Question by:privateland

Accepted Solution

blumi earned 800 total points
ID: 23627799
htmlspecialchars  Convert special characters to HTML entities

mysql_real_escape_string  Escapes special characters in a string for use in a SQL statement

general i would say that any string should go through mysql_real_escape_string. When you get the text out you have to use stripslashes()

if you don't want things like tags in a text field then use htmlspacialchars, strip_tags or both.

Assisted Solution

blumi earned 800 total points
ID: 23627828
It is more important that you use strip_tags() etc. for any output, if no tags wanted. I use a collection of regexp to check some fields in front of saving to database.

May useful
	 * @desc Contains an array with regexp to validate strings
	 * @param $parameter string 
	 * @param $type string 
	 * @return Boolean
	public static function ParamFilter($parameter, $type){
		$regexp = array(
			'files'				=> '/^[a-z0-9._\-\/]*$/i',
			'filename'			=> '/([[:alnum:]_\.-]*)/',
			'username'			=> '/^[a-z\d_]{4,28}$/i',
			'path'				=> '/^[a-z0-9_\/]*$/i',
			'string'			=> '/^[a-z0-9_\-]*$/i',
			'table'				=> '/^[0-9]*$/i',
			'hash'				=> '/^[a-z0-9]{32}$/i',
			'email'				=> '/^[A-Z0-9._%-]+@[A-Z0-9.-]+.[A-Z]{2,4}$/i',
			'spam'				=> '/(\n+|\r+|%0A|%0D)/i',
			'spam_mulitline'	=> '/(%0A|%0D|\n+|\r+)(content-type:|to:|cc:|bcc:)/i',
			'suffix'			=> '/(.doc|.docx|.pdf|.odt|.txt|.ods|.xsl)/i',
			'phone'				=> '/^[0-9\+]*$/i',
		if(@preg_match($regexp[$type], (string)$parameter)){
			return TRUE;	
		return FALSE;

Open in new window

LVL 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 600 total points
ID: 23627915
In my experience, you should use mysql_real_escape_string() on any field that is to be put into the MySQL data base.  This is a little bit "smarter" than addslashes, as a look into the man pages will show you.

I do not strip the tags from my input if I want to keep code in the data base.  I just escape it and put it in.  However if I want to present it to the client machine, I use htmlentities when I echo it out.  The thinking about this is that sometimes I may want to store executable code in the data base, so I do not want to make a practice of munging the code that I put in there.

For many fields, where I know I only want text or numbers, I will set up a function to eliminate the unacceptable characters - something like ereg_replace('^0-9', '') to eliminate non-numbers, for example.

You do not need to unescape information that is brought out of the data base.  MySQL does not store the escape strings.

Beware of magic_quotes.  Like register_globals, it is NOT your friend.

For the latest and most definitive information on app security, follow the work of Chris Shiflett.  He wrote the book, literally, on the subject.  http://shiflett.org

HTH, ~Ray


Assisted Solution

digital0iced0 earned 600 total points
ID: 23628568
Just use both.. they do different things.  You need htmlspecialchars to prevent cross site scripting attacks, and you need mysql_real_escape_string to stop sql injections.  The problem is that sql injections are not just about ' and " there are other characters that cause problems.  Just run the data through both before inserting it in the database and that way you wont have malicious code in your database.  The only drawback is that it will take a small amount more of space but in my opinion its worth it.  The problem with just using  htmlentities when you output the data is that the malicious code is in your database already.  So if you ever forget to run it or you use phpmyadmin or something like that then the malicious attack will run.

Author Comment

ID: 23632619
Thanks for all the wonderful information :)

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An introduction to the wonderful sport of Scam Baiting.  Learn how to help fight scammers by beating them at their own game. This great pass time helps the world, while providing an endless source of entertainment. Enjoy!
In this article, we’ll look at how to deploy ProxySQL.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question