Is this query safe against sql injection in combination with Dapper? If not, what would be the correct way to write it under MySql? Or is there a better version without using concat?
这个查询是否可以安全地防止与Dapper结合使用sql注入？如果没有，在MySql下编写它的正确方法是什么？或者没有使用concat有更好的版本？
string sql = "SELECT * from user_profile WHERE FirstName LIKE CONCAT("%",@name,"%");"
var result = connection.query<profile>(sql, new {name});
2 个解决方案
#1
4
This is safe because you are not building SQL dynamically at all. Name is just a normal parameter. Actually, it has nothing to do with Dapper.
这是安全的，因为您根本没有动态构建SQL。名称只是一个普通参数。实际上，它与Dapper无关。
Using a string concat here is the right choice. Alternatively you could use the SUBSTRING_INDEX function.
在这里使用字符串连接是正确的选择。或者，您可以使用SUBSTRING_INDEX函数。
#2
15
There isn't a problem with that code, but another approach is to perform the the concat at the caller, i.e.
该代码没有问题，但另一种方法是在调用者处执行concat，即
const string sql = "SELECT * from user_profile WHERE FirstName LIKE @name;";
var result = connection.Query<Profile>(sql, new {name = "%"+name+"%"});
#1
4
This is safe because you are not building SQL dynamically at all. Name is just a normal parameter. Actually, it has nothing to do with Dapper.
这是安全的，因为您根本没有动态构建SQL。名称只是一个普通参数。实际上，它与Dapper无关。
Using a string concat here is the right choice. Alternatively you could use the SUBSTRING_INDEX function.
在这里使用字符串连接是正确的选择。或者，您可以使用SUBSTRING_INDEX函数。
#2
15
There isn't a problem with that code, but another approach is to perform the the concat at the caller, i.e.
该代码没有问题，但另一种方法是在调用者处执行concat，即
const string sql = "SELECT * from user_profile WHERE FirstName LIKE @name;";
var result = connection.Query<Profile>(sql, new {name = "%"+name+"%"});