Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 934
Ben Avatar asked May 08 '12 14:05

Ben


2 Answers

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

like image 151
fancyPants Avatar answered Oct 03 '22 08:10

fancyPants


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.

like image 26
user5629388 Avatar answered Oct 03 '22 06:10

user5629388