Mysql: need to escape column names

jeanluca has asked for the wisdom of the Perl Monks concerning the following question:

Dear Monks

I have this situation in which the user specifies the column name and definition, so I can create a table. So I have to escape both!
The only function to escape special characters I know about is quote. But this functions adds quotes around the string, which I don't need. Also the column names can have spaces so I need to add ` around them.For column names this now gives: `'my col name'`.
Any suggestions how to fix this ?

cheers
LuCa

Comment on Mysql: need to escape column names

Replies are listed 'Best First'.
Re: Mysql: need to escape column names by moritz (Cardinal) on Aug 07, 2010 at 23:20 UTC
You probably want `$sth->quote_identifer` instead of `quote`. Perl 6 - links to (nearly) everything that is Perl 6.	[reply] [d/l] [select]
Re^2: Mysql: need to escape column names by jeanluca (Deacon) on Aug 08, 2010 at 08:27 UTC
Yep, thats it for the identifiers!! One last thing, is there also a solution for the definition of the column ? Because the user defines the column name and its definition (for example: VARCHAR(100) NOT NULL) What would be the best way to process these ?	[reply]
Re: Mysql: need to escape column names by NetWallah (Canon) on Aug 08, 2010 at 00:46 UTC
From http://www.ispirer.com/doc/sqlways39/Output/SQLWays-1-038.html: "In MySQL delimited identifier is an identifier enclosed in back ticks (`) by default. When MySQL is running in ANSI double quotes (" ) will also work to quote identifiers. Delimited identifier cannot contain ASCII(0), ASCII(255) or the quoting character. If the delimited identifier is a reserved word or contains special characters, you must always quote it with a ` (back tick). " I have previously used square brackets for this on psql, jet, and SQL server. Syntactic sugar causes cancer of the semicolon. --Alan Perlis	[reply]
Re: Mysql: need to escape column names by aquarium (Curate) on Aug 09, 2010 at 01:05 UTC
The easiest and in my opinion best solution is to not allow special characters in column names. Otherwise you'll have to always be on the lookout for column names that break SQL standard or break MySQL at some stage. So to avoid your program getting criticised later (by users), only allow a-zA-Z and underscore. It will be difficult to change to this precautionary naming later, when you already have many funky name columns in tables. the hardest line to type correctly is: stty erase ^H	[reply]