c++ - MySQL QUOTE() vs mysql_real_escape_string()?

In MySQL, what are the differences between QUOTE() and mysql_real_escape_string()? From the MySQL documentation, I know the following:

QUOTE()

• Written into SQL query


• Escapes backslash, single quote, NUL, CTRL+Z



• Returns a single-quoted string


• Behavior relies on the MySQL server's character set

mysql_real_escape_string()

• Written in C/C++ before a query is executed, allowing the escaped string to be read/modified before submission


• Very inconvenient to use when compared to QUOTE()


• Escapes backslash, single quote, NUL, CTRL+Z, and double quote, \n, and \r



• Apparently adds more quotes to make characters easily readable in log files


• Behavior relies on the MySQL server's character set

Ignoring logs, is it useful to escape \n and \r characters? With these two functions, is there a difference in client/server function efficiency? mysql_real_escape_string() sounds useful if it's desirable for a developer to process the escaped string before it's entered into a query. However, does QUOTE() not provide the most secure and reliable method of escaping strings?

I wonder if I should use QUOTE() for all queries in all languages and forget escaping strings with language-specific functions.

---

Answer

It seems that QUOTE() is meant to be used within SQL statements that construct other SQL statements. If you are outside of SQL, you should use mysql_real_escape_string().

[...] In a C program, you can use the mysql_real_escape_string() C API function to escape characters. [...] Within SQL statements that construct other SQL statements, you can use the QUOTE() function.

As explained at the bottom of String Literals (MySQL Manual).