Bug #784 encrypt with salt fails under windows, libcrypt.a under cygwin helps?
Submitted: 2 Jul 2003 12:05 Modified: 20 Nov 2005 21:49
Reporter: Ralf Hauser Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Documentation Severity:S2 (Serious)
Version:Ver 4.0.13-nt OS:Microsoft Windows (win32)
Assigned to: CPU Architecture:Any
Triage: D4 (Minor)

[2 Jul 2003 12:05] Ralf Hauser
Description:
Under unix doing the passwords like the below appears to be a standard approach.
Does having /usr/lib/libcrypt.a by cygwin help? If so, pls enhance the documentation accordingly!

Thx Ralf
====================================================
mysql> update TBL_USER set password= encrypt('holderio', '$1$saltsalt$') where login= 'ameyer';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select encrypt( 'testpassword', password) = password from TBL_USER where login='ameyer';
+-----------------------------------------------+
| encrypt( 'testpassword', password) = password |
+-----------------------------------------------+
|                                             0 |
+-----------------------------------------------+
1 row in set (0.00 sec)

mysql> select encrypt( 'holderio', password) = password from TBL_USER where login='ameyer';
+-------------------------------------------+
| encrypt( 'holderio', password) = password |
+-------------------------------------------+
|                                         1 |
+-------------------------------------------+
1 row in set (0.00 sec)

How to repeat:
under windows, it fails.

Suggested fix:
enhance the documentation how to configure mysqld to benefit from libcrypt.a or /usr/bin/crypt.exe accordingly
[3 Jul 2003 17:22] Michael Widenius
I extended the manual documentation a bit:

If crypt() is not available on your system, ENCRYPT() always
returns NULL.  Becasue of this we recommend you to use MD5()
or SHA1() instead as these exists in all platforms.

The above is a much better solution than try to get crypt() from cygwin to work with MySQL.
[3 Jul 2003 23:04] Ralf Hauser
1) I do have crypt!
Microsoft Windows 2000 [Version 5.00.2195]
(C) Copyright 1985-2000 Microsoft Corp.

C:\>crypt
usage: crypt passwd
       crypt salt passwd

C:\>crypt saltsalt holderio
samNJTG7AOcqs

C:\>

2) If I use MD5, does this also have the nice feature that both salt and digest get stored in the standard way in the database?

$1$saltsalt$i9cJkDelJCZ5584r0rxAQ/
[7 Jul 2003 0:06] Ralf Hauser
or would I rather have to look for cygcrypto.dll as per http://www.cygwin.com/ml/cygwin/2003-07/msg00252.html
[7 Jul 2003 0:09] Ralf Hauser
-rwxr-xr-x    1 rhauser  mkgroup    868352 Apr 11 12:34 /usr/bin/cygcrypto-0.9.7.dll
-rwxr-xr-x    1 rhauser  mkgroup    660480 Apr 11 12:37 /usr/bin/cygcrypto.dll
[7 Jul 2003 1:09] Sergei Golubchik
Sorry, but the bug system is not the appropriate forum for asking
support questions. Your problem is not the result of a bug.
For a list of more appropriate places to ask for help using MySQL
products, please visit http://www.mysql.com/support/

Thank you for your interest in MySQL.
[7 Jul 2003 2:47] Ralf Hauser
In the beginning, this was an RFE for more documentation. I hope, the next qualifies to be a useful piece that could go into the documentation:

From Gerrit P. Haase [gerrit@familiehaase.de], the latest binary mysql client executables and client library (static library only) separatly now (1.8 MB) can be obtained at:
http://anfaenger.de/cygwin/mysql/mysql-client-3.23.43-pc-cygwin-i586.tar.bz2 

gerrit.haase@convey.de
[7 Jul 2003 5:54] Ralf Hauser
There is also as per "show variables": 
| have_crypt    | NO
...
| have_openssl  | NO
...

However, it appears that they cannot be set:
set GLOBAL have_crypt=YES
Unknown system variable 'have_crypt'

Under linux, have_crypt is YES
[20 Jul 2003 3:06] Ralf Hauser
Gerrit has been able to compile a win32-nonCygwin version of mysqld that has a working "encrypt()" function - at least kind of working:
- we are not sure that it handles the salt properly
- the output is not compatible with the output of the linux version
But it correctly responds to the password verification requests. Thus programs doing password check will work both under linux and win32.

Here is how he did it:
<<In the config.h (the win32 version) change:
#undef HAVE_CRYPT
to
define HAVE_CRYPT 1

from the cygwin crypt package, take encrypt.h and encrypt.c, put encrypt.h in the'include' folder as crypt.h, enrcypt.c in the 'sql' folder (and if needed also in the "libmysqld" folder). In VisualC++ put encrypt.c in the corresponding project and then compile it.

Delete '#include <_ansi.h>' from (en)crypt.h and change the lines:

void _EXFUN(encrypt, (char *block, int edflag));
void _EXFUN(setkey, (char *key));
char * _EXFUN(crypt, (const char *key, const char *salt));

to:

void encrypt (char *block, int edflag);
void setkey (char *key);
char * crypt (const char *key, const char *salt);
>>
[14 Sep 2003 23:08] Ralf Hauser
Two findings:
1) Gerrit's windows version creates shorter entries into the password table:
  $1gywq6R87ibc
and not something like
  $1$bdfb1bad$1VuRKjCl.BHl/JXAkx0YE/
as per my July 3 comment.
2) also the mysql-provided linux version is working fine with the short password table entries (probably at the  cost of the password not really being salted!)!
[20 Oct 2003 6:51] Ralf Hauser
see also: http://bugs.mysql.com/1607
[13 Jan 2005 7:29] Ralf Hauser
also just for the record: use this approach ONLY with prepared-statements, otherwise,

select encrypt( '$1', password) = password from TBL_USER where
login='ameyer'

where $1 will be replaced with the user provided password can be misused with the following SQL-Attack:
User-input = "wrongPw',password) = password || FIND_IN_SET('$"
[16 Apr 2005 13:55] Ralf Hauser
For those who want this equally simple, but independent of the dbms in pure Java, see the "j-gnu-crypt-extension" package on https://sourceforge.net/projects/privasphere/
[20 Nov 2005 21:49] Ralf Hauser
see also http://bugs.mysql.com/bug.php?id=15086 for using a similar mechanism for AES initialization vectors.