[FQuest Announce] MySQL Account MGMT Now Available To ALL!

[dank]

Believe it or not, I was waiting for this question to be asked, along the lines of why those delimiter characters were chosen...

Happy to not disappoint.

I wasn't aware of all the escapism issues surrounding the wild and free spirit known as the underscore... Makes good sense, in that light.

So ah, it's got to be Dan's got a phpMyAdmin problem ay?

Actually, no... It was, ahem, some hack programmer's coding sans back ticks. Adding them in does indeed circumvent the error, which I guess is somewhat debatable if it's actually an error or just a hidden gotcha.

Dan
-- need problems found, call 1-800-386-2277, need problems solved, call Terra or Paul

[Terra]

After digging into the MySQL source code, I find that I don't have many options available for characters to use for identifiers that don't need to be quoted...

From mysql/sql/sql_lex.c

void lex_init(void)
{
  uint i;
  DBUG_ENTER("lex_init");
  for (i=0 ; i < array_elements(symbols) ; i++)
    symbols[i].length=(uchar) strlen(symbols[i].name);
  for (i=0 ; i < array_elements(sql_functions) ; i++)
    sql_functions[i].length=(uchar) strlen(sql_functions[i].name);

  VOID(pthread_key_create(&THR_LEX,NULL));

  /* Fill state_map with states to get a faster parser */
  for (i=0; i < 256 ; i++)
  {
    if (isalpha(i))
      state_map[i]=(uchar) STATE_IDENT;
    else if (isdigit(i))
      state_map[i]=(uchar) STATE_NUMBER_IDENT;
#if defined(USE_MB) && defined(USE_MB_IDENT)
    else if (use_mb(default_charset_info) && my_ismbhead(default_charset_info, i))
      state_map[i]=(uchar) STATE_IDENT;
#endif
    else if (!isgraph(i))
      state_map[i]=(uchar) STATE_SKIP;      
    else
      state_map[i]=(uchar) STATE_CHAR;
  }
  state_map[(uchar)'_']=state_map[(uchar)'$']=(uchar) STATE_IDENT;
  state_map[(uchar)'\'']=state_map[(uchar)'"']=(uchar) STATE_STRING;
  state_map[(uchar)'-']=state_map[(uchar)'+']=(uchar) STATE_SIGNED_NUMBER;
  state_map[(uchar)'.']=(uchar) STATE_REAL_OR_POINT;
  state_map[(uchar)'>']=state_map[(uchar)'=']=state_map[(uchar)'!']= (uchar) STATE_CMP_OP;
  state_map[(uchar)'<']= (uchar) STATE_LONG_CMP_OP;
  state_map[(uchar)'&']=state_map[(uchar)'|']=(uchar) STATE_BOOL;
  state_map[(uchar)'#']=(uchar) STATE_COMMENT;
  state_map[(uchar)';']=(uchar) STATE_COLON;
  state_map[(uchar)':']=(uchar) STATE_SET_VAR;
  state_map[0]=(uchar) STATE_EOL;
  state_map[(uchar)'\\']= (uchar) STATE_ESCAPE;
  state_map[(uchar)'/']= (uchar) STATE_LONG_COMMENT;
  state_map[(uchar)'*']= (uchar) STATE_END_LONG_COMMENT;
  state_map[(uchar)'@']= (uchar) STATE_USER_END;
  state_map[(uchar) '`']= (uchar) STATE_USER_VARIABLE_DELIMITER;
  if (opt_sql_mode & MODE_ANSI_QUOTES)
  {
    state_map[(uchar) '"'] = STATE_USER_VARIABLE_DELIMITER;
  }
  DBUG_VOID_RETURN;
}

The important items of this subroutine are:

if (isalpha(i))
state_map[i]=(uchar) STATE_IDENT;
else if (isdigit(i))
state_map[i]=(uchar) STATE_NUMBER_IDENT;
-and-
state_map[(uchar)'_']=state_map[(uchar)'$']=(uchar) STATE_IDENT;
-and-
if (opt_sql_mode & MODE_ANSI_QUOTES)
{
state_map[(uchar) '"'] = STATE_USER_VARIABLE_DELIMITER;
}

What this all boils down to are that the following characters are parsed as identifiers:
1) a-z
2) A-Z
3) 0-9
4) _
5) $
6) "

Regarding #6, that is only a recent addition to the MySQL 4.x server, therefore we cannot use it yet across the board...

The above 5 sets of characters are the only ones I can use to delimit the 'xdom' name from your chosen 'database' name...

To analyze using the format: xexample[]database
1) xexampleadatabase
2) xexampleAdatabase
3) xexample1database
**These only lead to ambiguity and make parsing pretty much impossible

4) xexample_database
**This is a security risk because in the GRANT table, the '_' is considered a wildcard character...

5) xexample$database
**This wreaks havoc for command line administrative scripts since you are joining the 'xdom' with a potential variable name... This one is just plain dangerous and is a huge accident waiting to happen...

state_map[(uchar) '`']= (uchar) STATE_USER_VARIABLE_DELIMITER;

This is special in that when the 1st '`' character is seen, the lexer state machine simply chooses to not apply further states until it exits from the STATE_USER_VARIABLE_DELIMITER state when it sees the 2nd '`'...

Now a little background information on MySQL and its language grammer and why it is not easy (or next to impossible) to workaround... MySQL language is expressed with the Bison lexer... One of the criteria for Bison (and yacc) to be able to parse a language, is that the language must be described in a 'context-free grammer'... In other words, if you type in:
CREATE DATABASE xexample-foobar;

You cannot have an expectant context that the next word after the 'CREATE DATABASE' command is supposed to be an identifier (database name) as it could also be a legal expression... Which means that I cannot override the '-' being seen as a special character, because to do so states that I must enforce context...

At this point there is just simply no good choice for delimiters as stipulated by MySQL syntax as my hands are tied by the core language itself... It appears now that using quoted identifiers will be a necessary evil that will work within the guidelines of the SQL language...

--
Terra
--hijacking the SQL language is simply not a viable solution--
FutureQuest