CODEWITHSUNDEEP
phpmysql
sweaty
sweaty

Reputation: 389

php mysql escaping characters in INSERT statement

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));
}

Upvotes: 0

Views: 338

Answers (1)

Vlad
Vlad

Reputation: 978

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");

Upvotes: 2

Related Questions