为什么串联SQL字符串是一个坏主意？ [英] Why is concatenating SQL strings a bad idea?
问题描述
我已阅读，这是一个坏主意来连接SQL字符串，像这样：
I have read that it's a bad idea to concatenate SQL strings like so:
cmd.CommandText = "Insert INTO workers Values (" + User.Identity.Name + "," + WorkerName.Text + "," + GetUniqueWorkerKey() + ");";
所以推荐的方式是：
So the recommended way was:
cmd.CommandText = "Insert INTO workers Values (@Username, @WorkerName, @WorkerKey)";
cmd.Parameters.AddWithValue("@Username", User.Identity.Name);
cmd.Paramters.AddWithValue("@WorkerName", TheWorkerNameYouPassedToThisMethod);
我一直在回避串联SQL字符串自从我读到它，但我从来没有真正知道的理由背后不这样做。会不会在 AddWithValue（）方法最终做幕后相同字符串连接？
I have been avoiding concatenating SQL strings ever since I read about it, but I never really know the rationale behind not doing so. Wouldn't the AddWithValue() method eventually do the same string concatenation behind the scene?
也许这方法剥去特殊字符和将字符转换为HTML实体，以防止SQL注入，但串联我的SQL之前，我可以做到这一切，我也得到同样的效果太，不行吗？还是有其他原因没练字符串连接的SQL后
Maybe that method strips off special characters and convert characters to html entities to prevent sql injection, but I can do all these before concatenating my SQL and I get the same effect too, can't I? Or are there other reasons for not practising string concatenation for SQLs?
推荐答案
答案很简单：通过连接字符串构建查询通常允许SQL注入
Short answer: building queries by concatenating strings usually allows SQL injection.
想象一下，有人试图创建一个名为鲍勃，乔，12345）的用户; DROP TABLE工作者; - 。你最终构建查询插入工人值（鲍勃，乔，12345）; DROP TABLE工作者; --name，34345236）; 再见数据库。 SQL注入也可能导致查询返回的数据，他们不应该，例如密码表。你有SQL注入任何时候，只是假设你允许任意第三方发给你的数据库任意命令。
Imagine that someone tries to create a user with the name "Bob, Joe, 12345); DROP TABLE workers; --". You end up building the query "Insert INTO workers Values(Bob, Joe, 12345); DROP TABLE workers; --name, 34345236);" Bye-bye database. SQL injection can also lead to queries returning data that they shouldn't, such as password tables. Any time that you have SQL injection, just assume that you're allowing arbitrary third parties to issue arbitrary commands to your database.
AddWithValue（ ）方法被称为参数化查询。它导致生成非常相似的命令，但AddWithValue（）需要像在参数值空格和引号任何怪异的东西小心，可能会导致你的命令的意思是不是你希望它是什么意思以外的东西。当然，你的可能的做手工逃脱，但它可能会非常棘手得到正确的。它更容易，更安全，让库为您处理它。
The AddWithValue() approach is called "parametrized queries". It results in very similar commands being generated, but AddWithValue() takes care of any weird stuff like spaces and quotes in the parameter values that could cause your command to mean something other than what you want it to mean. Sure, you could do that escaping manually, but it can be tricky to get correct. It's much easier and safer to let the library handle it for you.
这篇关于为什么串联SQL字符串是一个坏主意？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
