将双引号和其他字符插入 MySQL 数据库时，何时需要转义它们? [英] When is it necessary to escape double quotes and other characters when inserting them into MySQL database?

问题描述

在 MySQL 中插入文本字段时，何时需要转义双引号和/或特殊字符?

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?

推荐答案

MySQL(非标准)允许您使用双引号作为字符串文字的分隔符:

MySQL (nonstandardly) allows you to use double-quotes as delimiters of string literals:

SELECT * FROM Accounts WHERE first_name = "Mel"

如果您将内容插入到 SQL 字符串文字中，并且您的内容包含双引号，则会遇到麻烦:

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"

如果攻击者声称他们的帐户名为 Mel" OR "X"="X 并且这称为 SQL 注入，则可能会发生这种情况.

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"

但是，使用查询参数更简单，因此您可以确保内容与 SQL 代码分开，并且永远不会导致意外的表达式:

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 = ?

参数允许您使用占位符准备查询，然后在执行时为每个占位符提供动态内容.例如在带有 PDO 的 PHP 中:

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") );

查看我的演示文稿 SQL 注入误区和谬误更多信息.

See my presentation SQL Injection Myths and Fallacies for lots more information.

这篇关于将双引号和其他字符插入 MySQL 数据库时，何时需要转义它们?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！