如何在MySql中转义撇号(')?

2021-11-20 00:00:00 escaping mysql

MySQL 文档说它应该是 \'.但是,scite 和 mysql 都显示 '' 有效.我看到了,它的工作原理.我该怎么办?

解决方案

您引用的 MySQL 文档实际上比您提到的要多一些.它还说,

<块引用>

在用'"引用的字符串中的'"可以写成''".

(另外,您链接到 MySQL 5.0 版本的表 8.1.特殊字符转义序列,当前版本是 5.6 — 但当前的 表 8.1.特殊字符转义序列看起来非常相似.)

我认为 Postgres 注释backslash_quote (string) 参数 提供信息:

<块引用>

这控制引号是否可以在字符串文字中用 \' 表示.表示引号的首选 SQL 标准方式是将引号加倍 (''),但 PostgreSQL 历来也接受 \'.但是,使用 \' 会带来安全风险...

这对我说,与使用反斜杠转义单引号相比,使用双引号字符是更好的整体和长期选择.

现在,如果您还想在等式中添加语言选择、​​SQL 数据库及其非标准特性的选择以及查询框架的选择,那么您最终可能会有不同的选择.你没有提供太多关于你的约束的信息.

The MySQL documentation says that it should be \'. However, both scite and mysql shows that '' works. I saw that and it works. What should I do?

解决方案

The MySQL documentation you cite actually says a little bit more than you mention. It also says,

A "'" inside a string quoted with "'" may be written as "''".

(Also, you linked to the MySQL 5.0 version of Table 8.1. Special Character Escape Sequences, and the current version is 5.6 — but the current Table 8.1. Special Character Escape Sequences looks pretty similar.)

I think the Postgres note on the backslash_quote (string) parameter is informative:

This controls whether a quote mark can be represented by \' in a string literal. The preferred, SQL-standard way to represent a quote mark is by doubling it ('') but PostgreSQL has historically also accepted \'. However, use of \' creates security risks...

That says to me that using a doubled single-quote character is a better overall and long-term choice than using a backslash to escape the single-quote.

Now if you also want to add choice of language, choice of SQL database and its non-standard quirks, and choice of query framework to the equation, then you might end up with a different choice. You don't give much information about your constraints.

相关文章