Repository: sqlfluff/sqlfluff
Pull request: New rule to flag special characters in identifiers
Fixes #1746 

### Brief summary of the change made
A new linting rule: flag special characters in identifiers.

If an identifier includes a character other than alphanumeric or underscore, this rule will flag it.
This rule includes configuration settings to control which types of identifiers fall under the rule.
This rule also includes a configuration setting to allow or disallow spaces as part of an identifier.

+L057 python script
+L057 test YAML
+L057 configuration defaults
+allow_space_in_identifier description in config_info.py

Problem statement:
New rule suggestion: identify and flag special characters in object names
Should these sorts of issues be bugs or enhancements?

We should be able to identify and flag special characters in names when creating objects.

Anti-pattern:
```
CREATE TABLE DBO.ColumnNames
(
    [TrailingSpace ] INT,
    [Greater>Than] INT,
    [Less<Than] INT,
    Number# INT
)
```

Hints from issue discussion:
Enhancement I say. It’s asking for additional functionality not saying existing functionality doesn’t work.

Dialect missing things are more interesting. Is it a bug that we don’t support a syntax that’s a valid part of the language? Or an enhancement to add that support? Bug has the template to fill out though so probably better to use that. Enhancement has a fairly barebones template (perfect for this issue here!).
I don't know whether any dialect other than TSQL allows for such a thing, but if others do allow for it I think they should be flagged in those dialects as well.

Commit message: Rule: Special Characters in names: code review results

Patch to review:
diff --git a/src/sqlfluff/core/default_config.cfg b/src/sqlfluff/core/default_config.cfg
index 1805c701b57..1a4b2dbf985 100644
--- a/src/sqlfluff/core/default_config.cfg
+++ b/src/sqlfluff/core/default_config.cfg
@@ -96,3 +96,8 @@ require_final_semicolon = False
 
 [sqlfluff:rules:L054]  # GROUP BY/ORDER BY column references.
 group_by_and_order_by_style = consistent
+
+[sqlfluff:rules:L057]  # Special characters in identifiers
+unquoted_identifiers_policy = all
+quoted_identifiers_policy = all
+allow_space_in_identifier = False
diff --git a/src/sqlfluff/core/rules/config_info.py b/src/sqlfluff/core/rules/config_info.py
index 251ec001451..553dce55ede 100644
--- a/src/sqlfluff/core/rules/config_info.py
+++ b/src/sqlfluff/core/rules/config_info.py
@@ -124,6 +124,10 @@
             "or implicit positional references"
         ),
     },
+    "allow_space_in_identifier": {
+        "validation": [True, False],
+        "definition": ("Should spaces in identifiers be allowed?"),
+    },
 }
 
 
