php mysql escaping characters in INSERT statement

Question

When data is entered into my web forms I use mysql_real_escape_string() to escape the bad stuff and store it in MySQL using PHP. I now setup a script to create a backup SQL file in case I have to reload the data.

Problem: the extra strings in things like "...Joe\\'s trucking..." in the insert statements are causing my code below to fail. Is there a better way to automate a backup and restore for mysql? Or maybe how do I fix the data that is causing the restore to fail at that point?

$mysqli_link = mysqli_connect("localhost","xxxx","xxxxxx","xxxxxxx");

/* check connection */
if (mysqli_connect_errno()) {
   printf("Connect failed: %s\n", mysqli_connect_error());
   exit();
}

$query =".......
LOCK TABLES `tbl_serv_prov` WRITE;
/*!40000 ALTER TABLE `tbl_serv_prov` DISABLE KEYS */;
$select =" INSERT INTO `tbl_serv_prov` VALUES (17,7,'2013-06-15 04:45:22','Joe\\\'s Trucking','----','N')";
/*!40000 ALTER TABLE `tbl_serv_prov` ENABLE KEYS */;
UNLOCK TABLES;......";


/* execute multi query */
if (mysqli_multi_query($mysqli_link, $query)) {
   do {
       /* store first result set */
       if ($result = mysqli_store_result($mysqli_link)) {
           //do nothing since there's nothing to handle
           mysqli_free_result($result);
       }
       /* print divider */
       if (mysqli_more_results($mysqli_link)) {
           //I just kept this since it seems useful
           //try removing and see for yourself
       }
   } while (mysqli_next_result($mysqli_link));
}

Use Fred's example, strip the slashes when migrating or displaying the data and escape it when going back in. — Rottingham
– Rottingham, Commented Sep 12, 2013 at 18:11
Are you set on doing this via PHP? If you have server access mysqldump will allow you to export your data and send it to a local file on the server. — Chris Rasco
– Chris Rasco, Commented Sep 12, 2013 at 18:11
Fred. This makes a lot of sense, but if I take these out wouldn't that then make the ' a string terminator instead of a regular apostrophe? — sweaty
– sweaty, Commented Sep 12, 2013 at 18:19
maybe the bigger problem is why my code is putting data like "6 O\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\'CLOCK" in. I think it is because I"m escaping characters, but that seems like an excessive amount of \ — sweaty
– sweaty, Commented Sep 12, 2013 at 18:20

Vlad · Accepted Answer · 2013-09-12 18:26:40Z

2

It would be easier to let MySQL do the work...

shell_exec("mysqldump -hHOST -uUSERNAME -pPASSWORD DBNAME TABLE1 TABLE2 TABLE3  > db_backup.sql");

You can set your filenames to have a date if you want to keep multiple copies.

Edit (to load file):

shell_exec("mysql -hHOST -uUSERNAME -pPASSWORD DBNAME < filename");

edited Sep 12, 2013 at 18:26

answered Sep 12, 2013 at 18:20

Vlad

9786 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

sweaty Over a year ago

Vlad I already have that. Now I'm just wondering how to get that darn file sql file back loaded into the mysql database?

sweaty Over a year ago

Vlad. Perfect. Thanks, sorry this was one of those questions I missed the easiest answer

Collectives™ on Stack Overflow

php mysql escaping characters in INSERT statement

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related