Htmlentities vs addslashes vs mysqli_real_escape_string

Question

I've been doing some reading on securing PHP applications, and it seems to me that mysqli_real_escape_string is the correct function to use when inserting data into MySQL tables because addslashes can cause some weird things to happen for a smart attacker. Right?

However, there is one thing that is confusing me. I seem to remember being advised addslashes is better than htmlentities when echoing user-entered data back to users to protect their data, but it seems like addslashes is the one with the vulnerability. Is this true, or am I remembering incorrectly?

Answer 1

They are different tools for different purposes.

mysqli_real_escape_string makes data safe for inserting into MySQL (but parametrized queries are better).

Htmlentities makes data safe for outputting into an HTML document

addslashes makes data safe for a few other situations, but is insufficient for MySQL

Answer 2

There are different contexts for your data. The context of inserting data into the database needs to be escaped differently than the context of rendering html/xml or even an email message.

Escaping data going into a db should be deprecated in all new code in favor of prepared statements. Anyone who tells you otherwise is doing you a great disservice.

Escaping data going to the browser needs to be escaped in a number of different ways depending on the target. Sometimes htmlspecialchars is enough, sometimes you need to use htmlentities. Sometimes you need numeric entities. It is a topic you should do some research on to know all of the nuances.

The general rule I live by is validate (not filter, reject if incorrect) input & escape output (based on context).

Answer 3

You could also use the PDO libs which does most of the escaping for you, in case you may use PHP5 on the servers.

On echoing back I'd personally prefer htmlspecialchars, but one might correct me

Answer 4

yes, use the mysqli_real_escape_string or a library like PDO on all user input. When echoing back, I use htmlentities with ENT_QUOTES as the second parameter, as it escapes all applicable characters to their html entities, including quotes.

Answer 5

Note: Using htmlentities() in an UTF-8 encoded document should be avoided. See:

• Interfacing with Systems using other Charsets
• Common Problem Areas with UTF-8

Pay attention to (quoted from phpwact.org):

With modern web browsers and widespead support for UTF-8, you don’t need htmlentities because all of these characters can be represented directly in UTF-8. More importantly, in general, only browsers support HTML‘s special characters - a normal text editor, for example, is unaware of HTML entities. Depending on what you’re doing, using htmlentities may reduce the ability of other systems to “consume” your content.

Also (not confirmed but sounds reasonable - from anon comment here), character entities (stuff like » or —) do not work when a document is served as application/xml+xhtml (unless you define them). You can still get away with the numeric form though.

Answer 6

Another interesting solution for PHP 5.2 and above is to use the filter extension: http://www.php.net/manual/en/book.filter.php

It allows you to validate and sanitize user inputs. There are many built-in filters available and they can be combined with flags to tweak their behaviour. In addition hese filters can also be used to validate/sanitize ints, floats, emails, specific regular expressions.

I personally have started using them in my projects to validate forms and to output user-entered data, and I am very glad I did. Although, when I insert values in a MySQL database, I use prepared queries for added security. These solutions together can help avoid most SQL injections and XSS-type attacks.

Answer 7

You can't have one "escape" function and expect it to work all of the time. There are different attacks that require specific sanitation routines. The only way to understand this concept is to write some vulnerable code and then exploit it. Writing exploit code is vital to the understanding of any security system.

For instance this query is vulnerable to Sql injection:

$host=htmlspecialchars($_GET[host],ENT_QUOTES);
$name=htmlspecialchars($_GET[name],ENT_QUOTES);
mysql_query("select * from user where Host='$host' and Name='$name' ");

Exploit: http://localhost/sqli_test.php?host=\&name=%20sleep(20)--%201

The best escape function for mysql is mysqli_real_escape_string() but this can fail:

mysql_query("select * from user where id=".mysqli_real_escape_string($_GET[id]));

exploit: http://localhost/sqli_test.php?id=1%20or%20sleep(20)

In fact the best way to take care of sql injection isn't calling an escape function, Its using ADODB's parametrized quires for sql injection. Use htmlspecialcahrs($var,ENT_QUTOES) for XSS. Read the OWASP top 10 because there is a whole lot more than can go wrong with web application security.