Odd problem with Database and Strings

Junsee · 11-30-2012, 11:36 AM

testc (...only messing)

I wrote a php script that just pulls out all the information of all the tables for backup. It just prints out the text, its for webhosts that decide they will not allow system or exec (Security risk).

here it is, minus login details and headers for email

PHP Code:


			
    $dbhost = '';

    $dbuser = '';

    $dbpass = '';

    $dbname = '';

    

    $MyPlainText = NULL;



    $site = "";

    $sendto = "";

    $sendsubject = "$site - DB Plain Text Backup [$dbname] " .date("D d-M-Y g:ia");

    $headers = "From: $site <>\r\n";



    $conn = mysql_connect($dbhost, $dbuser, $dbpass) or die("Could not Connect");

    $rs = mysql_select_db($dbname, $conn) or die("Couldn't Find Database");

    

    $MyPlainText .= " -- \r\n -- Plain Text Backup of MySQL Database [Generated: " .date("D d-M-Y g:ia"). "] \r\n -- \r\n\r\n";

    $MyPlainText .= " -- \r\n -- Backup of Database: $dbname \r\n -- On Host: $dbhost \r\n -- \r\n\r\n";

    

    #Finds the Tables in Database

    $sqlshowtables = "SHOW TABLES; ";

    $rsshowtables = mysql_query($sqlshowtables, $conn);

    #$NumberofTables = mysql_num_rows($rsshowtables);

    

    while ($row = mysql_fetch_array ($rsshowtables)){

        $TableName = $row[0];

        #echo $TableName. '<br />';

        

        $MyPlainText .= " -- \r\n -- Table: $TableName \r\n -- \r\n\r\n";

        

        $sqlcreatetable = "SHOW CREATE TABLE `$TableName`; ";

        $rscreatetable = mysql_query($sqlcreatetable, $conn);

        $MyPlainText .= mysql_result($rscreatetable,0,'Create Table');



        $MyPlainText .= "\r\n\r\n";

        

        $sqlfindnumberofcolumns = "SHOW COLUMNS FROM `$TableName`; ";

        $rsfindnumberofcolumns = mysql_query($sqlfindnumberofcolumns, $conn);

        $NumberofColumnsinTable = count( mysql_fetch_row($rsfindnumberofcolumns) ) - 1;

        #echo 'Number of cols:' .$NumberofColumnsinTable;



        

        $MyPlainText .= " -- \r\n -- Values for: $TableName \r\n -- \r\n\r\n";



        $sqlfindcolumns = "SELECT * FROM `$TableName`; ";

        $rsfindcolumns = mysql_query($sqlfindcolumns, $conn);

        while ($row = mysql_fetch_array ($rsfindcolumns)){

            $MyPlainText .= "INSERT INTO `$TableName` VALUES('";

            

            for ($j=0; $j<$NumberofColumnsinTable; $j++){

                $MyPlainText .= $row[$j]. "', '";

            }

            $MyPlainText = substr($MyPlainText, 0, -4);

            $MyPlainText .= "');\r\n";

            

        }

        $MyPlainText .= "\r\n\r\n";

    }

    #$Display = preg_replace('#\\r\\n#', '<br />', $MyPlainText);

    #echo $Display;

    

    mail($sendto,$sendsubject,$MyPlainText,$headers);

    echo "done";

at the bottom I left in the:

PHP Code:


			
    #$Display = preg_replace('#\\r\\n#', '<br />', $MyPlainText);

    #echo $Display;

now the funny thing is that when I print out the results to the screen, the email comes out correctly. All the tables and values are shown...

But if I hash out the display (stop from displaying) only half the email is there.

I don't want to print out the screen because thats giving access to the database info. but it seems silly, is there something I am missing?

Fou-Lu · 11-30-2012, 01:51 PM

\r\n is used in email to dictate header separations. Body should use only \n. Try removing \r\n and using just \n.
You don't need to email this text though like this. This may cause problems between html versus plaintext view. Write the data to a file, then email the file as an attachment.
Check if you have cron though, if you can crontab the backup, you are much better off using mysqldump for the export.

Junsee · 11-30-2012, 07:26 PM

Yeah totally agree, but its one of those basic packages with a lot of the neat features disabled. I use cronjobs myself, and I have started noticing that webhosts are clamping down on system, exec, and the other one.

this way it to make it simple for the user, for all cases and webhosts, push the big button and the work is done for you.

I hope they don't clamp down on mail()

