I know that mysql_escape_string is deprecated from 5.3 but what was the actual difference in mysql_real_escape_string.
What I thought was that mysql_real_escape_string is the exact same as mysql_escape_string apart from mysql_real_escape_string takes a second argument for the mysql resource.
So then I thought well surely there must be some difference as to how strings are handled because there would not be a need for 2 functions.
So then I thought that the difference was purely down to locale and character encodings?
The difference is that mysql_escape_string just treats the string as raw bytes, and adds escaping where it believes it's appropriate.
mysql_real_escape_string, on the other hand, uses the information about the character set used for the MySQL connection. This means the string is escaped while treating multi-byte characters properly; i.e., it won't insert escaping characters in the middle of a character. This is why you need a connection for mysql_real_escape_string; it's necessary in order to know how the string should be treated.
However, instead of escaping, it's a better idea to use parameterized queries from the MySQLi library; there has previously been bugs in the escaping routine, and it's possible that some could appear again. Parameterizing the query is much, much harder to mess up, so it's less likely that you can get compromised by a MySQL bug.