Jump to content

  • Log in with Facebook Log in with Twitter Log In with Google      Sign In   
  • Create Account

Subscribe to HRA Now!

 



Are you a Google Analytics enthusiast?

Share and download Custom Google Analytics Reports, dashboards and advanced segments--for FREE! 

 



 

 www.CustomReportSharing.com 

From the folks who brought you High Rankings!



Photo
- - - - -

How To Backup/restore Encypted Data From/to Mysql Using Php


  • Please log in to reply
13 replies to this topic

#1 rolf

rolf

    HR 6

  • Active Members
  • PipPipPipPipPipPip
  • 675 posts
  • Location:Suffolk UK

Posted 17 June 2009 - 08:36 AM

I've been working on a system using php/mysql to store/retrieve data, some of which is AES encrypted and it all works as expected.

I created a simple 'one click' backup/restore system that exports all the data in a given table into an array then writes a text file containing full mysql queries. To restore it just splits the text files into an array of queries and feeds them to mysql, but I'm having a problem because the encryption outputs problematic characters such as , ' " and | that need to be used as markup, and when I run the restore function these characters trip the whole thing up.

I've tried using addslashes() at the appropriate times, which solves some of the problems, but I'm still having a problem with | and , and I'm also not sure if a backslash might sometimes be used as an output character, potentially creating further problems.

Before getting too bogged down in this mire, I was wondering if anyone can suggest a better way of backing up/restoring, that can be set up to be left in the hands of the office staff for one click backup/restore. Unfortunately phpmyadmin is not an option as it would be too complicated for them to use.

