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

https://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]

Re: Valid MySQL database/table/column name regexp


On 15-Aug-2003, Martin Lucas-Smith <mvl22@cam.ac.u k> wrote:
[color=blue]
> 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.
>[/color]

http://www.mysql.com/doc/en/Legal_names.html

--
Tom Thackrey

https://www.creative-light.com

[matty]

Re: Valid MySQL database/table/column name regexp

Tom Thackrey wrote:
[color=blue]
>
> On 15-Aug-2003, Martin Lucas-Smith <mvl22@cam.ac.u k> wrote:
>[color=green]
>> 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?
>>[/color][/color]

You could always do

if ($fieldname == adslashes($fiel dname))

[Andy Hassall]

Re: Valid MySQL database/table/column name regexp

On Fri, 15 Aug 2003 18:44:56 +0100, Martin Lucas-Smith <mvl22@cam.ac.u k> wrote:
[color=blue]
>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.[/color]

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.
[color=blue]
>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.[/color]

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).
[color=blue]
>In the longer term, I plan some sort of entity conversion script so that
>theoreticall y any character could be used, using some sort of escaping
>mechanism probably.
>
>Can anyone supply or suggest routes to such a regexp?[/color]

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