FreeTDS Development Group

[Frediano Ziglio <freddyz77 AT tin.it>]

Il giorno sab, 11/03/2006 alle 01.50 -0800, John Wythe (work) ha
scritto:
> 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.
> 

Specification do not speak about already quoted identifiers. You should
use "{}" style quoting for SQLDriverConnect (as correctly documented)

> There are two bugs:
> a) tdsfree needs to quote identifiers that begin with a digit.

Fixed many time ago in 0.64 (which should had been released on
January...).

> 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;
> }
> 

Current code

/**
 * 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;

        CHECK_TDS_EXTRA(tds);

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

        /* need quote ?? */
        for (i = 0; i < idlen; ++i) {
                char c = id[i];

                if (c >= 'a' && c <= 'z')
                        continue;
                if (c >= 'A' && c <= 'Z')
                        continue;
                if (i > 0 && c >= '0' && c <= '9')
                        continue;
                if (c == '_')
                        continue;
                return tds_quote(tds, buffer, TDS_IS_MSSQL(tds) ? ']' :
'\"', id, idlen);
        }

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

bye
  freddy77