Pregunta ¿Por qué utilizar bin2hex al insertar datos binarios de PHP en MySQL?


Escuché el rumor de que al insertar datos binarios (archivos y demás) en MySQL, debe usar bin2hex() función y enviarlo como un valor HEX-codificado, en lugar de solo usar mysql_real_escape_string en la cadena binaria y usar eso.

// That you should do
$hex = bin2hex($raw_bin);
$sql = "INSERT INTO `table`(`file`) VALUES (X'{$hex}')";

// Rather than
$bin = mysql_real_escape_string($raw_bin);
$sql = "INSERT INTO `table`(`file`) VALUES ('{$bin}')";

Se supone que es por motivos de rendimiento. Algo relacionado con la forma en que MySQL maneja cadenas grandes en comparación con cómo maneja valores codificados HEX

Sin embargo, estoy teniendo dificultades para confirmar esto. Todas mis pruebas indican exactamente lo contrario; que el bin2hex el método es ~ 85% más lento y usa ~ 24% más de memoria.
(Estoy probando esto en PHP 5.3, MySQL 5.1, Win7 x64 - Usando un bucle de inserción farily simple.)

Por ejemplo, este gráfico muestra el uso de la memoria privada del mysqld proceso mientras el código de prueba se estaba ejecutando:

Bytes privados utilizados por el proceso mysqld http://atli.advefir.com/images/priv_mem_cropped.gif

¿Alguien tiene alguna explicación o recurso que pueda aclarar esto?

Gracias.


12
2018-04-01 06:07


origen


Respuestas:


Esto me suena como una leyenda urbana.

bin2hex() mapea cada byte en la entrada a dos bytes en la salida ('a' -> '61'), por lo que debe observar un aumento significativo de la memoria del script que realiza la consulta; debe usar al menos la misma cantidad de memoria que la longitud del byte de los datos binarios que se insertarán.

Además, esto implica que se ejecuta bin2hex() en una larga cadena toma mucho más que correr mysql_real_escape string(), que, como se explica en Documentación de MySQL - Solo escapa de 6 caracteres: NULL, \r, \n, \, , y 'Control-Z'.

Eso fue para la parte PHP, ahora para MySQL: el servidor necesita hacer la operación inversa para almacenar los datos correctamente. Invertir cualquiera de las funciones lleva casi tanto tiempo como la operación original - la función inversa de mysql_real_escape_string() necesita reemplazar valores escapados (\\) con los no escamados (\), mientras que el reverso de bin2hex() necesitaría reemplazar todas y cada una de las tuplas de bytes con un nuevo byte

Desde que llama mysql_real_escape_string() en datos binarios es seguro (de acuerdo con MySQL y Documentación de PHP o incluso cuando solo se considera que la operación no realiza otras conversiones distintas a las mencionadas anteriormente), no tendría ningún sentido realizar una operación tan costosa.


9
2018-04-10 10:14



He estado probando esto yo mismo, y he obtenido resultados bastante consistentes. (Aunque mis pruebas son un poco rudimentarias)

He probado tres computadoras

  1. Windows 7 (x64), PHP 5.3, MySQL 5.1
  2. Ubuntu 9.10 (x64) PHP 5.2, MySQL 5.1
  3. Ubuntu 10.04 (x32) PHP 5.3, MySQL 5.1

Hasta ahora, las pruebas en las tres plataformas han indicado los mismos tonos:

  • La inserción en un BLOB es 2x a 8x más rápido en MyISAM que en InnoDB. La diferencia parece ser mayor en cadenas binarias que en cadenas codificadas con HEX. (Ver los datos a continuación)
  • Usando una cadena HEX codificada (bin2hex en un X'...') usa más memoria, en promedio, que usar una cadena binaria escapada (mysql_real_escape_string en los datos brutos). - Esto parece ser cierto tanto para MyISAM como para InnoDB.
  • La cadena binaria es más rápida en MyISAM, pero los datos con código HEX son más rápidos en InnoDB.

La prueba fue básicamente un simple bucle que escapó o codificó hexadecimamente los datos sin procesar (una imagen 2.4 MiB recuperada una vez en la parte superior del script), construyó la cadena de consulta y la ejecutó a través del mysql_query o mysqli::query funciones. - Probé con ambas extensiones. No parecía haber ninguna diferencia.

Puse los resultados de Ubuntu 10.04 (n. ° 3) en las hojas de cálculo. Los resultados de la máquina Ubuntu 9.10 (n. ° 2) fueron prácticamente los mismos, así que no me molesté en configurarlos:
(¡Finalmente, una excusa para probar lo de Google Docs correctamente! XD)

Estos gráficos muestran el uso de memoria privada por el mysqld proceso en la máquina Win7 (n. ° 1).


5
2018-04-03 04:57



Una cadena hexagonal es significativamente más larga que la cadena binaria correspondiente. Simplemente el tiempo de transferencia y la copia dentro de la memoria de PHP y MySQL pueden hacer el truco.

Honestamente, no soy experto en la implementación subyacente, pero ¿no sería mejor no pasar los datos dentro del SQL en absoluto, pero usando, por ejemplo, PDOStatement¿enlace de parámetros? Tal vez alguien más conocedor aquí pueda confirmar si eso causará que los datos se envíen como una cadena binaria, fuera de cualquier declaración SQL, o si PDO simplemente hace la manipulación de cadenas de escape y consulta bajo el capó.

De cualquier forma, obtienes un beneficio de seguridad (y simplicidad) allí mismo.


4
2018-04-01 07:20



por ejemplo, si encuentra un problema similar como se describe aquí: http://www.php.net/manual/en/function.mysql-real-escape-string.php#82015

p.ej. aunque mysql_real_escape_string parece ser "binario seguro", no puede usarlo (como un ejemplo) en combinación con igbinary_serialize; la deserialización simplemente fallará.

en ese caso necesita bin2hex antes de insertar los datos en mysql.

Además, generalmente lee más a menudo datos de mysql que insertar :)


0
2018-04-12 17:46