How to work with Base64 encryption in Mysql?

Question

I need to work with encryption in Mysql and need to use base64 because hearing that MD5 has a small flaw that allows it to have two distinct passwords with the same MD5. I need to encrypt and decrypt.

insert into scl_usuario (login, senha, email, nome, sobrenome, setor) value ("jose", base64_encode("123"), "[email protected]", "José", "da Silva", "Desenvolvimento");

Answer 1

Mysql >= 5.6.1

In Mysql, encoding or decoding in BASE64 is only possible from version 5.6.1. Previous versions give the following error:

#1305 - FUNCTION impor_website_2014.TO_BASE64 does not exist 

The functions introduced in the above version were:

TO_BASE64()

Converts the argument to a coded form in base-64 and returns the result as a string with the Character and collation connection to the database. If the argument is not a string, he is converted to string before conversion. The result is NULL if the argument is NULL.

FROM_BASE64()

Catch a string coded with the base-64 rules used by TO_BASE64() and returns the decoded result as a string binary. The result is NULL if the argument is NULL or not a string on a 64-basis.

Example of use:

SELECT TO_BASE64('abc'), FROM_BASE64(TO_BASE64('abc'));    # Devolve: 'JWJj', 'abc'

---

Mysql < 5.6.1

There is a project on Github to solve BASE64 encoding and decoding for older versions of Mysql:

mysql-udf-Base64

It is essentially a UDF ^{User Defined Function} aimed at creating the: base64encode() and base64decode().

Example of use:

SELECT base64encode('data,binary,text,...');
SELECT base64decode('b64strings');
INSERT INTO t1 (body) VALUES (base64encode('something'));

To use, you have to build and activate:

Build

$ git clone https://github.com/y-ken/mysql-udf-base64.git
$ cd mysql-udf-base64
$ gcc -Wall -fPIC -I/usr/local/include -shared base64.c -o base64.so
$ sudo install -m 755 base64.so `mysql_config --plugindir`

Activation

mysql> CREATE FUNCTION base64encode RETURNS STRING SONAME 'base64.so';
mysql> CREATE FUNCTION base64decode RETURNS STRING SONAME 'base64.so';

^{Examples and method to build and activate taken from Github’s project page.}

---

^{Note:
It should be pointed out that this answer is intended to deal with the above problem: The use of a function and the fact that it is giving an error when trying to use it.
Base64 does not produce the result described in encryption, Base64 is not encryption, it is only the conversion of the string to a Radix-64 standard commonly used to standardize characters, such as the case of image transfer between different systems. Any system of Encode who has the method Decode should not be used for passwords (passwords).}

Answer 2

Base64 is not encryption, if it is only a standard encoding as other UTF-8 among numerous encoding standards, this said the MD5 has rather its possibility of collision, but it is very small.

see this paper for a more detailed explanation of the collision: <https://ad-pdf.s3.amazonaws.com/papers/wp.MD5_Collisions.en_us.pdf>

Differences between encryption, cryptographic hash and coding.

Cryptography There are two types a symmetric and asymmetric, the basic principle is that when we talk about cryptography we have a key to encrypt/decrypt the content. Some encryption algorithms are:

1. AES

2. DES

3. RSA

4. Blowfish

Coding is what we use to transit information between different systems, for example an image, it can be encoded in Base64 and transferred without data loss and then decoded, note that here we do not treat a secret, the information is always visible, Only in a different way. This is a very extensive subject and a quick search on google about encoding, discover more about, but in essential coding are tables that will tell us what that binary means in characters.

Crypto Hash or Digest Here I come to what I believe will in fact be useful for your application, which from what I understand is to save the password of the user in the database so that it n be exposed to no bank administrator and possible hacker, thus ensuring that the password is only known by the user. A hash algorithm what it will do is calculate a unique and one-size-fits-all code, so it is hashed what the user types and compared to what hash is in the database to see if the password is valid. More than the collision problem of md5 I believe I have other more serious problems to worry as:

• Rainbow Tables The problem here are pre-calculated hashes and saved in a database so for example if the user has a weak password it will be simple to discover the password of it, to solve this we have several strategies, the first is to force the user to have a strong password and the second call of salt, is together with the password that the user typed more data to make it more complex thus making it difficult that this hash has already been pre-calculated, the information that is placed next to the password can be absolutely anything, but I recommend it to be pseudo random values.

• What to use? Collision on most Hash algorithms is not a very serious thing to worry about, as it would take a long time to calculate a possible collision, and if done a good hash it becomes totally unviable, some algorithms to use to store passwords, bcrypt(Blowfish based), SHA-256. Hashes with high computational cost are generally used because they avoid brute force attacks and Rainbow Tables generation, lower computational cost hashes are commonly used in other applications such as network file conferencing and database (in this case even less is important to have or not collision).