note 55172 deleted from function.addslashes by philip

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 "&nbsp;";
	echo "<input type='checkbox' name='execute'
value='true'";
	if($_POST['execute'] == 'true')
	{ echo " checked"; }
	echo ">";
	echo "&nbsp;";
	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. 

-- 
PHP Notes Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub
.php