二进制数据未正确存储在MySQL中 [英] Binary data not stored properly in MySQL

问题描述

我想将base64_encoded字符串存储为二进制数据.因此，在插入MySQL的LONGBLOB字段之前，请对字符串使用base64_decode().到目前为止，到目前为止还不错，但是当我从MySQL检索数据时，我无法生成以...开头的正确的base64_encoded字符串.

I want to store a base64_encoded string as binary data. So use base64_decode() on the string before inserting in a LONGBLOB field of MySQL. So far so good, however when I retrieve the data from MySQL, I can't generate the correct base64_encoded string which I have started with...

这怎么可能?预先感谢

EDIT

EDIT

存储的数据是使用AES-256CBC OPENSSL加密例程的加密字符串.

The stored data is an encrypted string with AES-256CBC OPENSSL encryption routine.

CODE

CODE

对于我的代码，我使用OpenSSL加密字符串

For my code I use OpenSSL to encrypt a string

$string = "Test";
$IV = "1234567890123456";
$key = "12345678901234561234567890123456";
$encrypted = openssl_encrypt($string, "AES-256-CBC", $key, false, $IV);

$encrypted string is stored in LONGBLOB field by 
$sql_insert_data = $mysqli->prepare("INSERT INTO `TBLName` (String) Values(?)");
$sql_insert_data->bind_param('s', $mysqli->real_escape_string($encrypted));

//Thereafter, it is retrieved by a select statement
$decrypted = openssl_decrypt($row['String'], "AES-256-CBC", $key, true, $IV);

当我在字符串上执行base64_encode并将其作为TEXT值存储在DB中时，以上方法有效.但是，当我不这样做时，以上内容将无法正常工作...

When I do base64_encode on the string and store that as a TEXT value in the DB, the above works. However, when I do not, the above does not work...

谢谢

推荐答案

您不正确地使用了准备好的语句:

You are using prepared statements incorrectly:

$sql_insert_data->bind_param('s', $mysqli->real_escape_string($encrypted));
                                           ^^^^^^^^^^^^^^^^^^ :-!

您的代码会将随机反斜杠添加到二进制流中.您完全不需要real_escape_string().

Your code will add random backslashes to the binary stream. You don't need real_escape_string() at all.

这篇关于二进制数据未正确存储在MySQL中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！