Valid MySQL database/table/column name regexp

[Martin Lucas-Smith]

Can anyone point me to a regular expression in PHP which could be used to
check that a proposed (My)SQL database/table/column name is valid, i.e.
shouldn't result in an SQL error when created?

The user of my (hopefully to be opensourced) program has the ability to
create database/table/column names on the fly.

I'm aware of obvious characters such as ., [space], things like >, etc.,
which won't work, but haven't been able to source a definitive list,
including having googled the MySQL site.

Obviously certain characters need to be filtered out, as noted above, but
I want to be as unrestrictive as possible; hence just [a-z]* isn't good
enough because things like _ are acceptable.

Ideally, I'd prefer a regexp that applies to _all_ vendors' databases, not
just MySQL as I'm about to migrate the program to being
database-independent, probably using PEAR DB, but even a MySQL-specific
regexp would do the job.

In the longer term, I plan some sort of entity conversion script so that
theoretically any character could be used, using some sort of escaping
mechanism probably.

Can anyone supply or suggest routes to such a regexp?
Martin Lucas-Smith www.geog.cam.ac.uk/~mvl22
www.lucas-smith.co.uk

Senior Computing Technician (Web Technician)
Department of Geography, University of Cambridge (01223 3)33390

& Webmaster, SPRI
Scott Polar Research Institute, University of Cambridge

[Tom Thackrey]

On 15-Aug-2003, Martin Lucas-Smith <mv***@cam.ac.uk> wrote:

Can anyone point me to a regular expression in PHP which could be used to
check that a proposed (My)SQL database/table/column name is valid, i.e.
shouldn't result in an SQL error when created?

The user of my (hopefully to be opensourced) program has the ability to
create database/table/column names on the fly.

I'm aware of obvious characters such as ., [space], things like >, etc.,
which won't work, but haven't been able to source a definitive list,
including having googled the MySQL site.

http://www.mysql.com/doc/en/Legal_names.html
--
Tom Thackrey
www.creative-light.com

[matty]

Tom Thackrey wrote:

On 15-Aug-2003, Martin Lucas-Smith <mv***@cam.ac.uk> wrote:

Can anyone point me to a regular expression in PHP which could be used to
check that a proposed (My)SQL database/table/column name is valid, i.e.
shouldn't result in an SQL error when created?

You could always do

if ($fieldname == adslashes($fieldname))

[Andy Hassall]

On Fri, 15 Aug 2003 18:44:56 +0100, Martin Lucas-Smith <mv***@cam.ac.uk> wrote:

Can anyone point me to a regular expression in PHP which could be used to
check that a proposed (My)SQL database/table/column name is valid, i.e.
shouldn't result in an SQL error when created?

The user of my (hopefully to be opensourced) program has the ability to
create database/table/column names on the fly.

I'm aware of obvious characters such as ., [space], things like >, etc.,
which won't work, but haven't been able to source a definitive list,
including having googled the MySQL site.
You can have spaces and all sorts of characters in a table and column name, if
you double quote it. But if you do so, they'd be a pain to work with. Sounds
like you're sensibly limiting it to valid identifiers that don't need quoting.
Obviously certain characters need to be filtered out, as noted above, but
I want to be as unrestrictive as possible; hence just [a-z]* isn't good
enough because things like _ are acceptable.

Ideally, I'd prefer a regexp that applies to _all_ vendors' databases, not
just MySQL as I'm about to migrate the program to being
database-independent, probably using PEAR DB, but even a MySQL-specific
regexp would do the job.
If you want something general, you're really best just sticking to:

[A-Za-z0-9_]+

In fact, it will be more restrictive than that; e.g. in Oracle an identifier
has to start with an alphabetic character (although it can be in Unicode...).

So perhaps:

[A-Za-z][A-Za-z0-9_]*

And then you have to watch out for reserved words, which differ between
databases, so you probably end up with a list of reserved words, and then a
regexp. And you also have to watch for the maximum length (64 in MySQL, 30 in
Oracle, others will vary).
In the longer term, I plan some sort of entity conversion script so that
theoretically any character could be used, using some sort of escaping
mechanism probably.

Can anyone supply or suggest routes to such a regexp?

--
Andy Hassall (an**@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)