There are two ways to submit ad hoc queries to SQL Server:
- a string containing the parameter values
- a string containing the parameters names and a SQLParameter array containing the values
Unparameterized SQL (passing values in ad hoc sql)
string firstName = "Bill";
string adhocSql = "SELECT *
FROM Employees
WHERE FirstName = '"+firstName+"'";
Why is this bad?
- Allows SQL injection attack.
- Bloats query plan cache (and therefore server RAM). SQL Server stores every variation of this query text (for each new value).
- Wastes server CPU on computing query plans for every variation.
For example, let’s say this query is used on a webpage to search employees by first name.
- User enters “Brian.” This query is passed to SQL Server:
SELECT * FROM Employees WHERE FirstName = 'Brian'SQL Server computes a query plan on that, stores it in the plan cache, and then executes the plan. - User enters “Sith.” This query is passed to SQL Server:
SELECT * FROM Employees WHERE FirstName = 'Sith'SQL Server computes a query plan on that, stores it in the plan cache, and then executes the plan. - User enters “Sisilia.” This query is passed to SQL Server:
SELECT * FROM Employees WHERE FirstName = 'Sisilia'SQL Server computes a query plan on that, stores it in the plan cache, and then executes the plan. - And so on…
Let’s say 1000 names are searched in one day. SQL Server would compute 1000 different plans and store them all in memory. That is wasteful for the server’s RAM and CPU. One query plan would suffice for all these variations.
Also consider SQL injection attack. Let’s say the user enters “Brian; DROP TABLE Employees; –”. You’d lose your Employees table. Not good!
Parameterized SQL (naming parameters in ad hoc sql and passing values in an array)
string firstName = "Bill";
string adhocSql = "SELECT *
FROM Employees
WHERE FirstName = @FirstName";
SQLParameter[] parameters = new [] { new SQLParameter("FirstName", firstName) };
Why is this good?
- Prevents SQL injection. Any malicious SQL is treated as a string value–not executable code.
- Conserves plan cache size (and server RAM). SQL Server stores this query only once. The query text stays exactly the same, regardless of the values passed in.
- Conserves server CPU. SQL Server computes the query plan the first time, and then skips to execution all other times.
For example, let’s say this query is used on a webpage to search employees by first name.
- User enters “Brian.” This query is passed to SQL Server:
SELECT * FROM Employees WHERE FirstName = @FirstNameSQL Server computes a query plan on that, stores it in the plan cache, and then executes the plan. - User enters “Sith.” This query is passed to SQL Server:
SELECT * FROM Employees WHERE FirstName = @FirstNameSQL Server finds the query plan in cache and executes it. - User enters “Sisilia.” This query is passed to SQL Server:
SELECT * FROM Employees WHERE FirstName = @FirstNameSQL Server finds the query plan in cache and executes it. - And so on…
Let’s say 1000 names are searched in one day. SQL Server would compute a plan on the first run and store it in memory. For the remaining 999 runs, it would grab the plan from memory and skip directly to execution. Thus server RAM and CPU are conserved.
The benefits are clear. Parameterized SQL queries conserve RAM and CPU and prevent SQL injection. So, parameterize your ad hoc SQL! 