postgresql 检查有效的 SQL 列名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4977898/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Check for valid SQL column name
提问by Timo Huovinen
How would you check in php that a string is a valid compatible column name for a sql statement? just a string match.
您将如何在 php 中检查字符串是否是 sql 语句的有效兼容列名？只是一个字符串匹配。
回答by a_horse_with_no_name
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).
最终，每个字符串一旦被双引号括起来就是一个有效的列名（MySQL 可能不遵守该规则，具体取决于配置。它在默认安装中不使用双引号作为标识符引号）。
However if you want to be cross platform (as the different DBMS tags suggest), you should check for the least common denominator.
但是，如果您想要跨平台（如不同的 DBMS 标签所建议的那样），您应该检查最小公分母。
The PostgreSQL manual has a nice definitionof this:
在PostgreSQL的手册有一个很好的定义的这样的：
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
SQL 标识符和关键字必须以字母（az，但也可以是带有变音符和非拉丁字母的字母）或下划线 (_) 开头。标识符或关键字中的后续字符可以是字母、下划线、数字 (0-9) 或美元符号 ($)。请注意，根据 SQL 标准的字母，标识符中不允许使用美元符号，因此它们的使用可能会使应用程序的可移植性降低
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_]*$
^[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.
由于 SQL 不区分大小写（除非使用双引号）允许大写和小写字母。
回答by JamesHalsall
You can use the MySQL query as follows to get the fields from a particular table:
您可以按如下方式使用 MySQL 查询从特定表中获取字段：
SHOW FIELDS FROM tbl_name
and then some simple PHP:
然后是一些简单的 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";
}
回答by Vivek Goel
Use
用
Either use show columns or describe query. and than validate from the result.
使用显示列或描述查询。然后从结果中验证。
回答by Your Common Sense
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.
但我永远不会遇到这样的问题，因为基本的拉丁字符、数字和下划线足以命名我使用的任何字段。所以我会保持很好的可移植性和可维护性。
