删除App后，相似的AppID权限异常，并无法恢复

[xyangcm]

使用mysql数据库时，当与删除appid中包含下划线'_'时，导致相似的app权限异常

在mysql中like查询时，下划线代表任意一个字符

当删除 hello_world 后，访问 helloaworld 时，相关权限提示异常，重新授权无法恢复

发现版本：1.4.1
当前master版本中代码仍在使用like的方式查找并删除

[dosubot]

针对你遇到的问题，由于MySQL中的LIKE查询将下划线（_）视为任意单个字符的通配符，这就导致了权限异常的问题。为了解决这个问题，你需要在查询中转义下划线。在MySQL中，可以通过在下划线前加上反斜杠（\）来转义特殊字符。例如：

SELECT * FROM YourTable WHERE YourColumn LIKE 'your\_pattern' ESCAPE '\';

如果你的应用程序动态构建查询，并且需要转义用户输入，那么在将输入字符串包含到查询中之前，你需要在程序中将每个下划线（_）替换为（\\_）。

以Python为例，可以这样做：

user_input = "example_app_id"  # 假设这是需要包含在LIKE模式中的输入
escaped_input = user_input.replace("_", "\\_")
query = f"SELECT * FROM App WHERE AppId LIKE '{escaped_input}' ESCAPE '\\';"

关于删除后的AppID权限异常并无法恢复的问题，你提到的Apollo仓库代码中并没有处理恢复已删除AppID的逻辑。删除操作通过更新IsDeleted标志为true来标记appId为已删除。要恢复，需要一个将IsDeleted重新设置为false的方法，这在提供的代码中是不存在的。

希望这能帮到你！如果还有其他问题，随时提问。

Details

Sources

• scripts/sql/profiles/mysql-database-not-specified/apolloconfigdb.sql
• apollo-portal/src/main/java/com/ctrip/framework/apollo/portal/repository/AppRepository.java
• apollo-adminservice/src/test/java/com/ctrip/framework/apollo/adminservice/controller/AppControllerTest.java
• apollo-common/src/main/java/com/ctrip/framework/apollo/common/utils/InputValidator.java

About Dosu
This response is meant to be useful and save you time. It is not meant to be a precise solution, but rather a starting point for your own research.

Help me be more useful! Please leave a 👍 if this is helpful and 👎 if it is irrelevant.

To continue the conversation, mention @dosu.

[xyangcm]

当我从Apollo管理页面中删除AppID为hello_world 的应用后，因为下换线的原因，helloaworld 这个应用的权限出现了异常，重新给helloaworld出现异常的人员再次授权，仍无法使用

[dosubot]

To address the issue with AppID permissions after deleting an app with an underscore in its ID, and the subsequent problem with similar AppIDs like hello_world and helloaworld, you'll need to ensure that underscores are correctly handled in your SQL queries. This is crucial because MySQL treats underscores as wildcards in LIKE queries.

First, when performing queries that involve AppIDs with underscores, make sure to escape the underscore. This prevents MySQL from interpreting it as a wildcard. For example, to search for an AppID hello_world, your query should escape the underscore like this:

SELECT * FROM App WHERE AppId LIKE 'hello\\_world';

This ensures that MySQL searches for the exact AppID, including the underscore, rather than treating the underscore as a wildcard.

If you're dynamically generating these queries based on user input, you'll need to replace underscores in the input with \\_ before including them in the query. Here's how you could do it in Python:

user_input = "hello_world"  # The AppID to search for
escaped_input = user_input.replace("_", "\\_")
query = f"SELECT * FROM App WHERE AppId LIKE '{escaped_input}' ESCAPE '\\';"

Regarding the issue of permissions not being restored correctly for helloaworld after hello_world has been deleted, it's important to directly address the permission mappings in your database. Ensure that any operation that deletes or modifies AppIDs also correctly updates the permissions associated with those AppIDs. This might involve more complex SQL operations to identify and correct mismatches in permissions caused by the deletion or modification of AppIDs.

Remember, the key to resolving these issues is careful handling of special characters like underscores in SQL queries and ensuring that your application's logic correctly manages the relationships between AppIDs and their associated permissions.

Details

Sources

• scripts/sql/profiles/mysql-default/apolloconfigdb.sql
• scripts/sql/src/apolloportaldb.sql
• apollo-portal/src/main/java/com/ctrip/framework/apollo/portal/controller/PermissionController.java
• apollo-portal/src/main/resources/static/scripts/controller/role/SystemRoleController.js
• apollo-portal/src/main/resources/static/scripts/services/PermissionService.js
• apollo-portal/src/main/java/com/ctrip/framework/apollo/portal/component/PermissionValidator.java
• apollo-common/src/main/java/com/ctrip/framework/apollo/common/dto/AppDTO.java
• apollo-common/src/main/java/com/ctrip/framework/apollo/common/utils/InputValidator.java
• apollo-portal/src/main/java/com/ctrip/framework/apollo/portal/service/AppService.java

About Dosu
This response is meant to be useful and save you time. It is not meant to be a precise solution, but rather a starting point for your own research.

Help me be more useful! Please leave a 👍 if this is helpful and 👎 if it is irrelevant.

To continue the conversation, mention @dosu.

[nobodyiam]

当我从Apollo管理页面中删除AppID为hello_world 的应用后，因为下换线的原因，helloaworld 这个应用的权限出现了异常，重新给helloaworld出现异常的人员再次授权，仍无法使用

可以尝试手动恢复一下被删除的数据，如

select * from Role where RoleName in ('ModifyNamespace+helloaworld+application', 'ReleaseNamespace+helloaworld+application');
select * from Permission where TargetId = 'helloaworld+application';