The system it will ultimately be running on is winXP/Apache/php/mysql so the few linux based tutorials I've found aren't much help :-(

Thanks for any suggestions

#2 1dmf

1dmf

    Keep Asking, Keep Questioning, Keep Learning

  • Active Members
  • PipPipPipPipPipPipPip
  • 2,167 posts
  • Location:Worthing - England

Posted 17 June 2009 - 09:29 AM

Well I'm not 100% but if you run myPHPadmin , I think there is a backup / export /restore / import functionality.

As for escaping the chars, you could simply store characters you want to exscape in a CSV string, split and to a global match replace to a bespoke marker which can be translated on restore.

Note this is PERL and a very rought of top of my head thing, so only a guide!
EG
CODE
my $SQL_STRING =  "what ever it contains";

my $exclude = "|=<pipe>,$=<dollar>,/=<fslash>,\\=<bslash>,'=<squote>,\"=<dquote>";

my @array = split(/,/,$exclude);

for(@array){

   my @rep = split(/=/,$_);

   $SQL_STRING =~ s/$rep[0]/$rep[1]/g;

}


then when you restore you simply use
CODE
   $SQL_STRING =~ s/$rep[1]/$rep[0]/g;


If you need anything explaining so you can apply PHP logic to the code, just ask smile.gif



#3 rolf

rolf

    HR 6

  • Active Members
  • PipPipPipPipPipPip
  • 675 posts
  • Location:Suffolk UK

Posted 17 June 2009 - 09:45 AM

QUOTE
Well I'm not 100% but if you run myPHPadmin , I think there is a backup / export /restore / import functionality


Unfortunately that's a little complicated for the people who are going to have to use it. I don't want to be mean or come across like one of those techie bigots, so I'll end that line of thought there...

As for the other suggestion - I can see the logic of the code and it certainly seems like good idea. If no-one suggests a different road to go down then that will be the next thing I try.

Cheers ale.gif

#4 1dmf

1dmf

    Keep Asking, Keep Questioning, Keep Learning

  • Active Members
  • PipPipPipPipPipPipPip
  • 2,167 posts
  • Location:Worthing - England

Posted 17 June 2009 - 10:10 AM

The only downside really is you are making one char into many , so if it was alot of data, you are bloating it slightly.

Depends on data size and overhead, how are you encrypting it?

#5 rolf

rolf

    HR 6

  • Active Members
  • PipPipPipPipPipPip
  • 675 posts
  • Location:Suffolk UK

Posted 17 June 2009 - 11:02 AM

using the aes_encrypt() and aes_decrypt() functions in mysql, so it's pretty simple and small amounts of data too so I don't think the overhead is anything to worry about really.

Shame cos it all seemed so simple and elegant 'till I hit this snag - shoulda known it never works out that way lol.gif

#6 1dmf

1dmf

    Keep Asking, Keep Questioning, Keep Learning

  • Active Members
  • PipPipPipPipPipPipPip
  • 2,167 posts
  • Location:Worthing - England

Posted 17 June 2009 - 11:10 AM

trying to deal with user input and every possible senario is always a nightmare, oh well , keeps you on ya toes and your brain engaged smile.gif

#7 rolf

rolf

    HR 6

  • Active Members
  • PipPipPipPipPipPip
  • 675 posts
  • Location:Suffolk UK

Posted 18 June 2009 - 04:34 AM

QUOTE
keeps you on ya toes and your brain engaged


sure does, and I suppose I have to admit it keeps the job interesting, even if it can be a little frustrating sometimes.

#8 rolf

rolf

    HR 6

  • Active Members
  • PipPipPipPipPipPip
  • 675 posts
  • Location:Suffolk UK

Posted 18 June 2009 - 12:23 PM

tried the suggestion about character replacement and it seems to work where I can identify the character, but there is one that is still giving me a problem.

This may be a little difficult to explain clearly, but I'll try.

The problem now only seems to occur when there is a character that causes a line break in the text file, for instance it shows up in the text file as follows -

("77","6","?H祙╒
樇w$%?,"45","34")

see how value 3 is split into 2 lines? whenever that happens there seems to be a problem with restoring, in that the syntax breaks and mysql wont accept the query. I'm assuming this is a character that means 'linebreak' in a text file, but I have no idea really, it's just a logical guess.

any ideas?

#9 rolf

rolf

    HR 6

  • Active Members
  • PipPipPipPipPipPip
  • 675 posts
  • Location:Suffolk UK

Posted 18 June 2009 - 12:35 PM

tell a lie, it's not working at other times too :'-(

I'm thinking that text files may just not be able to capture all the encrypted information/characters properly and I need to export to something else or in a different way? hmmm.... **rubs chin in ponderance**

#10 1dmf

1dmf

    Keep Asking, Keep Questioning, Keep Learning

  • Active Members
  • PipPipPipPipPipPipPip
  • 2,167 posts
  • Location:Worthing - England

Posted 22 June 2009 - 04:24 AM

Well to deal with newlines and CRLF (Cariage Return Line Feeds), in perl you use..

/r = return
/n = newline
/r/n = CRLF

Now in SQL I find they are CRLF so have to convert /r/n to just /n (/r/n shows as the square block)

To deal with them in PERL I use...
CODE
$address =~ s/\r\n/\n/g;


To deal with the Adress in one fa\cility I wrote I needed to export data to CSV and had the same problem with CRLF, I used the following...
CODE
        
#format address to CSV
        if($row->{'Address'}){
            $row->{'Address'} =~ s/(\s*\r\n)+/\r\n/g;        
            $row->{'Address'} =~ s/\s*,*\s*(\r\n)/,/g;
            $row->{'Address'} =~ s/,$//g;            
        }
        else{
            $row->{'Address'} = "";
        }


which translates to....
$row->{'Address'} =~ s/(\s*\r\n)+/\r\n/g; = replace any number of whitespace (\s*) chars then a CRLF 1 or more times (\r\n+) to CRLF.
$row->{'Address'} =~ s/\s*,*\s*(\r\n)/,/g; = then replace any number of whitespace chars, a comma, any number of whitespace chars, and a CRLF into just a comma.
$row->{'Address'} =~ s/,$//g; = then remove the extra comma from end of string.

That was cleansing a textarea input form field for the address.

So let me know the chars you're having problems with and I'll do what I can to help convert them wink1.gif

Flat text files shouldn't be an issue, it's how it all used to be stored in the good old days, it's just a matter of identifying problematic characters and correctly escaping them, plus putting them back the other way on restore of course

#11 rolf

rolf

    HR 6

  • Active Members
  • PipPipPipPipPipPip
  • 675 posts
  • Location:Suffolk UK

Posted 23 June 2009 - 08:20 AM

thanks, I was thinking maybe backing up encrypted data to a csv was a lost cause so its good to know that there is a way through this - it's certainly my preferred way if I can make it work.

I'm stuck dealing with another project for the next week or so, but I'll be coming back to this so I'll look further into it then and let you know.

Thanks again for the help.

#12 1dmf

1dmf

    Keep Asking, Keep Questioning, Keep Learning

  • Active Members
  • PipPipPipPipPipPipPip
  • 2,167 posts
  • Location:Worthing - England

Posted 23 June 2009 - 09:42 AM

no probs, If I can help I will smile.gif

though I made a complete hash of the post above, got my slashes the wrong way round...
QUOTE
/r = return
/n = newline
/r/n = CRLF

Now in SQL I find they are CRLF so have to convert /r/n to just /n (/r/n shows as the square block)


They should be back slashes \ NOT forward d'oh ohno.gif

#13 rolf

rolf

    HR 6

  • Active Members
  • PipPipPipPipPipPip
  • 675 posts
  • Location:Suffolk UK

Posted 09 July 2009 - 07:27 AM

Just a quick update on this. After reading through your suggestions and testing a few things out I made a couple of changes and carefully went through the code making sure character escapes were in place at all stages, and that all the code was solid.

I don't want to jump the gun but that seems to be catching all problematic characters from the testing I've done so far - although the real test will be when the system is in use by real people so I may ask for more help after all lol.gif

Anyway, just wanted to fill you in and say thanks.

Edited by rolf, 09 July 2009 - 09:35 AM.


#14 Nueromancer

Nueromancer

    HR 5

  • Active Members
  • PipPipPipPipPip
  • 301 posts
  • Location:Bedford Uk

Posted 09 July 2009 - 10:32 AM

QUOTE(rolf @ Jun 17 2009, 02:36 PM) View Post
I've been working on a system using php/mysql to store/retrieve data, some of which is AES encrypted and it all works as expected.

I created a simple 'one click' backup/restore system that exports all the data in a given table into an array then writes a text file containing full mysql queries. To restore it just splits the text files into an array of queries and feeds them to mysql, but I'm having a problem because the encryption outputs problematic characters such as , ' " and | that need to be used as markup, and when I run the restore function these characters trip the whole thing up.

I've tried using addslashes() at the appropriate times, which solves some of the problems, but I'm still having a problem with | and , and I'm also not sure if a backslash might sometimes be used as an output character, potentially creating further problems.

Before getting too bogged down in this mire, I was wondering if anyone can suggest a better way of backing up/restoring, that can be set up to be left in the hands of the office staff for one click backup/restore. Unfortunately phpmyadmin is not an option as it would be too complicated for them to use.

The system it will ultimately be running on is winXP/Apache/php/mysql so the few linux based tutorials I've found aren't much help :-(

Thanks for any suggestions


cant you use mysqldump ? with the apropraite flags and just have a timed back up of do system call and call a batch or powershell script if you want tehm to be able to trigger backups

Edited by Nueromancer, 09 July 2009 - 10:37 AM.





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

We are now a read-only forum.
 
No new posts or registrations allowed.