View Files from Database blob

Hey there, i have files saved on database using this script

     <form method="post" enctype="multipart/form-data" >
     <input type="file" name="myfile"/>
     <button name="btns"> Incarca Schita </button>
     </form>
     <?php
     $dbh = new PDO("mysql:host=localhost;dbname=highmob_comenzi", "highmob", "PW");
     if(isset($_POST['btns'])){
         $name = $_FILES['myfile']['name'];
         $type = $_FILES['myfile']['type'];
         $data = file_get_contents($_FILES['myfile']['tmp_name']);
         $stmt = $dbh->prepare("UPDATE players SET data='$myfile', name='$name', mime='$type' WHERE id='$id'");
         $stmt->bindParam(1,$name);
         $stmt->bindParam(2,$type);
         $stmt->bindParam(3,$data);
	     $stmt->execute();
     }
     ?>
  <?php
$stat = $dbh->prepare("select * from players where id='$id'");
$stat->execute();
while($row = $stat->fetch()){
	echo "<a href='viewschita.php?id=".$row['id']."'>".$row['name']."</a>
	";
}
?>

Open in new window


this is the viewschita.php

<?php
$db = mysqli_connect("localhost","highmob","PW","highmob_comenzi"); //keep your db name
$sql = "SELECT * FROM players WHERE id = $id";
$sth = $db->query($sql);
$result=mysqli_fetch_array($sth);
header('Content-Type:'.$row['mime']);
echo  $row['data'];

?>

Open in new window


The problem is that I'm unable to see the file
Pirjol NeluAsked:
Who is Participating?
 
gr8gonzoConnect With a Mentor ConsultantCommented:
@NerdsOfTech - I have to strongly disagree with you. Base64 encoding adds a LOT of overhead. Not only does having a narrow character set result in an approximate 33% increase in overall size in its encoded form, but you also need 2x the memory for the data in PHP because you're holding a copy of the raw data and a copy of the encoded data at the same time.

So if you're storing a 3 megabyte file, for example, you'll end up need 7 megabytes of memory - 3 megabytes for the raw data, and 4 megabytes for the Base64-encoded string. Then you're storing 4 megs in the database instead of 3. Then when you get it back out, you're using up 33% more bandwidth because of the encoding overhead, and then you have the same memory usage issue on the decoding side.

The proper way to store binary data in a blob is to simply use the hex encoding:
INSERT INTO table (blobfieldhere) VALUES (0x100ef9e...hex data etc...);

Open in new window

Note: the "100ef9e" is just for illustration - that's not some special code that should be part of your query)

You'll still need roughly 2x the memory for the storage flow (converting raw data to hex since it's still a temporary string in memory), but you'll incur no overhead penalties for storage or retrieval later on.

You can even eliminate the memory issue when storing by using prepared statements with "send_long_data()" :
http://php.net/manual/en/mysqli-stmt.send-long-data.php
1
 
