MySQL MySQL插入包含单引号和双引号的数据给出语法错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10500880/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL insert data containing single and double quotes giving a syntax error
提问by Ben
I am using the following insert statement within a tool pulling data from one DB into another one.
我在一个工具中使用以下插入语句，将数据从一个数据库提取到另一个数据库中。
INSERT INTO act_vulnerabilities_internal_test (device_type, ip_address, user_tag,
repositoryID, severity, pluginID, pluginName, pluginText)
VALUES ("@Data.device_type~", "@Data.ip_address~", "@Data.user_tag~",
"@Data.repositoryID~", "@Data.severity~", "@Data.pluginID~", "@Data.pluginName~",
@Data.pluginText~)
Error message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\nSynopsis :\n\nIt is possible to retrieve file backups from the remote web serv' at line 3
错误消息：您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，以了解在“\nSynopsis :\n\nIt is possible to retrieve file backups from the remote web serv”（第 3 行）附近的正确语法
The data I am trying to pull from one of the columns has a lot of single and double quotes in it (this is pulling from a proprietary tool and I cannot edit the data). The column giving me the problems is the one named pluginText. Is there a way to make the db ignore the ' and " contained within the rows?
我试图从其中一列中提取的数据中有很多单引号和双引号（这是从专有工具中提取的，我无法编辑数据）。给我带来问题的那一栏是 pluginText。有没有办法让数据库忽略行中包含的 ' 和 "？
Is mysql_real_escape_string what I need to do this properly?
mysql_real_escape_string 是我需要正确执行此操作的吗？
采纳答案by fancyPants
Update: Do it with the QUOTE()function.
更新：用QUOTE()函数来做。
Original answer:
原答案：
Please try this:
请试试这个：
INSERT INTO
...
VALUES (
...
, REPLACE(@Data.pluginText, '"', '\"')
)
or if you have single anddouble quotes in it:
或者如果你有单引号和双引号：
INSERT INTO
...
VALUES (
...
, REPLACE(REPLACE(@Data.pluginText, '"', '\"'), "'", "\'")
)
You can read more about it here
回答by user5629388
We need to use the addslashes($text)function to escape all single quotes and double quotes, NULL, etc. to make it understandable by MYSQL.
Here is some information on that.
我们需要使用addslashes($text)函数来转义所有的单引号和双引号、NULL等，让MYSQL可以理解。
这里有一些关于这方面的信息。
