Search code examples
mysqlinsertquotes

MySQL insert data containing single and double quotes giving a syntax error

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?

Solution

  • 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