When is it necessary to escape double quotes and/or special characters when inserting into a text field in MySQL?
Assuming you have a text field that holds descriptions or articles that have double quotes and/or ampersands, is it necessary to escape them before writing them to the database table?
You don't need to escape them before storing them in the database, but if you are writing code to process user input make sure you read about and fully understand the risks of SQL Injection vulnerabilities or you are in for a world of hurt.
MySQL (nonstandardly) allows you to use double-quotes as delimiters of string literals:
SELECT * FROM Accounts WHERE first_name = "Mel"
You would run into trouble if you interpolated content into your SQL string literal, and your content contained double-quotes:
SELECT * FROM Articles WHERE description = "She said, "Murder"!"
This can be a simple accident, and this probably just causes a syntax error. But attackers can also exploit this cleverly to make your queries do something you didn't intend.
UPDATE Accounts SET PASSWORD = "..." WHERE account_name = "Mel" OR "X"="X"
This could happen if the attacker claims their account name is Mel" OR "X"="X and this is called SQL Injection.
But if you escape the double-quotes in the content, you can defeat their mischief:
UPDATE Accounts SET PASSWORD = "..." WHERE account_name = "Mel\" OR \"X\"=\"X"
However, it's simpler to use query parameters, so you ensure that content is separate from SQL code, and can never result in unintended expressions:
UPDATE Accounts SET PASSWORD = ? WHERE account_name = ?
Parameters allow you to prepare a query with placeholders, and then provide dynamic content for each placeholder when you execute. For example in PHP with PDO:
$sql = "UPDATE Accounts SET PASSWORD = ? WHERE account_name = ?";
$stmt = $pdo->prepare($sql);
$stmt->execute( array("...", "Mel") );
See my presentation SQL Injection Myths and Fallacies for lots more information.
It would only be necessary to encode double quotes if you were adding a string into a string literal bounded by double-quotes. But, you shouldn't use double-quote-string-literals, because they're a weird non-standard MySQL syntax not supported by other databases.
Instead, use single-quote-string-literals (eg name='Mel'), which are ANSI standard SQL and work everywhere. And then of course you need to escape single quotes in the value, not double quotes.
The other main character you need to escape in MySQL string literals is \, because MySQL uses it as an escaping character. This is more non-standard behaviour and quite annoying.
Most webdev environments will give you an SQL-string-literal escaping function so you don't have to worry about which exact characters need escaping for inclusion in a query. For example, mysql_real_escape_string in PHP. However, as Bill mentioned, you are better off using parameterised queries where available, so you don't have to worry about SQL-escaping at all. Your text will jump straight into the database as raw strings without any intermediate escaping-unescaping step.
As for ampersands, they are not special in SQL string literals and should not be escaped when creating queries from strings. They're special in HTML, but you don't want HTML-encoded data in your database. Instead, use an HTML-escaping function (like htmlspecialchars in PHP) at the moment you insert a string into HTML content, and not before.
It is a common mistake to try to HTML-escape or ‘sanitise’ content in the input stage from a form submission or at the SQL-building stage. But this is an HTML output stage issue, which should only ever be tackled at the templating stage.