CODEWITHSUNDEEP
mysqlencryption
chocolata
chocolata

Reputation: 3338

MySQL encrypt() with salt accepts random characters after password

I have a table with usernames and encrypted passwords.

The passwords are encrypted by means of MySQL encrypt() together with a salt (the first two characters of the password).

Recently I've noticed that MySQL accepts passwords even if they contain random characters at the end.

Suppose we have this kind of table:

SET NAMES utf8;
SET foreign_key_checks = 0;
SET time_zone = 'SYSTEM';
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `user` (`id`, `username`, `password`) VALUES
(11,    'ricardomontalban', ENCRYPT(11111111,11));

And now I query for my user:

-- The following shows the appropriate response --
SELECT * FROM user WHERE username = "ricardomontalban" AND password = ENCRYPT(11111111,11);

-- HOWEVER, the following query also shows a result, even with random characters appended!!! --
SELECT * FROM user WHERE username = "ricardomontalban" AND password = ENCRYPT("11111111-55669964s5465sqsfqsdf",11);

-- No problem with prepended random characters though --
SELECT * FROM user WHERE username = "ricardomontalban" AND password = ENCRYPT("smlkfjmlsdkfjslqf-11111111",11);

I've created an SQL Fiddle to show this example in real time: http://sqlfiddle.com/#!2/898d5/9

What am I doing wrong? Should I even be using this encryption method? Any suggestions are greatly appreciated.

Upvotes: 0

Views: 768

Answers (2)

Shawn Balestracci
Shawn Balestracci

Reputation: 7530

To answer your other questions:

Using the first two (or any) characters from the password as the salt is also wrong. The idea of the salt is to provide some randomness to the encrypted passwords so that users who have the same password will have different hashes.

Use an established encryption library (such as bcrypt) for whatever programming language you are using.

Upvotes: 1

Marc B
Marc B

Reputation: 360662

As per the documentation:

ENCRYPT() ignores all but the first eight characters of str, at least on some systems. This behavior is determined by the implementation of the underlying crypt() system call.

Upvotes: 4

Related Questions