Pirjol NeluAuthor Commented:
So nobody can help me with this problem? :(
0
 
gr8gonzoConsultantCommented:
What is the exact result you're seeing?
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
hieloConnect With a Mentor Commented:
>>  $data = file_get_contents($_FILES['myfile']['tmp_name']);
>> "UPDATE players SET data='$myfile', name='$name', mime='$type' WHERE id='$id'"
change your update statement to use $data instead of $myfile.

 "UPDATE players SET data='$data', name='$name', mime='$type' WHERE id='$id'"
1
 
NerdsOfTechTechnology ScientistCommented:
Besides @hielo's correction,

After further study, 'most', if not all, binary data should be BASE64 encoded first which converts ALL bits into 0-9a-zA-z+/ characters (64 character set), THEN be put into the database. Some programmers in the past used addslashes() and stripslashes(), but that can unfortunately corrupt binary data.

"UPDATE players SET data='" . base64_encode($data) . "', name='$name', mime='$type' WHERE id='$id'"

Open in new window


When retrieving the data simply output the binary data using the file's mime and base64_decode; just change the last line to:

echo base64_decode($row['data']);

Open in new window

0
 
NerdsOfTechTechnology ScientistCommented:
Ah an even better way. Thanks for the info!

What if the data has unescaped characters? Does it matter with that function?
0
 
gr8gonzoConsultantCommented:
Nope with that last approach, no escaping is necessary.
1
 
gr8gonzoConsultantCommented:
Recommend ID: 42440966 as the best answer.
1
 
hieloCommented:
The only acceptable answer here is ID: 42439281, which actually addresses the poster's problem.  The "proposed answer" is addressing the "issue" introduced by someone else's comment, but said comment didn't actually solved the problem either.
0
 
gr8gonzoConsultantCommented:
No, #42439281 is a bad answer. It is extremely likely to simply break because it is injecting the raw, unknown contents of a file into a query. Any file that had an apostrophe character would result in a SQL error. Or if you had a malicious file, it would result in SQL injection. You never just inject variable content straight into a query. That is bad, bad, bad.

There are a few CORRECT ways to insert data into a blob column, and 42440966 covers two of them (inserting either hex-encoded data or using the send_long_data call).
0
 
gr8gonzoConsultantCommented:
Objecting to the bad answer being accepted.
0
 
hieloCommented:
Again, you did not answer the original question.  The original issue at hand is equivalent to:

$x = 1;
$y = $z;

followed by "why doesn't $y retain the value of $x?". Clearly it is a simply matter of assigning the correct variable to $y.

You answered something else altogether.  I'm not saying it is a technically incorrect/bad answer.  It is simply not the answer for what was asked.  Given the sql UPDATE statement posted, if the question had been "What is the best practice when updating a blob field in a db table?", then I see your response as being relevant (although given what he posted, instead of your response I would have showed him how to properly used prepared statements -- in which case you don't need to convert to hex).

Regards,
Hielo
0
 
gr8gonzoConsultantCommented:
The original issue was about being unable to see the data. The answer to that is to insert the data properly into the database, which requires instructions on how to properly do that.

I agree that there was a basic problem with the variable usages, but that alone is not a correct answer because what you proposed is still broken and will still result in the original problem (assuming that the data contains apostrophes, and the vast majority of binary files do - and it's more than likely they are binary due to the OP's viewer code, plus a bit of reading between the lines). I'd put a sizable bet on the fact that the OP was trying to upload photos/images of the players. Try grepping through a folder of images to see how many are apostrophe-free and you'll likely find that all (or nearly all) have apostrophes, regardless of format.

Regardless of if I'm even right about the images, the method you presented was simply unreliable, and therefore incorrect. If someone came up to you and asked, "I'm not rolling dice properly, how do I ensure that a pair of dice always landed with sixes facing up?", and you came back and told them how to simply roll the dice, that wouldn't be a valid answer because you'd be wrong about 97% of the time on average. You might have corrected a very minor mechanism but the required result is still not being met.

My answer (at least the main portion of it) requires the OP to hex-encode the data, which also implicitly addresses the variable usage problem, since I point out that you have to hex-encode the DATA. Following the instructions in my answer would resolve the initial problem and resolve it in a way that is both reliable and safe.

If you had provided an example using prepared statements and binding, I would have absolutely agreed that your answer was correct, but the fact is that you didn't do so - you tried to present an answer that explicitly did NOT use binding, which is why it is wrong.
0
 
hieloCommented:
>> The original issue was about being unable to see the data. The answer to that is to insert the data properly into the database, which requires instructions on how to properly do that.
I am not saying that encoding the data is wrong.  I am saying that simply encoding the data will not fix the problem when the UPDATE statement is dereferencing the wrong variable.  If it helps you consider the following:

function blobToHex($str){
  // do hex encoding on $str
  // ...
return $result;//where $result contains the hex encoding of $str
}

    $data = blobToHex( file_get_contents($_FILES['myfile']['tmp_name']) );
     $stmt = $dbh->prepare("UPDATE players SET data='$myfile', name='$name', mime='$type' WHERE id='$id'");

