Dapper LIKE query for MySql safe against Sql Injection?

Question

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?

string sql = "SELECT * from user_profile WHERE FirstName LIKE CONCAT("%",@name,"%");"
var result = connection.query<profile>(sql, new {name});

Answer 1

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.

Using a string concat here is the right choice. Alternatively you could use the SUBSTRING_INDEX function.

Answer 2

There isn't a problem with that code, but another approach is to perform the the concat at the caller, i.e.

const string sql = "SELECT * from user_profile WHERE FirstName LIKE @name;";
var result = connection.Query<Profile>(sql, new {name = "%"+name+"%"});