检查有效的SQL列名称 [英] Check for valid SQL column name

问题描述

您将如何在php中检查字符串是sql语句的有效兼容列名称? 只是一个字符串匹配.

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

推荐答案

一旦每个字符串用双引号引起来，它最终就是一个有效的列名(根据配置，MySQL可能不遵守该规则.它不使用double引号作为默认安装中的标识符引号.

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).

但是，如果要跨平台使用(如不同的DBMS标签所建议的那样)，则应检查最小公分母.

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

PostgreSQL手册有很好的定义其中:

SQL标识符和关键字必须以字母(a-z，但带有变音符号和非拉丁字母的字母)或下划线(_)开头.标识符或关键字中的后续字符可以是字母，下划线，数字(0-9)或美元符号($).请注意，根据SQL标准字母，不允许在标识符中使用美元符号，因此使用美元符号可能会使应用程序的可移植性降低

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:

• 以字母开头
• 仅包含字符(字母)和数字以及下划线

因此，如下所示的正则表达式应涵盖以下内容:

So a regular expression like the following should cover this:

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

由于SQL不区分大小写(除非使用双引号，所以允许使用大小写字母.

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

这篇关于检查有效的SQL列名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！