Beefy Boxes and Bandwidth Generously Provided by pair Networks
go ahead... be a heretic
 
PerlMonks  

Mysql: need to escape column names

by jeanluca (Deacon)
on Aug 07, 2010 at 19:35 UTC ( #853593=perlquestion: print w/ replies, xml ) Need Help??
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
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.
      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 ?
Re: Mysql: need to escape column names
by NetWallah (Abbot) 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

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

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://853593]
Approved by ahmad
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others scrutinizing the Monastery: (5)
As of 2014-07-10 01:51 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    When choosing user names for websites, I prefer to use:








    Results (198 votes), past polls