MySQL insert data containing single and double quotes giving a syntax error
Solution 1
Update: Do it with the QUOTE()
function.
Original answer:
Please try this:
INSERT INTO
...
VALUES (
...
, REPLACE(@Data.pluginText, '"', '\"')
)
or if you have single and double quotes in it:
INSERT INTO
...
VALUES (
...
, REPLACE(REPLACE(@Data.pluginText, '"', '\"'), "'", "\'")
)
You can read more about it here
Solution 2
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.
Ben
Security/Networking Analyst, amateur self-teaching (and with the help of this forum) MYSQL, SQL Server & SQLite. Have taken courses in Java and Python but it hasn't stuck yet.
Updated on April 22, 2020Comments
-
Ben about 4 years
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
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?
Is mysql_real_escape_string what I need to do this properly?