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.

Answer 1

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.

Answer 2

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"; }