在 .NET/SQL 中参数化表名? [英] Parameterise table name in .NET/SQL?

问题描述

正如主题所暗示的，我希望能够使用 .NET(实际上与哪种语言无关)和 SQL Server 将表名作为参数传递.

As the topic suggests I wish to be able to pass table names as parameters using .NET (doesn't matter which language really) and SQL Server.

我知道如何为值执行此操作，例如command.Parameters.AddWithValue("whatever",whatever) 在查询中使用 @whatever 来表示参数.问题是我希望能够使用查询的其他部分(例如列名和表名)来执行此操作.

I know how to do this for values, e.g. command.Parameters.AddWithValue("whatever", whatever) using @whatever in the query to denote the parameter. The thing is I am in a situation where I wish to be able to do this with other parts of the query such as column and table names.

这不是一个理想的情况，但这是我必须使用的情况，它不太容易发生 SQL 注入，因为只有使用代码的人才能设置这些表名，而不是最终用户.不过很乱.

This is not an ideal situation but it's one I have to use, it's not really prone to SQL injection as only someone using the code can set these table names and not the end-user. It is messy however.

那么，我问的可能吗?

为了清楚说明 SQL 注入，表名仅由源代码传入，具体取决于情况.是开发人员指定的.无论如何，开发人员都可以访问数据库层，所以我问的原因不是为了安全，而是为了让代码更简洁.

To make the point about SQL injection clear, the table names are only passed in by source code, depending on the situation. It is the developer who specifies this. The developer will have access to the database layer anyway, so the reason I am asking is not so much for security but just to make the code cleaner.

推荐答案

我不认为我在我见过的任何 SQL 方言中都见过这种功能，但这不是一个领域的专业知识.

I don't think I've ever seen this capability in any SQL dialect I've seen, but it's not an area of expertise.

我建议将字符限制为 AZ, az, 0-9, '.', '_' 和 ' ' - 然后使用适合数据库的任何适当括号(例如 [] 用于 SQL Server，我相信) 环绕整个事情.然后直接放到SQL里面就行了.

I would suggest restricting the characters to A-Z, a-z, 0-9, '.', '_' and ' ' - and then use whatever the appropriate bracketing is for the database (e.g. [] for SQL Server, I believe) to wrap round the whole thing. Then just place it directly in the SQL.

您所说的不是 SQL 注入风险是什么意思并不完全清楚 - 您的意思是这些名称将在源代码中并且仅在源代码中?如果是这样，我同意这会让事情变得更好.您甚至可能不需要自动进行括号括起来，如果您相信您的开发人员不是白痴(有意或无意).

It's not entirely clear what you meant about it not being a SQL injection risk - do you mean the names will be in source code and only in source code? If so, I agree that makes things better. You may not even need to do the bracketing automatically, if you trust your developers not to be cretins (deliberately or not).

这篇关于在 .NET/SQL 中参数化表名?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！