When are the most recommended times to use mysql_real_escape_string()

Question

Hi all, Im actually new to using this function.. and was using preg_replace and addslashes previous to finding it.

I'm mostly curious, because Im about to go through, and tighten security in the posting areas in my first large app, and wanted to know the best instances where this function is effective, and highly recommended. I've seen this function applied in a few different situations, and not just before user input is posted.. but when queries are done in general, so Im really curious about its full possibilities, and how to implement it to its full effectiveness.

Also, any infallible security methods, and suggestions in general will be really appreciated.

Cheers all!

Answer 1

Pretty much any time there could be characters in the data that could mess up a query, most notably quote and single quote. There are some extended characters that are dependent on your current MySQL character encoding that could do it too, which is why you want to use the one in mysql instead of addslashes().

Answer 2

Ideally you should never be using it, because Parameterized queries (either through PDO or mysqli) are the correct way to prevent SQL injection.

Answer 3

You should probably use mysqli_real_escape_string or PDO with bound parameters instead. The main use for any of them is to escape characters like single and double quotes. This is generally to prevent SQL injection.

Answer 4

The most recommended times to use mysql_real_escape_string(): Whenever you are putting data into the database. If you treat the input from anywhere (in from a webpage, in from the database, in from a webservice) as a hostile attack that you have to defend and filter against problems, then you won't go far wrong

Do it the hard way the first few times, then use a framework (I use ZendFramework), or at least a part of it like Zend_DB, to make it easier on yourself.

For an infallible security method - a server that cannot be broken into:

1. disconnect the machine from the network
2. disconnect the machine from power
3. put the machine into a safe
4. drop it into the Marianas trench
5. post a guard.

Note: it's usefulness is not guaranteed at this point. it's very secure though. Not 100%, but as good as it's gonna get.

And keep learning about security and best practices.

Answer 5

Please see this response to a similar questions a little while ago.

Basically, there is much more to security than just avoiding SQL Injection attacks. Also, anytime you run a query against the database that contains any sort of dynamic data there is a danger of sql injection. Lastly, it is better to use Prepared Statements with the PDO library than to use mysql_real_escape_string().

1) http://stackoverflow.com/questions/585358/security-of-striptags-and-mysqlirealescapestring/585568#585568

Answer 6

The best thing is to parse the content as data, not as code (never execute the code). For example, JSON (Javascript) is a very easy way to assemble data from a server at a client, but you should never execute it. You should parse the data with a Regular Expression to confirm it is correct and only contains data.

That said, SQL injection usually happens when you accept form data in html. The key would be to check for SQL reserved words that cause a problem, namely '; (single quote, semi-colon.) You could easily create a list of reserved words and run them through a RegEx expression (jQuery probably has something nice or C# on the server.) I am sure PHP could do this easily too. CRUD (create retrieve update and delete) verbs are nice to look for, but don't forget about the server side SQL stored procedures that already exist to do things or worse execute sql statements (dynamic sql). You could url encode the "escape" characters if you don't need to use them outside of a web page too.

Answer 7

mysql_real_escape_string(magic_quotes_gpc() ? strip_slashes($variable) : $variable)

If you insist on not using mysqli or PDO, then always.

Answer 8

where i use it: i use a database layer in my sites, and that layer goes through all query input and escapes it. that way, input that is not sent to the database is not escaped, but everything that does is escaped when send sent to the database. it also allows much more flexibility when transferring between databases. so the rule of thumb i suggest- use all mysql function with a database wrapper. it makes them be used at the right time, and your app won't be crashing when you switch