mysql_real_escape_string vs htmlspecialchars

Posted on 2009-02-12
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
    LVL 3

    Accepted Solution

    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.
    LVL 3

    Assisted Solution

    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 107

    Assisted Solution

    by:Ray Paseur
    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.

    HTH, ~Ray

    LVL 6

    Assisted Solution

    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

    Thanks for all the wonderful information :)

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    HOW TO REMOTELY CLEAN MEROND.O WITH ESET SILENTLY PROBLEM       If you have the fortunate luck to contract the Merond.O virus on your network, it can be quite troublesome to remove as it propagates to network shares on your network. In my case, the …
    Creating and Managing Databases with phpMyAdmin in cPanel.
    Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
    Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

    729 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now