Editing Data type (section)

=== 雜湊碼 (hash value) e.g. MD5, SHA ===

'''Hash Algorithm Output and Schema Reference'''
<ref>[https://en.wikipedia.org/wiki/Secure_Hash_Algorithms Secure Hash Algorithms - Wikipedia]</ref>

<table border="1" class="wikitable sortable" style="border-collapse: collapse; width: 100%;">
       <tr style="background-color: #f2f2f2;">
           <th style="text-align: left;">Algorithm</th>
           <th style="text-align: left;">Output Size (bits)</th>
           <th style="text-align: left;">Max Length (chars)</th>
           <th style="text-align: left;">Schema Recommendation (Text)</th>
           <th style="text-align: left;">Schema Recommendation (Binary)</th>
       </tr>
       <tr>
           <td>MD5</td>
           <td>128</td>
           <td>32</td>
           <td>char(32)</td>
           <td>binary(16)</td>
       </tr>
       <tr>
           <td>SHA-1</td>
           <td>160</td>
           <td>40</td>
           <td>char(40)</td>
           <td>binary(20)</td>
       </tr>
       <tr>
           <td>SHA-224</td>
           <td>224</td>
           <td>56</td>
           <td>char(56)</td>
           <td>binary(28)</td>
       </tr>
       <tr>
           <td>SHA-256</td>
           <td>256</td>
           <td>64</td>
           <td>char(64)</td>
           <td>binary(32)</td>
       </tr>
       <tr>
           <td>SHA-384</td>
           <td>384</td>
           <td>96</td>
           <td>char(96)</td>
           <td>binary(48)</td>
       </tr>
       <tr>
           <td>SHA-512</td>
           <td>512</td>
           <td>128</td>
           <td>char(128)</td>
           <td>binary(64)</td>
       </tr>
</table>

Explain the conversion process from 224 bits to 56 characters for SHA-224's output. Conversion process:
* Original output: 224 bits
* '''Text Representation (Hexadecimal)''': Every 4 bits converts to 1 hexadecimal character (hex character): 224 ÷ 4 = 56 Therefore, it requires 56 hexadecimal characters to represent
* '''Binary Storage Representation''': Every 8 bits equals 1 byte in binary storage: 224 ÷ 8 = 28 Therefore, it requires 28 bytes (binary(28)) to store efficiently

[https://zh.wikipedia.org/wiki/MD5 MD5]: {{exclaim}} Not recommended to use this function to secure passwords
* Data type: CHAR(32) or VARCHAR(32)<ref>[https://stackoverflow.com/questions/14922208/can-i-use-varchar32-for-md5-values php - Can I use VARCHAR(32) for md5() values? - Stack Overflow]</ref>
* Framework: MySQL [https://www.w3resource.com/mysql/encryption-and-compression-functions/md5().php md5()], [https://www.php.net/manual/en/function.md5.php PHP: md5 - Manual]

[https://zh.wikipedia.org/wiki/SHA%E5%AE%B6%E6%97%8F SHA]<ref>[https://stackoverflow.com/questions/2240973/how-long-is-the-sha256-hash mysql - How long is the SHA256 hash? - Stack Overflow]</ref><ref>[http://fishjerky.blogspot.com/2013/06/md5sha512.html 魚乾的筆記本: MD5被破解了，要改用SHA]</ref>: 
* (1) HEX: CHAR(64) Using [http://php.net/manual/en/function.hash-file.php PHP: hash_file()] or [https://www.php.net/manual/en/function.hash.php PHP: hash()], MySQL [https://dev.mysql.com/doc/refman/5.6/en/encryption-functions.html#function_sha2 SHA2(str, hash_length)]
* (2) Binary: BINARY(32) In PHP using [http://php.net/manual/en/function.hex2bin.php hex2bin()] e.g. {{kbd | key=<nowiki>echo hex2bin(hash_file('sha256', $file_path));</nowiki>}}, In MySQL using [https://www.w3resource.com/mysql/string-functions/mysql-unhex-function.php UNHEX() function] e.g. {{kbd | key=<nowiki>SELECT UNHEX(SHA2('The quick brown fox jumped over the lazy dog.', 256))</nowiki>}}

[https://zh.wikipedia.org/zh-tw/SHA-1 SHA-1] (Secure Hash Algorithm 1) {{exclaim}} Not recommended to use this function to secure passwords
* Data type: 
** CHAR(40) - The hexadecimal representation of a SHA-1 hash consists of 40 characters. SHA-1 is a 160-bit hash function, resulting in 160 binary bits. Since each hexadecimal character corresponds to 4 binary bits, 40 hexadecimal characters are needed to represent a complete SHA-1 hash.
** BINARY(20) - If stored in binary format, a SHA-1 hash has a length of 20 bytes. Each byte contains 8 binary bits. Therefore, the BINARY(20) data type is used to store the binary representation of a SHA-1 hash.
* Framework: MySQL [https://www.w3resource.com/mysql/encryption-and-compression-functions/sha1().php SHA1() function], [https://www.php.net/manual/en/function.sha1.php PHP: sha1 - Manual]

SHA-256 ([https://zh.wikipedia.org/zh-tw/SHA-2 SHA-2])
* Data type: 
** CHAR(64)　- The hexadecimal representation of a SHA-256 hash consists of 64 characters. This is because SHA-256 is a 256-bit hash function, resulting in 256 binary bits. Since each hexadecimal character corresponds to 4 binary bits, 64 hexadecimal characters are needed to represent a complete SHA-256 hash.
** BINARY(32) - If stored in binary format, a SHA-256 hash has a length of 32 bytes. Each byte contains 8 binary bits. Therefore, the BINARY(32) data type is used to store the binary representation of a SHA-256 hash.
* Framework: MySQL [https://dev.mysql.com/doc/refman/8.0/en/sha256-pluggable-authentication.html MySQL :: MySQL 8.0 Reference Manual :: 8.4.1.3 SHA-256 Pluggable Authentication], [https://www.php.net/manual/zh/function.hash.php PHP: hash - Manual]


==== Retrieve the hash value from string or file content ====

PHP:
<pre>
<?php
/* Create a file to calculate hash of */
file_put_contents('example.txt', 'The quick brown fox jumped over the lazy dog.');

echo hash_file('sha256', 'example.txt');
// expected result: 68b1282b91de2c054c36629cb8dd447f12f096d3e3c587978dc2248444633483
// 64 characters

// calculate the hash of string
echo hash('sha256', 'The quick brown fox jumped over the lazy dog.');
?>
</pre>

MySQL:
<pre>
SELECT SHA2('The quick brown fox jumped over the lazy dog.', 256)
</pre>

==== Retrieve the hash value from binary value ====
* PHP: [http://php.net/manual/en/function.bin2hex.php bin2hex()] e.g. {{kbd | key=<nowiki>echo bin2hex(hex2bin(hash('sha256', 'The quick brown fox jumped over the lazy dog.')));</nowiki>}}
* MySQL: [https://dev.mysql.com/doc/refman/8.0/en/hexadecimal-literals.html Hexadecimal Literals] e.g. {{kbd | key=<nowiki>SELECT HEX(UNHEX(SHA2('The quick brown fox jumped over the lazy dog.', 256)));</nowiki>}}<ref>[https://stackoverflow.com/questions/14608413/storing-a-binary-sha1-hash-into-a-mysql-binary20-column insert - Storing a binary SHA1 hash into a mySQL BINARY(20) column - Stack Overflow]</ref>