Check for valid SQL column name

Question

How would you check in php that a string is a valid compatible column name for a sql statement? just a string match.

What do you mean by valid? Do you mean that string exists as a column in DB table? or Do you mean that does database support this string as a column name? — Shakti Singh
– Shakti Singh, Commented Feb 12, 2011 at 11:59

Darren Cook · Accepted Answer · 2013-01-18 09:01:20Z

56

Ultimately every string is a valid column name once it is enclosed in double quotes (MySQL might not obey to that rule depending on the configuration. It does not use double quotes as identifier quotes in the default installation).

However if you want to be cross platform (as the different DBMS tags suggest), you should check for the least common denominator.

The PostgreSQL manual has a nice definition of this:

SQL identifiers and key words must begin with a letter (a-z, but also letters with diacritical marks and non-Latin letters) or an underscore (_). Subsequent characters in an identifier or key word can be letters, underscores, digits (0-9), or dollar signs ($). Note that dollar signs are not allowed in identifiers according to the letter of the SQL standard, so their use might render applications less portable

So you should check the following with a regular expression:

starts with a letter
only contains characters (letters) and digits and an underscore

So a regular expression like the following should cover this:

^[a-zA-Z_][a-zA-Z0-9_]*$

As SQL is not case sensitive (unless double quotes are used) upper and lower case letters are allowed.

edited Jan 18, 2013 at 9:01

Darren Cook

29.2k13 gold badges127 silver badges239 bronze badges

answered Feb 12, 2011 at 12:28

user330315

Sign up to request clarification or add additional context in comments.

7 Comments

user330315 Over a year ago

@Smudge: unicode characters aren't allowed for non-quoted identifiers. And once you use a quoted identifier you don't need a regex, because anything is allowed when you quote the name.

smudge Over a year ago

@a_horse_with_no_name Okay, makes sense. What I was actually thinking was that it doesn't support the supposed "non-Latin letters" and "letters with diacritical marks" which that PostgreSQL quote claims as valid. Today I just happened to be hitting a similar regex string which also affected Unicode support.

user330315 Over a year ago

@Smudge: I recommend to stay away from any name that needs quoting. If you restrict yourself to "ASCII only" names, you will have lot less trouble in the long run.

smudge Over a year ago

@a_horse_with_no_name I agree that it's less troublesome, but if you're building a database-connected system (cross-platform or otherwise) and you want it to be used internationally, validating against non-ASCII characters is a real benefit, no matter how troublesome. Sure, delimited identifiers usually solve the problem, but there are some cases (for instance, function parameters) where using quotes or brackets is not possible, but where Unicode is still supported on some platforms. So it's an interesting problem to solve. My point was simply that the [a-zA-Z] regex won't always cut it.

Timo Huovinen Over a year ago

@a_horse_with_no_name I agree that you should avoid names that need quoting but even with your regex names like "order" and "group" would still need quoting.

|

JamesHalsall · Accepted Answer · 2011-02-12 12:00:38Z

1

You can use the MySQL query as follows to get the fields from a particular table:

SHOW FIELDS FROM tbl_name

and then some simple PHP:

$string_to_check = 'sample';
$valid = false;
$q = mysql_query("SHOW FIELDS FROM tbl_name");
while($row = mysql_fetch_object($q)) {
  if($row->Field == $string_to_check) {
     $valid = true; break;
  }
}
if($valid) {
  echo "Field exists";
}

answered Feb 12, 2011 at 12:00

JamesHalsall

13.5k4 gold badges43 silver badges68 bronze badges

Comments

Vivek Goel · Accepted Answer · 2011-02-12 11:59:05Z

0

Use

Either use show columns or describe query. and than validate from the result.

answered Feb 12, 2011 at 11:59

Vivek Goel

24.4k30 gold badges119 silver badges194 bronze badges

Comments

Your Common Sense · Accepted Answer · 2011-02-12 12:06:06Z

-3

If i'd had the same question, I'd search particular database documentation for the certain character list and then implement it in the form of regexp.

But I would never face such a question because basic latin characters, numbers and underscore are more than enough to name any field I use. So I'd keep great portability and maintainability.

answered Feb 12, 2011 at 12:06

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

11 Comments

Your Common Sense Over a year ago

I would never check, dude. To create a database table dynamically, based on some unknown source, would be least thing I would do ever.

Timo Huovinen Over a year ago

I'm not creating a table dynamically, I'm simply making sure in a independent module that the internal input variable looks like a column name, you know, assertions

zerkms Over a year ago

@YuriKolovsky: you can use anything as name if you enclose it with ` in mysql.

Mark E. Haase Over a year ago

@zerkms you can't literally use anything as a table name, e.g. create table `💩` (`id` int not null); ERROR 1300 (HY000): Invalid utf8 character string: '💩'

zerkms Over a year ago

@Apostle: just double it and it will work "create table `te``st` (c int);"

|

Collectives™ on Stack Overflow

Check for valid SQL column name

4 Answers 4

7 Comments

Comments

Comments

11 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

7 Comments

Comments

Comments

11 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related