if anyone wants the new code here is: (in this case I have used a sleep, and seems to correct the issue)

PHP Code:


			
    

    $sendfrom = "";

    $sendto = "";

    $sendsubject = "-Keep this Safe- DB Backup " .date("D d-M-Y g:ia");

    $attachmentname = "DB backup [" .date("D d-M-Y gia"). "].txt";



    $conn = mysql_connect($dbhost, $dbuser, $dbpass) or die("Could not Connect");

    $rs = mysql_select_db($dbname, $conn) or die("Couldn't Find Database");

    

    $insertline = " -- \n -- Plain Text Backup of MySQL Database [Generated: " .date("D d-M-Y g:ia"). "] \n -- \n\n -- \n -- Backup of Database: $dbname \n -- On Host: $dbhost \n -- \n\n";

    

    #Finds the Tables in Database

    $sqlshowtables = "SHOW TABLES; ";

    $rsshowtables = mysql_query($sqlshowtables, $conn);

    #$NumberofTables = mysql_num_rows($rsshowtables);

    

    while ($row = mysql_fetch_array ($rsshowtables)){

        $TableName = $row[0];

        #echo $TableName. '<br />';

        

        $insertline .= " -- \n -- Table: $TableName \n -- \n\n";

        

        $sqlcreatetable = "SHOW CREATE TABLE `$TableName`; ";

        $rscreatetable = mysql_query($sqlcreatetable, $conn);

        $CreateTable = mysql_result($rscreatetable,0,'Create Table');



        #Replace "CREATE TABLE" with "IF NOT EXISTS" statements

        $insertline .= preg_replace('#CREATE TABLE#', 'CREATE TABLE IF NOT EXISTS', $CreateTable);

        $insertline .= "\n\n";

        

        $sqlfindnumberofcolumns = "SHOW COLUMNS FROM `$TableName`; ";

        $rsfindnumberofcolumns = mysql_query($sqlfindnumberofcolumns, $conn);

        $NumberofColumnsinTable = count( mysql_fetch_row($rsfindnumberofcolumns) ) - 1;

        #echo 'Number of cols:' .$NumberofColumnsinTable;



        $insertline .= " -- \n -- Values for: $TableName \n -- \n\n";



        $sqlfindcolumns = "SELECT * FROM `$TableName`; ";

        $rsfindcolumns = mysql_query($sqlfindcolumns, $conn);

        while ($row = mysql_fetch_array ($rsfindcolumns)){

            $insertline .= "INSERT INTO `$TableName` VALUES('";

            

            for ($j=0; $j<$NumberofColumnsinTable; $j++){

                $insertline .= $row[$j]. "', '";

            }

            $insertline = substr($insertline, 0, -4);

            $insertline .= "');\n";

            

        }

        $insertline .= "\n\n";

    }



    #$Display = preg_replace('#\\r\\n#', '<br />', $MyPlainText);

    #echo $Display;



    $message = "Automated Backup and Store of MySQL Database, for $sendfrom.com\nDatabase Backup File: $attachmentname attached.";

    $mime_boundary = "< <<:" . md5(time());

    #$data = chunk_split(base64_encode(implode("", file($writefile))));

    $data = chunk_split(base64_encode($insertline));

    sleep(4);



    $headers = "From: $sendfrom <>\r\n";

    $headers .= "MIME-Version: 1.0\r\n";

    $headers .= "Content-type: multipart/mixed;\r\n";

    $headers .= " boundary=\"".$mime_boundary."\"\r\n";

    

    $content = "This is a multi-part message in MIME format.\r\n\r\n";

    $content.= "--".$mime_boundary."\r\n";

    $content.= "Content-Type: text/plain; charset=\"iso-8859-1\"\r\n";

    $content.= "Content-Transfer-Encoding: 7bit\r\n\r\n";

    $content.= $message."\r\n";

    $content.= "--".$mime_boundary."\r\n";

    $content.= "Content-Disposition: attachment;\r\n";

    $content.= "Content-Type: Application/Octet-Stream; name=\"$attachmentname\"\r\n";

    $content.= "Content-Transfer-Encoding: base64\r\n\r\n";

    $content.= $data."\r\n";

    $content.= "--" . $mime_boundary . "\r\n";

    

    if (mail($sendto, $sendsubject, $content, $headers)){

        header("location:stokerslounge1a.php?D=Email");

    } else {

        header("location:stokerslounge1a.php?D=NotSent");

    }

    exit;