diff --git a/src/sqlfluff/rules/L057.py b/src/sqlfluff/rules/L057.py
new file mode 100644
index 00000000000..275a1b95ecd
--- /dev/null
+++ b/src/sqlfluff/rules/L057.py
@@ -0,0 +1,88 @@
+"""Implementation of Rule L057."""
+
+from typing import Optional
+
+from sqlfluff.core.rules.base import BaseRule, LintResult, RuleContext
+from sqlfluff.core.rules.doc_decorators import (
+    document_configuration,
+)
+from sqlfluff.rules.L014 import identifiers_policy_applicable
+
+
+@document_configuration
+class Rule_L057(BaseRule):
+    """Do not use special characters in identifiers.
+
+    | **Anti-pattern**
+    | Using special characters within identifiers when creating or aliasing objects.
+
+    .. code-block:: sql
+
+        CREATE TABLE DBO.ColumnNames
+        (
+            [Internal Space] INT,
+            [Greater>Than] INT,
+            [Less<Than] INT,
+            Number# INT
+        )
+
+    | **Best practice**
+    | Identifiers should include only alphanumerics and underscores.
+
+    .. code-block:: sql
+
+        CREATE TABLE DBO.ColumnNames
+        (
+            [Internal_Space] INT,
+            [GreaterThan] INT,
+            [LessThan] INT,
+            NumberVal INT
+        )
+
+    """
+
+    config_keywords = [
+        "quoted_identifiers_policy",
+        "unquoted_identifiers_policy",
+        "allow_space_in_identifier",
+    ]
+
+    def _eval(self, context: RuleContext) -> Optional[LintResult]:
+        """Do not use special characters in object names."""
+        if (
+            (
+                context.segment.name == "naked_identifier"
+                and identifiers_policy_applicable(
+                    self.unquoted_identifiers_policy, context.parent_stack  # type: ignore
+                )
+                and not (
+                    context.segment.raw.replace("_", "").isalnum()
+                    or (
+                        self.allow_space_in_identifier  # type: ignore
+                        and context.segment.raw.replace("_", "")
+                        .replace(" ", "")
+                        .isalnum()
+                    )
+                )
+            )
+        ) or (
+            (
+                context.segment.name == "quoted_identifier"
+                and identifiers_policy_applicable(
+                    self.quoted_identifiers_policy, context.parent_stack  # type: ignore
+                )
+                and not (
+                    context.segment.raw[1:-1].replace("_", "").isalnum()
+                    or (
+                        self.allow_space_in_identifier  # type: ignore
+                        and context.segment.raw[1:-1]
+                        .replace("_", "")
+                        .replace(" ", "")
+                        .isalnum()
+                    )
+                )
+            )
+        ):
+            return LintResult(anchor=context.segment)
+        else:
+            return None
diff --git a/test/fixtures/rules/std_rule_cases/L057.yml b/test/fixtures/rules/std_rule_cases/L057.yml
new file mode 100644
index 00000000000..32104cf287c
--- /dev/null
+++ b/test/fixtures/rules/std_rule_cases/L057.yml
@@ -0,0 +1,81 @@
+rule: L057
+
+test_fail_special_chars_create_table_space:
+  fail_str:
+    CREATE TABLE DBO.ColumnNames
+    (
+        "Internal Space" INT
+    )
+
+test_fail_special_chars_create_table_gt:
+  fail_str:
+    CREATE TABLE DBO.ColumnNames
+    (
+        "Greater>Than" INT
+    )
+
+test_fail_special_chars_create_table_lt:
+  fail_str:
+    CREATE TABLE DBO.ColumnNames
+    (
+        "Less<Than" INT
+    )
+
+test_fail_special_chars_create_table_hash_unquoted:
+  fail_str:
+    CREATE TABLE DBO.ColumnNames
+    (
+        Number# INT
+    )
+  configs:
+    core:
+      dialect: tsql
+
+test_pass_special_chars_create_table_none:
+  pass_str:
+    CREATE TABLE DBO.ColumnNames
+    (
+        "No_Internal_Space" INT,
+        GreaterThan INT,
+        LessThan INT,
+        NumberVal INT
+    )
+
+test_pass_special_chars_create_table_none_quoted_tsql:
+  pass_str:
+    CREATE TABLE DBO.ColumnNames
+    (
+        [No_Internal_Space] INT,
+        [GreaterThan] INT,
+        [LessThan] INT,
+        NumberVal INT
+    )
+  configs:
+    core:
+      dialect: tsql
+
+
+test_pass_special_chars_create_table_space_allowed:
+  pass_str:
+    CREATE TABLE DBO.ColumnNames
+    (
+        "Internal Space" INT
+    )
+  configs:
+    rules:
+      L057:
+        allow_space_in_identifier: True
+
+test_fail_special_chars_alias:
+  fail_str:
+    SELECT a as "alias with space"
+    FROM DBO.ColumnNames
+
+test_pass_special_chars_reference:
+  pass_str:
+    SELECT "column with space" as a
+    FROM DBO.ColumnNames
+  configs:
+    rules:
+      L057:
+        quoted_identifiers_policy: aliases


