Tuesday, December 30, 2008

Parameterized SQL Query Over Ad Hoc SQL

I have been using Ad Hoc SQL query for very long time in various .Net projects, including ASP.Net. I realized the danger of SQL injection attacks, however, in most cases, I don't provide UI for client or hacker to enter any parks of SQL query. As most people mentioned that the reason of Ad Hoc SQL queries are used in most cases is based on the fact that most of examples codes are using Ad Hoc SQL queries.

Parameterized SQL query is better than Ad Hoc SQL query not only because it could prevent SQL injection attacks. It is better than Ad Hoc one in terms of performance. SQL server has ability to cache parameterized SQL query as stored procedure so that they could be compiled and executed in less time.

Based on the second reason, I have been convert most of SQL queries into parametrized SQL query. The conversion is actually very simple in most cases. I think this should be a rule for any applications with SQL related queies.