i want to find a function in .NET framework to calls SQL-Server's library for escaping really a value for sending to sql server, as mysql-real-escape-string() calls MySQL's library to do in PHP.
please propose a method that i can call it and it return escaped string via one round-trip to database, no need fo executing It's query
Is there it?
1 Answer
Why do you want to do that? The correct way to send user input to the database is not to escape it, but to use query parameters.
using(var command = new SqlCommand("insert into MyTable(X, Y) values(@x, @y)", connection))
{
command.Parameters.Add("@x", textBoxX.Text);
command.Parameters.Add("@y", textBoxY.Text);
command.ExecuteNonQuery();
}
This provides better performance, because the query text is always the same so the query execution plan can be cached. This also protects you against SQL injection attacks. And it also allows you to ignore data formatting issues (e.g. how is a DateTime formatted in SQL-Server? How should you represent a number in SQL? and so on)
4 Comments
ahoo
yes, i thought about it well. but parameter way has a problem: parameters count is fixed, but i want to dynamically create sql with count of parameters is dependent on user input values.
Thomas Levesque
@ahoo, the parameter count isn't fixed, nothing prevents you from adding parameters dynamically in a loop... Of course in that case you don't get the performance benefit, but it's still better than hard-coding the values in the SQL query.
ahoo
thanks @Thomas, so it is just like "Prepared Statement" in PHP (Using Functions: mysqli_stmt_prepare(),mysqli_stmt_bind_param()). is it right?
Thomas Levesque
@ahoo, yes, it's roughly the same. BTW, you can explicitly
Prepare() a command in .NET too if you intend to reuse it multiple times with different parameter values
mysql_real_escape_string() calls MySQL's library function mysql_real_escape_stringpart in the documentation. It obviously refers to the client library.