32

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

Timo Huovinen
  • 53,325
  • 33
  • 152
  • 143

4 Answers4

53

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.

Darren Cook
  • 27,837
  • 13
  • 117
  • 217
  • 2
    @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. –  May 09 '13 at 21:46
  • 1
    @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. – smudge May 10 '13 at 00:19
  • 1
    @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. –  May 10 '13 at 08:00
  • @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. – smudge May 10 '13 at 22:43
  • I think there are some words that are not possible as a SQL column name, even if enclosed. – Sliq Jul 15 '13 at 09:56
  • 1
    @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. – Timo Huovinen Jun 12 '14 at 06:42
  • This can be further refined by using the concise character class syntax, ie. `^[a-zA-Z_]\w*$` – Dennis Bauszus Jul 26 '23 at 10:39
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";
}
JamesHalsall
  • 13,224
  • 4
  • 41
  • 66
0

Use

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

Vivek Goel
  • 22,942
  • 29
  • 114
  • 186
-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.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
  • good answer, except how would you check for such a alphanumeric string that cant have the first character be a number? – Timo Huovinen Feb 12 '11 at 12:07
  • 1
    I would never check, dude. To create a database table dynamically, based on some unknown source, would be **least thing I would do ever**. – Your Common Sense Feb 12 '11 at 12:11
  • 1
    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 – Timo Huovinen Feb 12 '11 at 12:13
  • 1
    @YuriKolovsky: you can use anything as name if you enclose it with ` in mysql. – zerkms Feb 12 '11 at 12:16
  • doesn't your module get these names from datbaase already? why to check then? – Your Common Sense Feb 12 '11 at 12:17
  • @Sharpnel why would it get the names? thats another sql request just for double-check? but I see the question stems from the flaw in my mind that I thought that columns were limited in characters or combinations or had reserved names. @zerkms thanks, I was not aware of this. – Timo Huovinen Feb 12 '11 at 12:19
  • 1
    @YuriKolovsky: you should rather use `"` instead of the backticks to quote identifiers if you want this to work on multiple DBMS –  Feb 12 '11 at 13:16
  • 2
    @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: '' – Mark E. Haase Dec 13 '11 at 16:43
  • @zerkms also obviously you can't use backticks in the name. – Apostle Jul 31 '13 at 08:07
  • 3
    @Apostle: just double it and it will work "create table \`te``st\` (c int);" – zerkms Jul 31 '13 at 08:35
  • @zerkms Sorry! Really, this query `mysql> SELECT 123 AS ```test`````;` show the name of the field as `test``. – Apostle Jul 31 '13 at 11:22