0

I am trying to insert binary data into BLOB data type field on a MySql database and i have the following code:

$dataset = array();
if (!empty($_FILES)) {
    foreach ($_FILES as $k => $f) {
          $dataset[$k] = "data:" . $_FILES[$k]["type"] . ";" . file_get_contents($_FILES[$k]["tmp_name"]);
    }
}

$sql = "UPDATE table_name SET ";
foreach ($dataset as $key => $val) {
    if (!is_null($val) && $val !== false)
          $sql .= $key . "=" . (is_numeric($val) ? $val : "'" . $val . "'") . ",";
    }
}
$sql = rtrim($sql, ",");
$sql .= " WHERE id = 1";

$mysql = new mysqli("localhost" , "db_username", "db_password", "db_name");
$mysql->query($sql);

I already read about base64_encode, but i want to put the raw binary into database, so the mysql blob type will treat it like it should.

I also found about mysql_real_escape_string() function, but i saw another code working without escaping the binary data.

I know that is the attempt to send the raw binary to mysql that is making trouble, because mysql returns a 1064 error and when i remove the binary from the query, it simply works magically!

Can somebody help?

PS: Please, do not mark it as duplicate, because before asking here, i already did a good research on other threads, but all the answers i got did not work.

Valentoni
  • 308
  • 4
  • 19
  • 1
    *"I also found about mysql_real_escape_string() function"* - You mean `mysqli_real_escape_string($con, $var)` and passing connection to that function, *right?*. If not, then that's why your attempt at it failed. You need to escape that data going into BLOB with your file(s). – Funk Forty Niner Feb 23 '16 at 20:53
  • "but I saw other code work without escaping" - that other code undoubtedly (by chance/design) didn't have sql metacharacters in whatever data they were inserting. just because one query worked in one particular situation doesn't mean ALL queries will work. And yes, this is a duplicate. You have an sql injection vulnerability, and the exact methods to prevent that vulnerability are what will make your query work, regardless of what binary "garbage" you're inserting. – Marc B Feb 23 '16 at 20:54
  • Yes, i read about mysqli_real_escape_string($data,$cnn). But i saw another working code that does not escape the raw binary. In fact it does exactly: "data:" . $_FILES[$k]["type"] . ";" . file_get_contents($_FILES[$k]["tmp_name"]); – Valentoni Feb 23 '16 at 20:55
  • Thank you for nothing, @MarcB – Valentoni Feb 23 '16 at 21:24
  • yes, and? just because it worked on one file means nothing. you'll pulling in raw binary data, at SOME POINT there'll be a file that has at least one (or more) `'` in it, and when you stuff that into your query string, you'll be producing an sql syntax error - which is EXACTLY what sql injection is. go ahead, try `select true from dual where 'foo'bar' = 'foo'bar'` and see how far you get. – Marc B Feb 23 '16 at 21:36
  • and since your query calls simply assume success, you'll never see the warnings mysql would have given you if you had checked. `$result = $mysql->query(...) or die($mysql->error));` should be the absolute barebones error handling you never ever go without. – Marc B Feb 23 '16 at 21:39
  • Now, you actually helped, thank! Is the ' that is causing me trouble. And, yes, i'm going for the SQL PREPARED STATEMENTS. Do you think it will solve? @MarcB – Valentoni Feb 23 '16 at 21:44
  • Ah, i did `or die($mysql->error)` just omitted it here in the thread. This is how i got the `1064 mysql error`. Duuuurr! @MarcB – Valentoni Feb 23 '16 at 22:00

0 Answers0