note 55172 deleted from function.addslashes by philip
| From: | philip@php.net | Date: | Fri, 31 Mar 2006 15:30:47 +0000 |
| Subject: | note 55172 deleted from function.addslashes by philip | ||
| References: | 1 | Groups: | php.notes |
| Request: | Send a blank email to php-notes+get-107082@lists.php.net to get a copy of this message | ||
Note Submitter: development at lab-9 dot com
----
Dumping a binary content from your database into a valid mysql insert command can be rather tricky.
Even more tricky when you have huge data sizes.
First idea was to save these INSERT INTO
$table SET
value='mydata'; line after line into a file (can be compressed or not,
doesn't matter).
Now there I had a few problems. It seemed as if a simple addslashes() would create a wrong syntax
for the mysql parser. But it's still needed.
After a lot of testing (about 4hours) I tried to replace critical strings with valid, normal
strings:
<?php
$search = array("\x00", "\x0a", "\x0d", "\x1a");
$replace = array('\0', '\n', '\r', '\Z');
?>
and hooray, it worked fine!
here the part of the code, where I replace it:
<?php
$writestring .= $field."='".str_replace($search, $replace,
addslashes($data))."'";
?>
PLEASE NOTE: each line must be broken with a correct \n sign. If you break up in the middle of texts
for example, you won't be able to restore the data. So keep an eye on how you're saving
this stuff. :-)
And here the complete parser, that executes a file you input line after line (it's nothing big,
just useful if you have large inserts or a lot of inserts which may cannot be executed with
phpmyadmin):
<?php
function getfilesize($size)
{
$units = array(' Bytes', ' KB', ' MB', ' GB', '
TB');
for($i = 0; $size > 1024; $i++)
{
$size /= 1024;
}
return round($size, 2).$units[$i];
}
$MAX_QUERYSIZE = 0;
$vars = mysql_query("SHOW VARIABLES");
if($vars == false)
{ echo "Get variables: ".mysql_error()."<br />"; }
while($row = mysql_fetch_array($vars))
{
if($row['Variable_name'] == "max_allowed_packet")
{
$MAX_QUERYSIZE = $row["Value"];
echo "Max_query_size: ".getfilesize($row["Value"])."<br />";
break;
}
}
echo "<form action='' method='post'>";
echo "<input type='text' name='href'
value='".$_POST['href']."'>";
echo " ";
echo "<input type='checkbox' name='execute'
value='true'";
if($_POST['execute'] == 'true')
{ echo " checked"; }
echo ">";
echo " ";
echo "<input type='submit'>";
echo "</form>";
if($_POST['href'] != "")
{
if(@file_exists($_POST['href']))
{
if(@is_readable($_POST['href']))
{
echo "Filesize: ".getfilesize(@filesize($_POST['href']))."<br
/>";
$buffer = @file($_POST['href']);
if(is_array($buffer))
{
echo "Lines: ".@count($buffer)."<br />";
if($_POST['execute'] == 'true')
{
$_DB = array( 'user' => '***',
'password' => '***',
'host' => '***',
'port' => '3306',
'database' => '***');
$connection = mysql_connect($_DB['host'], $_DB['user'],
$_DB['password']);
if($connection)
{
if(mysql_select_db($_DB['database']))
{
for($i=0; $i<count($buffer); $i++)
{
if(mysql_query(trim($buffer[$i])))
{
echo " L ".($i+1).": executed fine :-)<br />";
}
else
{
echo "L ".($i+1).": ".mysql_error()." (LEN:
".getfilesize(strlen($buffer[$i])).")";
if(strlen($buffer[$i]) >= $MAX_QUERYSIZE)
{
echo " [QUERY IS TOO BIG!]";
}
echo "<br />";
}
}
}
else
{ echo "Could not access the database!"; }
}
else
{ echo "Database connect failed!"; }
}
else
{
echo "Selected to not process file.";
}
}
else
{
echo "File was not read!";
}
}
else
{
echo "Cannot read file!";
}
}
else
{
echo "File does not exist!";
}
echo "<br /><br />";
}
?>
If someone may has an idea, how to save and insert such blob contents with hex-decoded format (
using f.ex. bin2hex() ), please mail me. I would be happy about it. :-)