$myfile does not have the encoded data.  How will simply encoding the value of $data allow the poster to see the blob when the blob always fails to be updated in the table?

I agree that IF there is an apostrophe in the payload then a runtime warning (or error) will occur, in which case the encoding addresses that issue. But, as the poster stated, "...The problem is that I'm unable to see the file".  He did not report a runtime warning/error.  He is simply not seeing any output because the data he is inserting is not on the variable he is using.

At best, what you are suggesting is an assist, but it is not "the best answer" because it does not answer the original question.
0
 
gr8gonzoConsultantCommented:
He didn't report an error/warning because there wasn't any data yet. Your answer would inevitably lead to that error.

My answer demonstrates the correct full query, including where the data should go within the query.

Remember this is also about choosing what will help others in the future who might come across this question. If they see your post marked as an accepted answer, they will likely try to use it themselves and set themselves up for future failure and vulnerability.
0
 
gr8gonzoConsultantCommented:
There, you get some points for the variable catch. If you'd like to keep fighting about this because you care more about points than about answer quality, then we can let another objective moderator pick the best answer.
0
 
hieloCommented:
>> He didn't report an error/warning because there wasn't any data yet
Exactly my point.  There is not data in updated in the table field because he did not specify use the right variable.  The reason I highlighted the variables in my first post was for the poster to realize that he needs to be make sure he dereferences the correct variables.

>> My answer demonstrates the correct full query...
Hmmm? My browser shows that the poster is dealing with an UPDATE query and your post shows a generic INSERT query!!!  Strange, don't you think?

>> Remember this is also about choosing what will help others in the future who might come across this question.
Agree.  And the lesson behind my answer is simply pay extra attention to the variables you are using.  That IS the reason he poster was not able to see the data.

>> If they see your post marked as an accepted answer, they will likely try to use it themselves and set themselves up for future failure and vulnerability.
You are missing the point.  The reason it needs to be marked as an answer is to pay extra attention to the variables they are dereferencing. As ID: 42474387 shows, encoding will not help you is you don't pay attention to the right variables.  Like it or not, the issue occurred because  the wrong variable was used.

>> There, you get some points for the variable catch.
LOL.  I like your sense of humor.  I haven't needed points for a very long time!

>> If you'd like to keep fighting about this because you care more about points than about answer quality, then we can let another objective moderator pick the best answer.
Buddy, if we are fighting, then I assure you that I am winning because I was not aware of it.

I propose:

Accepted Answer: hielo's comment #a42439281 (0 points) [ simply because it points out the issue that really caused the problem ]
Assist: gr8gonzo's comment #a42440966 (1000 points) [ because it is a good suggestion ]
0
 
gr8gonzoConsultantCommented:
That IS the reason he poster was not able to see the data.
And if he followed your suggestion, he would still end up with an error, which makes it an invalid answer.

And if others saw your post as the "answer", then they would follow your suggestion and say, "Experts Exchange is recommending answers that don't work."

If you don't care about points, then that's one less thing to worry about. In that case, I would suggest:
Accepted Answer: gr8gonzo's comment #a42440966 (1000 points) [ because it FULLY addressed the problem and would not lead to another problem ]
Assist: hielo's comment #a42439281 (0 points) [ because it pointed out a minor problem, but would still leave the user with the same problem of being unable to correctly insert data (and thus view it) but with a different root cause ]

...if we are fighting...
I'm referring to the constant objections you keep posting because you seem to want to have your post be "the answer" even if it is not a good answer. It is absolutely bizarre to me that you are somehow blind to the fact that your comment would fix one issue but still leave the OP with their original problem (which means it's not a solution). Just let it go, man. Anyone that comes across this post in the future is not going to come across it because they searched for a problem dealing with basic variable misuse, but because they're dealing with the issue of how to deal with inserting or updating BLOB data.
0
 
NerdsOfTechTechnology ScientistCommented:
I concur with vote:
Accepted Answer: gr8gonzo's comment #a42440966 (1000 points)
Assist: hielo's comment #a42439281 (0 points)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.