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 you
The fundamental problem is that you're giving the database engine a long string of text (the sql command) that is by design trying to adhere to Microsoft's approximation of SQL92 standard. I think the best way, without deviating from best practice is to pass your request parameters to a loop that automatically sanitizes all your user data. You could write a single routine for this that you use at the top of any script that is processing user input. This is a snap in PHP, sufficiently straightforward in other environments. If you wanted to establish a filter that all your sql goes through if you want to parse it yourself to look for bad input, which again I don't recommend, you could setup a before insert & before update table trigger to watch for unexpected input if you wanted something on the database layer. You could also abstract all your database calls and you could write your filter code in the application layer and run your queries through it prior to sending to the db. Basically, you'd be just looking for things like 1 or 2 semi-colons in the string (1 if queries aren't usually semi-colon terminated in the query). You could also process the string sequentially, ensuring you see a comma between quotation encapsulated values.
What language are you using to build this data and sent it to MSSql? Are you still using PHP to connect to the MSSql server? If so their are tons of functions/libraries for doing this, including the new filter functions in PHP 5. (filter_input, etc). www.php.net/manual/en/ref.filter.php Tutorial on using the built in filter functions: http://phpro.org/tutorials/Filtering-Data-with-PHP.html Their is also a great sanitize function library by OWASP: www.owasp.org/index.php/OWASP_PHP_Filters Tutorial at PHP Builder on using the OWASP Filters: www.phpbuilder.com/columns/ryan_mcgeehan20060627.php3 For more information on data validation, see the following section at OWASP: www.owasp.org/index.php/Data_Validation These are practices you should use regardless of DB, I never trust mysql_escape_string to do everything I need, sure it escapes data, but that's not the only thing you should be worried about. As far as what to use with MSSql, that all depends on what you're using to connect to it. This really is a language specific issue. You can use the tools I listed about with any database really, from MySql, to Postgres or MSSql. So if you're sticking with PHP any of the above will help. If you're using something else like ASP.Net, etc, look for specific information on that language.