VFP MySQL String Connect Issue

Posted on 2012-09-12
Last Modified: 2012-09-14
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.
Question by:formadmirer
    LVL 82

    Assisted Solution

    by:Dave Baldwin
    From ...
    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.
    LVL 29

    Expert Comment

    by:Olaf Doschke
    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.
    LVL 82

    Expert Comment

    by:Dave Baldwin
    The problem is with the ODBC interface, not the driver.  You can look here and see that all of the ODBC connection strings use punctuation as delimiters in the connection strings.
    LVL 29

    Accepted Solution

    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.
    LVL 82

    Expert Comment

    by:Dave Baldwin
    I'm glad you tested that.  I have never seen an example where quotes were used before.

    Author Comment

    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!
    LVL 29

    Expert Comment

    by:Olaf Doschke
    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:

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Microsoft Visual FoxPro (short VFP) is a programming language with it’s own IDE and database, ranking somewhat between Access and VB.NET + SQL Server (Express). Product Description: (http://msd…
    ADCs have gained traction within the last decade, largely due to increased demand for legacy load balancing appliances to handle more advanced application delivery requirements and improve application performance.
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now