C# - 在表名上使用 OleDbParameter
我想保护我的应用程序免受 SQL 注入。我想在 SQL 查询中使用 OleDbParameter 作为表名 ({1})。
问题是它不起作用（FROM 中的错误或类似的东西）。我可以在{3}中传递OleDbParameter。 示例：
IDbCommand cmd = m_oConnection.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = String.Format("SELECT {0} FROM {1} WHERE {2}={3}",
"ParentID",
"?",
sWhere,
"?"
);
cmd.Parameters.Add(new OleDbParameter("@sTable", sTable));
cmd.Parameters.Add(new OleDbParameter("@id", id));
我能做什么？我是否被迫编写一个手动转义某些 SQL 字符的函数？如果可以的话哪里可以找到完美的功能呢？
谢谢
I want to protect my app from SQL injection. I want to use OleDbParameter in a SQL query for the table name ({1}).
The problem is that it doesn't work (error in FROM or something like that). I can pass the OleDbParameter in {3} thought.
Example:
IDbCommand cmd = m_oConnection.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = String.Format("SELECT {0} FROM {1} WHERE {2}={3}",
"ParentID",
"?",
sWhere,
"?"
);
cmd.Parameters.Add(new OleDbParameter("@sTable", sTable));
cmd.Parameters.Add(new OleDbParameter("@id", id));
What can I do? Am I forced to write a function which escapes some SQL characters by hand? If yes, where can I find a perfect function?
Thanks
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
所以你知道你不能参数化表名，但你可以这样做，
但是当且仅当 sTable 来自用户输入时，这才是危险的。如果您直接控制 sTable 则不必担心。
如果它确实来自用户输入，您将需要保护自己。最简单的方法是确保 sTable 是有效的表、附加表或查询名称。
为此，只需执行
即可获取 sTable 的有效值列表。
根据您的应用程序，您可能可以执行一次并缓存结果，这样您就不必每次调用此方法时都执行此操作。
So you know that you can't parameterize table names but you could do this
But this is dangerous if and only if sTable comes from user input. If you directly control the sTable you don't have to worry about it.
If it does indeed come from user input you'll need to protect yourself. The easiest way is to make sure that
sTableis a valid table, Attached table, or query nameTo do that just execute
to get the list of valid values for sTable.
Depending on your application you could probably execute it once and cache the results so you don't have do it every time you call this method.
您将需要使用动态 sql 来生成命令。
但是当您传递表名时，很容易在参数中嵌入 sql 字符串，但这并不好。
看：
http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson06.aspx
'任何放入参数中的内容都将被视为字段数据，而不是 SQL 语句的一部分'
You will need to use dynamic sql to generate your command.
but as you are passing in the table name it is easily possible to embed a sql string in the parameter though, this is not good.
see:
http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson06.aspx
'Anything placed into a parameter will be treated as field data, not part of the SQL statement'