Php: change mysql_real_escape_string effects to binary
I created a web page where users can submit a PDF file, which is then inserted into the MySQL database in the middle cell for later retrieval.
Everything works fine except when the PDF contains images or embedded fonts, in which case the images get corrupted and any text using the font disappears (Acrobat displays a missing font message).
I figured the problem was due to the transfer of pdf data via mysql_real_escape_string_function. I switched to base64_encode / base64_decode on submit / search, which fixed the issue for all new files, but I have about 25 already submitted PDFs that I need to be able to read.
Can the effects of mysql_real_escape_string be reversed? Or are these files corrupted without repair?
Of course it should be fixed. You just need to figure out what mysql_real_escape_string does exactly. I believe you just need to remove any forward slashes that immediately precede CR, LF, TAB, single quote, double quote, NUL, or other forward slash. There should be a one line regex fix.
a source to share
To be honest, I don't know what else could be. When I changed this bit of code it fixed the problem and I found other instances on the internet where people were having the same problem (but no solutions).
Here is the embed code:
function db_value( $mysqli, $value ) {
if( empty($value) )
return "''";
if( get_magic_quotes_gpc() )
$value = stripslashes($value);
if( !is_numeric($value) || ($value[0] == '0' && $value != 0) )
$value = "'".mysqli_real_escape_string($mysqli, $value)."'";
return $value;
}
function saveToDatabase( $data, $fileTempName, $abstractFileName ) {
$fileHandle = fopen( $fileTempName, 'r' );
$abstractFile = fread( $fileHandle, filesize( $fileTempName ) );
fclose( $fileHandle );
$abstractFileMimeType = $fileUpload->get_mime();
$mysqli = connect_to_database();
if( $mysqli != FALSE ) {
$insertQuery = "INSERT INTO `paper_submissions` (
`name`,
`affiliation`,
`email`,
`phone_number`,
`title`,
`abstract`,
`abstract_file`,
`abstract_file_name`,
`abstract_file_mime_type`,
`requests_financial_support`,
`HTTP_USER_AGENT`,
`REMOTE_ADDR`
)
VALUES (
".db_value( $mysqli, $data['submitter_name'] ).",
".db_value( $mysqli, $data['submitter_affiliation'] ).",
".db_value( $mysqli, $data['submitter_email'] ).",
".db_value( $mysqli, $data['submitter_phone'] ).",
".db_value( $mysqli, $data['paper_title'] ).",
".db_value( $mysqli, $data['abstract_text'] ).",
".db_value( $mysqli, $abstractFile ).",
".db_value( $mysqli, $abstractFileName ).",
".db_value( $mysqli, $abstractFileMimeType ).",
".db_value( $mysqli, $data['request_financial_support'] ).",
".db_value($mysqli, $_SERVER['HTTP_USER_AGENT']).",
".db_value($mysqli, $_SERVER['REMOTE_ADDR'])."
)";
$insertResult = $mysqli->query( $insertQuery );
close_database( $insertResult, $mysqli );
return $insertResult;
}
return FALSE;
}
And here is the extraction code:
$selectQuery = "SELECT `abstract_file_name`, `abstract_file_mime_type`, `abstract_file`
FROM `paper_submissions`
WHERE `id` = ".db_value( $mysqli, $id );
$result = $mysqli->query( $selectQuery );
if( $result != FALSE ) {
if( $result->num_rows ) {
$paper = $result->fetch_array( MYSQL_ASSOC );
$fileSize = strlen( $paper['abstract_file'] );
header( 'Date: '.gmdate( "D, d M Y H:i:s" ).' GMT' );
header( 'Expires: Thu, 19 Nov 1981 08:52:00 GMT' );
header( 'Cache-Control: no-store, no-cache, must-revalidate, post-check=0, pre-check=0' );
header( 'Pragma: no-cache' );
header( 'Content-Type: '.$paper['abstract_file_mime_type'].'; charset=utf-8' );
header( 'Content-Length: '.$paper['abstract_file_size'] );
header( 'Content-Disposition: inline; filename="'.$paper['abstract_file_name'].'"' );
echo $paper['abstract_file'];
exit();
}
}
Olafur,
I compiled this from the php manual and even tried the following:
$search = array( "\\0", "\\n", "\\r", "\\\\", "\\'", "\\\"", "\Z", );
$replace = array( "\x00", "\n", "\r", "\\", "'", "\"", "\x1a" );
$desiredString = str_replace( $search, $replace, $escapedString );
This seems to work fine when working with text, but applying it to binary data only degrades the PDF (e.g., paragraphs are missing).