VFP MySQL String Connect Issue

Hi all.

I am using VFP 9 connecting to MySQL with the MySQL ODBC driver v3.51 and am having the following issue.

If a password to a database contains non-alphanumeric characters, for example a ';' or '[' the connection will fail. If I change the password to the db to just characters and numbers the connection succeeds.

Other programs that connect to the database can use the original password containing the special characters, so I know the issue lies within the VFP/ODBC connection.

I was just wondering if anyone else has experienced this and if there is a known workaround.
Who is Participating?
Olaf DoschkeSoftware DeveloperCommented:
I can only say this works:

#Define ccConnectionString   "DRIVER={SQL Server};SERVER=...;USR=testuser;PWD='[]{}(),;?*!@123';DATABASE=..."
lnHandle = Sqlstringconnect(ccConnectionString,.T.)
? lnHandle>0 && outputs .T., which in VFP means true. And if a handle returned is > 0 that means success.

That password contains all the "forbidden" chars that should not work by what the article said, but it works.

If you can set a password when adding a login or user in SQL Server (or in MySQL), you can also send it by Connectionstring, you just have to put the password in quotes, so the ODBC driver parses the connection string correctly.

Whatever you say is not true, not for all ODBC drivers at least. I don't have a MySQL server at hand here and right now, but it should work the same way, just put the password in single quotes.

PS: To make the point clear: The password defined was just []{}(),;?*!@123 without the single quotes. You can specify the password with or without single quote delimiters in a connection string, which just helps the parser, the single quotes are not becoming part of the password. This compares to the alternatives you have in html putting attribute values in quotes or not. So a semicolon makes problems, if it's unquoted, as in that case that semicolon is taken as end delimiter of the password instead of a part of it, that's all about it.

Bye, Olaf.
Dave BaldwinFixer of ProblemsCommented:
From http://msdn.microsoft.com/en-us/library/ms161962.aspx ...
If used in an OLE DB or ODBC connection string, a login or password must not contain the following characters: [] {}() , ; ? * ! @. These characters are used to either initialize a connection or separate connection values.
The ODBC interface does not provide every resource that the database has available and has limits on what it does provide.
Olaf DoschkeSoftware DeveloperCommented:
I wonder if this is just stated for MSSQL Server, but ; also is a seperator in connection strings to denote the end of one config section as in "server=localhost;db=test;..."

What you can try is putting a password with such chars in quotes:

Bye, Olaf.
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Dave BaldwinFixer of ProblemsCommented:
The problem is with the ODBC interface, not the driver.  You can look here http://www.connectionstrings.com/ and see that all of the ODBC connection strings use punctuation as delimiters in the connection strings.
Dave BaldwinFixer of ProblemsCommented:
I'm glad you tested that.  I have never seen an example where quotes were used before.
formadmirerAuthor Commented:
It seems to be very finicky, very hit and miss about what it will accept and not accept.
I created an account and tried these passwords, both with and without single quotes.



The top two passwords were accepted both with and without quotes, even though they contain illegal characters.

The third password, Olaf's example, I could not get to work no matter what I did.

I think I'll just scan the passwords chosen for illegal chars and suggest they choose another.

thanks for all the help!
Olaf DoschkeSoftware DeveloperCommented:
Just notice, I checked this with an SQL Server. In regard to the article mentioned this should not work, especially with SQL Server. And in regard to David not with any ODBC driver.

The MySQL docs also don't mention allowed or disallowed chars, but you find somthing in the bugtracking list: http://bugs.mysql.com/bug.php?id=52404

As you say other applications or clients can login the error has to be either the old ODBC version (we have ODBC 5.1.11) or it's even chars you can't write into an ANSI Codepage 1252 encode foxpro prg. If it's the latter, there are ways to convert eg unicode chars to their hex representation and generate the unicode string in FoxPro programmatically, instead of using a string literal for the connection string.

Bye, Olaf.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.