MySQL Bugs: #45378: SQLDriverConnect can't connect to DB with connection string

Bug #45378	SQLDriverConnect can't connect to DB with connection string
Submitted:	8 Jun 2009 16:11	Modified:	24 May 2010 12:23
Reporter:	York Lo	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	5.1.5	OS:	Linux (RedHat EL v3)
Assigned to:		CPU Architecture:	Any

Description:
If the program is invoked by root user, SQLDriverConnect() can't use user name and password from connection string to connect to database.  The following is the error from connection:
retcode=-1, DriverConnect Failed.
SQLDriverConnect failed with ret=0, errText=[MySQL][ODBC 5.1 Driver]Access denied for user 'root'@'localhost' (using password: NO).

However, if user and password information is defined in the odbc.ini file, then it has no problem to connect to db.

More strangely, if the program is invoked by a normal user, SQLDriverConnect can use user and password information from the connection string to connect to DB.

The test system I used is:
uname -a
Linux nc-linux1 2.4.21.-27.EL #1 Wed Dec 1 22:08:15 EST 2004 i686 i686 i386 GNU/Linux

Also, I have the following relevant softwares:
MySQL: 5.1.31 MySQL Community Server
MySQL Connector/ODBC: 5.1.5
unixODBC: 2.2.14 (only libodbcint.so library).

How to repeat:
The following is the simple test program to repeat the problem on my linux system:
----------------
#include <stdio.h>
#include <sqltypes.h>
#include <sql.h>
#include <sqlext.h>

int main() {
  SQLHENV henv;
  SQLHDBC hdbc;
  SQLHSTMT hstmt;
  SQLRETURN retcode;
  SQLPOINTER rgbValue;
  int i = 5;
  rgbValue = &i;

  SQLCHAR connStr[] = {"dsn=MyODBC; user=nervectr; password=nervectr;"};

  SQLCHAR OutConnStr[255];
  SQLSMALLINT OutConnStrLen;

  // Allocate environment handle
  retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);

  // Set the ODBC version environment attribute
  if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
    retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER*)SQL_OV_ODBC3, 0); 

    // Allocate connection handle
    if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
      retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc); 

      // Set login timeout to 5 seconds
      if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
	SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)(rgbValue), 0);

	retcode = SQLDriverConnect( // SQL_NULL_HDBC
				   hdbc, 
				   NULL, 
				   connStr, 
				   SQL_NTS,
				   OutConnStr,
				   255, 
				   &OutConnStrLen,
				   SQL_DRIVER_NOPROMPT );

	// Allocate statement handle
	if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {               
	  retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt); 

	  // Process data
	  retcode = SQLGetInfo(hdbc, SQL_DATABASE_NAME, OutConnStr, 255, &OutConnStrLen);
	  printf ("ret=%d, SQLGetInfo: retStr=%s. \n", retcode, OutConnStr);

	  if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
	    SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
	  }

	  SQLDisconnect(hdbc);
	} 
	else {
	  printf("retcode=%d, DriverConnect Failed.\n", retcode);

	  SQLCHAR sqlstate[255];
	  SQLINTEGER nativeErrorPtr;

	  retcode = SQLGetDiagRec(SQL_HANDLE_DBC, hdbc, 1, sqlstate, &nativeErrorPtr, OutConnStr, 255, &OutConnStrLen);
	  printf("SQLDriverConnect failed with ret=%d, errText=%s.\n", retcode, OutConnStr);
	  
	}
       

	SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
      }
    }
    SQLFreeHandle(SQL_HANDLE_ENV, henv);
  }
}
-----------------
The above program is compiled with the following command:
g++ -ggdb -lodbc test.cpp

The odbc.ini file contains the following information:
[ncdev@nc-linux1 odbctest]$ cat /opt/OSInc/db/unixODBC/etc/odbc.ini
[ODBC Data Sources]
MyODBC          = MyODBC 5.1.5 Driver DSN
 