Code review comments:
diff_hunk: @@ -0,0 +1,88 @@
+"""Implementation of Rule L057."""
+
+from typing import Optional
+
+from sqlfluff.core.rules.base import BaseRule, LintResult, RuleContext
+from sqlfluff.core.rules.doc_decorators import (
+    document_configuration,
+)
+from sqlfluff.rules.L014 import identifiers_policy_applicable
+
+
+@document_configuration
+class Rule_L057(BaseRule):
+    """Do not use special characters in identifiers.
+
+    | **Anti-pattern**
+    | Using special characters within identifiers when creating or aliasing objects.
+
+    .. code-block:: sql
+
+        CREATE TABLE DBO.ColumnNames
+        (
+            [Internal Space] INT,
+            [Greater>Than] INT,
+            [Less<Than] INT,
+            Number# INT
+        )
+
+    | **Best practice**
+    | Identifiers should include only alphanumerics and underscores.
+
+    .. code-block:: sql
+
+        CREATE TABLE DBO.ColumnNames
+        (
+            [Internal_Space] INT,
+            [GreaterThan] INT,
+            [LessThan] INT,
+            NumberVal INT
+        )
+
+    """
+
+    config_keywords = [
+        "quoted_identifiers_policy",
+        "unquoted_identifiers_policy",
+        "allow_space_in_identifier",
+    ]
+
+    def _eval(self, context: RuleContext) -> Optional[LintResult]:
+        """Do not use special characters in object names."""
+        if (
+            (
+                context.segment.name == "naked_identifier"
+                and identifiers_policy_applicable(
+                    self.unquoted_identifiers_policy, context.parent_stack  # type: ignore
+                )
+                and not (
+                    context.segment.raw.replace("_", "").isalnum()
+                    or (
+                        self.allow_space_in_identifier  # type: ignore
+                        and context.segment.raw.replace("_", "")
+                        .replace(" ", "")
+                        .isalnum()
+                    )
+                )
+            )
+        ) or (
+            (
+                context.segment.name == "quoted_identifier"
+                and identifiers_policy_applicable(
+                    self.quoted_identifiers_policy, context.parent_stack  # type: ignore
path: src/sqlfluff/rules/L057.py
text: @user1:
remove type ignore comment

@author:
@user2-git adjusted, thanks!

diff_hunk: @@ -0,0 +1,88 @@
+"""Implementation of Rule L057."""
+
+from typing import Optional
+
+from sqlfluff.core.rules.base import BaseRule, LintResult, RuleContext
+from sqlfluff.core.rules.doc_decorators import (
+    document_configuration,
+)
+from sqlfluff.rules.L014 import identifiers_policy_applicable
+
+
+@document_configuration
+class Rule_L057(BaseRule):
+    """Do not use special characters in identifiers.
+
+    | **Anti-pattern**
+    | Using special characters within identifiers when creating or aliasing objects.
+
+    .. code-block:: sql
+
+        CREATE TABLE DBO.ColumnNames
+        (
+            [Internal Space] INT,
+            [Greater>Than] INT,
+            [Less<Than] INT,
+            Number# INT
+        )
+
+    | **Best practice**
+    | Identifiers should include only alphanumerics and underscores.
+
+    .. code-block:: sql
+
+        CREATE TABLE DBO.ColumnNames
+        (
+            [Internal_Space] INT,
+            [GreaterThan] INT,
+            [LessThan] INT,
+            NumberVal INT
+        )
+
+    """
+
+    config_keywords = [
+        "quoted_identifiers_policy",
+        "unquoted_identifiers_policy",
+        "allow_space_in_identifier",
+    ]
+
+    def _eval(self, context: RuleContext) -> Optional[LintResult]:
+        """Do not use special characters in object names."""
+        if (
path: src/sqlfluff/rules/L057.py
text: @user1:
suggest a slight tweak to the structure for readability:
```python
# Check for non-alphanumeric characters in unquoted identifiers:
if <unquoted_identifer_conditions>:
    return LintResult(anchor=context.segment)

# Check for non-alphanumeric characters in quoted identifiers:
if <quoted_identifer_conditions>:
    return LintResult(anchor=context.segment)

return None
```

@author:
@user2-git adjusted, thanks!

diff_hunk: @@ -0,0 +1,88 @@
+"""Implementation of Rule L057."""
+
+from typing import Optional
+
+from sqlfluff.core.rules.base import BaseRule, LintResult, RuleContext
+from sqlfluff.core.rules.doc_decorators import (
+    document_configuration,
+)
+from sqlfluff.rules.L014 import identifiers_policy_applicable
+
+
+@document_configuration
+class Rule_L057(BaseRule):
+    """Do not use special characters in identifiers.
+
+    | **Anti-pattern**
+    | Using special characters within identifiers when creating or aliasing objects.
+
+    .. code-block:: sql
+
+        CREATE TABLE DBO.ColumnNames
+        (
+            [Internal Space] INT,
+            [Greater>Than] INT,
+            [Less<Than] INT,
+            Number# INT
+        )
+
+    | **Best practice**
+    | Identifiers should include only alphanumerics and underscores.
+
+    .. code-block:: sql
+
+        CREATE TABLE DBO.ColumnNames
+        (
+            [Internal_Space] INT,
+            [GreaterThan] INT,
+            [LessThan] INT,
+            NumberVal INT
+        )
+
+    """
+
+    config_keywords = [
+        "quoted_identifiers_policy",
+        "unquoted_identifiers_policy",
+        "allow_space_in_identifier",
+    ]
+
+    def _eval(self, context: RuleContext) -> Optional[LintResult]:
+        """Do not use special characters in object names."""
+        if (
+            (
+                context.segment.name == "naked_identifier"
+                and identifiers_policy_applicable(
+                    self.unquoted_identifiers_policy, context.parent_stack  # type: ignore
+                )
+                and not (
+                    context.segment.raw.replace("_", "").isalnum()
+                    or (
+                        self.allow_space_in_identifier  # type: ignore
+                        and context.segment.raw.replace("_", "")
+                        .replace(" ", "")
+                        .isalnum()
+                    )
+                )
+            )
+        ) or (
+            (
+                context.segment.name == "quoted_identifier"
+                and identifiers_policy_applicable(
+                    self.quoted_identifiers_policy, context.parent_stack  # type: ignore
+                )
+                and not (
+                    context.segment.raw[1:-1].replace("_", "").isalnum()
+                    or (
+                        self.allow_space_in_identifier  # type: ignore
+                        and context.segment.raw[1:-1]
+                        .replace("_", "")
+                        .replace(" ", "")
+                        .isalnum()
+                    )
+                )
+            )
+        ):
+            return LintResult(anchor=context.segment)
+        else:
+            return None
path: src/sqlfluff/rules/L057.py
text: @user1:
no need for `else`, just have final `return None` as a general catch for non-matched segment
```suggestion
        
        return None
```

@author:
@user2-git adjusted, thanks!

diff_hunk: @@ -0,0 +1,88 @@
+"""Implementation of Rule L057."""
+
+from typing import Optional
+
+from sqlfluff.core.rules.base import BaseRule, LintResult, RuleContext
+from sqlfluff.core.rules.doc_decorators import (
+    document_configuration,
+)
+from sqlfluff.rules.L014 import identifiers_policy_applicable
+
+
+@document_configuration
+class Rule_L057(BaseRule):
+    """Do not use special characters in identifiers.
+
+    | **Anti-pattern**
+    | Using special characters within identifiers when creating or aliasing objects.
+
+    .. code-block:: sql
+
+        CREATE TABLE DBO.ColumnNames
+        (
+            [Internal Space] INT,
+            [Greater>Than] INT,
+            [Less<Than] INT,
+            Number# INT
+        )
+
+    | **Best practice**
+    | Identifiers should include only alphanumerics and underscores.
+
+    .. code-block:: sql
+
+        CREATE TABLE DBO.ColumnNames
+        (
+            [Internal_Space] INT,
+            [GreaterThan] INT,
+            [LessThan] INT,
+            NumberVal INT
+        )
+
+    """
+
+    config_keywords = [
+        "quoted_identifiers_policy",
+        "unquoted_identifiers_policy",
+        "allow_space_in_identifier",
+    ]
+
+    def _eval(self, context: RuleContext) -> Optional[LintResult]:
+        """Do not use special characters in object names."""
+        if (
+            (
+                context.segment.name == "naked_identifier"
+                and identifiers_policy_applicable(
+                    self.unquoted_identifiers_policy, context.parent_stack  # type: ignore
path: src/sqlfluff/rules/L057.py
text: @user1:
remove type ignore comment. 

instead include a type hint at the top of this method:
```python
# Config type hints
self.quoted_identifiers_policy: str
self.unquoted_identifiers_policy: str
self.allow_space_in_identifier: bool
```

see L054 for reference

@author:
@user2-git adjusted, thanks!

diff_hunk: @@ -0,0 +1,88 @@
+"""Implementation of Rule L057."""
+
+from typing import Optional
+
+from sqlfluff.core.rules.base import BaseRule, LintResult, RuleContext
+from sqlfluff.core.rules.doc_decorators import (
+    document_configuration,
+)
+from sqlfluff.rules.L014 import identifiers_policy_applicable
+
+
+@document_configuration
+class Rule_L057(BaseRule):
+    """Do not use special characters in identifiers.
+
+    | **Anti-pattern**
+    | Using special characters within identifiers when creating or aliasing objects.
+
+    .. code-block:: sql
+
+        CREATE TABLE DBO.ColumnNames
+        (
+            [Internal Space] INT,
+            [Greater>Than] INT,
+            [Less<Than] INT,
+            Number# INT
+        )
+
+    | **Best practice**
+    | Identifiers should include only alphanumerics and underscores.
+
+    .. code-block:: sql
+
+        CREATE TABLE DBO.ColumnNames
+        (
+            [Internal_Space] INT,
+            [GreaterThan] INT,
+            [LessThan] INT,
+            NumberVal INT
+        )
+
+    """
+
+    config_keywords = [
+        "quoted_identifiers_policy",
+        "unquoted_identifiers_policy",
+        "allow_space_in_identifier",
+    ]
+
+    def _eval(self, context: RuleContext) -> Optional[LintResult]:
+        """Do not use special characters in object names."""
+        if (
+            (
+                context.segment.name == "naked_identifier"
+                and identifiers_policy_applicable(
+                    self.unquoted_identifiers_policy, context.parent_stack  # type: ignore
+                )
+                and not (
+                    context.segment.raw.replace("_", "").isalnum()
+                    or (
+                        self.allow_space_in_identifier  # type: ignore
path: src/sqlfluff/rules/L057.py
text: @user1:
remove type ignore comment

@author:
@user2-git adjusted, thanks!

diff_hunk: @@ -0,0 +1,88 @@
+"""Implementation of Rule L057."""
+
+from typing import Optional
+
+from sqlfluff.core.rules.base import BaseRule, LintResult, RuleContext
+from sqlfluff.core.rules.doc_decorators import (
+    document_configuration,
+)
+from sqlfluff.rules.L014 import identifiers_policy_applicable
+
+
+@document_configuration
+class Rule_L057(BaseRule):
+    """Do not use special characters in identifiers.
+
+    | **Anti-pattern**
+    | Using special characters within identifiers when creating or aliasing objects.
+
+    .. code-block:: sql
+
+        CREATE TABLE DBO.ColumnNames
+        (
+            [Internal Space] INT,
+            [Greater>Than] INT,
+            [Less<Than] INT,
+            Number# INT
+        )
+
+    | **Best practice**
+    | Identifiers should include only alphanumerics and underscores.
+
+    .. code-block:: sql
+
+        CREATE TABLE DBO.ColumnNames
+        (
+            [Internal_Space] INT,
+            [GreaterThan] INT,
+            [LessThan] INT,
+            NumberVal INT
+        )
+
+    """
+
+    config_keywords = [
+        "quoted_identifiers_policy",
+        "unquoted_identifiers_policy",
+        "allow_space_in_identifier",
+    ]
+
+    def _eval(self, context: RuleContext) -> Optional[LintResult]:
+        """Do not use special characters in object names."""
+        if (
+            (
+                context.segment.name == "naked_identifier"
+                and identifiers_policy_applicable(
+                    self.unquoted_identifiers_policy, context.parent_stack  # type: ignore
+                )
+                and not (
+                    context.segment.raw.replace("_", "").isalnum()
+                    or (
+                        self.allow_space_in_identifier  # type: ignore
+                        and context.segment.raw.replace("_", "")
+                        .replace(" ", "")
+                        .isalnum()
+                    )
+                )
+            )
+        ) or (
+            (
+                context.segment.name == "quoted_identifier"
+                and identifiers_policy_applicable(
+                    self.quoted_identifiers_policy, context.parent_stack  # type: ignore
+                )
+                and not (
+                    context.segment.raw[1:-1].replace("_", "").isalnum()
+                    or (
+                        self.allow_space_in_identifier  # type: ignore
path: src/sqlfluff/rules/L057.py
text: @user1:
remove type ignore comment

@author:
@user2-git adjusted, thanks!