Right method for escaping MySQL injections and filtering out XSS attack attempts

Question

I need to check something here, I know with some code they filter out AS the input is obtained in the one single line of code, here I have done it AFTER obtaining the code, in a sequential order, is this also acceptable? or do I have to figure out someway of filtering and escaping the data in the one line whilst at the same time obtaining the data? Here's a sample of what Im sort of talking about...

  // Get data and prevent XSS attack
  $user = htmlentities($_POST['email'], ENT_QUOTES, 'UTF-8');
  $pass = htmlentities($_POST['pass'], ENT_QUOTES, 'UTF-8');

  // MySQL Injection prevention
  $userdata = mysql_real_escape_string($user);
  $passdata = mysql_real_escape_string($pass);

Thoughts?

Key objective I'm trying to achieve here is to escape a MySQL injection attempt AND prevent an XSS attack

score 2 · Answer 1 · answered Jul 10 '12 at 08:13

2

Key objective I'm trying to achieve here is to escape a MySQL injection attempt AND prevent an XSS attack

You can't do both of those at the same time.

SQL-escaping needs to happen at the point you create SQL queries including text strings. Although you are better off using parameterised queries (eg mysqli or PDO), in order not to have to worry about it.

HTML-escaping needs to happen at the point you create HTML markup including text strings. Although in an ideal world you'd be using a templating language that HTML-escaped by default, so you didn't have to worry about it.

If you apply both HTML-escaping and SQL-escaping at the input stage instead of their respective output stages, you'll get HTML-encoded data in your database that you won't be able to apply consistent text handling to (search, substrings, etc), and you'll get SQL-encoded data spat out onto the page where the value hasn't gone through a database I/O cycle (the cause of the O\\\\\\\\'Reilly problem. Plus you will still be at risk from any data that hasn't gone through the input path - for example fetch a string from the database, process it and return it to the database, and it'll not have had an escaping step and you're vulnerable to SQL injection again.

Neither escaping scheme is suitable to blanket-apply to input. Input filtering should only be about blocking characters you never want to handle and enforcing business rules. Do output escaping only at the moment you move text content into a new context - and wherever possible use frameworks that prevent you from having to manually escape at this point.

answered Jul 10 '12 at 08:13

bobince

528,062
107
651
834

So in a nut shell I should be filtering my MySQL injection attacks when obtaining input from say $_POST for example and only escaping XSS attacks when I print or output stuff so that the browser doesnt interpret it as code yeah? Or have I understood that wrong? – Kalcoder Jul 10 '12 at 12:43
Almost, but you should do the SQL escaping when creating SQL queries only. You shouldn't do any escaping in the input filtering step. – bobince Jul 10 '12 at 14:38
I'm curious, if we're talking about MySQL attacks here (not the XSS portion of defence) would it not be a better measure to prevent such malicious code being sent via input in the first place? Isnt that how MySQL injections occur in the first place, by writing something into an input form or the like to say, dump the entire MySQL table? – Kalcoder Jul 10 '12 at 14:59
Actually, more on this, did a bit more research into the whole XSS thing. Seems to be good practice to do more than just escape the output. Validate, sanitize the input for XSS and also for good measure escape the output. – Kalcoder Jul 10 '12 at 17:03
You can filter metacharacters at the input layer if your business rules say you don't need them. You'll usually find that you can't get away with filtering out all punctuation that might possibly be harmful, because people tend to have names like "O'Reilly" or might want to have a password with `<` in it. A filtering input validation layer is a good defence in depth measure, but it should be an addition to the correct handling of metacharacters when preparing queries/markup, not a replacement for that. (A escaping input validation layer, on the other hand, is just a mistake.) – bobince Jul 11 '12 at 15:03

score 1 · Accepted Answer · answered Jul 10 '12 at 04:42

It is not enough to use mysql_real_escape_string. There are certain situations where invalid multi-byte encodings can be exploited to inject SQL attacks (unlike with addslashes, this type of attack with mysql_real_escape_string can only happen if the character encoding is overridden in the connection string).

You should also use prepared statements when interacting with MySQL.

With regard to XSS, consider integrating HTML Purifier.

HTML Purifier is a standards-compliant HTML filter library written in PHP. HTML Purifier will not only remove all malicious code (better known as XSS) with a thoroughly audited, secure yet permissive whitelist, it will also make sure your documents are standards compliant, something only achievable with a comprehensive knowledge of W3C's specifications.

This is only possible because [you shouldn’t use `SET CHARACTER SET`](http://stackoverflow.com/a/5289141/53114). — Gumbo, Jul 10 '12 at 05:15
Yes. Unfortunately, one can. Looking through my IIS logs at the number of zombie-launched exploits targeting URL's ending in .php, I bet this happens *far* more often than one would hope. — Eric J., Jul 10 '12 at 06:10

score 0 · Answer 3 · answered Nov 10 '13 at 07:54

0

I will prefer using a function to pass all my string.

function safe($value){ 
return mysql_real_escape_string($value);
}

If i want to collect input i will do this:

$name=safe($_POST['name']);

answered Nov 10 '13 at 07:54

Anyadike Prince

19
3

Right method for escaping MySQL injections and filtering out XSS attack attempts

3 Answers3