[MyODBC]
Driver          = /opt/OSInc/db/mysqlODBC/lib/libodbc.so
Description     = MyODBC 5.1.5 Driver DSN
SERVER          = localhost
PORT            = 4000
Database        = nervecenter
OPTION          = 3
SOCKET          = /opt/OSInc/db/mysql/data/mysql.sock
TraceFile       = /opt/OSInc/db/odbc.trace
Trace           = On
 
[Default]
Driver          = /opt/OSInc/db/mysqlODBC/lib/libodbc.so
Description     = MyODBC 5.1.5 Driver DSN
SERVER          = localhost
PORT            = 4000
Database        = nervecenter
SOCKET          = /opt/OSInc/db/mysql/data/mysql.sock
TraceFile       = /opt/OSInc/db/odbc.trace
Trace           = On

Thank you very much to look at this problem.  If you need any additional information, please let me know.

Hi York and thanks for your report.

Just a few quick questions. Does "normal" user have the *same* name as MySQL user? Are you sure you have only libodbcinst library? It appears to me your compile command would failed in that case...

Hi Tonci,

Normal user is not the same as MySQL user.  In my testing case, the "normal" user is ncdev, and MySQL's user is nervectr.

For compiling/linking, it is used the libs from /usr/local/lib.  To ensure that I am using the right libs, I modified the g++ command line as following:
g++ -ggdb -L/opt/OSInc/db/lib -lodbc -lodbcinst test.cpp

after compile, the following is the ldd result:
ldd a.out
        libmyodbc5-5.1.5.so => /opt/OSInc/db/lib/libmyodbc5-5.1.5.so (0x00627000)
        libodbcinst.so.1 => /opt/OSInc/db/lib/libodbcinst.so.1 (0x00838000)
        libstdc++.so.5 => /usr/lib/libstdc++.so.5 (0x00da9000)
        libm.so.6 => /lib/tls/libm.so.6 (0x00b44000)
        libgcc_s.so.1 => /lib/libgcc_s.so.1 (0x00fc7000)
        libc.so.6 => /lib/tls/libc.so.6 (0x00111000)
        libdl.so.2 => /lib/libdl.so.2 (0x003cf000)
        libcrypt.so.1 => /lib/libcrypt.so.1 (0x00249000)
        libnsl.so.1 => /lib/libnsl.so.1 (0x00f15000)
        libpthread.so.0 => /lib/tls/libpthread.so.0 (0x003f4000)
        /lib/ld-linux.so.2 => /lib/ld-linux.so.2 (0x00517000)

Again, if I am invoking the program as root, I am still getting the same error as I had reported.

Are you able to see the same problem?

Thank you for all helps.

York

Thanks for info provided, looking into this.

fix + test

Attachment: bug45378.diff (text/x-diff), 3.08 KiB.

This needs an additional fix to prevent a problem when the value is only spaces:
=== modified file 'util/installer.c'
--- util/installer.c    2009-06-16 11:58:20 +0000
+++ util/installer.c    2009-08-13 21:26:24 +0000
@@ -953,7 +953,7 @@

     /* remove trailing spaces on value (not escaped part) */
     len = end - split - 1;
-    while (split[len] == ' ' && split[len+1] != '}')
+    while (end > split && split[len] == ' ' && split[len+1] != '}')
     {
       len--;
       end--;

I am getting a strange error on W2K on a connection string that works perfectly in XP, Vista, 7, etc. When I removed the spaces between semicolons and the next attribute from connection string, it worked.

Even though I was not connecting to localhost, the error message was "Can't connect to MySQL server on 'localhost'". Would I be correct to assume that this patch will correct the problem?

yes, that is correct assumption.

An entry has been added to the 5.1.7 changelog:

If an application was invoked by the root user, SQLDriverConnect() was not able to use the username and password in the connection string to connect to the database.