FreeTDS Development Group

["John Wythe (work)" <jwythe AT silksystems.com>]

The is a discrepancy in the way freetds handles quoted/quoting identifiers as 
compared to MS SQL's ODBC windows driver.

In particular I am having a problem when I set the DATABASE field,  of the 
connection string passed to SQLDriverConnect, to 210001.  I tried setting it 
to [210001], but that didn't work either.  On Windows with MS SQL's ODBC 
driver both work just fine.

There are two bugs:
a) tdsfree needs to quote identifiers that begin with a digit.
b) tdsfree should not quote an identifier that is already quoted with []

Here is the fix I came up with.  src/tds/query.c

BEFORE:
/**
 * Quote an id
 * \param tds    state information for the socket and the TDS protocol
 * \param buffer buffer to store quoted id. If NULL do not write anything
 *        (useful to compute quote length)
 * \param id     id to quote
 * \param idlen  id length
 * \result written chars (not including needed terminator)
 */
int
tds_quote_id(TDSSOCKET * tds, char *buffer, const char *id, int idlen)
{
    int i;

    if (idlen < 0)
        idlen = strlen(id);

    /* need quote ?? */
    for (i = 0; i < idlen; ++i)
        switch(id[i]) {
        case '\"':
        case '\'':
        case ' ':
        case '(':
        case ')':
        case '[':
        case ']':
        case '{':
        case '}':
            return tds_quote(tds, buffer, TDS_IS_MSSQL(tds) ? ']' : '\"', id, 
idlen);
        }

    if (buffer) {
        memcpy(buffer, id, idlen);
        buffer[idlen] = '\0';
    }
    return idlen;
}

AFTER:
/**
 * Quote an id
 * \param tds    state information for the socket and the TDS protocol
 * \param buffer buffer to store quoted id. If NULL do not write anything
 *        (useful to compute quote length)
 * \param id     id to quote
 * \param idlen  id length
 * \result written chars (not including needed terminator)
 */
int
tds_quote_id(TDSSOCKET * tds, char *buffer, const char *id, int idlen)
{
    int i;
    int need_quote = 0;
    int quoted = 0;

    if (idlen < 0)
        idlen = strlen(id);

/* identifiers that begin with a digit need quoting jwythe AT silksystems.com */
/* don't quote id's that are already quoted */
/* let's assume quoted_identifier is off for MS SQL */
/* not sure what this change will do to sybase, so let's only do for MS SQL */
    /* need quote ?? */
    if (TDS_IS_MSSQL(tds))
        {
        if (isdigit(*id))
            need_quote = 1;
        else
            if (*id == '[' && id[idlen-1] == ']')
                quoted = 1;
        }

    if (!quoted)
        {
        for (i = 0; i < idlen && !need_quote; ++i)
            switch(id[i]) {
            case '\"':
            case '\'':
            case ' ':
            case '(':
            case ')':
            case '[':
            case ']':
            case '{':
            case '}':
                need_quote = 1;
            }

        if (need_quote)
            return tds_quote(
                tds,
                buffer,
                TDS_IS_MSSQL(tds) ? ']' : '\"',
                id,
                idlen);
        }

    if (buffer) {
        memcpy(buffer, id, idlen);
        buffer[idlen] = '\0';
    }
    return idlen;
}


Regards

John