Hello,
I wanted to ask if anyone knows of a way to prevent injection in an SQL SERVER 2005. I mean, is there any way to do all the blocking in the server and not have to escape each special character one-by-one?
For example, in PHP I used mysql_escape_string and automatically the string was OK to send to the database... Is there something similar in SQL Server?
Thank youWell firstly make sure that the database user only has db_datareader and db_datawriter roles assigned (unless you allow them to change DB structure). This will already limit them to only be able to delete all your data (as opposed to also creating/deleting tables).
Best to create your own ASP function to perform escape routines and just send your query to that function the same way you would mysql_escape_string (which is deprecated BTW).Are you using .NET?
If you are you can use the SqlCommand object to create a query.
i.e.
SqlCommand cmd = new SqlCommand(connection);
cmd.CommandText = "Update myTable set somecol = @param1";
cmd.Parameters.Add(new SqlParameter("@param1", value));
cmd.ExecuteNonQuery();
This way, the call to the DB (which you can monitor using SQL Profiler) is completely safe.that is not completely true, because it can occur as a script make sure that no HTML is allowed. This is done by default in .net but you may turn it off.Like Kram said above, I'd parameterize the query...However, the quick and dirty solution is:
(This is VB...Not sure of the keyword in C#)
strSQL = Replace("'", "''")
(This replaces every single quote with a double quote, thus escaping it)String.Replace("","")that is not completely true, because it can occur as a script make sure that no HTML is allowed. This is done by default in .net but you may turn it off.
Yes, but like you said, this type of attack is prevented by default, so the injection attack cannot be made if you use the parameterized approach.
I think its just a good philosophy to always parse user input, as the saying goes, "assume every submission as an attack, until proven otherwise". We use common classes all the time to parse user input through regular expressions. This technique works quite well, and it also helps with user requirements definitions (data type definitions,etc...)the assumation is that they are using .net at all. the statement still stands because of that.
I wanted to ask if anyone knows of a way to prevent injection in an SQL SERVER 2005. I mean, is there any way to do all the blocking in the server and not have to escape each special character one-by-one?
For example, in PHP I used mysql_escape_string and automatically the string was OK to send to the database... Is there something similar in SQL Server?
Thank youWell firstly make sure that the database user only has db_datareader and db_datawriter roles assigned (unless you allow them to change DB structure). This will already limit them to only be able to delete all your data (as opposed to also creating/deleting tables).
Best to create your own ASP function to perform escape routines and just send your query to that function the same way you would mysql_escape_string (which is deprecated BTW).Are you using .NET?
If you are you can use the SqlCommand object to create a query.
i.e.
SqlCommand cmd = new SqlCommand(connection);
cmd.CommandText = "Update myTable set somecol = @param1";
cmd.Parameters.Add(new SqlParameter("@param1", value));
cmd.ExecuteNonQuery();
This way, the call to the DB (which you can monitor using SQL Profiler) is completely safe.that is not completely true, because it can occur as a script make sure that no HTML is allowed. This is done by default in .net but you may turn it off.Like Kram said above, I'd parameterize the query...However, the quick and dirty solution is:
(This is VB...Not sure of the keyword in C#)
strSQL = Replace("'", "''")
(This replaces every single quote with a double quote, thus escaping it)String.Replace("","")that is not completely true, because it can occur as a script make sure that no HTML is allowed. This is done by default in .net but you may turn it off.
Yes, but like you said, this type of attack is prevented by default, so the injection attack cannot be made if you use the parameterized approach.
I think its just a good philosophy to always parse user input, as the saying goes, "assume every submission as an attack, until proven otherwise". We use common classes all the time to parse user input through regular expressions. This technique works quite well, and it also helps with user requirements definitions (data type definitions,etc...)the assumation is that they are using .net at all. the statement still stands because of that.